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

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

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

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

Contents

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

Page 2 of 2 — ← Prev page 1 [2]

#27013

On 12/21/2013 4:43 AM, Andreas Leitgeb wrote:
> Arne Vajhøj <arne@vajhoej.dk> wrote:
>> On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
>>> How would I tell the JDBC engine, that if it is a select then it
>>> should pass certain flags, and if it is an insert, then I would be
>>> interested in the generated keys?  prepareStatement() doesn't seem to
>>> have an overload to accept both variants.
>> True - you would need to know whether it is a SELECT or an INSERT.
>> Just like you need to know whether to call executeQuery or
>> executeUpdate.
>
> I do not need to decide between these two, as there is also a plain
> execute() method, that will work for either kind - except for the
> problem at hand...

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

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

#27029

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

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

#27030

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

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

#27034

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

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

#27035

On 12/21/2013 7:47 PM, Andreas Leitgeb wrote:
> Arne Vajhøj <arne@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.
>> 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

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

#27036

On 12/21/2013 8:10 PM, Arne Vajhøj wrote:
> On 12/21/2013 7:47 PM, Andreas Leitgeb wrote:
>> Arne Vajhøj <arne@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.
>>> 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.

I know very little about your context.

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

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

#27037

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

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

#27056

On 12/22/2013 5:59 AM, Andreas Leitgeb wrote:
> Arne Vajhøj <arne@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
> 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.

I still believe that it is the code responsible for the SQL that
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

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

#27039

Arne Vajhøj <arne@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 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!

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

#27008

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

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

#27020

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

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

#27748

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

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?

It could be an optimization; the server has to do a bit of work to service RETURN_GENERATED_KEYS and variants. Of course, a driver could send a separate special request to the server when it saw a getGeneratedKeys(). Then again, it could be more 'catering'.

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

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

#28443

On 3/12/2014 11:23 PM, firstsql@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
> 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
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

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

#28448

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.

And on the other side there could be good reasons execute multiple 
statements as a block. Preferably performance issues.


Marcel

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

#28461

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

For INSERT/UPDATE/DELETE you can do it safely in JDBC via batch.

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

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

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

csiph-web