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 On Tue, Aug 20, 2019 at 3:38 PM Andrew Leung <anwle...@gmail.com> wrote: > 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 > > On Tuesday, August 20, 2019 at 2:06:51 AM UTC-5, Knut Wannheden wrote: >> >> 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 >> >> On Tue, Aug 20, 2019 at 6:12 AM Andrew Leung <anwl...@gmail.com> wrote: >> >>> 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 >>> >>> -- >>> 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/ae4434a9-e472-454a-948d-a32706638ae2%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/ae4434a9-e472-454a-948d-a32706638ae2%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > 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+unsubscr...@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 > <https://groups.google.com/d/msgid/jooq-user/a44bbf62-ee3b-4445-94ea-43d32bcab38c%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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+unsubscr...@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.
