Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys
On 21.12.2013 01:14, Arne Vajh=F8j 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:
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.
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.
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.
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