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

2007-01-17 Thread robert rottermann 
-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

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

2007-01-17 Thread Kevin Campbell 
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

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

2007-01-17 Thread Charlie Clark 

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

2007-01-17 Thread robert rottermann 
-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

2007-01-17 Thread robert rottermann 
-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

2007-01-17 Thread 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
___
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

2007-01-17 Thread Charlie Clark 

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

2007-01-17 Thread M.-A. Lemburg 
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

2007-01-17 Thread robert rottermann 
-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

2007-01-17 Thread robert rottermann 
-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:
Products.mxODBCZopeDA.ZopeDA.DatabaseConnection DSN=myodbc thread
1082132800/1082132800 at 0x5b24710 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
   - PythonScript at /scout/scout/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

2007-01-17 Thread Dieter Maurer 
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