Multiple statements, one database roundtrip

[friso.v...@gmail.com]

Hi,

I was wondering if it is possible to send two statements and have one database roundtrip. More specifically: 

Given (MySQL):

CREATE TABLE t ( 

id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

    name VARCHAR(10) NOT NULL

);

I want to send this to the database:

INSERT INTO t VALUES (NULL, 'Bob');SELECT * FROM t WHERE id=LAST_INSERT_ID();

And get the result in one roundtrip. I tried with insert...returning...fetchOne, but it looks like that is taking two trips (not really sure, but looks like it).

Any clues?

Groeten,

Friso

[Lukas Eder]

Hi Friso,

MySQL, unfortunately, currently doesn't implement a SQL statement that allows for fetching the last inserted ID in one go (like, e.g. Firebird, PostgreSQL, Oracle, DB2, and others). One option how you could work around this would be to create those exact two statements you've mentioned and "batch" them using the plain SQL API, calling ResultQuery.fetchMany() - in case of which you'd get all the results. Along the lines of this:

DSL.using(configuration)

   .resultQuery("{0}; {1};", insertQuery, selectQuery) // Construct queries with jOOQ

   .fetchMany();

There is currently no way to create such a statement batch without resorting to plain SQL.

I hope this helps,

Lukas

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[friso.v...@gmail.com]

On Monday, December 4, 2017 at 10:40:47 AM UTC+1, Lukas Eder wrote:

I hope this helps,

Lukas

Sure does, thanks for the prompt reply!

Groeten,

Friso