[Zope] ZSQL Methods and transaction control
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 controll 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:vcard
fn:robert rottermann
n:rottermann;robert
email;internet:[EMAIL PROTECTED]
tel;work:031 333 10 20
tel;fax:031 333 10 23
tel;home:031 333 36 03
x-mozilla-html:FALSE
version:2.1
end:vcard
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Methods and transaction control
robert rottermann schrieb:
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.
Can you elaborate on that? I dont see a reason why you would
commit around zopes transaction handling (which would actually
be dangerous anyway - think of the retry mechanism in case of
database conflicts)
I understand that there is a way to handle transaction controll in the
ZMySQL
methods. However I find no documentation at all on how to use it.
Well most databases accept COMMIT as sql command.
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
what does the loop?
..
db.commit()
How can I mimic that using ZSQL Methods
Are you adding users in a series? What exactly
are you doing? If its kind of migation, consider
running it as a script with zopectl run migationscript.py
once. There you could just write python code as you like
w/o interfering w/ Zopes session handling.
Regards
Tino
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Methods and transaction control
Are you sure it doesn't do it out of the box?
I know it does for ZPsycopgDA which is the Postgresq Zope database adapter.
With that one I don't have to come near handling the SQL transactions.
What Zope commits and rollsback is done for SQL too automatically.
robert rottermann wrote:
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 controll 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
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
--
Peter Bengtsson,
work www.fry-it.com
home www.peterbe.com
hobby www.issuetrackerproduct.com
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Methods and transaction control
robert rottermann wrote at 2007-1-10 09:00 +0100: ... 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. If you know what you do, then you can probably do something along the following lines. Almost surely, MySQL will provide some SQL command to control transaction. Maybe, they are called commit and rollback (as is the case for Postgres). In this case, you ZSQL method (or your stored procedure) can use it. In the ZSQL method, it could look like: --- some SQL --- dtml-var sql_delimiter commit -- Dieter ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
[Zope-DB] [Zope] ZSQL Methods and transaction control
-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-DB] [Zope] ZSQL Methods and transaction control
Am 10.01.2007, 13:00 Uhr, schrieb robert rottermann [EMAIL PROTECTED]: 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-DB] [Zope] ZSQL Methods and transaction control
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
