[Zope] ZMySQLDA and ZSQLMethods

[Andrew Altepeter]

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