JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys

[Andreas Leitgeb]

I have to write code to execute runtime-specified SQL-statements,
and there are two things that I wasn't able to understand from reading
the javadocs:

1.)
When would one call .getMoreResults() on a statement instead of just
another rs.next() on the first one (obtained from stmnt.getResultSet())?
Are these equivalent, or is there really a concept of multiple ResultSets,
each of which has its own independent set of rows?

2.)
As I don't know the statement beforehand, I can't decide at coding
time, if it might be an "insert" eventually returning a serial key,
and thusly requiring the argument RETURN_GENERATED_KEYS), or a select
that will need cursor-like treatment such as passing resultSetType,
resultSetConcurrency and resultSetHoldability flags.

How would I tell the JDBC engine, that if it is a select then it
should pass certain flags, and if it is an insert, then I would be
interested in the generated keys? prepareStatement() doesn't seem to
have an overload to accept both variants.
Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?

Seasonal Greetings, and thanks in advance

 

[Arne Vajhøj]

I have to write code to execute runtime-specified SQL-statements,
and there are two things that I wasn't able to understand from reading
the javadocs:

1.)
When would one call .getMoreResults() on a statement instead of just
another rs.next() on the first one (obtained from stmnt.getResultSet())?
Are these equivalent, or is there really a concept of multiple ResultSets,
each of which has its own independent set of rows?

Yes.

Some databases support stored procedures returning multiple
result sets.

It is relative common in the MS SQLServer and Sybase ASE world.

Ultra simple example:

CallableStatement cstmt = con.prepareCall("{CALL test()}");
cstmt.execute();
ResultSet rs = cstmt.getResultSet();
while(rs.next()) {
System.out.println(rs.getInt(1));
}
rs.close();
cstmt.getMoreResults();
rs = cstmt.getResultSet();
while(rs.next()) {
System.out.println(rs.getInt(1));
}
rs.close();

getMoreResults() returns a boolean whether there are more result set,
but typical you will know how many result sets there will be.

Note that the different result sets does not need to have same
number of columns or same column types.

Arne

 

[Arne Vajhøj]

I have to write code to execute runtime-specified SQL-statements,
and there are two things that I wasn't able to understand from reading
the javadocs:

2.)
As I don't know the statement beforehand, I can't decide at coding
time, if it might be an "insert" eventually returning a serial key,
and thusly requiring the argument RETURN_GENERATED_KEYS), or a select
that will need cursor-like treatment such as passing resultSetType,
resultSetConcurrency and resultSetHoldability flags.

How would I tell the JDBC engine, that if it is a select then it
should pass certain flags, and if it is an insert, then I would be
interested in the generated keys? prepareStatement() doesn't seem to
have an overload to accept both variants.

True - you would need to know whether it is a SELECT or an INSERT.

Just like you need to know whether to call executeQuery or
executeUpdate.

Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?

In most implementations it result in another round trip to
the server with a SELECT.

That is a significant overhead for something that is rarely
needed.

Arne

 

[Robert Klemme]

Yes.

Some databases support stored procedures returning multiple
result sets.

That feature is not limited to stored procedures. You can have multiple
statements executed with a single Statement. Consequently you get
multiple results.

Note that the different result sets does not need to have same
number of columns or same column types.

Exactly.

Kind regards

robert

 

[Andreas Leitgeb]

True - you would need to know whether it is a SELECT or an INSERT.
Just like you need to know whether to call executeQuery or
executeUpdate.

I do not need to decide between these two, as there is also a plain
execute() method, that will work for either kind - except for the
problem at hand...

In most implementations it result in another round trip to
the server with a SELECT.
That is a significant overhead for something that is rarely
needed.

Ok, that sounds reasonable.

Thanks.

PS: Thanks also to the others who replied about multiple ResultSets.

 

[Robert Klemme]

True - you would need to know whether it is a SELECT or an INSERT.

An UPDATE also can generate keys. And a DELETE can also return data via
a RETURNING clause.

Just like you need to know whether to call executeQuery or
executeUpdate.


In most implementations it result in another round trip to
the server with a SELECT.

I'm not sure about "most". The feature used for that would be the same
as that used for a RETURNING clause with INSERT, UPDATE and DELETE for
databases that have it:
http://www.postgresql.org/docs/9.3/static/sql-insert.html
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_9014.htm#i2122356

Any reasonable implementation of a protocol would not make an additional
roundtrip to the SQL engine or even an additional SELECT call necessary.
I believe efficiency was the primary reason for introduction of
RETURNING because then results could be sent back immediately. Also,
for a database without RETURNING in the worst case there would be no
reliable way to identify the generated keys (i.e. if there is no UNIQUE
INDEX or UNIQUE constraint on the table that is not including the column
with the auto generated key).

The generated keys are only related to the particular statement
execution. A separate SELECT would need to query some system tables and
use a handle identifying the particular execution of that statement. Of
course that would be possible for a JDBC driver to do if the database
implementation would provide such a handle for every execution but I
would assume that this is returned along with the other result data
(e.g. number of inserted / updated rows).

That is a significant overhead for something that is rarely
needed.

Iff it was done that way, yes.

Generally I believe that creating something which executes arbitrary SQL
at it and does all the things like result set retrieval efficiently
would be pretty difficult without knowledge about the statement.
Andreas, it may be that you need to look whether the first token in the
SQL statement is a "select" (and probably "with"), "update", "insert",
"delete" to make a reasonable choice of algorithm. Maybe the source
code of http://www.squirrelsql.org/ gives you some ideas.

Kind regards

robert

 

[Joerg Meier]

I do not need to decide between these two, as there is also a plain
execute() method, that will work for either kind - except for the
problem at hand...

Or getting any sort of result from the executed statement. Are you sure you
want to use it to execute SELECT statements when you then have no way to
look at the results ? That doesn't seem terribly useful.

Liebe Gruesse,
Joerg

 

[Arne Vajhøj]

I do not need to decide between these two, as there is also a plain
execute() method, that will work for either kind - except for the
problem at hand...

I tend to use that only for SP calls.

But you would still need to do some test later to determine
whether you need to get result set or not.

An OO solution with an abstract base class and two
concrete sub classes - one for update and one for query?

Arne

 

[Arne Vajhøj]

That feature is not limited to stored procedures. You can have multiple
statements executed with a single Statement. Consequently you get
multiple results.

The common interpretation of JDBC spec and API docs is that
multiple SQL statements in single JDBC statement object is
not supported.

And it fact it typical does not work.

Only exception I know about is MySQL where you can specify
allowMultiQueries=true in the connection string to enable the
feature.

Many other database API's are more flexible (flexible includes
both programmer intentions and what can be done via SQL injection).

Arne

 

[Robert Klemme]

The common interpretation of JDBC spec and API docs is that
multiple SQL statements in single JDBC statement object is
not supported.

And it fact it typical does not work.

Only exception I know about is MySQL where you can specify
allowMultiQueries=true in the connection string to enable the
feature.

Oracle IIRC as well.

Cheers

robert

 

[Arne Vajhøj]

Oracle IIRC as well.

It is not allowed by default.

And I have never heard of an option to enable it.

But my knowledge about Oracle is limited so an option may certainly
exist.

Arne

 

[Arne Vajhøj]

An UPDATE also can generate keys. And a DELETE can also return data via
a RETURNING clause.


I'm not sure about "most". The feature used for that would be the same
as that used for a RETURNING clause with INSERT, UPDATE and DELETE for
databases that have it:
http://www.postgresql.org/docs/9.3/static/sql-insert.html
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_9014.htm#i2122356


Any reasonable implementation of a protocol would not make an additional
roundtrip to the SQL engine or even an additional SELECT call necessary.

Just checked.

Oracle JDBC does utilize RETURNING so no second roundtrip.

I believe efficiency was the primary reason for introduction of
RETURNING because then results could be sent back immediately. Also,
for a database without RETURNING in the worst case there would be no
reliable way to identify the generated keys (i.e. if there is no UNIQUE
INDEX or UNIQUE constraint on the table that is not including the column
with the auto generated key).

The generated keys are only related to the particular statement
execution. A separate SELECT would need to query some system tables and
use a handle identifying the particular execution of that statement. Of
course that would be possible for a JDBC driver to do if the database
implementation would provide such a handle for every execution but I
would assume that this is returned along with the other result data
(e.g. number of inserted / updated rows).

The databases using auto increment instead of sequences typical store
the last generated key in the connection and limit auto increment to
only one column per table so it is easy to grab.

Arne

 

[Arved Sandstrom]

Yes.

Some databases support stored procedures returning multiple
result sets.

[ SNIP ]

This might just be me, but I dislike the entire idea. I am aware of the
possibility, I've never used it. To me a stored proc (SP) should adhere
to the same principles as functions or procedures - you try to simplify
your components (and your application) by not having them do multiple
things (side-effects, overly complex return values etc).

AHS

 

[Arne Vajhøj]

Yes.

Some databases support stored procedures returning multiple
result sets.

[ SNIP ]

This might just be me, but I dislike the entire idea. I am aware of the
possibility, I've never used it. To me a stored proc (SP) should adhere
to the same principles as functions or procedures - you try to simplify
your components (and your application) by not having them do multiple
things (side-effects, overly complex return values etc).

I can follow you.

But I have seen SP's return 20+ result sets.

I guess that the argument is to save round trips.

Arne

 

[Andreas Leitgeb]

On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
How would I tell the JDBC engine, that if it is a select then it
should pass certain flags, and if it is an insert, then I would be
interested in the generated keys? prepareStatement() doesn't seem to
have an overload to accept both variants.

[SELECT and INSERT and ... -> execute()]

But you would still need to do some test later to determine
whether you need to get result set or not.

These tests are easy to do: if the statement was a query and thus
offered a ResultSet, then execute() returns true. If instead it
returns false, then I'd check for updateCount() and getGeneratedKeys().

Except that I could only request reporting of generated keys, if I knew
beforehand that I wouldn't need any isolation,etc.-flags for the select-
case.

 

[Arne Vajhøj]

On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
How would I tell the JDBC engine, that if it is a select then it
should pass certain flags, and if it is an insert, then I would be
interested in the generated keys? prepareStatement() doesn't seem to
have an overload to accept both variants.
[SELECT and INSERT and ... -> execute()]

But you would still need to do some test later to determine
whether you need to get result set or not.

These tests are easy to do: if the statement was a query and thus
offered a ResultSet, then execute() returns true. If instead it
returns false, then I'd check for updateCount() and getGeneratedKeys().

I know, but I still think a base & sub class solution is cleaner.

Except that I could only request reporting of generated keys, if I knew
beforehand that I wouldn't need any isolation,etc.-flags for the select-
case.

????

getGeneratedKeys() should be good no matter transaction isolation level
etc. - I have not read the fine print in the JDBC spec, but all the
implementations discussed in this thread are concurrency safe (assuming
you do not make concurrent calls on the same connection object).

Arne

 

[Arved Sandstrom]

On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
I have to write code to execute runtime-specified SQL-statements,
and there are two things that I wasn't able to understand from reading
the javadocs:

1.)
When would one call .getMoreResults() on a statement instead of just
another rs.next() on the first one (obtained from
stmnt.getResultSet())?
Are these equivalent, or is there really a concept of multiple
ResultSets,
each of which has its own independent set of rows?

Yes.

Some databases support stored procedures returning multiple
result sets.

[ SNIP ]

This might just be me, but I dislike the entire idea. I am aware of the
possibility, I've never used it. To me a stored proc (SP) should adhere
to the same principles as functions or procedures - you try to simplify
your components (and your application) by not having them do multiple
things (side-effects, overly complex return values etc).

I can follow you.

But I have seen SP's return 20+ result sets.

I guess that the argument is to save round trips.

Arne

No doubt. I've never been compelled to use multiple result sets myself.
I haven't not once encountered a project where anyone felt the need to
use multiple result sets. So I actually Googled to find out what the
rationale is.

There appear to be two different things that are referred to as multiple
result sets. One is multiple active result sets (MARS), which is just
the ability to execute multiple batches on a single connection. I can
defend this idea: expensive resource acquisition, re-use it if you can.

The other is stored procs returning multiple result sets. I had no
success finding any article that provided motivation for the concept:
the authors of a few articles did mention that they wished not to
explain *why*, but just *how*...which is faint praise in my books.
This particular concept seems to me to be much more "we can do this, so
we will".

AHS

 

[Arne Vajhøj]

On 12/20/2013 08:08 PM, Arne Vajhøj wrote:
On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
I have to write code to execute runtime-specified SQL-statements,
and there are two things that I wasn't able to understand from reading
the javadocs:

1.)
When would one call .getMoreResults() on a statement instead of just
another rs.next() on the first one (obtained from
stmnt.getResultSet())?
Are these equivalent, or is there really a concept of multiple
ResultSets,
each of which has its own independent set of rows?

Yes.

Some databases support stored procedures returning multiple
result sets.
[ SNIP ]

This might just be me, but I dislike the entire idea. I am aware of the
possibility, I've never used it. To me a stored proc (SP) should adhere
to the same principles as functions or procedures - you try to simplify
your components (and your application) by not having them do multiple
things (side-effects, overly complex return values etc).

I can follow you.

But I have seen SP's return 20+ result sets.

I guess that the argument is to save round trips.

No doubt. I've never been compelled to use multiple result sets myself.
I haven't not once encountered a project where anyone felt the need to
use multiple result sets. So I actually Googled to find out what the
rationale is.

The other is stored procs returning multiple result sets. I had no
success finding any article that provided motivation for the concept:
the authors of a few articles did mention that they wished not to
explain *why*, but just *how*...which is faint praise in my books.
This particular concept seems to me to be much more "we can do this, so
we will".

http://msdn.microsoft.com/en-us/data/jj691402.aspx

http://stackoverflow.com/questions/2336362/execute-multiple-sql-commands-in-one-round-trip

do mention the round trip aspect.

Arne

 

[Andreas Leitgeb]

getGeneratedKeys() should be good no matter transaction isolation level
etc. - I have not read the fine print in the JDBC spec, but all the
implementations discussed in this thread are concurrency safe (assuming
you do not make concurrent calls on the same connection object).

There's two kinds of "prepareStatement" overloads:
- those with options relevant to selects (isolation,...)
- those with options relevant to the other ones (generatedkeys)
Then there is execute() on the preparedStatement, that
will work on any kind of sql-statement and will give me all
the necessary information/behaviour... provided I was lucky
with my choice of prepareStatement().

By the time I find out that the sql was e.g. an insert/update...,
(namely when .execute() returns false), then it is already
too late to pick the "generatedkeys"-overload of prepareStatement,
which I'd need to have specified for prepareStatement(), to now
be able to actually obtain the generated keys.

In the other case, if I used the "generatedkeys"-overload
and it turns out (from execute() returning true) that it was a
query, then I can no longer specify isolation levels, holdability
or scollability for the ResultSet.

 

[Arne Vajhøj]

There's two kinds of "prepareStatement" overloads:
- those with options relevant to selects (isolation,...)
- those with options relevant to the other ones (generatedkeys)
Then there is execute() on the preparedStatement, that
will work on any kind of sql-statement and will give me all
the necessary information/behaviour... provided I was lucky
with my choice of prepareStatement().

By the time I find out that the sql was e.g. an insert/update...,
(namely when .execute() returns false), then it is already
too late to pick the "generatedkeys"-overload of prepareStatement,
which I'd need to have specified for prepareStatement(), to now
be able to actually obtain the generated keys.

In the other case, if I used the "generatedkeys"-overload
and it turns out (from execute() returning true) that it was a
query, then I can no longer specify isolation levels, holdability
or scollability for the ResultSet.

I think you should set transaction isolation level on the connection
when you create it and use the same for queries and updates.

But I still think that the abstract super class and two sub classes
for query and update will make you code be so much nicer.

Arne