[Zope] LAST_INSERT_ID and ZMySQLDA

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 )