JDBC keygen select - Mailing list pgsql-jdbc

Hello,

I'm a newbie on PostGRE but have experience using SQL Server & Oracle
via JDBC. Something that works fine on these does not work in PostGRE so
I'm looking for advice.

In my schema all primary keys are integers and the DB automatically
assigns values (using sequences or identities). When I insert into these
tables via JDBC I do not specify any value for the primary key, and I
use the RETURN_GENERATED_KEYS flag so the generated key is provided in
the RecordSet returned from the insert command - something like this:

pStmt = itsDbConn.prepareStatement(
    "insert into tbl (col1) values (?)",
    Statement.RETURN_GENERATED_KEYS);
pStmt.setString(1, "foo");
count = pStmt.executeUpdate();
if(count > 0)
{
    rs = pStmt.getGeneratedKeys();
    rs.next();
    pk = rs.getLong(1);
}

This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
latter, it throws an exception in prepareStatement() saying "this method
is not yet implemented".

So my question is, how does one do this? This keygen approach is
important for performance, scalability and robustness. Launching a
separate SQL command to fetch the generated key has performance
problems. Self-generating the keys has problems with concurrency across
multiple clients.

Thanks

Michael R. Clements
Principal Architect, Actional Corp.
mclements@actional.com
FREE! Actional SOAPstation Developer Version
Web services routing, security, transformation and versioning
http://www.actional.com/sstdownload