[Zope-DB] Strange problem with ZSQL Method called from another ZSQL Method

Stefan Schmid stschmid@iew.unizh.ch
Thu, 22 Nov 2001 13:57:01 +0100


Hi,

I have recently started to use Zope with MySQL and have encountered some
strange problems with ZSQL methods. I needed a mechanism to generate a
unique id (sequence number) that I can use in a later INSERT statement. So I
created a single-row table 'objectid' containing a value 'oid' that is
updated each time I want the next value in the sequence.

# ZSQL Method next_objectid
UPDATE objectid SET oid = LAST_INSERT_ID(oid+1)
<dtml-var sql_delimiter>
SELECT LAST_INSERT_ID() AS oid FROM objectid

This ZSQL method works as expected when called from a DTML Document but
calls from a second ZSQL method (i.e. 'insert_test') generate a sequence of
numbers that increase by 2 each time.

# ZSQL Method insert_test
INSERT INTO test(oid, descr)
VALUES (<dtml-var expr="next_objectid()[0].oid">, "Test")

Is anybody noticing anything similar, or if you have any opinion on what
might be going on, please reply.

I'm using Zope 2.4.1, ZMySQLDA 2.0.7, MySQLdb 0.9.0 and MySQL 3.23.41 on
WinNT 4.0 SP6.

Thanks,

Stefan