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.
