Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!news.wtal.de!news.tal.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Robert Klemme Newsgroups: comp.lang.java.programmer Subject: Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Date: Sat, 21 Dec 2013 11:46:50 +0100 Lines: 82 Message-ID: References: <52b4dd7d$0$305$14726298@news.sunsite.dk> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Trace: individual.net rzO24I6h3jcHdOzPWe4YkA6YWjyRuF3E9TcrjQ6THtumEoyN0= Cancel-Lock: sha1:GMTGa66zaWyRiauI0DR+DtfHia8= User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.2.0 In-Reply-To: <52b4dd7d$0$305$14726298@news.sunsite.dk> Xref: csiph.com comp.lang.java.programmer:27008 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=20 as that used for a RETURNING clause with INSERT, UPDATE and DELETE for=20 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=20 RETURNING because then results could be sent back immediately. Also,=20 for a database without RETURNING in the worst case there would be no=20 reliable way to identify the generated keys (i.e. if there is no UNIQUE=20 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=20 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=20 implementation would provide such a handle for every execution but I=20 would assume that this is returned along with the other result data=20 (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=20 would be pretty difficult without knowledge about the statement.=20 Andreas, it may be that you need to look whether the first token in the=20 SQL statement is a "select" (and probably "with"), "update", "insert",=20 "delete" to make a reasonable choice of algorithm. Maybe the source=20 code of http://www.squirrelsql.org/ gives you some ideas. Kind regards robert