Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Zope: DB

sql query works in database connector but not in ZSQL method

 

 

Zope db RSS feed   Index | Next | Previous | View Threaded


robert at redcor

Jan 17, 2007, 6:42 AM

Post #1 of 21 (5061 views)
Permalink
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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


kev at logicalware

Jan 17, 2007, 6:41 AM

Post #2 of 21 (4938 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Jan 17, 2007, 7:33 AM

Post #3 of 21 (4950 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

Am 17.01.2007, 15:42 Uhr, schrieb robert rottermann <robert [at] redcor>:

> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 17, 2007, 8:14 AM

Post #4 of 21 (4941 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Charlie Clark schrieb:
> Am 17.01.2007, 15:42 Uhr, schrieb robert rottermann <robert [at] redcor>:
>
>> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 17, 2007, 8:18 AM

Post #5 of 21 (4945 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

-----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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


kev at logicalware

Jan 17, 2007, 8:24 AM

Post #6 of 21 (4955 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Jan 17, 2007, 8:42 AM

Post #7 of 21 (4945 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

Am 17.01.2007, 17:14 Uhr, schrieb robert rottermann <robert [at] redcor>:

> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


mal at egenix

Jan 17, 2007, 8:50 AM

Post #8 of 21 (4938 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

On 2007-01-17 17:42, Charlie Clark wrote:
> Am 17.01.2007, 17:14 Uhr, schrieb robert rottermann <robert [at] redcor>:
>
>> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 17, 2007, 8:57 AM

Post #9 of 21 (4954 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Charlie Clark schrieb:
> Am 17.01.2007, 17:14 Uhr, schrieb robert rottermann <robert [at] redcor>:
>
>> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Jan 17, 2007, 8:58 AM

Post #10 of 21 (4960 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

Am 17.01.2007, 17:57 Uhr, schrieb robert rottermann <robert [at] redcor>:

> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 17, 2007, 9:19 AM

Post #11 of 21 (4946 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Charlie Clark schrieb:
> Am 17.01.2007, 17:57 Uhr, schrieb robert rottermann <robert [at] redcor>:
>
>> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Jan 17, 2007, 9:25 AM

Post #12 of 21 (4955 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

Am 17.01.2007, 18:19 Uhr, schrieb Re: [Zope-DB] sql query works in
database connector but not in ZSQL methodrobert rottermann
<robert [at] redcor>:

> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 17, 2007, 9:27 AM

Post #13 of 21 (4960 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

-----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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


dieter at handshake

Jan 17, 2007, 2:26 PM

Post #14 of 21 (4938 views)
Permalink
Re: sql query works in database connector but not in ZSQL method [In reply to]

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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 18, 2007, 8:58 AM

Post #15 of 21 (4962 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

hi there,

I think I found the reason, for my problems with stored procedures.
it seems that on can not call a stored MySQL 5.0x procedure from zope
that returns a data set.
when I call the body of the stored procedure everything works fine.

what I now do as an interim solution, I call the "read only" procedures
directly from python.
procedures that alter data i call from a zope connector.

I would be glad to here of any reason not to do this.

thanks for your help.

robert
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFFr6c9GaryJ0T9kUYRAlkiAJ9TZ3rKw3iiTHuZ1fiQuJE6Sdoa0wCfZr63
uNOpVr8pc+rYV6jwbZ9l6sI=
=p3jD
-----END PGP SIGNATURE-----
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Jan 18, 2007, 9:21 AM

Post #16 of 21 (4946 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

> what I now do as an interim solution, I call the "read only" procedures
> directly from python.
> procedures that alter data i call from a zope connector.
>
What do you mean by 'directly from python' and 'from a zope connector'?
> I would be glad to here of any reason not to do this.
First I need to understand what and how you're doing. Possible
issues may be with transactional integrity.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Jan 18, 2007, 12:07 PM

Post #17 of 21 (4938 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

Am 18.01.2007, 17:58 Uhr, schrieb robert rottermann <robert [at] redcor>:

> I think I found the reason, for my problems with stored procedures.
> it seems that on can not call a stored MySQL 5.0x procedure from zope
> that returns a data set.

If you use the mxODBC Zope DA callproc() method then you can read the last
result set returned by a procedure.

> when I call the body of the stored procedure everything works fine.
> what I now do as an interim solution, I call the "read only" procedures
> directly from python.
> procedures that alter data i call from a zope connector.
> I would be glad to here of any reason not to do this.

Yes, if you really do something outside of Zope then you are compromising
your transactional integrity.

Charlie


_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 18, 2007, 7:38 PM

Post #18 of 21 (4932 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

Maciej Wisniowski wrote:
>> what I now do as an interim solution, I call the "read only" procedures
>> directly from python.
>> procedures that alter data i call from a zope connector.
>>
>>
> What do you mean by 'directly from python' and 'from a zope connector'?
>
directly from python: I mean I read the database using a MySQLdb call
a zope connector: I mean a ZSQL object (I am currently testing mxODBC
Zope DA)
>> I would be glad to here of any reason not to do this.
>>
> First I need to understand what and how you're doing. Possible
> issues may be with transactional integrity.
>
>
I do only read data to display and do not store it. do you still think
that could affect integrity.

robert
Attachments: robert.vcf (0.20 KB)


maciej.wisniowski at coig

Jan 19, 2007, 3:25 AM

Post #19 of 21 (4952 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

> I do only read data to display and do not store it. do you still think
> that could affect integrity.
>
>
As far as I understand you're creating new connection in
python (External method or product) to call some queries
on it and to read data. You're also using ZSQLMethod in Zope
to call procedures that modify data. Yes?

I don't know the order of these actions but suppose that:
1. You call a procedure (with Zope machinery) that modified some data
2. You call query 'from python' to get these data
3. Your results are incorrect because you're using different
database connections, and commit was not done yet (at last Zope
commits 'automatically' at the end of request).

But maybe this is not your case.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


robert at redcor

Jan 19, 2007, 8:50 AM

Post #20 of 21 (4940 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Maciej Wisniowski schrieb:
>> I do only read data to display and do not store it. do you still think
>> that could affect integrity.
>>
>>
> As far as I understand you're creating new connection in
> python (External method or product) to call some queries
> on it and to read data. You're also using ZSQLMethod in Zope
> to call procedures that modify data. Yes?
>
> I don't know the order of these actions but suppose that:
> 1. You call a procedure (with Zope machinery) that modified some data
> 2. You call query 'from python' to get these data
> 3. Your results are incorrect because you're using different
> database connections, and commit was not done yet (at last Zope
> commits 'automatically' at the end of request).
>
> But maybe this is not your case.
>
it in deed seems not to work.
however I do not understand why.

This is now academical as I resolved to not to use stored procedures
that return a record set (tough its a pain in the back).

what I do (did):
1. write data (in a ZSQL connction), did commit it, did flush the table.
(reading from plain mysql shows the data)
2. in a "python modoul" I
- flush the tables (to force a reread)
- read the data
- send it to plone to display

unfortunately I get stale data.

As we need to go online next week, I resolved to not to use Stored
Procedure to retrieve the data.
However I really like to know why I cant.

robert

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFFsPbTGaryJ0T9kUYRAjm1AJ4mkSbRklcxSabGE1doGrIVKBihOgCfdDHM
kTuhpQTZoJlxcIPS+gPAwLg=
=uPBh
-----END PGP SIGNATURE-----
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Jan 19, 2007, 9:00 AM

Post #21 of 21 (4934 views)
Permalink
Re: found reason: sql query works in database connector but not in ZSQL method [In reply to]

Am 19.01.2007, 17:50 Uhr, schrieb robert rottermann <robert [at] redcor>:

> it in deed seems not to work.
> however I do not understand why.

If you use callproc() we could probably help you.

> This is now academical as I resolved to not to use stored procedures
> that return a record set (tough its a pain in the back).
> what I do (did):

> 1. write data (in a ZSQL connction), did commit it, did flush the table.
> (reading from plain mysql shows the data)
> 2. in a "python modoul" I
> - flush the tables (to force a reread)
> - read the data
> - send it to plone to display
> unfortunately I get stale data.
> As we need to go online next week, I resolved to not to use Stored
> Procedure to retrieve the data.
> However I really like to know why I cant.

Don't forget that Zope will also cache results from the database as indeed
the ODBC driver may. You should really try this using a single connection
object.
call the procedure + get the results if possible
if not call the results with an additional query
run the query that uses the results of stored procedure.

You can do all this in a single ExternalMethod.

Charlie

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

Zope db RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.