[Zope] ZSQL retreive auto_increment id inserted [Q]

Sam Gendler sgendler@teknolojix.com
Fri, 19 Nov 1999 09:29:20 -0800


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