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

Discussion in 'Java' started by Andreas Leitgeb, Dec 20, 2013.

  1. 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
    Andreas Leitgeb, Dec 20, 2013
    #1
    1. Advertising

  2. Andreas Leitgeb

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Dec 21, 2013
    #2
    1. Advertising

  3. Andreas Leitgeb

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Dec 21, 2013
    #3
  4. 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
    Robert Klemme, Dec 21, 2013
    #4
  5. Arne Vajhøj <> 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.
    Andreas Leitgeb, Dec 21, 2013
    #5
  6. On 21.12.2013 01:14, Arne Vajhøj wrote:
    > On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
    >> I have to write code to execute runtime-specified SQL-statements,
    >> and there are two things that I wasn't able to understand from reading
    >> the javadocs:

    >
    >> 2.)
    >> As I don't know the statement beforehand, I can't decide at coding
    >> time, if it might be an "insert" eventually returning a serial key,
    >> and thusly requiring the argument RETURN_GENERATED_KEYS), or a select
    >> that will need cursor-like treatment such as passing resultSetType,
    >> resultSetConcurrency and resultSetHoldability flags.
    >>
    >> How would I tell the JDBC engine, that if it is a select then it
    >> should pass certain flags, and if it is an insert, then I would be
    >> interested in the generated keys? prepareStatement() doesn't seem to
    >> have an overload to accept both variants.

    >
    > True - you would need to know whether it is a SELECT or an INSERT.


    An UPDATE also can generate keys. And a DELETE can also return data via
    a RETURNING clause.

    > Just like you need to know whether to call executeQuery or
    > executeUpdate.
    >
    >> Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?

    >
    > In most implementations it result in another round trip to
    > the server with a SELECT.


    I'm not sure about "most". The feature used for that would be the same
    as that used for a RETURNING clause with INSERT, UPDATE and DELETE for
    databases that have it:
    http://www.postgresql.org/docs/9.3/static/sql-insert.html
    http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_9014.htm#i2122356

    Any reasonable implementation of a protocol would not make an additional
    roundtrip to the SQL engine or even an additional SELECT call necessary.
    I believe efficiency was the primary reason for introduction of
    RETURNING because then results could be sent back immediately. Also,
    for a database without RETURNING in the worst case there would be no
    reliable way to identify the generated keys (i.e. if there is no UNIQUE
    INDEX or UNIQUE constraint on the table that is not including the column
    with the auto generated key).

    The generated keys are only related to the particular statement
    execution. A separate SELECT would need to query some system tables and
    use a handle identifying the particular execution of that statement. Of
    course that would be possible for a JDBC driver to do if the database
    implementation would provide such a handle for every execution but I
    would assume that this is returned along with the other result data
    (e.g. number of inserted / updated rows).

    > That is a significant overhead for something that is rarely
    > needed.


    Iff it was done that way, yes.

    Generally I believe that creating something which executes arbitrary SQL
    at it and does all the things like result set retrieval efficiently
    would be pretty difficult without knowledge about the statement.
    Andreas, it may be that you need to look whether the first token in the
    SQL statement is a "select" (and probably "with"), "update", "insert",
    "delete" to make a reasonable choice of algorithm. Maybe the source
    code of http://www.squirrelsql.org/ gives you some ideas.

    Kind regards

    robert
    Robert Klemme, Dec 21, 2013
    #6
  7. Andreas Leitgeb

    Joerg Meier Guest

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

    > Arne Vajhøj <> 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.
    Joerg Meier, Dec 21, 2013
    #7
  8. On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
    > Arne Vajhøj <> 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.

    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
    #8
  9. Andreas Leitgeb

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Dec 21, 2013
    #9
  10. 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
    Robert Klemme, Dec 21, 2013
    #10
  11. Andreas Leitgeb

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Dec 21, 2013
    #11
  12. Andreas Leitgeb

    Arne Vajhøj Guest

    On 12/21/2013 5:46 AM, Robert Klemme wrote:
    > On 21.12.2013 01:14, Arne Vajhøj wrote:
    >> On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
    >>> I have to write code to execute runtime-specified SQL-statements,
    >>> and there are two things that I wasn't able to understand from reading
    >>> the javadocs:

    >>
    >>> 2.)
    >>> As I don't know the statement beforehand, I can't decide at coding
    >>> time, if it might be an "insert" eventually returning a serial key,
    >>> and thusly requiring the argument RETURN_GENERATED_KEYS), or a select
    >>> that will need cursor-like treatment such as passing resultSetType,
    >>> resultSetConcurrency and resultSetHoldability flags.
    >>>
    >>> How would I tell the JDBC engine, that if it is a select then it
    >>> should pass certain flags, and if it is an insert, then I would be
    >>> interested in the generated keys? prepareStatement() doesn't seem to
    >>> have an overload to accept both variants.

    >>
    >> True - you would need to know whether it is a SELECT or an INSERT.

    >
    > An UPDATE also can generate keys. And a DELETE can also return data via
    > a RETURNING clause.
    >
    >> Just like you need to know whether to call executeQuery or
    >> executeUpdate.
    >>
    >>> Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?

    >>
    >> In most implementations it result in another round trip to
    >> the server with a SELECT.

    >
    > I'm not sure about "most". The feature used for that would be the same
    > as that used for a RETURNING clause with INSERT, UPDATE and DELETE for
    > databases that have it:
    > http://www.postgresql.org/docs/9.3/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.


    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
    Arne Vajhøj, Dec 21, 2013
    #12
  13. 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
    Arved Sandstrom, Dec 21, 2013
    #13
  14. Andreas Leitgeb

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Dec 21, 2013
    #14
  15. Arne Vajhøj <> wrote:
    > On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
    >> Arne Vajhøj <> 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.
    Andreas Leitgeb, Dec 21, 2013
    #15
  16. On 12/21/2013 4:43 PM, Andreas Leitgeb wrote:
    > Arne Vajhøj <> wrote:
    >> On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
    >>> Arne Vajhøj <> wrote:
    >>>> On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
    >>>>> How would I tell the JDBC engine, that if it is a select then it
    >>>>> should pass certain flags, and if it is an insert, then I would be
    >>>>> interested in the generated keys? prepareStatement() doesn't seem to
    >>>>> have an overload to accept both variants.
    >>> [SELECT and INSERT and ... -> execute()]

    >> But you would still need to do some test later to determine
    >> whether you need to get result set or not.

    >
    > These tests are easy to do: if the statement was a query and thus
    > offered a ResultSet, then execute() returns true. If instead it
    > returns false, then I'd check for updateCount() and getGeneratedKeys().


    I know, but I still think a base & sub class solution is cleaner.

    > Except that I could only request reporting of generated keys, if I knew
    > beforehand that I wouldn't need any isolation,etc.-flags for the select-
    > case.


    ????

    getGeneratedKeys() should be good no matter transaction isolation level
    etc. - I have not read the fine print in the JDBC spec, but all the
    implementations discussed in this thread are concurrency safe (assuming
    you do not make concurrent calls on the same connection object).

    Arne
    Arne Vajhøj, Dec 21, 2013
    #16
  17. 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
    Arved Sandstrom, Dec 21, 2013
    #17
  18. Andreas Leitgeb

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Dec 21, 2013
    #18
  19. Arne Vajhøj <> wrote:
    >> Except that I could only request reporting of generated keys, if I knew
    >> beforehand that I wouldn't need any isolation,etc.-flags for the select-
    >> case.

    > getGeneratedKeys() should be good no matter transaction isolation level
    > etc. - I have not read the fine print in the JDBC spec, but all the
    > implementations discussed in this thread are concurrency safe (assuming
    > you do not make concurrent calls on the same connection object).


    There's two kinds of "prepareStatement" overloads:
    - those with options relevant to selects (isolation,...)
    - those with options relevant to the other ones (generatedkeys)
    Then there is execute() on the preparedStatement, that
    will work on any kind of sql-statement and will give me all
    the necessary information/behaviour... provided I was lucky
    with my choice of prepareStatement().

    By the time I find out that the sql was e.g. an insert/update...,
    (namely when .execute() returns false), then it is already
    too late to pick the "generatedkeys"-overload of prepareStatement,
    which I'd need to have specified for prepareStatement(), to now
    be able to actually obtain the generated keys.

    In the other case, if I used the "generatedkeys"-overload
    and it turns out (from execute() returning true) that it was a
    query, then I can no longer specify isolation levels, holdability
    or scollability for the ResultSet.
    Andreas Leitgeb, Dec 22, 2013
    #19
  20. On 12/21/2013 7:47 PM, Andreas Leitgeb wrote:
    > Arne Vajhøj <> wrote:
    >>> Except that I could only request reporting of generated keys, if I knew
    >>> beforehand that I wouldn't need any isolation,etc.-flags for the select-
    >>> case.

    >> getGeneratedKeys() should be good no matter transaction isolation level
    >> etc. - I have not read the fine print in the JDBC spec, but all the
    >> implementations discussed in this thread are concurrency safe (assuming
    >> you do not make concurrent calls on the same connection object).

    >
    > There's two kinds of "prepareStatement" overloads:
    > - those with options relevant to selects (isolation,...)
    > - those with options relevant to the other ones (generatedkeys)
    > Then there is execute() on the preparedStatement, that
    > will work on any kind of sql-statement and will give me all
    > the necessary information/behaviour... provided I was lucky
    > with my choice of prepareStatement().
    >
    > By the time I find out that the sql was e.g. an insert/update...,
    > (namely when .execute() returns false), then it is already
    > too late to pick the "generatedkeys"-overload of prepareStatement,
    > which I'd need to have specified for prepareStatement(), to now
    > be able to actually obtain the generated keys.
    >
    > In the other case, if I used the "generatedkeys"-overload
    > and it turns out (from execute() returning true) that it was a
    > query, then I can no longer specify isolation levels, holdability
    > or scollability for the ResultSet.


    I think you should set transaction isolation level on the connection
    when you create it and use the same for queries and updates.

    But I still think that the abstract super class and two sub classes
    for query and update will make you code be so much nicer.

    Arne
    Arne Vajhøj, Dec 22, 2013
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    733
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,847
    Juha Laiho
    May 21, 2005
  3. Deniz Bahar
    Replies:
    2
    Views:
    453
    Andrey Tarasevich
    Mar 9, 2005
  4. stef
    Replies:
    1
    Views:
    1,345
    mdtsdca
    Oct 30, 2007
  5. Paul Butcher
    Replies:
    12
    Views:
    693
    Gary Wright
    Nov 28, 2007
Loading...

Share This Page