[Zope] LAST_INSERT_ID and ZMySQLDA

[paul_s_johnson@urscorp.com]

I have created a ZSQL method with an INSERT statement and a SELECT
statement containing a LAST_INSERT_ID function in it.  These statements are
separated by a <dtml-var sql_delimiter> tag.  This is a typical scenario in
which I want to grab the UNIQUE ID field value of the last record that was
inserted so I can use that value to insert a linked record in another
table.  The ZSQL method returns nothing!  If I understand this correctly
each ZSQL is a unique MySQL instance so these statements need to be in the
same ZSQL method in order to capture the correct LAST_INSERT_ID value,
right?

Here's my ZSQL Method
========================================
INSERT INTO FileTypes (FileType, Extension)
VALUES ("AutoCAD","DWG")
<dtml-var sql_delimiter>
SELECT LAST_INSERT_ID() AS LastID
========================================

This inserts a new record in the table and returns nothing.

I also double checked and yes there is an AUTO_INCREMENT field in the
FileTypes table.  I also tried using the alternate statement "SELECT * FROM
FileTypes" after the delimiter tag thinking that perhaps the LAST_INSERT_ID
() function was triggering the problem. This alternative returns nothing as
well.  It seems to me that you cannot have more than one statement in a
ZSQL method and have it return anything.

As a work-around I could use a MAX() function in a separate ZSQL method and
have it be correct 99.99% of the time since this is a low traffic site, but
I still prefer a method that is 100%.  Why doesn't this work? Aargh.

P. Johnson