Hi Ben, I see, that's indeed an interesting use-case for https://github.com/jOOQ/jOOQ/issues/3715, although I would really like to fix that issue first (if it's even possible).
As far as this workaround technique is concerned, what you could do is throw an "exception" in ExecuteListener.bindEnd(), when the statement is ready for execution. You can then pass the PreparedStatement from the ExecuteListener's ExecuteContext with the Exception, and catch the exception, extracting the statement. It's ugly, but it is currently the best way to access the PreparedStatement (and abort query execution). Let me know if this helps (and if it works in the first place), and if you need any additional info. Cheers, Lukas 2015-12-13 23:23 GMT+01:00 Ben Hood <[email protected]>: > Hi Lukas, > > I take your point about the impedance mismatch between the DSLContext > and wanting to access a statement directly. I've been using JOOQ for a > number of years for many different use cases and I've never come > across a need to do so. > > That said, the use case I currently have is the following high level flow: > > 1. UPSERT a parent record > 2. INSERT 2 child records with the PK of parent (ignoring duplicate > keys for both) > > So I'm looking for a solution to a get the PK of the parent back from > the UPSERT, hence I was asking about getting a reference to the > PreparedStatement so that I can get the generated key back from the > statement, as is done in the JOOQ library itself. > > That then said, maybe I could implement the flow with a CTE or a proc > instead. But I was looking intially for a JOOQ-only solution. > > So the insert portion of the UPSERT looks like this in the JOOQ DSL: > > ctx.insertInto(SUBSCRIPTIONS, SUBSCRIPTIONS.TENANT, > SUBSCRIPTIONS.UPSTREAM, SUBSCRIPTIONS.START_DATE, > SUBSCRIPTIONS.END_DATE). > values(bigTenant, bigUpstream, lowerBound, upperBound). > select( > ctx.select(DSL.val(bigTenant), DSL.val(bigUpstream), > DSL.val(lowerBound), DSL.val(upperBound)). > from(SUBSCRIPTIONS). > whereNotExists( > ctx.select(DSL.val(1)). > from(SUBSCRIPTIONS). > where(uniqueKeyCondition) > )). > execute(); > > The is the same statement I was having an issue with the returning() > stanza in the related post about ORA-00933, but I started a new thread > because I thought it was a slightly different question and it would > provide a more separated discussion thread for people googling this in > the future. > > Cheers, > > Ben > > > On Sun, Dec 13, 2015 at 10:20 AM, Lukas Eder <[email protected]> wrote: > > Hi Ben, > > > > In my opinion, there is no point in accessing a PreparedStatement from a > > DSLContext. The DSLContext (= Configuration) lifecycle can match your > entire > > application's lifecycle, not that of individual transactions, let alone > > statements. > > > > The jOOQ type that corresponds to a JDBC Statement is the Query, and it > may > > indeed maintain a live reference to an open Statement, when > > Query.keepStatement == true. > > > > - https://github.com/jOOQ/jOOQ/issues/3715 is one way to use jOOQ (in > the > > future) to prepare statements, which can then be used outside of jOOQ, > e.g. > > for execution. > > - ExecuteListeners are another way to access statements during execution > > (Statement is available after the prepareEnd() event) > > > > Maybe, could you explain what your use-case is and what you're trying to > > achieve? Maybe we're not talking about the right thing (yet). > > > > Cheers, > > Lukas > > > > > > > > 2015-12-12 21:23 GMT+01:00 Ben Hood <[email protected]>: > >> > >> Might this question be related to > https://github.com/jOOQ/jOOQ/issues/3715 > >> ? > >> > >> On Sat, Dec 12, 2015 at 8:21 PM, Ben Hood <[email protected]> wrote: > >> > Hi Lukas, > >> > > >> > I'm wondering what the idiomatic way of accessing the > >> > PreparedStatement from a DSLContext is in JOOQ 3.7. I can see the > >> > BindContext interface offers > >> > > >> > PreparedStatement statement(); > >> > > >> > But I was wondering how you access this from the DSLContext. > >> > > >> > Cheers, > >> > > >> > Ben > >> > >> -- > >> 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. > > > > > > -- > > 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. > > -- > 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. > -- 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.
