Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys

Reply
Thread Tools

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

 
 
Arne Vajh°j
Guest
Posts: n/a
 
      12-21-2013
On 12/21/2013 11:14 AM, Robert Klemme wrote:
> On 21.12.2013 16:59, Arne Vajh°j wrote:
>> On 12/21/2013 4:15 AM, Robert Klemme wrote:
>>> On 21.12.2013 01:08, 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.
>>>
>>> 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.

>
> 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


 
Reply With Quote
 
 
 
 
Arne Vajh°j
Guest
Posts: n/a
 
      12-21-2013
On 12/21/2013 5:46 AM, Robert Klemme wrote:
> On 21.12.2013 01:14, 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:

>>
>>> 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/s...ql-insert.html
> http://docs.oracle.com/cd/E16655_01/...4.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

 
Reply With Quote
 
 
 
 
Arved Sandstrom
Guest
Posts: n/a
 
      12-21-2013
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).

AHS
--
When a true genius appears, you can know him by this sign:
that all the dunces are in a confederacy against him.
-- Jonathan Swift
 
Reply With Quote
 
Arne Vajh°j
Guest
Posts: n/a
 
      12-21-2013
On 12/21/2013 1:59 PM, Arved Sandstrom wrote:
> 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.

Arne


 
Reply With Quote
 
Andreas Leitgeb
Guest
Posts: n/a
 
      12-21-2013
Arne Vajh├Şj <(E-Mail Removed)> wrote:
> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
>> Arne Vajh├Şj <(E-Mail Removed)> wrote:
>>> 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.

 
Reply With Quote
 
Arne Vajh├Şj
Guest
Posts: n/a
 
      12-21-2013
On 12/21/2013 4:43 PM, Andreas Leitgeb wrote:
> Arne Vajh├Şj <(E-Mail Removed)> wrote:
>> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
>>> Arne Vajh├Şj <(E-Mail Removed)> wrote:
>>>> 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


 
Reply With Quote
 
Arved Sandstrom
Guest
Posts: n/a
 
      12-21-2013
On 12/21/2013 04:04 PM, Arne Vajh°j wrote:
> On 12/21/2013 1:59 PM, Arved Sandstrom wrote:
>> 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.
>
> 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
--
When a true genius appears, you can know him by this sign:
that all the dunces are in a confederacy against him.
-- Jonathan Swift
 
Reply With Quote
 
Arne Vajh°j
Guest
Posts: n/a
 
      12-21-2013
On 12/21/2013 5:56 PM, Arved Sandstrom wrote:
> On 12/21/2013 04:04 PM, Arne Vajh°j wrote:
>> On 12/21/2013 1:59 PM, Arved Sandstrom wrote:
>>> 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/2...one-round-trip

do mention the round trip aspect.

Arne

 
Reply With Quote
 
Andreas Leitgeb
Guest
Posts: n/a
 
      12-22-2013
Arne Vajh├Şj <(E-Mail Removed)> wrote:
>> 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).


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.

 
Reply With Quote
 
Arne Vajh├Şj
Guest
Posts: n/a
 
      12-22-2013
On 12/21/2013 7:47 PM, Andreas Leitgeb wrote:
> Arne Vajh├Şj <(E-Mail Removed)> wrote:
>>> 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).

>
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
#######modernpractice.webs.com&&&&&&& sharmi3435@gmail.com Python 0 04-06-2009 06:24 AM
Re: Mozilla versus IE versus Opera versus Safari Peter Potamus the Purple Hippo Firefox 0 05-08-2008 12:56 PM
equal? versus eql? versus == versus === verus <=> Paul Butcher Ruby 12 11-28-2007 06:06 AM
JDBC/JTds getMoreResults trouble stef Java 1 10-30-2007 05:27 PM
CurrentElement->next = CurrentElement->next->next (UNDEFINED?) Deniz Bahar C Programming 2 03-09-2005 12:45 AM



Advertisments