Response
Niels Gron over 9 years ago
You can right mouse click on the error node to view the details.
If you enable Help->SQL Log, you can then see the queries executed with you expand a node and whether they fail.
Aqua Data Studio 13.0 is bundled with the Apache Derby 10.9 jars. You may need to update these jars to the 10.10 jars for compatibility.
Ron Stewart over 9 years ago
@Niels: Thanks for the follow-up. Right clicking on the error node shows the following:
java.sql.SQLSyntaxErrorException: Comparisons between ‘CHAR (UCS_BASIC)’ and ‘CHAR (TERRITORY_BASED:PRIMARY)’ are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = ‘T1’)
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Statement.execute(Unknown Source)
at \\…\\ .\\हिñçêČάй語简�?한\\.t⠬ꇻꄧ᠅.execute(Unknown Source)
at com.common.ui.tree.Pꋀꇻꈾꂞ.dw(Unknown Source)
at com.common.ui.tree.Wꃛꉧꄛꄄ package else.process(Unknown Source)
at com.common.ui.util.BackgroundThread.run(Unknown Source)
Caused by: org.apache.derby.client.am.SqlException: Comparisons between ‘CHAR (UCS_BASIC)’ and ‘CHAR (TERRITORY_BASED:PRIMARY)’ are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = ‘T1’)
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeX(Unknown Source)
… 5 more
This appears to be related to collation. When I recreated the database after updating to Derby 10.10.1.1, I (intentionally) specified “collation=TERRITORY_BASED:PRIMARY” because I wanted case-insensitive collation… something I didn’t have previously and want for what I am currently working on.
I dropped the database and recreated it without specifying the collation parameter on the connection string. According to the docs for this at db.apache.org/…/rrefattribcollation.html the default is UCS_BASIC. When created with the default collation in this manner, ADS has no issues listing tables… but I’m back to case-sensitive collation.
I have updated to ADS 13.0.2, but am still running ADS with the Derby driver jars distributed with ADS (presumably the 10.9 jars, given your comment). Based on what I see in the Derby docs, the collation parameters for the Derby JDBC driver have not changed since 10.6.
Based on this, I’m not sure that replacing the Derby driver jars (derby.jar, derbyclient.jar, derbynet.jar) distributed with ADS with their 10.10 versions will fix this. If you still believe that to be the case, I’m willing to at least give it a shot.
If there is more I can provide on this from my side, please let me know.
—
/ron
Ivan Gron over 9 years ago
The collation attribute applies only to user-defined tables. The system tables use the Unicode codepoint collation. When you create a connection to the database the current_schema will be that of the user used. This user schema will be in the collation used to create the database yet the system tables are in Unicode no matter what. It seems that when you send the sql to the server it uses the collation of the current_schema and those all the system tables are failing to compare when collation is mismatching.
quick solution to get datastudio functional for user: go to the server properties -> Script Tab and place the following:
SET SCHEMA SYS
This will set the current_schema to sys and the tree nodes will execute queries under the system collation when extracting catalog information. When you open a query analyzer you will need to set the schema to any non system schema for your queries run under the collation you used when creating the database.
I am researching to see what type of code changes we could make to data studio to attempt to handle this mismatch of collations between system schemas and user based schemas when the collations are created differently.
Ron Stewart over 9 years ago
Thanks, Ivan.
That definitely gets me much closer to being able to go forward…
There is definitely lots going on with the collation and how it is handled by Derby, by ADS, or both.
For the sake of illustration, let’s assume I use user “u” on the initial JDBC connection string in the Derby “ij” utility to create the empty database with the desired collation attribute. I then use that same user “u” and the corresponding password in ADS to connect to the server and database. Let’s further assume I create a table “t” with several columns including a column “vc” of type varchar(32).
In ADS, I connect to the server and open a query analyzer. I issue the following select statement:
select * from t where vc like ‘Inj%’
I get the following error:
Table/View ‘T’ does not exist.
I issue the following select statement (note the use of the schema designator with the table name):
select * from u.t where vc like ‘Inj%’
I get the following error:
Operand of LIKE predicate with type VARCHAR(32) and collation TERRITORY_BASED:PRIMARY is not compatable with LIKE pattern operand with type CHAR(4) and collation UCS_BASIC.
I issue the following two statements:
use schema u
select * from t where vc like ‘Inj%’
… and it works. One thing to note: even before issuing the “use schema u” command, the query analyzer window clearly shows that the expected database, schema “U” and user “u” are in use. But the above sequence would lead me to believe that it is not really using schema “U” in spite of what the query analyzer window shows.
As a point of comparison, if I connect to the database as this same user using Derby’s “ij” utility — as shown in the attached screenshot — I don’t see this same behavior (at least from the standpoint that I am clearly using the user’s default schema from the outset, as expected, and that there’s nothing odd going on with the LIKE operator).
I’m trying to convince myself that all of this makes sense… with the possible exception of ADS showing that it using the user’s schema even though it is not.
—
/ron
Ivan Gron over 9 years ago
I noticed that the query analyzer was not supporting schemas correctly. I will add support to extract and display the correct schema. also will add support for a drop down list. This will make it easier for a user to see and change the schema that he is using in the query analyzer. This will not fix the issue of having different collations between different schema but should make it so that if you change to schema the table is in, that a query would use the correct collation. Trying to query a table in a different schema that has a different collation can still give errors. I am still trying to figure out a work around for this.
Sachin Prakash over 9 years ago
with the possible exception of ADS showing that it using the user’s schema even though it is not.
This is a bug in ADS and Ivan is working on a fix for it. Should have a fix out for this either today or tomorrow.
Jonathan Powers over 9 years ago
Hi Ron
We have a fix for this issue.
Download v13 patch : http://dd1.aquafold.com/download/v13.0.0/ads-13.0.2-2-patch.zip
Patch Update Instructions : http://www.aquafold.com/support-update.html#v13
Regards
Ron Stewart over 9 years ago
@Ivan et al: Thanks much for the quick fix to this. Works much better. The only wrinkle we probably need to make sure people are aware of is the need to ensure the schema is set to “SYS” in the server properties script if the database was created with anything other than the default collation property, perhaps on the “Registering a Database Server > Apache Derby > Server Scripts” page in the docs?
As always, you and your support are absolutely phenomenal!
Jonathan Powers over 9 years ago
Hello Ron,
We’ve updated our documentation for v13 to indicate what you’ve suggested:
https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation13/page/214/Apache-Derby
notice the last link at the top of the page, “Connecting to databases created with non-default collation properties”