Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #26999 > unrolled thread

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

Back to article view | Back to comp.lang.java.programmer

Contents

  JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-20 14:07 +0000
    Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys lipska the kat <"lipskathekat at yahoo dot co dot uk"> - 2013-12-20 16:29 +0000
      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-03-05 14:10 +0000
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys lipska the kat <lipskathekat@example.com> - 2014-03-05 20:34 +0000
          Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-03-11 15:04 +0000
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2014-03-11 20:02 -0400
          Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-03-12 00:13 +0000
    Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-20 19:08 -0500
      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Robert Klemme <shortcutter@googlemail.com> - 2013-12-21 10:15 +0100
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 10:59 -0500
          Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Robert Klemme <shortcutter@googlemail.com> - 2013-12-21 17:14 +0100
            Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 12:19 -0500
      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arved Sandstrom <asandstrom2@eastlink.ca> - 2013-12-21 14:59 -0400
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 15:04 -0500
          Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arved Sandstrom <asandstrom2@eastlink.ca> - 2013-12-21 18:56 -0400
            Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 18:33 -0500
              Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-22 11:24 +0000
    Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-20 19:14 -0500
      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-21 09:43 +0000
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Joerg Meier <joergmmeier@arcor.de> - 2013-12-21 11:59 +0100
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 09:15 -0500
          Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-21 21:43 +0000
            Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 17:34 -0500
              Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-22 00:47 +0000
                Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 20:10 -0500
                  Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 22:45 -0500
                    Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-22 10:59 +0000
                      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-22 20:09 -0500
                  Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Andreas Leitgeb <avl@gamma.logic.tuwien.ac.at> - 2013-12-22 11:08 +0000
      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Robert Klemme <shortcutter@googlemail.com> - 2013-12-21 11:46 +0100
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2013-12-21 13:44 -0500
    Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys firstsql@gmail.com - 2014-03-12 20:23 -0700
      Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2014-05-11 22:10 -0400
        Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Marcel Müller <news.5.maazl@spamgourmet.org> - 2014-05-12 18:29 +0200
          Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys Arne Vajhøj <arne@vajhoej.dk> - 2014-05-13 20:43 -0400

Page 1 of 2  [1] 2  Next page →

#26999 — JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys

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

[toc] | [next] | [standalone]

#27001

On 20/12/13 14:07, 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?

IIRC multiple ResultSet[s] can be returned from a stored procedure, in 
this case you would need to iterate over the ResultSet[s] using the 
method getResultSet() to get the first results and getMoreResults() to 
check if there are more results. If there are, calling getResultSet() 
again would return the next results. I've never seen a single sql 
statement that would return multiple result sets but this doesn't mean 
it's not possible (obviously)

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

You would do this in Java, surely. Use some abstract factory type 
mechanism to generate a suitable class depending on the type of query.
I can't immediately see a way of doing this without identifying the 
'type' of the statement before it's executed.

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

[guess]Because keys aren't always generated perhaps?

> Seasonal Greetings, and thanks in advance
>


-- 
Lipska the Kat©: Troll hunter, sandbox destroyer,
treacherous feline and farscape dreamer of Aeryn Sun
GNU/Linux user #560883 - http://www.linuxcounter.net

[toc] | [prev] | [next] | [standalone]

#27688

Sorry for lateness of answer...

lipska the kat <"lipskathekat at yahoo dot co dot uk"> wrote:
> IIRC multiple ResultSet[s] can be returned from a stored procedure, in 
> this case you would need to iterate over the ResultSet[s] using the 
> method getResultSet() to get the first results and getMoreResults() to 
> check if there are more results. If there are, calling getResultSet() 
> again would return the next results. I've never seen a single sql 
> statement that would return multiple result sets but this doesn't mean 
> it's not possible (obviously)

Ok, thanks.  I haven't yet come across a stored proc returning
more than one "table"-ful of data, so I learnt something new here.

>> 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.
> You would do this in Java, surely. Use some abstract factory type 
> mechanism to generate a suitable class depending on the type of query.

This "depending on the type of query" is the very problem.

[toc] | [prev] | [next] | [standalone]

#27689

On 05/03/14 14:10, Andreas Leitgeb wrote:
> Sorry for lateness of answer...

<snip>

>>> 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.
>> You would do this in Java, surely. Use some abstract factory type
>> mechanism to generate a suitable class depending on the type of query.
>
> This "depending on the type of query" is the very problem.

Well sooner or later something has to know what you want doesn't it?
You have to make a decision sometime don't you?

What is it that determines the type of query?

Someone must make a decision, like press a button or type a command or 
press a key or something ... unless you have a mind reading application 
;-) even then, there will be a particular electrical impulse that tells 
something "I want a select" or "I want an insert". If it is a machine 
interface then there may be a bit field or something, *something* has to 
tell *something else* what is wanted.

This is the point when you can generate the SQL ... or am I missing 
something?

Where is the decision first made and by what or who?



-- 
Lipska the Kat©: Troll hunter, sandbox destroyer,
treacherous feline and farscape dreamer of Aeryn Sun
GNU/Linux user #560883 - http://www.linuxcounter.net

[toc] | [prev] | [next] | [standalone]

#27724

lipska the kat <lipskathekat@example.com> wrote:
> On 05/03/14 14:10, Andreas Leitgeb wrote:
>> This "depending on the type of query" is the very problem.
>
> Well sooner or later something has to know what you want doesn't it?
> You have to make a decision sometime don't you?

The workflow is like this: a method is called with an "arbitrary string"

I then create a PreparedStatement for this string, and execute() it,
and use execute()'s boolean return value to either obtain the ResultSet
or updateCount/generated keys, and assemble the first row or the
updatecount/keys into a response. (Think of an interactive utility a
bit like sqlplus.)

Eventually, however, next command may be to advance to another row and
for that case I need to have checked beforehand, that the implicit
cursor possibly created from that previous execute() would have certain
properties w.r.t *isolation*, *scrollability* & *holdability*.

Those cursor-related flags have been previously set up for the session,
but only two of the three flags can be set on the java.sql.Connection
instance.  Scrollability can not be pre-set on Connection level.

This means, that if session options are set for scrollable cursors,
then I just cannot report generated keys (because I need to use the
one prepareStatement-overload that allow me to specify scrollability
and does not allow me to also possibly request generated keys).

Seems like there's no other way than live with it, anyway, that if
generated keys are needed for some statement, then the scroll-flag 
needs to be (temporarily) turned off.

[toc] | [prev] | [next] | [standalone]

#27736

On 3/5/2014 9:10 AM, Andreas Leitgeb wrote:
> Sorry for lateness of answer...
>
> lipska the kat <"lipskathekat at yahoo dot co dot uk"> wrote:
>> IIRC multiple ResultSet[s] can be returned from a stored procedure, in
>> this case you would need to iterate over the ResultSet[s] using the
>> method getResultSet() to get the first results and getMoreResults() to
>> check if there are more results. If there are, calling getResultSet()
>> again would return the next results. I've never seen a single sql
>> statement that would return multiple result sets but this doesn't mean
>> it's not possible (obviously)
>
> Ok, thanks.  I haven't yet come across a stored proc returning
> more than one "table"-ful of data, so I learnt something new here.

It is rather common in the Sybase ASE/Microsoft SQLServer world.

And supported in some other.

And not supported in some (Oracle among those I believe).

Arne

[toc] | [prev] | [next] | [standalone]

#27738

Arne Vajhøj <arne@vajhoej.dk> wrote:
> On 3/5/2014 9:10 AM, Andreas Leitgeb wrote:
>> Sorry for lateness of answer...
>> lipska the kat <"lipskathekat at yahoo dot co dot uk"> wrote:
>>> IIRC multiple ResultSet[s] can be returned from a stored procedure, in
>>> this case you would need to iterate over the ResultSet[s] using the
>>> method getResultSet() to get the first results and getMoreResults() to
>>> check if there are more results. If there are, calling getResultSet()
>>> again would return the next results. I've never seen a single sql
>>> statement that would return multiple result sets but this doesn't mean
>>> it's not possible (obviously)
>> Ok, thanks.  I haven't yet come across a stored proc returning
>> more than one "table"-ful of data, so I learnt something new here.
> It is rather common in the Sybase ASE/Microsoft SQLServer world.
> And supported in some other.
> And not supported in some (Oracle among those I believe).

That explains, why I haven't come across it yet.

[toc] | [prev] | [next] | [standalone]

#27002

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.

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

[toc] | [prev] | [next] | [standalone]

#27006

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.

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

Exactly.

Kind regards

	robert

[toc] | [prev] | [next] | [standalone]

#27014

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.

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

Arne

[toc] | [prev] | [next] | [standalone]

#27015

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.

Cheers

	robert

[toc] | [prev] | [next] | [standalone]

#27017

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

[toc] | [prev] | [next] | [standalone]

#27022

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

[toc] | [prev] | [next] | [standalone]

#27025

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

[toc] | [prev] | [next] | [standalone]

#27031

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

[toc] | [prev] | [next] | [standalone]

#27032

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/2336362/execute-multiple-sql-commands-in-one-round-trip

do mention the round trip aspect.

Arne

[toc] | [prev] | [next] | [standalone]

#27041

Arne Vajhøj <arne@vajhoej.dk> wrote:
> 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:
>>>>>> [...] 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.
>>>> 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.

Thanks for this insightful thread about pros (saving round-trips) 
and cons (not "KISS") of the multiple-resultsets concept.

Fortunately, it's something that I will likely not have to deal with
within my current task. :)

[toc] | [prev] | [next] | [standalone]

#27003

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.

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

[toc] | [prev] | [next] | [standalone]

#27007

Arne Vajhøj <arne@vajhoej.dk> 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.
> 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...

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

Ok, that sounds reasonable.

Thanks.

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

[toc] | [prev] | [next] | [standalone]

#27009

On Sat, 21 Dec 2013 09:43:01 +0000 (UTC), Andreas Leitgeb wrote:

> Arne Vajhøj <arne@vajhoej.dk> 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.
>> 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...

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

-- 
Ich lese meine Emails nicht, replies to Email bleiben also leider
ungelesen.

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.java.programmer

csiph-web