Re: JDBC keygen select

Dave,

Thanks for the info. Right now I'm issuing a select currval('foo') after the insert to get the generated PK. This works but the extra SQL round trip slows down insert performance quite a bit.

Mike

> -----Original Message-----
> From: Dave Cramer [mailto:pg(at)fastcrypt(dot)com]
> Sent: Monday, October 24, 2005 11:20 AM
> To: Mike Clements
> Cc: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] 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
> >
> >
>
>
>