Re: [Zope-DB] sql query works in database connector but not in ZSQL method
robert rottermann wrote at 2007-1-17 15:42 +0100: > ... >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: Apparently, this error message comes from the "mysql" server. The most natural way to find out about this error is therefore, to consult the MySQL documentation to learn under what circumstances this error is raised. -- Dieter ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
Am 17.01.2007, 18:19 Uhr, schrieb Re: [Zope-DB] sql query works in database connector but not in ZSQL methodrobert rottermann <[EMAIL PROTECTED]>: query = "CALL selectVertexProperties(%s, @error2)" % dbid - - problem>> result = db.query((query).replace(';', sql_delimiter)) return result Call db.execute(SQL_string, (*paras)) instead. ie. mySQL = """CALL selectVertexProperties(?, @error2)""" db.execute(mySQL, (username, )) Not sure if you can combine SQL statements with ";" like this (which I don't think you should do anyway) but you should be able to create your SQL statements independent of the parameters. NB. "?" is the ODBC standard for placeholder. Most Python drivers use "%s" but this can cause confusion, ie. db.execute("SELECT * FROM table WHERE user = %s" ,(username, )) is not the same as db.execute("SELECT * FROM table WHERE user = '%s'" %(username, )) In the first case it is the responsibility of the ODBC driver to pass the parameter correctly. In the second case you are generating the entire query and passing it to the ODBC driver. Not only is this less efficient but it is also error prone and dangerous because it is open to SQL injection. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > > 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 > I tried that, it did not help BUT, I then looked at the console output. this is what I get (without the extra commit()) 2007-01-17 17:59:41 CRITICAL txn.1082132800 A storage error occurred during the second phase of the two-phase commit. Resources may be in an inconsistent state. eGenix mxODBC Zope DA: WARNING: could not rollback the transaction - the data source does not support transactions; this may result in data inconsistencies ! 2007-01-17 17:59:41 ERROR Zope.SiteErrorLog http://localhost:9880/scout/scout/AAUserInfo Traceback (innermost last): Module ZPublisher.Publish, line 115, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 41, in call_object Module Shared.DC.Scripts.Bindings, line 311, in __call__ Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec Module Products.PythonScripts.PythonScript, line 325, in _exec Module None, line 2, in AAUserInfo - - Line 2 Module Products.RedScout.RedScoutTool, line 96, in getUserDataById Module transaction._transaction, line 380, in commit Module transaction._transaction, line 378, in commit Module transaction._transaction, line 441, in _commitResources Module transaction._transaction, line 563, in tpc_finish Module Shared.DC.ZRDB.TM, line 60, in tpc_finish Module Products.mxODBCZopeDA.ZopeDA, line 1126, in _finish Module Products.mxODBCZopeDA.ZopeDA, line 999, in errorhandler OperationalError: ('HYT00', 2014, "[unixODBC][MySQL][ODBC 3.51 Driver]Commands out of sync; you can't run this command now", 1199) so it seems to be a transaction error caused by zope. I *think*, autocommit is not enabled. however, the odcb driver complains that the datasource does not support transactions. I just wonder if I have to enable that specifically. (my odbc knowledge i VERRY small). robert -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrlxwGaryJ0T9kUYRAiqGAJ4x12oZMQs4geNvw4LCVJT7prRuGQCfUEUV 2GJ5vG0NjtOP+Gz7hyEko9Q= =2okG -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Charlie Clark schrieb: > Am 17.01.2007, 17:57 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: > >> I of course can send them into the interface as integer, but the >> procedure is called with a sting (at least the error suggests that) >> - -->{call selectVertexProperties('1')}' at line 1", 6088) > > Remember you can use bound parameters with our DA so you don't have to > worry about type conversion. This is not the case if you use query!!! > > What is the exact code for your ExternalMethod? > > Charlie > > sorry to sound thick: how do I execute a query without calling db.query(..) ? this is what I do without using zope: - - def findUser(username): query = "CALL selectVertexIdFromLogin('%s', @id, @error1);" \ "CALL selectVertexProperties(@id, @error2)" % username cursor.execute(query) cursor.nextset() print cursor.fetchall() this is what I try to do in a method in a Zope product of ours: - --- def getUserDataById(self, userid=''): "return base user data for user. if userid is not given use looged in user" db = self.redscout_tool.scout_connection() sql_delimiter = self.sql_delimiter if not userid: userid = self.portal_membership.getAuthenticatedMember().getId() query = "CALL selectVertexIdFromLogin('%s', @id, @error); select @error, @id" % userid result = db.query((query).replace(';', sql_delimiter)) if result: result = result[1][0] error = int(result[0]) if error: "handle error" return 'fehler' else: dbid = int(result[1]) query = "CALL selectVertexProperties(%s, @error2)" % dbid - - problem>> result = db.query((query).replace(';', sql_delimiter)) return result robert -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrlqiGaryJ0T9kUYRAjpWAJoDM/VSPqawNs/+pM0IMT0/sUAS9wCdHt8B 5FZsyOj4J1QkcEXGDtcbUoU= =Jq2W -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
Am 17.01.2007, 17:57 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: I of course can send them into the interface as integer, but the procedure is called with a sting (at least the error suggests that) - -->{call selectVertexProperties('1')}' at line 1", 6088) Remember you can use bound parameters with our DA so you don't have to worry about type conversion. This is not the case if you use query!!! What is the exact code for your ExternalMethod? Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Charlie Clark schrieb: > Am 17.01.2007, 17:14 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: > >> If tried that, but somehow I did not get it rigth: >> questions: >> - - the first parameter is an integer, how do I force that? > > int(para) ? > I of course can send them into the interface as integer, but the procedure is called with a sting (at least the error suggests that) - -->{call selectVertexProperties('1')}' at line 1", 6088) robert -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrlVcGaryJ0T9kUYRAsKbAJoCrwSuF6YBYJpTUZazIlD/66rRqgCeOBhR N5uulcNxNGEpY7qjrKzdacI= =mBgs -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
On 2007-01-17 17:42, Charlie Clark wrote: > Am 17.01.2007, 17:14 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: > >> If tried that, but somehow I did not get it rigth: >> questions: >> - - the first parameter is an integer, how do I force that? > > int(para) ? > >> - - the second parameter is an out parameter, how do I specify that ? > > You don't, I think we don't support them currently. That's correct. You will have to wrap the stored procedure you're calling in another one that returns the data via a standard SELECT and then access the value using the result set. -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Jan 17 2007) >>> Python/Zope Consulting and Support ...http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ...http://python.egenix.com/ ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
Am 17.01.2007, 17:14 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: If tried that, but somehow I did not get it rigth: questions: - - the first parameter is an integer, how do I force that? int(para) ? - - the second parameter is an out parameter, how do I specify that ? You don't, I think we don't support them currently. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
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 ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
-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 > thanks for your answer, here is what I do in a plain python script (using MySQLdb): def findUser(username): query = "CALL selectVertexIdFromLogin('%s', @id, @error1);" \ "CALL selectVertexProperties(@id, @error2)" % username cursor.execute(query) cursor.nextset() print cursor.fetchall() this is what I try to do in a method in a Zope product of ours: def getUserDataById(self, userid=''): "return base user data for user. if userid is not given use looged in user" db = self.redscout_tool.scout_connection() sql_delimiter = self.sql_delimiter if not userid: userid = self.portal_membership.getAuthenticatedMember().getId() query = "CALL selectVertexIdFromLogin('%s', @id, @error); select @error, @id" % userid result = db.query((query).replace(';', sql_delimiter)) if result: result = result[1][0] error = int(result[0]) if error: "handle error" return 'fehler' else: dbid = int(result[1]) query = "CALL selectVertexProperties(%s, @error2)" % dbid - ->> result = db.query((query).replace(';', sql_delimiter)) return result thanks for your help robert -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrkw9GaryJ0T9kUYRArRdAJ9KR+vSS8oF8zIdmkSXEdBJ3d4p9wCfaVtb e1vDt07FPzJMYh0T4PqgEl4= =NbWY -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Charlie Clark schrieb: > Am 17.01.2007, 15:42 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: > >> 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: > > Robert, > > you might have to call this using the callproc() method of the DA in an > ExternalMethod. > > Charlie > If tried that, but somehow I did not get it rigth: questions: - - the first parameter is an integer, how do I force that? - - the second parameter is an out parameter, how do I specify that ? thanks robert ('23000', 1064, "[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-5.0.26]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{call selectVertexProperties('1')}' at line 1", 6088) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrktyGaryJ0T9kUYRAv7xAJ9fTQ819q6dpQELO4Y3Ix/LB7ARjgCfTmRx tA3og1VuWZnRmDqpcjhUqdY= =S1mn -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
Am 17.01.2007, 15:42 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: 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: Robert, you might have to call this using the callproc() method of the DA in an ExternalMethod. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] sql query works in database connector but not in ZSQL method
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 ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
[Zope-DB] sql query works in database connector but not in ZSQL method
-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 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFrjW8GaryJ0T9kUYRAj3iAJ0ccFcfy/y3q4eQs+80ZfDhQFuyfwCePLG+ wjLmLUZmHOvnkEkygo82HJA= =Jq1+ -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db