Search code examples
firebirdjooqjaybird

JOOQ generates CHAR OCTETS columns as CHAR instead of BINARY

My problem very close the one mentioned in Using UUID PK or FK in Firebird with Jooq

Setup: Jaybird 3.0.5, Firebird 2.5.7, jOOQ 3.11.7, JDK 1.8

My PK and FK fields like 

ID CHAR(16) CHARACTER SET OCTETS NOT NULL

and

TABLE_ID CHAR(16) CHARACTER SET OCTETS

and I want to use UUID as java data type in generated classes

I use JDBC connection in configuration like

<jdbc>
    <driver>org.firebirdsql.jdbc.FBDriver</driver>
    <url>jdbc:firebirdsql:localhost:c:/DBS/DB.FDB?octetsAsBytes=true</url>
    <properties>
        <property>
            <key>user</key>
            <value>SYSDBA</value>
        </property>
        <property>
            <key>password</key>
            <value>masterkey</value>
        </property>
    </properties>
</jdbc>

I have setup forcedType in generator like

<forcedType>
    <userType>java.util.UUID</userType>
    <binding>com.ekser.nakkash.icdv.converters.jooq.ByteArray2UUIDBinding</binding>
    <expression>.*ID$</expression>
    <types>CHAR\(16\)</types>
    <nullability>ALL</nullability>
</forcedType>

and I have class

class ByteArray2UUIDBinding implements Binding<byte[], UUID>

Now the problem

jOOQ generates

public final TableField<MyTableRecord, UUID> ID = createField("ID", org.jooq.impl.SQLDataType.CHAR(16).nullable(false), this, "", new ByteArray2UUIDBinding());

problem is SQLDataType.CHAR(16), it should be SQLDataType.BINARY(16).

jOOQ translate my char(16) octets fields as string (char(16)), it does not respect octetsAsBytes=true.

I have tried to put it to properties in <jdbc> like

<jdbc>
    <driver>org.firebirdsql.jdbc.FBDriver</driver>
    <url>jdbc:firebirdsql:localhost:c:/DBS/DB.FDB</url>
    <properties>
        <property>
            <key>user</key>
            <value>SYSDBA</value>
        </property>
        <property>
            <key>password</key>
            <value>masterkey</value>
        </property>
        <property>
            <key>octetsAsBytes</key>
            <value>true</value>
        </property>
    </properties>
</jdbc>

With the same result.

What is wrong? I am considering running search&replace for keyword CHAR(16) -> BINARY(16) in generated classes for now, which is not 'stylish'.

Solution

  • The setting octetsAsBytes does nothing in Jaybird 3, see Character set OCTETS handled as JDBC (VAR)BINARY in the Jaybird 3 release notes. Jaybird 3 always behaves as octetsAsBytes=true in previous versions with some further improvements.

    In other words, this is not related to that setting at all, but is instead a result of how jOOQ generates this.

    jOOQ does its own metadata introspection by directly querying the Firebird metadata tables and mapping the Firebird type codes to jOOQ SQL types (see FirebirdTableDefinition and FirebirdDatabase.FIELD_TYPE). It directly maps Firebird type '15' to CHAR, without further considering subtypes (== character sets for this type).

    In other words, you'll need to file an improvement ticket with jOOQ if you want to get this mapped to BINARY instead (although it's unclear to me why this is really a problem for you).