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.
