In PostgreSQL there is a SERIAL data type that is an autoincrementing 4 byte integer. If you have a table called data1 with a field called recid that is created as a SERIAL type and a char field called name, you would add a record and get the recid like this: insert into data1 (name) values ('George Smith'); <dtml-var sql_delimiter> select last_value from data1_recid_seq; data1_recid_seq is a 1 row sequence table created when you create the data1 table with the recid field as a SERIAL type __________________________________________________________________ Jim Sanford . Database Engineer / \ / Accelerated Technology, Inc. / / 720 Oak Circle Drive East / / \ Mobile, AL 36609 / / \ Voice: 334-661-5770 fax: 334-661-5788 / \ E-Mail: jsanford@atinucleus.com Web: http://www.atinucleus.com Source Code, No Royalties, Any CPU...It just make sense ! __________________________________________________________________ ----- Original Message ----- From: chas <panda@skinnyhippo.com> To: Anthony Baxter <anthony@interlink.com.au>; Sam Gendler <sgendler@teknolojix.com> Cc: Bruno Mattarollo <brunomadv@ciudad.com.ar>; <zope@zope.org> Sent: Thursday, November 18, 1999 12:00 PM Subject: Re: [Zope] ZSQL retreive auto_increment id inserted [Q]
Sam Gendler wrote I didn't take a long look, but it looks as though this solution
requires
you to call a separate ZSQL query in order to retrieve the ID. Needless to say, there is a wide open race condition if someone else inserts another entry before you get the results from the second query. I was looking for something that returns the last_insert_id as the result set to an INSERT query, much the way the apis (for mysql anyway) do in other environments. Please correct me if I am wrong.
I guess this all depends how Zope shares database connections between multiple ZSQL methods that are called within the same DTML method.
eg. With straight CGI and MySQL, this was never a problem; even if 2 people executed the same CGI script, each script had its own database connection and insert_id()'s were specific to the database connection in which the last insert was done.
Now, does Zope dedicate the database connection to all the ZSQL scripts within a DTML method before handing it over to another DTML method ? If not, is there any way of forcing this ?
Anthony Baxter wrote : What I do with Oracle and Sequences is have multiple ZSQL methods: the first looks up the next value:
select sequence_name.nextval from dual
I take this number back, and use it in subsequence DTML and SQL code.
Sorry, you've lost me here - is this specific to Oracle ?
chas
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope No cross posts or HTML encoding! (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )