executeBatch vs getGeneratedKeys() = only 1 keys :(

[mps]

Hi all!

Thomas, pls fix this ( Version: H2 1.0.71 (2008-04-25)):
org\h2\jdbc\JdbcConnection.java line 1285-1288

ResultInterface getGeneratedKeys(JdbcStatement statement, int id)
throws SQLException {
getGeneratedKeys = prepareCommand("CALL IDENTITY()",
getGeneratedKeys);
return getGeneratedKeys.executeQuery(0, false);
}

Thx, mps

Example:

public static void main(String[] args) throws Exception {
String DB_USR = "SA";
String DB_PSW = "";
String DB_URL = "jdbc:h2:file:/tmp/h2db";

org.h2.tools.Server database;
Connection conn;
Statement stmDB;
PreparedStatement pstmBatch;
ResultSet rstIDkeys;

Class.forName("org.h2.Driver");
database = org.h2.tools.Server.createTcpServer(new String[]
{});

conn = DriverManager.getConnection(DB_URL, DB_USR, DB_PSW);
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

stmDB =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CLOSE_CURSORS_AT_COMMIT);
stmDB.addBatch("DROP ALL OBJECTS DELETE FILES");
stmDB.addBatch("CREATE TABLE TBL_TEST (ID IDENTITY NOT NULL PRIMARY
KEY, DATA CHAR(10) NOT NULL)");
stmDB.executeBatch();
stmDB.close();
conn.commit();

pstmBatch = conn.prepareStatement("INSERT INTO TBL_TEST (DATA)
VALUES (?)", Statement.RETURN_GENERATED_KEYS);
pstmBatch.clearParameters();
pstmBatch.setString(1,"data1");//auto generated key1
pstmBatch.addBatch();

pstmBatch.clearParameters();
pstmBatch.setString(1,"data2");//auto generated key2
pstmBatch.addBatch();

System.out.println("Inserted
rows="+pstmBatch.executeBatch().length);
rstIDkeys = pstmBatch.getGeneratedKeys();

/** Only last id in result set !!!*/
while (rstIDkeys.next()) {
System.out.println("Generated ID="+rstIDkeys.getInt(1));
}

rstIDkeys.close();
pstmBatch.clearBatch();
pstmBatch.close();

conn.commit();
conn.close();
database.shutdown();
database.stop();
}

[Thomas Mueller]

Hi,

I have run your test case against PostgreSQL, MySQL, Derby, HSQLDB,
and H2. Both PostgreSQL and HSQLDB don't support getGeneratedKeys. The
behavior of Derby is the same as H2. MySQL does support what you have
described. Do you know if this is supported by other databases? I will
add this feature to the roadmap, but don't plan to implement it in the
near future. The documentation of getGeneratedKeys is already correct,
it says: "Return a result set that contains the last generated
autoincrement key for this connection... return the result set with
one row and one column containing the key"

Regards,
Thomas

[mps]

Hi Thomas!

Thanks your answer.

Pls, increment priority :-D, because:

* Mysql: MySQL Connector/J 05-27-04 - Version 3.0.13-production
Bug #3873 Statement.getGeneratedKeys method returns only 1 result
for batched insertions
- Fixed BUG#3873 - PreparedStatement.getGeneratedKeys() method returns
only 1 result for batched insertions
http://bugs.mysql.com/bug.php?id=3873

*Postgresql patch (02-15-08):
http://pgfoundry.org/tracker/index.php?func=detail&aid=1010291&group_id=1000224&atid=856

Thx, mps

On máj. 9, 18:57, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> I have run your test case against PostgreSQL, MySQL, Derby, HSQLDB,
> and H2. Both PostgreSQL and HSQLDB don't support getGeneratedKeys. The
> behavior of Derby is the same as H2. MySQL does support what you have
> described. Do you know if this is supported by other databases? I will
> add this feature to the roadmap, but don't plan to implement it in the
> near future. The documentation of getGeneratedKeys is already correct,
> it says: "Return a result set that contains the last generated
> autoincrement key for this connection... return the result set with
> one row and one column containing the key"
>
> Regards,
> Thomas
>

> On Fri, May 9, 2008 at 10:48 AM, mps <molnar.peter.san...@gmail.com> wrote:
>
> > old msg removed

[Chris Schanck]

I would point out that most of the time, if you are using Sequences
for your number generation, it is *much* more efficient to get a bulk
set of sequence identifiers upfront and then add your rows in batch.
Postgres has this wonderful function generate_series(start,stop,step)
which you can use to get a batch of sequences real fast. This let me
do 1 select to get a couple thousand ids, then 1 batched insert to
insert the couple thousand rows. Very efficient. Same thing works for
oracle, though you have to go through some strange gyrations to
generate an arbitrary row set.

The postgres lets me do:

select nextval('sequencename') from (select generate_series(1,?)) as temp;

to get 10 ids in a single statement. Very nifty.

Thomas, is there an analogous way to project a set of rows in H2?

[Thomas Mueller]

Hi,

> select nextval('sequencename') from (select generate_series(1,?)) as temp;

In H2, you could do this:

create sequence sequencename;
select next value for sequencename from system_range(1, 100);

Unfortunately, system_range doesn't support parameters at the moment.
I will fix this for the next release.

Regards,
Thomas