Sorry, I forgot to include the relevant DDL: CREATE TABLE subscriptions ( id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, tenant NUMBER(19) NOT NULL, upstream NUMBER(19) NOT NULL, start_date TIMESTAMP NOT NULL, end_date TIMESTAMP, period FOR subscription (start_date, end_date) );
On Fri, Dec 11, 2015 at 2:35 PM, Ben Hood <[email protected]> wrote: > Hi Lukas, > > I'm trying to an UPSERT without using the MERGE syntax because I would > like to get the generated key back from the DB. > > However, this results with the error: ORA-00933: SQL command not properly > ended > > This is what the DSL looks like: > > Optional<SubscriptionsRecord> record = > ctx.insertInto(SUBSCRIPTIONS, SUBSCRIPTIONS.TENANT, > SUBSCRIPTIONS.UPSTREAM,SUBSCRIPTIONS.START_DATE, > SUBSCRIPTIONS.END_DATE). > select( > ctx.select(DSL.val(bigTenant), DSL.val(bigUpstream), > DSL.val(lowerBound), DSL.val(upperBound)). > from(SUBSCRIPTIONS). > whereNotExists( > ctx.select(DSL.val(1)). > from(SUBSCRIPTIONS). > where(uniqueKeyCondition) > )). > returning(SUBSCRIPTIONS.ID). > fetchOptional(); > > And this is the SQL that is generated: > > SQL [insert into "SUBSCRIPTIONS"."SUBSCRIPTIONS" ("TENANT", > "UPSTREAM", "START_DATE", "END_DATE") select ?, ?, ?, ? from > "SUBSCRIPTIONS"."SUBSCRIPTIONS" where not exists (select ? from > "SUBSCRIPTIONS"."SUBSCRIPTIONS" where > ("SUBSCRIPTIONS"."SUBSCRIPTIONS"."TENANT" = ? and > "SUBSCRIPTIONS"."SUBSCRIPTIONS"."UPSTREAM" = ? and > "SUBSCRIPTIONS"."SUBSCRIPTIONS"."START_DATE" = ?))]; ORA-00933: SQL > command not properly ended > > It looks like the RETURNING INTO is not properly rendered. > > Am I somehow misusing the fluent DSL vis a vis RETURNING? > > Cheers, > > Ben -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
