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. Thanks for the effort, anyway
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.
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
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 )
At 01:01 PM 11/18/1999 -0600, Jim Sanford wrote:
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;
Aaah, clunk - it was that <dtml-var sql_delimiter> that I was missing. Works a treat and Anthony's description becomes clear. Thank you very much, Jim. Just for the record (no pun intended), the MySQL equivalent is then a rather simple : insert into data1 (name) values ('George Smith'); <dtml-var sql_delimiter> select last_insert_id() (Not certain if it requires the ugly patch to db.py in ZMySQLDA, as per previous email, but certainly works with it) chas
Does zope have some kind of locking around the ZSQLMethods, or is there a chance that another query could slip in between the first and the second query. If there is a guarantee in zope that a single ZSQLMethod will execute in a threadsafe manner, I am comfortable with it. I can make sure that there is no insert access from outside of zope. Of course, this becomes a problem againa as soon as you use Zeo for anything (something I was planning to do), since there will presumably be a different database connection from each zope instance, so no way to do this without locking of some sort. I can feel a switch to postgresql or oracle coming on... (Ugh!) --sam chas wrote:
At 01:01 PM 11/18/1999 -0600, Jim Sanford wrote:
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;
Aaah, clunk - it was that <dtml-var sql_delimiter> that I was missing. Works a treat and Anthony's description becomes clear. Thank you very much, Jim.
Just for the record (no pun intended), the MySQL equivalent is then a rather simple :
insert into data1 (name) values ('George Smith'); <dtml-var sql_delimiter> select last_insert_id()
(Not certain if it requires the ugly patch to db.py in ZMySQLDA, as per previous email, but certainly works with it)
chas
Sam Gendler wrote:
Does zope have some kind of locking around the ZSQLMethods, or is there a chance that another query could slip in between the first and the second query. If there is a guarantee in zope that a single ZSQLMethod will execute in a threadsafe manner, I am comfortable with it. I can make sure that there is no insert access from outside of zope. Of course, this becomes a problem againa as soon as you use Zeo for anything (something I was planning to do), since there will presumably be a different database connection from each zope instance, so no way to do this without locking of some sort. I can feel a switch to postgresql or oracle coming on... (Ugh!)
--sam
<CUT> It sounds a bit like you fear PostgreSQL (I don't know Oracle)...?! You have nothing to fear, I say. PostgreSQL is really nice to work with and lots of features that MySQL and others... ehh... has not. I dig PostgreSQL! -- Best regards / Mvh., Steen Suder sysadm kollegie6400.dk OpenSource --- Sign of the time
participants (5)
-
Anthony Baxter -
chas -
Jim Sanford -
Sam Gendler -
Steen Suder