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]>: > > > 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 >
