Generating classes for a derby embedded database. Generator complains about Comparable types.

[swingg...@gmail.com]

I have a derby embedded database that was created with the following URL

jdbc:derby:skeleton;create=true;collation=TERRITORY_BASED:PRIMARY

The collation value is set to allow case-insensitive matching for queries that use the sql LIKE keyword.

Now I'm trying to generate jOOQ files for it, and I'm getting the following error message: 

Comparisons between 'VARCHAR (UCS_BASIC)' and 'VARCHAR (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')

Does anyone know how to get around this?

I should say that I removed the <inputSchema>mySchema</inputSchema> tag from the library file since it generated a different error that said this: No schemata were loaded  : Please check your connection settings, and whether your database (and your database version!) is really supported by jOOQ.

Here's my library.xml file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.10.0.xsd">
 <!-- Configure the database connection here -->
 <jdbc>
  <driver>org.apache.derby.jdbc.EmbeddedDriver</driver>
  <url>jdbc:derby:skeleton;create=true;collation=TERRITORY_BASED:PRIMARY</url>
  <!--user and password are presumably not needed since we're embedded.-->
  <!--<user>root</user>-->
  <!--<password></password>-->
 </jdbc>

 <generator>
  <name>org.jooq.util.JavaGenerator</name>

  <database>
   <name>org.jooq.util.derby.DerbyDatabase</name>
   
   <!--I tried it both with and without these properties, which, frankly were a guess. 
   It failed both ways with the same message.-->
   <properties>
    <property>
     <key>collation</key>
     <value>TERRITORY_BASED:PRIMARY</value>
    </property>
   </properties>

   <!-- The database schema (or in the absence of schema support, in your RDBMS this
      can be the owner, user, database name) to be generated -->
   <!--<inputSchema>skeleton</inputSchema>-->

   <includes>.*</includes>

   <excludes></excludes>
  </database>

  <target>
   <!-- The destination package of your generated classes (within the destination directory) -->
   <packageName>com.neptunedreams.skeleton.data2</packageName>

   <!-- The destination directory of your generated classes. Using Maven directory layout here -->
   <directory>/Users/miguelmunoz/Documents/Skeleton2/src/main/java</directory>
  </target>
 </generator>
</configuration>

[Lukas Eder]

Hello,

For future reference, this issue was also reported here on GitHub:

https://github.com/jOOQ/jOOQ/issues/6797

I'm generally wary of setting the collation to something non-default on a connection / database level. I've only ever found this feature useful on an individual column level. In principle, 'A' != 'a' for most purposes (e.g. comparing two hashes), and only in some very few cases, I'd like this sort of case insensitive behaviour to work automatically, and even then, I generally prefer a programmatic workaround (e.g. creating a function based index on UPPER(column) or some other normalisation function, if UPPER() doesn't behave correctly in all languages).

I'm not sure about a workaround here - have you considered asking this question on Stack Overflow (and perhaps reducing your API usage to JDBC only, to get more people to look into this)?

Thanks,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.