ZMySQLDA and ZSQLMethods
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
Andrew Altepeter wrote at 2003-10-28 08:40 -0600:
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.
Any "select" returns an instance that behaves like a sequence. It never returns an integer. Thus, you should be able to access the id by "result[0].id" (when "result" is the result of your Z SQL Method). -- Dieter
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 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 )
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 )
Andrew, You're now falling foul of the limit zope puts on the maximum numbers of rows returned from a zsql query by default. It's easily fixed, goto the advanced tab in the manage view of the zsqlQuery, and set the limit to 0 (zero). 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 )
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 )
Andrew, It does only return one row, but, and here's the rub of it all, to limit the number of rows returned, zope tries to use the native rdbms facilities. Since MySQL has a LIMIT part to it's SELECT statement the sql that zope finally sends to MySQL looks like this: SELECT last_inserted_id() LIMIT 1000; Which is invalid as far as MySQL is concerned, *BUT* you actually want: SELECT last_inserted_id(); with no LIMIT statement. Zope does the limit bit regardless of how many rows would be returned, cos at the time it's building the sql statement it has no idea how many will be returned. It does what we tell it to. Setting the limit to 0 means that we want no limit, therefore zope doesn't put the LIMIT 1000 onto the end of the sql. daadaa :¬) Hope that helps Phil Andrew Altepeter wrote:
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?
<big snip>
participants (3)
-
Andrew Altepeter -
Dieter Maurer -
Phil Harris