Using onDuplicateKeyUpdate() on HSQLDB

[Andrew Leung]

Hi,

I am using jOOQ 3.11.2 on Java 1.8.0_51.

I am using embedded HSQLDB and trying to use the onDuplicateKeyUpdate() feature but receive a java.lang.IllegalStateException: The ON DUPLICATE KEY IGNORE/UPDATE clause cannot be emulated when inserting into non-updatable tables. I am expecting this feature to work on HSQLDB so was looking for some insight into what might be causing it. The tenant_id field is configured to be the primary key and as far as I know the table should be updatable.

The code block is:

dslContext
.insertInto(tenant_capacity,
        tenant_capacity.tenant_id,
        tenant_capacity.serialized_tenant_pending_capacity)
.values(tenantId,
        GrpcTenantModelConverters.toGrpcCapacity(capacity).toByteArray())
.onDuplicateKeyUpdate()
.set(tenant_capacity.serialized_tenant_pending_capacity, GrpcTenantModelConverters.toGrpcCapacity(capacity).toByteArray())
.execute();

During execution the jOOQ toMerge() step checks the following block (https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/InsertQueryImpl.java#L744) to decide whether to throw the exception:

if ((onConflict != null && onConflict.size() > 0) || onConstraint != null || !table().getKeys().isEmpty())

When executing onConflict and onConstraint are null and table is of type AbstractTable, which seems to always be empty (https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/AbstractTable.java#L487).https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/AbstractTable.java#L487). The InsertQueryImpl also has onDuplicateKeyUpdate set to true.

Any insights would be appreciated! Thanks in advance.

Andrew

[Knut Wannheden]

Hi Andrew,

Thank you for your request.

HSQLDB doesn't natively support the ON DUPLICATE KEY UPDATE syntax, so jOOQ attempts to emulate it using a MERGE statement. But for the MERGE statement to work correctly, the table must have a unique or primary key known to jOOQ. "Known to jOOQ" would typically mean that the jOOQ code generator was run against the target database and generated a TableImpl subclass overriding the getKeys() method. It would for instance not work if the table object was created using DSL#table(String). So in your case, what does "tenant_capacity" refer to?

Please also note that HSQLDB can actually itself "emulate" the ON DUPLICATE KEY UPDATE syntax, but that requires enabling the MySQL compatibility mode and jOOQ cannot reasonably support all these compatibility modes, which is why it only generates SQL in HSQLDB's native syntax.

Hope this helps,

Knut

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/ae4434a9-e472-454a-948d-a32706638ae2%40googlegroups.com.

[Andrew Leung]

Thanks for the quick response, Knut.

The tenant_capacity table is being auto-generated from jOOQ XML with tenant_id as VARCHAR PRIMARY_KEY.

The dialect I'm using is SQLDialect.HSQLDB. Is there something else needed to enable "MySQL" compatibility mode?

Thanks again.

Andrew

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Knut Wannheden]

Hi Andrew,

The Java class which "tenant_capacity" is an instance of should override the getKeys() method to return the primary key. Can you verify this? If not, is possibly your generated Java code out of date with respect to your database? 

Further, I am unsure what you mean by "is being auto-generated from jOOQ XML". Don't you run the jOOQ code generator against your HSQLDB database?

Knut

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a44bbf62-ee3b-4445-94ea-43d32bcab38c%40googlegroups.com.

[Lukas Eder]

Hi Andrew,

In case you're using the XMLDatabase, this is the XSD that you're using:

https://www.jooq.org/xsd/jooq-meta-3.11.0.xsd 

In order to tell jOOQ about your primary keys, you'll need to provide the TableConstraint and KeyColumnUsage types. You can generate an example file from an actual database connection using the XMLGenerator.

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAFx%3DKge_HAOn2YH6JYzTxfrF-LX_c-6yRZ9qesyrDoRyywAHvg%40mail.gmail.com.

[Andrew Leung]

Hi Knut & Lukas,

Sorry for the confusion. The jOOQ schema is defined in XML and I am running the generated jOOQ code against the HSQLDB database. I do have a primary key defined with both the table_constraint and key_column_usage constraints. Every other operation I've done (e.g., INSERT, SELECT, etc...) have all worked fine. So far onDuplicateKeyUpdate() is the only hiccup I've encountered.

The generated tenant_capacity class extends TableImpl. Using a debugger I can see the getKeys() method that gets used is the AbstractTable implementation (https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/AbstractTable.java#L486) which always returns an empty list, thus failing the last condition of the toMerge() check. So it appears it is not overriding the default.

I'm using embedded HSQLDB 2.4.1, in case that's useful to the "generated Java code out of date with respect to your database" question.

Thanks again for the help and I'm happy to provide whatever other info might be useful.

Andrew

On Tuesday, August 20, 2019 at 11:20:24 AM UTC-5, Lukas Eder wrote:

Hi Andrew,

In case you're using the XMLDatabase, this is the XSD that you're using:

https://www.jooq.org/xsd/jooq-meta-3.11.0.xsd 

In order to tell jOOQ about your primary keys, you'll need to provide the TableConstraint and KeyColumnUsage types. You can generate an example file from an actual database connection using the XMLGenerator.

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a44bbf62-ee3b-4445-94ea-43d32bcab38c%40googlegroups.com.

--
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...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAFx%3DKge_HAOn2YH6JYzTxfrF-LX_c-6yRZ9qesyrDoRyywAHvg%40mail.gmail.com.

[Lukas Eder]

Hi Andrew,

Thanks for providing that additional information. Some additional troubleshooting hints: Table names and column names are case sensitive in the XMLDatabase, including in the table_constraint and key_column_usage elements. Qualification of all tables and columns is necessary, including the schema (but the catalog is not necessary). Specifically, you have to specify the key_column_usage.constraint_schema and a key_column_usage.constraint_name.

If that doesn't help yet, an MCVE (minimal, complete, verifiable example) could be quite useful. We have a handy template on github, which you could fork and adapt to help reproduce the issue: https://github.com/jOOQ/jOOQ-mcve

Thanks,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6af0bb1d-ec26-4c31-b606-d33f0a773381%40googlegroups.com.

[Andrew Leung]

Thanks for the additional details, Lukas. I've mostly worked around the issue by adjusting the workflow so the key should always exist prior to this operation and a simple UPDATE is sufficient. I'll try and follow up with more details when I have some time to put a reproducible example together.

Andrew

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6af0bb1d-ec26-4c31-b606-d33f0a773381%40googlegroups.com.