Support for getting generated keys.

[Junyan Luo]

Although driver 8.4 is supposed to support getting generated keys,
this issue still remains quite unclear for most people (including me)
due to the lack of documentation. I have explored older threads for a
while and figured out the current workaround and here is my findings.
Please point out any mistake I made:
1. For a Statement, you MUST use the "RETURNING" clause in a "INSERT"
statement. The code to get generated keys will be like:
       ResultSet keys = Statement.executeQuery("INSERT...RETURNING [id
column]"); // Make sure it's executeQuery() not executeUpdate()
       keys.next();  //The result set contains generated keys;
       int key = keys.getInt(1);
However, Statement.getGeneratedKeys() does NOT work for Statement (Am
I correct?)
2. For a PreparedStatement, you can EITHER use the approach above, OR
use Statement.getGeneratedKeys(), and in the latter case you have to
make sure you use Connection.prepareStatement(sql, new String[]{"id
column"}) to indicate the NAME of your id column. Setting id column by
indexes are not supported;
3. Unfortunately, batch update is still not supported in EITHER case.
I tried to use a preparedStatement created with a id column in a batch
update situation, and it always give me this exception:
       java.sql.BatchUpdateException: Batch entry 0 INSERT INTO
test.keytest (name) VALUES ('my name') RETURNING "id" was aborted.
Am I missing anything? Thanks!

REGARDS,
Junyan

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Kris Jurka]

On Fri, 25 Sep 2009, Junyan Luo wrote:

> Although driver 8.4 is supposed to support getting generated keys,
> this issue still remains quite unclear for most people (including me)
> due to the lack of documentation. I have explored older threads for a
> while and figured out the current workaround and here is my findings.
> Please point out any mistake I made:
>
> 1. For a Statement, you MUST use the "RETURNING" clause in a "INSERT"
> statement. The code to get generated keys will be like:
>       ResultSet keys = Statement.executeQuery("INSERT...RETURNING [id
> column]"); // Make sure it's executeQuery() not executeUpdate()
>       keys.next();  //The result set contains generated keys;
>       int key = keys.getInt(1);
> However, Statement.getGeneratedKeys() does NOT work for Statement (Am
> I correct?)

You don't need to use RETURNING and executeQuery.  You can use a plain
insert statement and executeUpdate(sql, new String[]{"id"}) and the
results will be available via getGeneratedKeys.

Kris Jurka

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc