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 > > On Tue, Aug 20, 2019 at 4:42 PM Knut Wannheden <knut.w...@gmail.com > <javascript:>> wrote: > >> 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 <anwl...@gmail.com >> <javascript:>> 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...@googlegroups.com <javascript:>. >>> 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...@googlegroups.com <javascript:>. >> 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 >> >> <https://groups.google.com/d/msgid/jooq-user/CAFx%3DKge_HAOn2YH6JYzTxfrF-LX_c-6yRZ9qesyrDoRyywAHvg%40mail.gmail.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/6af0bb1d-ec26-4c31-b606-d33f0a773381%40googlegroups.com.
