Mailing List Archive: [Zope] ZSQL Methods and transaction control

[Zope] ZSQL Methods and transaction control

Jan 10, 2007, 4:00 AM

Post #1 of 7 (4710 views)

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

Hi there,

I am implementing a tool to handle userdata that is stored in a MySQL
db. The underlaying logic of the stored procedures used to maintain the
db content dictate that I have to control the transactions myself.

I understand that there is a way to handle transaction control in the
ZMySQL methods. However I find no documentation at all on how to use it.

Without using ZMySQL methods I would be using the following piece of code:
db = mysql.connect(host="localhost" ...)
cursor = db.cursor()
query = "CALL insertUser('%s', '$xxx$', @id, @error);select @id, \
@error" % 'JohnTheUser')
cursor.execute(query)
# get the next resultset, it has the result of the select
cursor.nextset()
result = cursor.fetchall()[0]
error = int(result[1])
if error:
print "error %s when trying to add user %s" % (ERRRORS[error],\
'JohnTheUser')
db.rollback()
continue #we are in a loop actually
..
db.commit()

How can I mimic that using ZSQL Methods

thanks for any pointers
Robert

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

iD8DBQFFpNVkGaryJ0T9kUYRAhWfAJ9MTOqEu2PXnUQlVJRfof8hj5zXcgCeJoCt
Dij5NsSXbjc6FWYE5i9nMTE=
=6Qql
-----END PGP SIGNATURE-----
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: [Zope] ZSQL Methods and transaction control [ In reply to ]

charlie at egenix

Jan 10, 2007, 4:03 AM

Post #2 of 7 (4541 views)

Permalink

Am 10.01.2007, 13:00 Uhr, schrieb robert rottermann <robert@redcor.ch>:

> I understand that there is a way to handle transaction control in the
> ZMySQL methods. However I find no documentation at all on how to use it.

This is incorrect. ZSQL methods are called within Zope transactions so you
need to look at the transaction manager.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: [Zope] ZSQL Methods and transaction control [ In reply to ]

robert at redcor

Jan 10, 2007, 4:16 AM

Post #3 of 7 (4552 views)

Permalink

Charlie Clark wrote:
> Am 10.01.2007, 13:00 Uhr, schrieb robert rottermann <robert@redcor.ch>:
>
>> I understand that there is a way to handle transaction control in the
>> ZMySQL methods. However I find no documentation at all on how to use it.
>
> This is incorrect. ZSQL methods are called within Zope transactions so
> you need to look at the transaction manager.
>
> Charlie
>
thanks charlie,

so that means, that the _commit and _abort methods are only for zope's
transaction controll.

do you have a suggestion how then to start/commit a transaction from
within a ZSQL method (or ZMySQLdb)

thanks
robert

Re: [Zope] ZSQL Methods and transaction control [ In reply to ]

charlie at egenix

Jan 10, 2007, 4:30 AM

Post #4 of 7 (4568 views)

Permalink

Am 10.01.2007, 13:16 Uhr, schrieb robert rottermann <robert@redcor.ch>:

> thanks charlie,
> so that means, that the _commit and _abort methods are only for zope's
> transaction controll.

Yes, and with good reason.

> do you have a suggestion how then to start/commit a transaction from
> within a ZSQL method (or ZMySQLdb)

Well, if you check last week's archives, you'll notice a very similar
question came up. We suggest using the our mxODBC ZopeDA with an
ExternalMethod for this kind of thing as it gives you a lot more control
and allows you to work outside the restrictions on ZSQL which, good as it
is, isn't suitable for this.

Charlie

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

Re: [Zope] ZSQL Methods and transaction control [ In reply to ]

maciej.wisniowski at coig

Jan 10, 2007, 5:10 AM

Post #5 of 7 (4567 views)

Permalink

>> thanks charlie,
>> so that means, that the _commit and _abort methods are only for zope's
>> transaction controll.
> Yes, and with good reason.
But I think that if you know what your'e doing you may
use them althought it may be tricky. It is better to
try external method like Charlie suggested for example
with something like:

def callSQLtransactionSet(self):
connObj = self.yourconnectionobject() # connection object from Zope
db = connObj.db # this depends
on your's Zope DA

# CODE FROM YOUR PREVIOUS POST HERE

# it may be necessary to return connection to the pool here
# if your DA uses one

If you'll have to do something more with database, then
do it after calling this function otherwise it will be
commited or rolled back during call to callSQLtransactionSet
method.

I'm not sure that this will work.

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

Re: [Zope] ZSQL Methods and transaction control [ In reply to ]

robert at redcor

Jan 10, 2007, 8:00 AM

Post #6 of 7 (4542 views)

Permalink

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

Charlie Clark schrieb:
> Am 10.01.2007, 13:16 Uhr, schrieb robert rottermann <robert@redcor.ch>:
>
>> thanks charlie,
>> so that means, that the _commit and _abort methods are only for zope's
>> transaction controll.
>
> Yes, and with good reason.
>
>> do you have a suggestion how then to start/commit a transaction from
>> within a ZSQL method (or ZMySQLdb)
>
> Well, if you check last week's archives, you'll notice a very similar
> question came up. We suggest using the our mxODBC ZopeDA with an
> ExternalMethod for this kind of thing as it gives you a lot more control
> and allows you to work outside the restrictions on ZSQL which, good as
> it is, isn't suitable for this.
>
> Charlie
>
>
thanks for your suggestion.
i am using mxODBC for 2 cusomers and I was under the impression, that it
is used (as the name suggests) to connect to a odbc database.
would using it mean that I had to access MySQL trough odbc. or does
mxODBC know how to access MySQL ?

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

iD8DBQFFpQ2aGaryJ0T9kUYRAirQAJ9e0ZT9EWubdUnywMX+dLZ2+u6OHwCdE2/z
1DJB8m6UhxqjQgl4s4JhpXA=
=JZPn
-----END PGP SIGNATURE-----
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: [Zope] ZSQL Methods and transaction control [ In reply to ]

charlie at egenix

Jan 10, 2007, 11:09 AM

Post #7 of 7 (4545 views)

Permalink

Am 10.01.2007, 17:00 Uhr, schrieb robert rottermann <robert@redcor.ch>:

> thanks for your suggestion.
> i am using mxODBC for 2 cusomers and I was under the impression, that it
> is used (as the name suggests) to connect to a odbc database.
> would using it mean that I had to access MySQL trough odbc. or does
> mxODBC know how to access MySQL ?

You'll still need an ODBC driver MySQL, of course.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Mailing List Archive

Mailing List Archive

Attached Files: