Woohoo! Thanks for the suggestion; it worked ;-)
You're now falling foul of the limit zope puts on the maximum numbers of rows returned from a zsql query by default.
I never would have thought that. Perhaps I'm not understanding this correctly, but doesn't select last_insert_id() only return one row? Then why do I need to unset the limit?
It's easily fixed, goto the advanced tab in the manage view of the zsqlQuery, and set the limit to 0 (zero).
Thanks for the tip. Andy
hth
Phil
Andrew Altepeter wrote:
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@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@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@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@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 )