Thanks Lukas... for now I'll implement the down-and-dirty "try insert, catch exception, and if DataAccessException wraps a SQLIntegrityConstraintViolationException, select id" approach. Yucky but it works ok.
Regards, Raman On Wednesday, January 16, 2013 8:07:11 AM UTC-5, Lukas Eder wrote: > > Hello, > > That's what I feared. The "RETURNING" clause isn't very standardised > across databases and JDBC drivers. Specifically with the MERGE > statement involved, I'm not sure if this can be solved thoroughly. > > I hope to be able to have a closer look within the next three weeks, > as I'm currently not at home. > > Cheers > Lukas > > 2013/1/14 Raman Gupta <[email protected] <javascript:>>: > > > > > > On Saturday, January 12, 2013 3:27:30 AM UTC-5, Lukas Eder wrote: > >> > >> Hello, > >> > >> > I am using onDuplicateKeyIgnore. However, I also need the generated > >> > primary > >> > key of the inserted record, or the primary key of the existing > record, > >> > in > >> > subsequent logic. From the current API, is this possible? > >> > >> You're right, thanks for reporting this. This isn't possible from the > >> current API, which is an API design flaw. There's no reason why this > >> shouldn't be possible. I have registered #2123 for this: > >> https://github.com/jOOQ/jOOQ/issues/2123 > >> > >> In the mean time, you may have two options for a workaround: > >> > >> 1. Do not use the DSL API, but the "classic" API instead. > >> --------------------------------------------------------------- > >> The entry point for the INSERT "classic" API is here: > >> > >> > http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#insertQuery(org.jooq.Table) > > >> > >> It will return an InsertQuery, which has all the required setters that > you > >> need: > >> http://www.jooq.org/javadoc/latest/org/jooq/InsertQuery.html > >> > >> 2. Manually cast your DSL object to InsertReturningStep > >> --------------------------------------------------------------- > >> Internally, jOOQ implements the INSERT DSL with a single > >> implementation object: org.jooq.impl.InsertImpl. This class implements > >> all the DSL interfaces, hence you can cast any INSERT Step to > >> InsertReturningStep, in order to access the returning() method > >> > >> Note, there are currently no integration tests covering this > >> combination. Any feedback about whether it works is welcome. > >> > > > > I tried the second approach above. I guess it worked as far as the API > goes, > > but the implementation with Oracle failed as it is including the > generated > > ID (the sequence.nextval) in the "on" clause of the SQL merge (which > results > > in ORA-02287: sequence number not allowed here). Is there a way to tell > the > > "onDuplicateKeyIgnore" which columns should actually be used for the > "key"? > > > > I also tried to create the Oracle merge myself, but that API also does > not > > appear to allow use of the "returning" clause. > > > > Regards, > > Raman > > >
