From: | Dave Cramer <Dave(at)micro-automation(dot)net> |
---|---|
To: | Daryl Beattie <dbeattie(at)insystems(dot)com> |
Cc: | "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: Sequence ID from INSERT |
Date: | 2002-07-23 15:16:45 |
Message-ID: | 1027437405.30768.21.camel@inspiron.cramers |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Daryl,
your second example where you get the id first is the only way I know
how to do it
BTW it doesn't have to be done in a transaction. the id will not be used
by another connection.
DAve
On Tue, 2002-07-23 at 11:05, Daryl Beattie wrote:
> Dear Postgresql-JDBCers,
>
> Let's say I have created a table like this:
>
> CREATE TABLE Food (
> Id SERIAL PRIMARY KEY,
> Name VARCHAR(10)
> );
>
> What is the best way to use JDBC to insert a row into the database
> and get the Sequence Id right out of it? I don't want to start a transaction
> and have to do another query that looks like this:
>
> BEGIN;
> INSERT INTO Food (Name) VALUES ('Apple');
> SELECT currval('Food_Id_seq');
> COMMIT;
>
> Or ones like these:
>
> BEGIN;
> new_id = output of "SELECT nextval('Food_Id_seq')";
> INSERT INTO Food (Id, Name) VALUES (new_id, 'Apple');
> COMMIT;
>
> BEGIN;
> INSERT INTO Food (Name) VALUES ('Apple');
> SELECT MAX(Id) FROM Food; -- will check ResultSet of this separate query.
> COMMIT;
>
> I want to do it in one statement. Normally I would
> setAutoCommit(false), execute two separate statements, and then commit().
> However, I was wondering if there's a way to do all this in a ... "cleaner"
> fashion? Can anybody provide me with a sample?
>
> Thanks,
>
> Daryl.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas De Vos | 2002-07-23 16:03:05 | Re: Sequence ID from INSERT |
Previous Message | Daryl Beattie | 2002-07-23 15:05:51 | Sequence ID from INSERT |