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
