Re: INSERT and get ID

On 27-Jul-05, at 8:56 AM, Peter(dot)Zoche(at)materna(dot)de wrote:

> Hi!
>
> Say I have done the following:
>
> CREATE SEQUENCE myseq
>
> CREATE TABLE foo (
> id INTEGER,
> val INTEGER,
> PRIMARY KEY( id ) );
>
> Is there an easy way to get the id of the newly inserted data set?
> So if I do the following:
>
> String insert = "INSERT INTO foo (id, val)
> VALUES( nextval('myseq'), 5 )";
> int count = statement.executeUpdate( insert );
>
> I only get the row count of inserted rows. Do I have to query the
> database again to get the id? Or is there another way? I have tried
> statement.executeUpdate( insert, Statement.RETURN_GENERATED_KEYS);
> but I get an PSQLException saying that the feature of auto-
> generated keys
> is not supported. (using postgresql 8.0 JDBC3) I googled but I did
> only find
> PostgreSQL-
> specific hints, no one for JDBC.
you can use select currval('myseq') after using nextval('myseq') this
will retrieve the value recently returned by nextval
in your session ( so it will not be over written by another connection )

>
> Thanks, Peter
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>
>