Re: transactions, serial ids, and JDBC

Will there every be something akin to Oracle's INSERT ... RETURNING
<fieldname>. In all our Oracle apps we used Triggers on our pk cols to
populate with the next sequence value. The app code ( java, php, pl/sql,
whatever ) would use the INSERT ... RETURNING syntax, let the Trigger and
sequence handle the id and assign the returned value to a local variable,
which could then be referenced as needed to UPDATE, DELETE, SELECT,
whatever, the target record by pk.

Paul

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Neil Conway
Sent: Wednesday, August 07, 2002 15:46
To: gss+pg(at)cs(dot)brown(dot)edu
Cc: PostgreSQL general mailing list
Subject: Re: [GENERAL] transactions, serial ids, and JDBC

Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu> writes:
> I don't know how to dependably get the id from the last insert. One
> possibility, I suppose, is to call nextval myself and use the value
> explicitly, but if there is a way to do it portably (i.e. not depending on
> PostgreSQL's specific implementation of a self-incrementing id field) I
> would prefer it.

Use currval() to get the last ID produced by a sequence. AFAIK most
databases implement a concept similar to sequences, but it's not
standardized -- i.e. it will be difficult or impossible to use the
same technique with different database systems.

> Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a
> long text string with all of this, or do I need to send each line
> (including BEGIN and END) as a separate Statement?

Either way will work.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org