Hi Paul, Thanks for your feedback. Interesting, I'll have to try running this code on my side. The RETURNING clause translates to a JDBC call to getGeneratedKeys(), which does some things behind the scenes inside of the Oracle JDBC driver. Perhaps, that usage is incompatible when referencing sequences in the VALUES clause - or there might have been some missing support for this usage in 11g. In order for me to try to reproduce this, would you mind telling me:
- The ojdbc version you're using - The server version you're using Indeed, when you work on different schemas, turning off the rendering of schemata can help. Alternatively, you can prevent the generation of the schema entirely by specifying <outputSchemaToDefault/> in the code generator. For more info about multitenancy and schema / table mapping, please refer to the following sections of the manual: - https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-render-mapping - https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-catalog-and-schema-mapping Thanks, Lukas 2017-08-30 15:51 GMT+02:00 Paul Hamer <[email protected]>: > Hi Lucas, > > Thanks for your quick reply. > > The issue should not be permission related, as the 2nd piece of code that > I quoted works perfectly. Also, in SQL Developer I'm indeed using the same > user. > However, I did give your suggestion a try (setting renderSchema to false), > and it indeed fixes the issue! A bit strange, but great! Can you explain it > maybe? > > As a bonus, this also solved the inconvenience that I ran in to every time > a wanted to connect to a different DB with a different name (ie. MPS1 > instead of MPS)... it would require regenerating all the jOOQ code. With > renderSchema on false, that is no longer needed. Nice! > > Thanks a lot & keep up the awesome work! > Paul > > > On Wednesday, August 30, 2017 at 10:57:40 AM UTC+2, Paul Hamer wrote: >> >> Hi, >> >> We're using jOOQ Pro 3.9.5 and recently we migrated from MySQL to Oracle >> 11g, which somehow broke my insertInto()...returning() statements. >> >> Here's my code: >> >> Record r = dsl.insertInto(MPS_PEOPLE) >> .set(MPS_PEOPLE.PPLE_ID, Sequences.MPS_PPLE_SEQ.nextval >> ()) <-- only this line was added (in MySQL this used to be an auto-increment >> column) >> .set(MPS_PEOPLE.OPERATIONAL_INITIALS, atco.getInitials()) >> .set(MPS_PEOPLE.FIRST_NAME, atco.getFirstName()) >> .set(MPS_PEOPLE.SURNAME, atco.getSurName()) >> .set(MPS_PEOPLE.EMAIL_ADDRESS, atco.getEmail()) >> .set(MPS_PEOPLE.GENDER, "M") >> .set(MPS_PEOPLE.BIRTH_DATE, new Date(0)) >> .set(MPS_PEOPLE.START_DATE, new Date(0)) >> .set(MPS_PEOPLE.REPORTING_MANAGER, "N") >> .set(MPS_PEOPLE.COUNTERSIGNING_MANAGER, "N") >> .set(MPS_PEOPLE.AUTOMATIC_INPUT, "N") >> .set(MPS_PEOPLE.CREATED_BY, "1") >> .set(MPS_PEOPLE.CREATED_DATE, new Date(System. >> currentTimeMillis())) >> .set(MPS_PEOPLE.MODIFIED_BY, "1") >> .set(MPS_PEOPLE.MODIFIED_DATE, new Date(System. >> currentTimeMillis())) >> .returning(MPS_PEOPLE.PPLE_ID) >> .fetchOne(); >> int id = r.getValue(MPS_PEOPLE.PPLE_ID).intValue(); >> >> This returns the following error: >> >> org.jooq.exception.DataAccessException: SQL [insert into "MPS". >> "MPS_PEOPLE" ("PPLE_ID", "OPERATIONAL_INITIALS", "FIRST_NAME", "SURNAME", >> "EMAIL_ADDRESS", "GENDER", "BIRTH_DATE", "START_DATE", >> "REPORTING_MANAGER", "COUNTERSIGNING_MANAGER", "AUTOMATIC_INPUT", >> "CREATED_BY", "CREATED_DATE", "MODIFIED_BY", "MODIFIED_DATE") values ( >> "MPS"."MPS_PPLE_SEQ".nextval, ?, ?, ?, ?, ?, cast(? as date), cast(? as >> date), ?, ?, ?, ?, cast(? as date), ?, cast(? as date))]; ORA-04043: >> object "MPS" does not exist >> >> However, running that query manually (of course with actual values in >> place of the '?') using Oracle SQL Developer works fine. >> >> Restructuring the code to the following works fine, but it requires an >> additional roundtrip to the database to get the generated id... >> >> int id = dsl.select(Sequences.MPS_PPLE_SEQ.nextval()).fetchOne().value1 >> ().intValue(); >> int count = dsl.insertInto(MPS_PEOPLE) >> .set(MPS_PEOPLE.PPLE_ID, (long) id) >> .set(MPS_PEOPLE.OPERATIONAL_INITIALS, atco.getInitials()) >> .set(MPS_PEOPLE.FIRST_NAME, atco.getFirstName()) >> .set(MPS_PEOPLE.SURNAME, atco.getSurName()) >> .set(MPS_PEOPLE.EMAIL_ADDRESS, atco.getEmail()) >> .set(MPS_PEOPLE.GENDER, "M") >> .set(MPS_PEOPLE.BIRTH_DATE, new Date(0)) >> .set(MPS_PEOPLE.START_DATE, new Date(0)) >> .set(MPS_PEOPLE.REPORTING_MANAGER, "N") >> .set(MPS_PEOPLE.COUNTERSIGNING_MANAGER, "N") >> .set(MPS_PEOPLE.AUTOMATIC_INPUT, "N") >> .set(MPS_PEOPLE.CREATED_BY, "1") >> .set(MPS_PEOPLE.CREATED_DATE, new Date(System. >> currentTimeMillis())) >> .set(MPS_PEOPLE.MODIFIED_BY, "1") >> .set(MPS_PEOPLE.MODIFIED_DATE, new Date(System. >> currentTimeMillis())) >> .execute(); >> >> Any idea what I'm doing wrong? >> >> Kind regards, >> Paul Hamer >> > -- > 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.
