Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys
975 views
Skip to first unread message
Andreas Leitgeb
Dec 20, 2013, 9:07:04 AM12/20/13
to
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
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
lipska the kat
Dec 20, 2013, 11:29:43 AM12/20/13
to
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
> 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?
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.
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?
> 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
Arne Vajhøj
Dec 20, 2013, 7:08:17 PM12/20/13
to
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.
> 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?
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
Dec 20, 2013, 7:14:48 PM12/20/13
to
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:
> 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.
> 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.
Just like you need to know whether to call executeQuery or
executeUpdate.
> Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?
the server with a SELECT.
That is a significant overhead for something that is rarely
needed.
Arne
Robert Klemme
Dec 21, 2013, 4:15:18 AM12/21/13
to
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
> 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.
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.
Kind regards
robert
Andreas Leitgeb
Dec 21, 2013, 4:43:01 AM12/21/13
to
Arne Vajhøj <ar...@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
> 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.
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.
Thanks.
PS: Thanks also to the others who replied about multiple ResultSets.
Robert Klemme
Dec 21, 2013, 5:46:50 AM12/21/13
to
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
> 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.
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.
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.
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
Dec 21, 2013, 5:59:41 AM12/21/13
to
On Sat, 21 Dec 2013 09:43:01 +0000 (UTC), Andreas Leitgeb wrote:
> Arne Vajh�j <ar...@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.
> Arne Vajh�j <ar...@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...
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.
Arne Vajhøj
Dec 21, 2013, 9:15:41 AM12/21/13
to
On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
> Arne Vajhøj <ar...@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...
I tend to use that only for SP calls.
> Arne Vajhøj <ar...@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...
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
Dec 21, 2013, 10:59:25 AM12/21/13
to
On 12/21/2013 4:15 AM, Robert Klemme wrote:
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
> 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
>> 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.
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
Dec 21, 2013, 11:14:16 AM12/21/13
to
On 21.12.2013 16:59, Arne Vajhøj wrote:
> On 12/21/2013 4:15 AM, Robert Klemme wrote:
> On 12/21/2013 4:15 AM, Robert Klemme wrote:
Cheers
robert
Arne Vajhøj
Dec 21, 2013, 12:19:51 PM12/21/13
to
On 12/21/2013 11:14 AM, Robert Klemme wrote:
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
> On 21.12.2013 16:59, Arne Vajh�j wrote:
>> On 12/21/2013 4:15 AM, Robert Klemme wrote:
>> On 12/21/2013 4:15 AM, Robert Klemme wrote:
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
Dec 21, 2013, 1:44:23 PM12/21/13
to
On 12/21/2013 5:46 AM, Robert Klemme wrote:
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
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 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
Dec 21, 2013, 1:59:08 PM12/21/13
to
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 ]
> 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.
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
Arne Vajhøj
Dec 21, 2013, 3:04:40 PM12/21/13
to
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.
> 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).
But I have seen SP's return 20+ result sets.
I guess that the argument is to save round trips.
Arne
Andreas Leitgeb
Dec 21, 2013, 4:43:00 PM12/21/13
to
Arne Vajhøj <ar...@vajhoej.dk> wrote:
> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
>> Arne Vajhøj <ar...@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.
>> [SELECT and INSERT and ... -> execute()]
> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
>> Arne Vajhøj <ar...@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.
> 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
> whether you need to get result set or not.
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
Dec 21, 2013, 5:34:47 PM12/21/13
to
On 12/21/2013 4:43 PM, Andreas Leitgeb wrote:
> Arne Vajhøj <ar...@vajhoej.dk> wrote:
>> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
>>> Arne Vajhøj <ar...@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.
>>> [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.
> Arne Vajhøj <ar...@vajhoej.dk> wrote:
>> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
>>> Arne Vajhøj <ar...@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.
>>> [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.
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
Dec 21, 2013, 5:56:35 PM12/21/13
to
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".
Arne Vajhøj
Dec 21, 2013, 6:33:36 PM12/21/13
to
> 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.
> 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
> 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://stackoverflow.com/questions/2336362/execute-multiple-sql-commands-in-one-round-trip
do mention the round trip aspect.
Arne
Andreas Leitgeb
Dec 21, 2013, 7:47:54 PM12/21/13
to
Arne Vajhøj <ar...@vajhoej.dk> 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.
>> 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:
> 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).
- 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
Dec 21, 2013, 8:10:28 PM12/21/13
to
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
Arne Vajhøj
Dec 21, 2013, 10:45:11 PM12/21/13
to
But here is a generic example to illustrate what I am talking about:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class OODBA {
public static class RowConsoleDisplay implements RowProcessor {
public void processRow(ResultSet rs) throws SQLException {
System.out.printf("%d %s\n", rs.getInt(1), rs.getString(2));
}
}
public static class RowCountConsoleDisplay implements
RowCountAndKeyProcessor {
public void processRowCount(int rc) {
System.out.printf("rc = %d\n", rc);
}
public void processKeyRow(ResultSet rs) {
// not used
}
}
public static void main(String[] args) throws
ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost/Test", "root", "");
DatabaseCommand q1 = new QueryCommand(con, "SELECT f1,f2 FROM
t1", new RowConsoleDisplay());
q1.execute();
DatabaseCommand u = new UpdateCommand(con, "INSERT INTO t1
VALUES(?,?)", new RowCountConsoleDisplay());
u.execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, 9);
pstmt.setString(2, "IIIIIIIII");
}
});
q1.execute();
DatabaseCommand q2 = new QueryCommand(con, "SELECT f1,f2 FROM
t1 WHERE f1 = ?", new RowConsoleDisplay());
q2.execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, 3);
};
});
q2.execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, 5);
};
});
con.close();
}
}
interface ParameterSetter {
public void set(PreparedStatement pstmt) throws SQLException;
}
interface RowProcessor {
public void processRow(ResultSet rs) throws SQLException;
}
interface RowCountAndKeyProcessor {
public void processRowCount(int rc);
public void processKeyRow(ResultSet rs);
}
abstract class DatabaseCommand {
private Connection con;
private String sqlstr;
public DatabaseCommand(Connection con, String sqlstr) {
this.con = con;
this.sqlstr = sqlstr;
}
public void execute() throws SQLException {
execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) {
}
});
}
public void execute(ParameterSetter params) throws SQLException {
PreparedStatement pstmt = getPreparedStatement(con, sqlstr);
params.set(pstmt);
process(pstmt);
pstmt.close();
}
public abstract PreparedStatement getPreparedStatement(Connection
con, String sqlstr) throws SQLException;
public abstract void process(PreparedStatement pstmt) throws
SQLException;
}
class QueryCommand extends DatabaseCommand {
private int rstype;
private int rsconcur;
private RowProcessor rowproc;
public QueryCommand(Connection con, String sqlstr, RowProcessor
rowproc) {
this(con, sqlstr, ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, rowproc);
}
public QueryCommand(Connection con, String sqlstr, int rstype, int
rsconcur, RowProcessor rowproc) {
super(con, sqlstr);
this.rstype = rstype;
this.rsconcur = rsconcur;
this.rowproc = rowproc;
}
public PreparedStatement getPreparedStatement(Connection con,
String sqlstr) throws SQLException {
return con.prepareStatement(sqlstr, rstype, rsconcur);
}
public void process(PreparedStatement pstmt) throws SQLException {
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
rowproc.processRow(rs);
}
rs.close();
}
}
class UpdateCommand extends DatabaseCommand {
private boolean genkey;
private RowCountAndKeyProcessor rcakproc;
public UpdateCommand(Connection con, String sqlstr,
RowCountAndKeyProcessor rcproc) {
this(con, sqlstr, false, rcproc);
}
public UpdateCommand(Connection con, String sqlstr, boolean genkey,
RowCountAndKeyProcessor rcakproc) {
super(con, sqlstr);
this.genkey = genkey;
this.rcakproc = rcakproc;
}
public PreparedStatement getPreparedStatement(Connection con,
String sqlstr) throws SQLException {
return con.prepareStatement(sqlstr, genkey ?
PreparedStatement.RETURN_GENERATED_KEYS :
PreparedStatement.NO_GENERATED_KEYS);
}
public void process(PreparedStatement pstmt) throws SQLException {
int rc = pstmt.executeUpdate();
rcakproc.processRowCount(rc);
if(genkey) {
ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()) {
rcakproc.processKeyRow(rs);
}
}
}
}
Arne
Andreas Leitgeb
Dec 22, 2013, 5:59:53 AM12/22/13
to
Arne Vajhøj <ar...@vajhoej.dk> wrote:
> On 12/21/2013 8:10 PM, Arne Vajhøj wrote:
>> 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.
> I know very little about your context.
> But here is a generic example to illustrate what I am talking about:
Well, the context is somewhat more complex, and I just simply
> On 12/21/2013 8:10 PM, Arne Vajhøj wrote:
>> 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.
> I know very little about your context.
> But here is a generic example to illustrate what I am talking about:
won't get that piece of information about the kind of statement
from my caller before the time of me calling prepareStatement(...).
"Not getting the information" implies, that the caller also
won't instantiate different classes of my package beforehand,
nor will call different methods of my classes beforehand.
ResultSets, updateCounts and generated keys are supposed to be kept
available for later request (through getters), except for the general
success feedback (as in: exception thrown or not) which is "delivered"
immediately, so I also can't just defer execution until the first
getter gets called.
That may all sound a bit like "mission impossible" now...
Eventually, peeking into the sql string might be my only way out,
It's nice of the JDBC to offer a general execute() suitable for
both modification statements and queries, but unfortunately they
didn't follow that concept to its end.
Andreas Leitgeb
Dec 22, 2013, 6:08:52 AM12/22/13
to
Arne Vajhøj <ar...@vajhoej.dk> wrote:
> I think you should set transaction isolation level on the connection
> when you create it and use the same for queries and updates.
I seem to have missed that line in my previous answer.
> I think you should set transaction isolation level on the connection
> when you create it and use the same for queries and updates.
I saw in the docs, that one could set some (only two of three)
of the query-specific options as defaults in the connection.
I was a bit disappointed that one couldn't do that for the
generatedkeys option nor for the resultType (scrolling or
forward-only) and so I dismissd it...
I'll re-think that path. Maybe it's already enough for my needs.
Thanks!
Andreas Leitgeb
Dec 22, 2013, 6:24:52 AM12/22/13
to
>> On 12/21/2013 04:04 PM, Arne Vajhøj wrote:
>>> On 12/21/2013 1:59 PM, Arved Sandstrom 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,
>>>>> On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
>>>>>> each of which has its own independent set of rows?
>>>>> Yes.
>>>>> Some databases support stored procedures returning multiple
>>>>> result sets.
>>>>> 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)
>>>> 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.
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. :)
Arne Vajhøj
Dec 22, 2013, 8:09:52 PM12/22/13
to
should know all the details about (whether keys will be generated
for an insert or whether the result set need to be scrolled
backwards etc.).
But You seem to have been given the context and need to find
a practical solution.
And as a practical solution, then peeking into the SQL string
is not bad.
Arne
Andreas Leitgeb
Mar 5, 2014, 9:10:00 AM3/5/14
to
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.
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)
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.
lipska the kat
Mar 5, 2014, 3:34:44 PM3/5/14
to
On 05/03/14 14:10, Andreas Leitgeb wrote:
> Sorry for lateness of answer...
<snip>
> Sorry for lateness of answer...
>>> 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.
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
Andreas Leitgeb
Mar 11, 2014, 11:04:28 AM3/11/14
to
lipska the kat <lipska...@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"
> 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?
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.
Arne Vajhøj
Mar 11, 2014, 8:02:47 PM3/11/14
to
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.
> 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.
And supported in some other.
And not supported in some (Oracle among those I believe).
Arne
Andreas Leitgeb
Mar 11, 2014, 8:13:02 PM3/11/14
to
firs...@gmail.com
Mar 12, 2014, 11:23:36 PM3/12/14
to
On Friday, December 20, 2013 6:07:04 AM UTC-8, 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,
> 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())?
>
>
> each of which has its own independent set of rows?
Yes, there is the concept of multiple independent ResultSets.
> each of which has its own independent set of rows?
Seeing the comments elsewhere in this thread, I was kind of surprised that stored procedures returning multiple ResultSets was somewhat obscure and that Oracle doesn't support them.
I was also under the apparently mistaken impression that passing multiple statements (optionally separated by semi-colons or whatever) to a single executeXXX(), or whatever, was reasonably well supported. This case allows not just multiple queries but also a mixture of statements: query, update, control and even DDL, returning multiple 'results', instead of just multiple ResultSets.
Our ORDBMS allows all of the above, plus the capability for a stored procedure to return a JDBC statement, potentially containing mixed results. I guess we're just the best ;^)
> 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.
Just another weakness in JDBC, choosing brevity over capability, and probably catering to their 'masters'. This really should be up to the drivers themselves.
>
> 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.
As to knowing before whether the SQL command can return generated keys, the javadocs say that the RETURN_GENERATED_KEYS parameter IS ignored if "an SQL statement [is not] able to return auto-generated keys". Unfortunately, some drivers may violate this ... YMMV. So given a compliant driver, you could specify RETURN_GENERATED_KEYS for any type of SQL statement. This could also work for multiple SQL command in a single SQL statement.
> Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?
--
Lee Fesperman, FirstSQL Software (http://www.firstsql.com)
=============================================================
* Pure Java implementation, runs on cellphones to mainframes
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
Arne Vajhøj
May 11, 2014, 10:10:01 PM5/11/14
to
On 3/12/2014 11:23 PM, firs...@gmail.com wrote:
> On Friday, December 20, 2013 6:07:04 AM UTC-8, Andreas Leitgeb wrote:
>> 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, there is the concept of multiple independent ResultSets.
>
> Seeing the comments elsewhere in this thread, I was kind of
> surprisedthat stored procedures returning multiple ResultSets was
> On Friday, December 20, 2013 6:07:04 AM UTC-8, Andreas Leitgeb wrote:
>> 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, there is the concept of multiple independent ResultSets.
>
> Seeing the comments elsewhere in this thread, I was kind of
> somewhat obscure and that Oracle doesn't support them.
>
> I was also under the apparently mistaken impression that passing
> multiple statements (optionally separated by semi-colons or whatever)
> to a single executeXXX(), or whatever, was reasonably well supported.
> This case allows not just multiple queries but also a mixture of
> statements: query, update, control and even DDL, returning multiple
> 'results', instead of just multiple ResultSets.
Allowing multiple SQL statements in a single execute can make
>
> I was also under the apparently mistaken impression that passing
> multiple statements (optionally separated by semi-colons or whatever)
> to a single executeXXX(), or whatever, was reasonably well supported.
> This case allows not just multiple queries but also a mixture of
> statements: query, update, control and even DDL, returning multiple
> 'results', instead of just multiple ResultSets.
SQL injection much worse.
I would consider it a security flaw.
And it is not widely supported.
A quick test shows that the following does not allow it:
- MySQL by default
- Oracle
- DB2
- H2
- Derby/JavaDB
and only the following allow it:
- SQLServer
- MySQL with allowMultiQueries=true
Arne
Marcel Müller
May 12, 2014, 12:29:40 PM5/12/14
to
On 12.05.14 04.10, Arne Vajh�j wrote:
> Allowing multiple SQL statements in a single execute can make
> SQL injection much worse.
> I would consider it a security flaw.
I do not agree.
> Allowing multiple SQL statements in a single execute can make
> SQL injection much worse.
> I would consider it a security flaw.
Code that passes user input as part of SQL code to a database is broken
by design, not the database driver that can execute more than one statement.
And on the other side there could be good reasons execute multiple
statements as a block. Preferably performance issues.
Marcel
Arne Vajhøj
May 13, 2014, 8:43:48 PM5/13/14
to
On 5/12/2014 12:29 PM, Marcel M�ller wrote:
> On 12.05.14 04.10, Arne Vajh�j wrote:
>> Allowing multiple SQL statements in a single execute can make
>> SQL injection much worse.
>
>> I would consider it a security flaw.
>
> I do not agree.
>
> Code that passes user input as part of SQL code to a database is broken
> by design, not the database driver that can execute more than one
> statement.
If SQL injection happens then there is certainly a bug in
> On 12.05.14 04.10, Arne Vajh�j wrote:
>> Allowing multiple SQL statements in a single execute can make
>> SQL injection much worse.
>
>> I would consider it a security flaw.
>
> I do not agree.
>
> Code that passes user input as part of SQL code to a database is broken
> by design, not the database driver that can execute more than one
> statement.
the application code.
But good software design does not assume that all other code
is implemented correctly.
So you:
* configure mod_security in Apache to check for injection attempts
* validate input in PL
* store data in strongly typed objects in BLL
* use prepared statements in DAL
* use a JDBC driver that does not allow multiple statements to be
executed in one call
* configure database security of that the connections has minimal priviliges
To defend against SQL injections in depth.
> And on the other side there could be good reasons execute multiple
> statements as a block. Preferably performance issues.
If the same functionality was needed for SELECT then add SELECT support
in batch would be safer than allowing multiple statements in one call.
Arne
0 new messages