Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (athena.apache.org: 83.137.146.145 is neither permitted
 nor denied by domain of stephan@republika.nl)
Message-ID: <47C055A6.3010303@republika.nl>
MIME-Version: 1.0
Date: Sat, 23 Feb 2008 18:19:34 +0100
From: "Stephan van Loendersloot (LIST)" <stephan@republika.nl>
Organization: REPUBLiKA B.V.
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
To: derby-dev@db.apache.org
Subject: dblook fails on TERRITORY_BASED databases
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello everyone,

At our firm we use Derby extensively in our projects and sometimes I 
need to recreate or duplicate parts of our databases, for which I 
occasionally use dblook.

However, dblook seems to fail on territory-based databases.

We use 10.3.2.1 on both Windows and Linux for development and production 
on various JVM's and the problem seems unrelated to any of these variables.

I also tried this with a 10.4 (trunk) build, which produces the same error.

I've created small patches for myself by replacing all related queries 
in the 'tools' section with CASTs to CHARs and VARCHARs and would like 
to contribute these to the community in case anyone else can confirm 
this is a bug.

A small test case to reproduce the problem is provided below, the 
version of Derby that provides the stacktrace is 10.3.2.1.


Regards,


Stephan van Loendersloot.


Reproduction steps:

---------- 1: create_territory_db.sql  ----------

CONNECT 
'jdbc:derby://localhost/dutch;user=dutch;password=dutch;create=true;territory=nl_NL;collation=TERRITORY_BASED';

AUTOCOMMIT OFF;

CREATE TABLE AIRLINES
  (
     AIRLINE CHAR(2) NOT NULL ,
     AIRLINE_FULL VARCHAR(24),
     BASIC_RATE DOUBLE PRECISION,
     DISTANCE_DISCOUNT DOUBLE PRECISION,
     BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
     FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
     ECONOMY_SEATS INTEGER,
     BUSINESS_SEATS INTEGER,
     FIRSTCLASS_SEATS INTEGER
  );

COMMIT;


DISCONNECT;
EXIT;

---------- 2: use dbloook ----------

dblook -d "jdbc:derby://localhost/dutch;user=dutch;password=dutch" -o 
dutch.sql

---------- 3: stacktrace ----------

java.sql.SQLSyntaxErrorException: Comparisons between 'CHAR (UCS_BASIC)' 
and 'CHAR (TERRITORY_BASED)' 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.executeQuery(Unknown Source)
   at org.apache.derby.tools.dblook.prepForDump(Unknown Source)
   at org.apache.derby.tools.dblook.go(Unknown Source)
   at org.apache.derby.tools.dblook.<init>(Unknown Source)
   at org.apache.derby.tools.dblook.main(Unknown Source)
Caused by: org.apache.derby.client.am.SqlException: Comparisons between 
'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' 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.executeQueryX(Unknown Source)
   ... 5 more
-- **--> DEBUG: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR 
(TERRITORY_BASED)' 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')