[Zope] ZMySQLDA and ZSQLMethods

[Andrew Altepeter]

On Tue, 2003-10-28 at 14:51, Phil Harris wrote:
> Andrew,
> 
> Think what this query actually does:
> 
> SELECT LAST_INSERT_ID() as id from cc_transactions
> 
> It does a select last_insert_id() for every row in cc_transactions.
> 
> therefore, you should get a list of ids.
> 
> I presume you really wanted,
> 
> select last_insert_id() as an_id

Ok; I've changed the zsql method to this:

INSERT INTO cc_transactions(acct,who,total,creationtime)
  VALUES(<dtml-sqlvar account type="string">,
         <dtml-sqlvar who type="string">,
         <dtml-sqlvar total type="float">,
         NOW())
<dtml-var sql_delimiter>

select last_insert_id() as an_id

And now I get this error:

Error Type: ProgrammingError
Error Value: (1064, "You have an error in your SQL syntax near 'LIMIT
1000' at line 1")
Error info:

With the helpful traceback:
Module ZPublisher.Publish, line 98, in publish
      * Module ZPublisher.mapply, line 88, in mapply
      * Module ZPublisher.Publish, line 39, in call_object
      * Module Shared.DC.ZRDB.DA, line 328, in manage_test
      * Module Shared.DC.ZRDB.DA, line 306, in manage_test
      * Module Shared.DC.ZRDB.DA, line 428, in __call__
      * Module Products.ZMySQLDA.db, line 284, in quer
      * 


> 
> 
> 
> 
> 
> Andrew Altepeter wrote:
> 
> >I have a zsql method that inserts data into a table with an
> >auto_increment column, and then calls select last_insert_id():
> >----------------------
> >INSERT INTO cc_transactions(acct,who,total,creationtime)
> >  VALUES(<dtml-sqlvar account type="string">,
> >         <dtml-sqlvar who type="string">,
> >         <dtml-sqlvar total type="float">,
> >         NOW())
> ><dtml-var sql_delimiter>
> >SELECT LAST_INSERT_ID() as id from cc_transactions
> >----------------------
> >
> >One would expect to get just one integer (well, id:integer) back from
> >calling this method.  However, I get a list of id:integers with every
> >id=actual last_insert_id, and a length equal to the number of rows in
> >the table.
> >
> >I haven't been able to recreate this behavior in a mysql shell client.
> >
> >Any ideas about what's going on?
> >
> >my mysql version is: 3.23.58
> >
> >Thanks,
> >Andy
> >
> >
> >_______________________________________________
> >Zope maillist  -  Zope at zope.org
> >http://mail.zope.org/mailman/listinfo/zope
> >**   No cross posts or HTML encoding!  **
> >(Related lists - 
> > http://mail.zope.org/mailman/listinfo/zope-announce
> > http://mail.zope.org/mailman/listinfo/zope-dev )
> >  
> >
> 
> 
> _______________________________________________
> Zope maillist  -  Zope at zope.org
> http://mail.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://mail.zope.org/mailman/listinfo/zope-announce
>  http://mail.zope.org/mailman/listinfo/zope-dev )