Hi Lukas Thanks for your input. I think I also tried it using val() but wasn't successful, but your input that Oracle finally *does* support IDENTITY columns led to the decision to switch to them instead, rendering the problem here obsolete.
Thanks for your quick help! Matt On Monday, December 19, 2022 at 11:01:15 AM UTC+1 lukas...@gmail.com wrote: > Just in case: Oracle 12c and later also support IDENTITY columns... > > If you want to mix bind values with expressions, you just have to turn the > bind values into an expression using DSL.val(): > - https://stackoverflow.com/q/67522537/521799 > - https://www.jooq.org/doc/latest/manual/sql-building/bind-values/ > > On Mon, Dec 19, 2022 at 10:49 AM Matthias Keller <matthia...@ergon.ch> > wrote: > >> Hi Lukas >> Thanks for your quick reply and the hint with the fetching of multiple >> sequence values! >> Sorry for not explaining the 'map' funktion above - here sequence is >> just an Optional<Sequence<Long>> meaning that depending on the DB used, it >> is present (oracle) or absent (all other DBs supporting some kind of auto >> increment). >> >> Having two completely different inserts is what we do now, but since our >> real table has about 10 columns, this results in a lot of duplicated code, >> with just the difference, that in one case, we have a Row11 (Oracle, >> includes the PK with the sequence number) and in the other case a Row10 >> (all other DBs). What we would like to do it reduce this duplication. >> I've seen the default methods, but have been unable to include them in >> such an insert clause using valuesOfRows, as this method takes Rows with >> concrete values as far as I understand it; or is it possible to have a >> default_() value inside a valuesOfRows() ? If yes, how exactly? >> >> Thanks Matt >> >> >> On Monday, December 19, 2022 at 10:33:21 AM UTC+1 lukas...@gmail.com >> wrote: >> >>> Hi Matt, >>> >>> Thanks for your message. First, I'd like to recommend you don't run a >>> sequence fetching round trip per row. Otherwise, you're not really >>> profiting from your bulk insertion with that many fetches. jOOQ allows you >>> to fetch a set of sequence values in one go, see: >>> https://blog.jooq.org/how-to-fetch-sequence-values-with-jooq/ >>> >>> But you can always just put the seq.nextval() call in your INSERT .. >>> VALUES clause, as that allows for expressions to be used. >>> >>> If you want to trigger DEFAULT behaviour, then instead of putting an >>> explicit NULL value there, you should put an explicit DEFAULT expression in >>> that row. You can do so using jOOQ's DSL::default_ method. >>> >>> Of course, I don't know what sequence.map(...) does, and when it can >>> produce an Optional.empty(), so it might as well be that instead of >>> embedding this decision in your internal loop, you just have 2 completely >>> different INSERT statements, depending on that sequence.map() decision? >>> >>> I hope this helps, >>> Lukas >>> >>> On Mon, Dec 19, 2022 at 10:15 AM Matthias Keller <matthia...@ergon.ch> >>> wrote: >>> >>>> Hi >>>> We're currently struggling to insert a list of data into a table having >>>> an auto-generated ID (or using a sequence on Oracle). Because our code >>>> must >>>> be runnable both on Oracle and other DBs (for example H2, SQL Server, >>>> MariaDB), we cannot completely optimizie this query. >>>> The problem is the following, assuming we have to insert data in a >>>> table having a PK column (numerical, autogenerated on most DBs and using a >>>> sequence on oracle). >>>> >>>> For example: >>>> >>>> db.insertInto(TABLE).columns(PK, COL) >>>> .valuesOfRows( >>>> rows.stream().map(rows -> DLS.row( >>>> sequence.map(seq -> >>>> db.dsl().fetchValue(seq.nextval())).orElse(null), // <- does not work for >>>> example on H2 >>>> rows.col())) >>>> >>>> >>>> However, this does not work for example for H2, as inserting NULL in >>>> that explicit NON-NULL column doesn't seem to trigger the autogeneration >>>> of >>>> the value. >>>> >>>> Using .insertInto(TABLE).set(..).set(..).newRecord() would work, as we >>>> could conditionally add the .set. However, the different return types >>>> makes >>>> this very very hard to use in a loop, as newRecord has to be called >>>> rows.size()-1 times. >>>> >>>> Is there a better way to insert such dynamic data and adding or not >>>> adding the sequence as needed? >>>> Thanks Matt >>>> >>>> -- >>>> 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 jooq-user+...@googlegroups.com. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/jooq-user/8f0b2a7d-009e-4dab-9210-3308b55ef84dn%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/jooq-user/8f0b2a7d-009e-4dab-9210-3308b55ef84dn%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- >> 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 jooq-user+...@googlegroups.com. >> > To view this discussion on the web visit >> https://groups.google.com/d/msgid/jooq-user/5bb55c10-92df-4f48-bf8e-db7acdbf7ec7n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/jooq-user/5bb55c10-92df-4f48-bf8e-db7acdbf7ec7n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/03b5b9bc-cd17-4375-a6a2-4257c8b1411dn%40googlegroups.com.
