Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-12 Thread Andrew Sullivan
On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote: I talked with the author or SQLObject about this recently and I thnk he's implementing this correctly, by querying the cursor for the last OID?: That won't scale unless you index oid. And your tables will all need oids, which is

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-12 Thread Greg Stark
scott.marlowe [EMAIL PROTECTED] writes: select tablename.fieldname.currval; That syntax would be problematic, it would mean to select all rows from tablename and evaluate fieldname.currval for each one. Actually it's worse, it would be confused with schemas I think. The postgres-ish way to do

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-12 Thread Doug McNaught
Scott Chapman [EMAIL PROTECTED] writes: On Wednesday 12 November 2003 11:29, Doug McNaught wrote: Scott Chapman [EMAIL PROTECTED] writes: It would be nice if PostgreSQL could return the primary key it inserted with but that may not be a fool-proof solution either. Is there a nice way

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-12 Thread Doug McNaught
Scott Chapman [EMAIL PROTECTED] writes: It would be nice if PostgreSQL could return the primary key it inserted with but that may not be a fool-proof solution either. Is there a nice way to handle this situation? Write a database function that inserts the record and returns the primary key

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-10 Thread Alvaro Herrera
On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote: Chronological events here: X inserts a new record into A. Y inserts a new record into A. X fetches currval of the SA. What value does X get in this case, the one from X's insert or Y's? X's. -- Alvaro Herrera

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-10 Thread Scott Chapman
On Monday 10 November 2003 08:23, David Green wrote: Are X Y two different connections? If you execute 2 statements on the same connection and then get currval() it will give the last generated id. Ex. On 1 connection: INSERT INTO A (fld) VALUES (val); -- id generated = 1 INSERT INTO A

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-10 Thread Kathy Zhu
I saw this method of Statement class in jdbc. Will the return int contain the autogenerated key value ?? public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException thanks, kathy Scott Chapman wrote: On Monday 10 November 2003

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-09 Thread Andreas Fromm
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martijn van Oosterhout wrote: After you've done the insert on the address table, you can use currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have to have used nextval() for the original insert. Hope this helps, ..going to

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-09 Thread Scott Chapman
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote: After you've done the insert on the address table, you can use currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have to have used nextval() for the original insert. What if someone else inserts another address

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-09 Thread Doug McNaught
Scott Chapman [EMAIL PROTECTED] writes: On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote: After you've done the insert on the address table, you can use currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have to have used nextval() for the original insert.

Re: [GENERAL] SQL-question: returning the id of an insert querry

2003-11-09 Thread Alvaro Herrera
On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote: On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote: After you've done the insert on the address table, you can use currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have to have used nextval() for