#39 getGeneratedKeys when using executeBatch

[Anthony Whitford]

I would like the ability to use getGeneratedKeys when
inserting several new records using executeBatch.
Presently, an attempt to do this results in a
BatchUpdateException saying "A statement attempted
to return a result set in executeBatch()."

I would expect that executeBatch would work and then
getGeneratedKeys would return me a result set of the
identities for the rows that I inserted. (Why else would
getGeneratedKeys return a ResultSet over something
like an Object or int?)

I understand that this works with mySQL. As a result,
sort of expected it to also work with SQL Server via
JTDS. Of course, I think this capability is only
important if it is being done efficiently.

I would expect that the TDS protocol should provide a
mechanism such that if you declare that you are
sending a preparedStatement requiring generated keys,
that the protocol responds with success and the
generated key(s). Alas, I get the impression
that "select @@identity" is the mechanism to acquire
keys. I would have expected something more efficient
for TDS 8.0.

If I want to insert many rows into a table with an identity
column and need the generated identity value(s), I am
basically being forced to insert one by one rather than
take advantage of batch execution. This is a shame.

[Sam]

I would like to add that this issue is still pending after four years later. Can someone please suggest if we should wait for a fix or if there is an alternative solution.
Thanks

[momo]

The JDBC specs do not specify any way to get generated keys from batch operations so implementing such feature may not be a good idea.

That said, I somewhat agree that this might be useful under performance aspects and I'm aware of the fact that other (e.g. the MySQL) drivers do support exactly what you requested. I'll have to take a look how such feature could be implemented in jTDS before deciding whether it's worth the effort. Of course, if anyone is willing to propose a solution or provide a patch this request will gain higher priority.

[Nils Kilden-Pedersen]

The JDBC API returns a ResultSet on getGeneratedKeys, strongly indicating that it should work, even for batch inserts.
It's killing my insert performance that I can't execute this as batch.

[Lukas Eder]

The JDBC API returns a ResultSet on getGeneratedKeys, strongly indicating that it should work, even for batch inserts.

That's not what this implies. Returning a ResultSet mostly serves two purposes:

1. Being able to return rows, not just single values. Some databases (e.g. Oracle, HSQLDB) allow for returning arbitrary columns from INSERT or UPDATE statements
2. Being able to return multiple rows after a multi-row INSERT, e.g. INSERT INTO t SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL

So, there were other compelling reasons why getGeneratedKeys() returns a ResultSet, that aren't necessarily related with batch execution.

[Anonymous]

+1 for this feature

[Anonymous]

+1 for this feature

[Anonymous]

+1 for this feature

[Anonymous]

+1

[Anonymous]

Will this feature ever be implemented?

[Anonymous]

The discussion opened in 2005 and the issue is still not addressed by Microsoft...hmm... Wow...

[Anonymous]

Yes please!