Oops... keep calling you Lucas, sorry Lukas ;-) On Wednesday, August 30, 2017 at 4:13:35 PM UTC+2, Paul Hamer wrote: > > Hi Lucas, > > Sure no problem: > ojdbc: Oracle ojdbc8 v12.2.0.1 > server: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit > Production > > Hope this helps. Thanks for the pointers on working with different > schemas, much appreciated. > > Regards, > Paul > > On Wednesday, August 30, 2017 at 4:05:09 PM UTC+2, Lukas Eder wrote: >> >> 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.
