OK, no then it's not related. I was perhaps a bit too quick with that assumption.
I'll try to reproduce this issue and get back to you with additional findings. Perhaps it is related to the RETURNING clause emulation in jOOQ... 2015-12-11 16:12 GMT+01:00 Ben Hood <[email protected]>: > OK, so I renamed the table to X_SUB but I still get the same behavior: > > SQL [insert into "SUBSCRIPTIONS"."X_SUB" ("TENANT", "UPSTREAM", > "START_DATE", "END_DATE") select ?, ?, ?, ? from > "SUBSCRIPTIONS"."X_SUB" where not exists (select ? from > "SUBSCRIPTIONS"."X_SUB" where ("SUBSCRIPTIONS"."X_SUB"."TENANT" = ? > and "SUBSCRIPTIONS"."X_SUB"."UPSTREAM" = ? and > "SUBSCRIPTIONS"."X_SUB"."START_DATE" = ?))]; ORA-00933: SQL command > not properly ended > > Should I maybe get rid of the SUB completely, i.e. just call the table > X for example? > > Or are there potentially some JDBC settings that I should be setting > when starting a transaction? > > > On Fri, Dec 11, 2015 at 2:55 PM, Lukas Eder <[email protected]> wrote: > > Yes - It's weird, I know, but it just rang a bell... > > > > 2015-12-11 15:51 GMT+01:00 Ben Hood <[email protected]>: > >> > >> Hey Lukas, > >> > >> If I understand you correctly, you're suggesting to rename the table > >> and re-execute the code to see if the behavior is consistent when > >> using a different table name? If so, I'll do that and report back. > >> > >> Ben > >> > >> On Fri, Dec 11, 2015 at 2:46 PM, Lukas Eder <[email protected]> > wrote: > >> > Hi Ben, > >> > > >> > Oracle SQL doesn't really support the RETURNING clause. It exists in > >> > PL/SQL, > >> > but it cannot be used in SQL directly. However, the ojdbc driver > >> > supports > >> > Statement.getGeneratedKeys(), which is what jOOQ calls behind the > >> > scenes. > >> > > >> > I don't really see any syntax problem. However, I've run into similar > >> > issues > >> > in the past when the schema name is the same as the table name - > >> > although > >> > only with packages, not with tables. Just to be sure, does this > problem > >> > also > >> > appear when the table is named differently? > >> > > >> > Cheers, > >> > Lukas > >> > > >> > 2015-12-11 15:38 GMT+01:00 Ben Hood <[email protected]>: > >> >> > >> >> 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. > >> > > >> > > >> > -- > >> > 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. > >> > >> -- > >> 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. > > > > > > -- > > 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. > > -- > 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. > -- 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.
