Hi Paul, Thank you very much for your enquiry. This is very strange. The error message seems to indicate that the schema "MPS" has not been found. Does the user you're connecting to the database with have access to the "MPS" schema? I.e. did you use the same user on your JDBC connection as you did in SQL Developer?
To verify if this is really the problem, you could specify Settings.renderSchema = false, which would prevent jOOQ from generating the schema prefix on all tables. Best Regards, Lukas 2017-08-30 10:57 GMT+02:00 Paul Hamer <[email protected]>: > 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.
