Re: LAST_INSERT_ID equivalent

It's going back a bit but I don't see any post replying to this in the hundreds
I have left unread in that time, so...

On Thu, 12 Jun 2003, Arjen van der Meijden wrote:

> When you can't use a transaction or don't want to use curval, you can
> use this:
>
> rowsUpdated = st.executeUpdate(); // Here's your insert
> if(!update) // Update was just a boolean I used to differentiate between
> updates and inserts, it's from a generic function
> {
> int lastOid =
> ((org.postgresql.jdbc1.AbstractJdbc1Statement)st).getInsertedOID();
> String oidQuery = "SELECT " + idcolumn + " FROM " + table + " WHERE
> oid = " + lastOid;
> Statement oidSt = db.createStatement();
> ResultSet oidRs = oidSt.executeQuery(oidQuery);
> if(oidRs.next())
> {
> generatedKey = oidRs.getInt(1);
> }
> }
>
> It's what I used to be a bit more certain about the curval and allowing
> to forget about transactions if necessary :)

1) While the oid method may well work it will _only_ work on tables that have
an oid field.

2) currval() has nothing to do with transactions, you can do a
nextval() followed by however many begin, commit or rollback statements you
desire and currval() will give you the same thing.

> There is in JDBC3 a function specified to retrieve the last generated
> key on a connection, but afaik it is still not implemented in
> postgresql's JDBC-driver.
>

If you're looking to actually have a suitable method in your jdbc objects why
not simply code up your requirements in derived class and use that instead? I'd
have thought that was a near perfect example of object orientation.

--
Nigel J. Andrews

[rest of message follows...]

> Arjen
>
> > -----Oorspronkelijk bericht-----
> > Van: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens Erik Price
> > Verzonden: donderdag 12 juni 2003 19:15
> > Aan: pgsql-general(at)postgresql(dot)org
> > Onderwerp: [GENERAL] LAST_INSERT_ID equivalent
> >
> >
> > I have a table with a SEQUENCE on it that increments the
> > primary key (a
> > BIGINT column) of the table whenever a new insert is performed.
> >
> > Is there a way to determine the last incremented value, so
> > that if I do
> > an insert, I can record the primary key of the record somewhere? I'm
> > interested in any technique for doing this, but especially a
> > JDBC-specific solution.
> >
> > Sorry if the answer should be obvious but I am coming from MySQL and
> > trying to learn the ANSI equivalent of the MySQL features.
> >
> >
> >
> > Thanks,
> >
> >
> > Erik
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> > majordomo(at)postgresql(dot)org
> >