JOOQ & Firebird - implementation limit exceeded

Question

Reference Information. I am using jOOQ to access the Firebird database. Firebird 2.x has a 64K line size limit. I had never encountered a limit before, but this particular database uses UTF8, which means a limit reduced to 16K characters.

This is how I use jOOQ:

• Download or create a POJO (generated) as needed. eg:

  Book book = context.fetchOne("select * from book where book_id = ?", 1).into(Book.class); 

• Use the book object as needed.

• Save it as a record if the user saves the changes.

   BookRecord rec = context.newRecord(Tables.BOOK, book); context.executeUpdate(rec); 

Step 3 fails with the executeUpdate() method, because somehow jOOQ distinguishes all empty VARCHAR to VARCHAR(4000) . Error message

"SQL error code = -204. Implementation limit exceeded. Block size exceeds implementation restriction."

This is a known limitation of Firebird, and unfortunately, nothing can be done to it.

In the table, I have about 8 empty VARCHAR(512) fields VARCHAR(512) , which should be about 8x4x512 (or 16 Kbytes) max in UTF8, but since jOOQ interprets it as VARCHAR(4000) , it is 8x4x4000 (128 KB), which obviously Limit.

If I set NULL or empty fields to some random string, then jOOQ will output it to the exact length of the string. ("ABC" will be added to varchar(3) )

So my question is: how do I get executeUpdate() to work without jOOQ by casting my null fields to VARCHAR(4000) ?

Answer 1

These drops are historical in jOOQ, as some databases have difficulty defining the data type from the binding variable alone, since they cannot delay decision making until the request is executed:

 SELECT ? -- What the type? Unknown at parse time. 

This is why jOOQ generates explicit casts for these databases, including Firebird:

 SELECT cast(? as varchar(4000)) -- Now, the type is clear 

This is currently being done even in statements / sentences where the type can be inferred from the context, including

 INSERT INTO t(a, b) VALUES (?, ?) -- ^ ^ | | -- +--|----------+ | -- +-------------+ Bind types can be inferred from column type 

In the case of Firebird, unfortunately, this practice quickly spreads to these size limits. There are several pending feature requests to improve this in jOOQ that are not yet implemented in jOOQ 3.9:

• # 1735 Add parameter to indicate that binding values ​​should not be added
• # 3398 Avoid creating new SQL statements for each variable binding length in Firebird

Bypass

If you want to install jOOQ on your side, the decision is made in DefaultBinding.sql(BindingSQLContext) . You will see several options for overriding the current behavior:

• Disable casting using Setting.paramCastMode == NEVER (available from jOOQ 3.10 s # 1735 )
• Override RenderContext.castMode() to always give NEVER
• Override the whole method in your own binding
• Correct the logic to never apply any casts
• Add an ExecuteListener that replaces cast\(\?[^)]*\) ExecuteListener ? in your SQL rows