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 <anwle...@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-user+unsubscr...@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/CAFx%3DKgdeALGY6b%3Ds4-oJpmmzPLHOxHV2wOzuP2hjm0O0V-o1mQ%40mail.gmail.com.
