Re: LAST_INSERT_ID equivalent - Mailing list pgsql-general

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@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] Namens Erik Price
> > Verzonden: donderdag 12 juni 2003 19:15
> > Aan: pgsql-general@postgresql.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@postgresql.org
> >