[Zope-DB] sql query works in database connector but not in ZSQL method

[Kevin Campbell]

On Wed, Jan 17, 2007 at 05:18:05PM +0100, robert rottermann wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Kevin Campbell schrieb:
> > On Wed, Jan 17, 2007 at 03:42:04PM +0100, robert rottermann wrote:
> >> -----BEGIN PGP SIGNED MESSAGE-----
> >> Hash: SHA1
> >>
> >> Hi there,
> >>
> >> I try the following statement:
> >> CALL selectVertexProperties(1, @error2)
> >>
> >> when I execute this directly in the test "window" of the database
> >> connection, it works fine.
> >> when i execute the same line in the test window of a ZSQL Method
> >> then I get an error:
> >>
> >> Error, Products.mxODBCZopeDA.ZopeDA.ReplayTransaction: OperationalError
> >> on : ('HYT00', 2014, "[unixODBC][MySQL][ODBC 3.51
> >> Driver][mysqld-5.0.26]Commands out of sync; you can't run this command
> >> now", 6113) SQL used:
> >>
> >> CALL selectVertexProperties(1, @error2)
> >>
> >> when I call this method from plain python, it works fine also.
> >>
> >> Any pointers what could be the reason would be greatly appreciated.
> > 
> > Robert,
> > 
> > Can you give an example of how you call this in plain python? It may be
> > that in using plain python you are starting a new transaction, and there
> > are previous sql commands which are causing problems. Perhaps some more
> > details of the sql statements issued would be helpful also.
> > 
> > Kevin

Robert,

Looks very much like an issue due to transactions. Are you using auto-commit 
on either the MySQLdb connection or the zope database adapter? Perhaps
you could test the issue by placing get_transaction().commit() before
the start of your zope database code, and get_transaction().commit() at
the end. Please don't do this in production code, this is just to
isolate the issue.

Regards,
Kevin