Re: JDBC keygen select - Mailing list pgsql-jdbc

When Postgres eventually does support keygen as you mentioned, which form is more likely to be supported?

ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"});

OR

ps = itsDBConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

In the first, the caller declares which columns he wants back so there is no possibility of any ambiguity. In the
second,the caller relies on the database to decide which column will be returned, and we hope the DB knows which one
thecaller wants (most likely the primary key column). 

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
> Sent: Monday, October 24, 2005 12:51 PM
> To: Dave Cramer
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] 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@fastcrypt.com]
> > Sent: Monday, October 24, 2005 11:20 AM
> > To: Mike Clements
> > Cc: pgsql-jdbc@postgresql.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@actional.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
> > >
> > >
> >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>