[Zope] LAST_INSERT_ID and ZMySQLDA

[paul_s_johnson@urscorp.com]

Thanks for the tip!

I had to do one other thing to get it work (this will hopefully help
someone else down the road):

When I did the SELECT this way:

SELECT LAST_INSERT_ID() AS LastID

I got this error:

Error, _mysql.OperationalError: (1064, "You have an error in your SQL
syntax near 'LIMIT 1000' at line 1")

So I changed the "Maximum number of rows retrieved" property to zero to
make that go away.

So it still did not work when I tried your suggestion:

SELECT filetypes_id where filetypes_id=LAST_INSERT_ID()

However when I switched the "Maximum number of rows retrieved" property to
something greater than zero it worked!

Apparently the former SELECT statement is incompatible with the LIMIT
clause that is tacked on by the "Maximum number of rows retrieved" property
being greater than zero.  Stated the way you suggest makes it compatible
with a LIMIT clause.

Thanks again.

P.S. For me it was not necessary to explicitly insert a NULL or 0 value
into the AUTO_INCREMENT field.



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 )