JOOQ & Firebird - Implementation Limit Exceeded

Question

Background: I'm using jOOQ to access a Firebird Database. Firebird 2.x has a row size limit of 64KB. I never hit the limit before, but this particular database uses UTF8 which means the limit shrunk to around 16K characters.

This is how I'm using jOOQ:

1. Load or Create a POJO (generated) as needed. e.g.:

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

2. Use the book object as needed.

3. Store it back as a record if user saves changes.

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

Step 3 fails on the executeUpdate() method because somehow jOOQ is casting all the empty VARCHAR fields to VARCHAR(4000). The error message is

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

This is a known Firebird limitation and nothing can be done about it unfortunately.

In the table I have around 8 empty VARCHAR(512) fields, which is supposed to be around 8x4x512 (or 16KB) max in UTF8, but since jOOQ's interpreting it as VARCHAR(4000), that's 8x4x4000 (128KB) which is obviously over the limit.

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

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

Answer 1

These casts are historic in jOOQ as some databases are having a hard time inferring a data type from a bind variable alone as they cannot delay any decisions until the query gets executed:

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

This is why jOOQ generates explicit casts for those databases, which includes Firebird:

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

This is currently being done even in statements / clauses, 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, it is unfortunate that this practice is running into the mentioned size limitations rather quickly. There are some pending feature requests to improve this in jOOQ, which aren't yet implemented in jOOQ 3.9:

• #1735 Add Setting to indicate that bind values should not be cast
• #3398 Avoid generating new SQL statements for every bind variable length in Firebird

Workaround

If you want to patch jOOQ on your side, the cast decision is made in DefaultBinding.sql(BindingSQLContext). You will see there a couple of options how to override the current behaviour:

1. Turn off casting with Setting.paramCastMode == NEVER (available from jOOQ 3.10 with #1735)
2. Override RenderContext.castMode() to always yield NEVER
3. Override the entire method in your own custom Binding
4. Patch the logic to never apply any casts
5. Implement an ExecuteListener that replaces cast\(\?[^)]*\) by ? in your SQL strings