Well this is not really a Zope question but since I just ported an sqlserver7 app to mySQL, I have the answer. 1) Name the auto_increment column in the column list 2) supply 0 or null as the value 3) To get the last_insert_id, the sytax is a little different, see below. Yours: INSERT INTO FileTypes (FileType, Extension) VALUES ("AutoCAD","DWG") <dtml-var sql_delimiter> SELECT LAST_INSERT_ID() AS LastID Changed (where filetypes_id is the name of your auto_increment column): INSERT INTO FileTypes (filetypes_id,FileType, Extension) VALUES (0,"AutoCAD","DWG") <dtml-var sql_delimiter> SELECT filetypes_id where filetypes_id=LAST_INSERT_ID() This was one of several issues I had to overcome, thank-you google.
------------ Original Message ----------- From: paul_s_johnson@urscorp.com <paul_s_johnson@urscorp.com> Date: Tue, 10 Apr 2001 12:36:17 -0500
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
_______________________________________________ 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 )