Re: JDBC keygen select

Mike,

Well, until we get insert returning implemented (allegedly soon), the
protocol doesn't support returning any values without another round
trip to the db

So you have two choices here.

1) get the ID before and insert it ie select nextval('sequence_name')
and insert the value explicitly. If you cache sequences you can cache
them on the connection too ( more work )
2) get the ID after using currval('sequence_name')

P.S. Using PostGRE is frowned upon the name is either Postgres, or
PostgreSQL

Dave
On 22-Oct-05, at 4:57 PM, Mike Clements wrote:

> 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(at)actional(dot)com
> FREE! Actional SOAPstation Developer Version
> Web services routing, security, transformation and versioning
> http://www.actional.com/sstdownload
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>