[Zope] ZMySQLDA and ZSQLMethods

[Phil Harris]

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 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 )
>>    
>>
>
>
>_______________________________________________
>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 )
>  
>