Hi Everyone, Just need someone to put me straight here. When I use Select statements in an SQL Method I cannot simply : Select Column1, Column2 etc from TableA. Instead I must use the AS statement ie Select Column1 AS Column1, Column2 AS Column2 from TableA. Is this just an oddity with the MySQLDA/Db adapters or am I overlooking something blatantly obvious here? All the examples on zope.org don't have the extra "AS Column1" additions on them and I was wondering if this is a Zope/mySQL-unique thing. I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database Adapter (ZMySQLDA) 2.0.0a1 and MySQLdb module release: 0.2.2 . Could someone please let me know as I don't want to have to type the extra bits on all my SQL statements from here on in if I don't need to (I am the world's worst and laziest typist). If it is the case that it's a necessary evil, then I want to put a HowTo or Tip up on zope.org because it took me a while to figure out what I was doing wrong, and if I can save someone else that same wasted time then I would like to. However I wanted to get my facts straight (via all you experts) first. Thanks for any help... - Michael ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168 " Can I trade this job for what's behind door #2? "
On Fri, 30 Jun 2000, Michael Blewett wrote:
Hi Everyone, Just need someone to put me straight here. When I use Select statements in an SQL Method I cannot simply :
Select Column1, Column2 etc from TableA.
Depending on the column names, yes you can. I just tested it not, and it works fine. If the column names are not valid zope/python names, you will run into problems.
Instead I must use the AS statement ie
Select Column1 AS Column1, Column2 AS Column2 from TableA.
Is this just an oddity with the MySQLDA/Db adapters or am I overlooking something blatantly obvious here? All the examples on zope.org don't have the extra "AS Column1" additions on them and I was wondering if this is a Zope/mySQL-unique thing.
Can you try to construct the simplest example of a query that doesn't work?
I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database Adapter (ZMySQLDA) 2.0.0a1 and MySQLdb module release: 0.2.2 .
Hmm.. I am using the same, except for ZMySQLda 1.1.3, and MySQLdb 0.1.2. So, perhaps it's new in ZMySQLda 2.0.
Could someone please let me know as I don't want to have to type the extra bits on all my SQL statements from here on in if I don't need to (I am the world's worst and laziest typist). If it is the case that it's a necessary
What, you've never heard of cut'n'paste? (o8
evil, then I want to put a HowTo or Tip up on zope.org because it took me a while to figure out what I was doing wrong, and if I can save someone else that same wasted time then I would like to. However I wanted to get my facts straight (via all you experts) first.
A wise move, and a wonderful gesture.
Thanks for any help...
- Michael
Have a better one, Curtis Maloney <dtml-var standard_work_disclaimer>
At 14:08 30/06/2000 +1000, Curtis Maloney wrote: <snip>
Is this just an oddity with the MySQLDA/Db adapters or am I overlooking something blatantly obvious here? All the examples on zope.org don't have the extra "AS Column1" additions on them and I was wondering if this is a Zope/mySQL-unique thing.
Can you try to construct the simplest example of a query that doesn't work? Sure. Here tis:
select Subject, Subjectname from test order by Subject
I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database Adapter (ZMySQLDA) 2.0.0a1 and MySQLdb module release: 0.2.2 .
Hmm.. I am using the same, except for ZMySQLda 1.1.3, and MySQLdb 0.1.2. So, perhaps it's new in ZMySQLda 2.0.
Quite possibly. I've sent a cc to Andy Dustman, just in case he's interested.
Could someone please let me know as I don't want to have to type the extra bits on all my SQL statements from here on in if I don't need to (I am the world's worst and laziest typist). If it is the case that it's a necessary
What, you've never heard of cut'n'paste? (o8
With two fields it's OK, but if you've got 30 fields in a table it gets pretty tiresome (even using cut & paste) :o)
evil, then I want to put a HowTo or Tip up on zope.org because it took me a while to figure out what I was doing wrong, and if I can save someone else that same wasted time then I would like to. However I wanted to get my facts straight (via all you experts) first.
A wise move, and a wonderful gesture.
That's how 'collaborative' software should work IMHO.
Have a better one, No - *you* have an even better one, Thanks for the reply...
- Michael ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168 " Can I trade this job for what's behind door #2? "
Why do you say you "must" use the AS statement? Do you get an error otherwise? MySQLdb 0.2.2 is broken when used with ZMySQLDA. But 0.2.1 works okay for me. Andy Dustman (author of the MySQLdb) said he will fix it and hinted that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe). Since that one now uses his MySQLdb, he will likely kill off his own DA. _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Michael Blewett Sent: Thursday, June 29, 2000 10:53 PM To: zope@zope.org Subject: [Zope] MySQL Select Statements
Hi Everyone, Just need someone to put me straight here. When I use Select statements in an SQL Method I cannot simply :
Select Column1, Column2 etc from TableA.
Instead I must use the AS statement ie
Select Column1 AS Column1, Column2 AS Column2 from TableA.
Is this just an oddity with the MySQLDA/Db adapters or am I overlooking something blatantly obvious here? All the examples on zope.org don't have the extra "AS Column1" additions on them and I was wondering if this is a Zope/mySQL-unique thing.
I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database Adapter (ZMySQLDA) 2.0.0a1 and MySQLdb module release: 0.2.2 .
Could someone please let me know as I don't want to have to type the extra bits on all my SQL statements from here on in if I don't need to (I am the world's worst and laziest typist). If it is the case that it's a necessary evil, then I want to put a HowTo or Tip up on zope.org because it took me a while to figure out what I was doing wrong, and if I can save someone else that same wasted time then I would like to. However I wanted to get my facts straight (via all you experts) first.
Thanks for any help...
- Michael ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168
" Can I trade this job for what's behind door #2? "
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Hi Ron, If I don't specify my Selects using "AS", I then get the following error message: Error Type: KeyError Error Value: SubjectName Traceback (innermost last): File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 179, in publish File /usr/local/Zope-2.1.6-linux2-x86/lib/python/Zope/__init__.py, line 202, in zpublisher_exception_hook (Object: ElementWithAttributes) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 165, in publish File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/mapply.py, line 160, in mapply (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 102, in call_object (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/OFS/DTMLDocument.py, line 166, in __call__ (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/DocumentTemplate/DT_String.py, line 502, in __call__ (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/DocumentTemplate/DT_In.py, line 691, in renderwob (Object: sqlRetrieveTestList) KeyError: (see above) (Where Subjectname was the first field referenced in the DTML document). Whereas, If I put the SQL as Select Subjectname AS Subjectname, etc, then it all works fine. It's funny that you say 0.2.2 is broken. Apart from this one glitch, it works fine for me (although from memory I think we had to do a little bit of fiddling at install time to get it to work). - Michael At 00:12 30/06/2000 -0400, Ron Bickers wrote:
Why do you say you "must" use the AS statement? Do you get an error otherwise?
MySQLdb 0.2.2 is broken when used with ZMySQLDA. But 0.2.1 works okay for me. Andy Dustman (author of the MySQLdb) said he will fix it and hinted that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe). Since that one now uses his MySQLdb, he will likely kill off his own DA.
_______________________
Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Michael Blewett Sent: Thursday, June 29, 2000 10:53 PM To: zope@zope.org Subject: [Zope] MySQL Select Statements
Hi Everyone, Just need someone to put me straight here. When I use Select statements in an SQL Method I cannot simply :
Select Column1, Column2 etc from TableA.
Instead I must use the AS statement ie
Select Column1 AS Column1, Column2 AS Column2 from TableA.
Is this just an oddity with the MySQLDA/Db adapters or am I overlooking something blatantly obvious here? All the examples on zope.org don't have the extra "AS Column1" additions on them and I was wondering if this is a Zope/mySQL-unique thing.
I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database Adapter (ZMySQLDA) 2.0.0a1 and MySQLdb module release: 0.2.2 .
Could someone please let me know as I don't want to have to type the extra bits on all my SQL statements from here on in if I don't need to (I am the world's worst and laziest typist). If it is the case that it's a necessary evil, then I want to put a HowTo or Tip up on zope.org because it took me a while to figure out what I was doing wrong, and if I can save someone else that same wasted time then I would like to. However I wanted to get my facts straight (via all you experts) first.
Thanks for any help...
- Michael ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168
" Can I trade this job for what's behind door #2? "
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168 " Can I trade this job for what's behind door #2? "
If I don't specify my Selects using "AS", I then get the following error message:
Error Type: KeyError Error Value: SubjectName
Traceback (innermost last): File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 179, in publish ... I overlooked that you are running 2.1.6. I don't know about the error message you're getting so this may be unrelated, but I and many many others have had a lot of problems with 2.1.6 and ZSQLMethods. So many that I stuck with 2.1.4 and I'll skip directly to 2.2.
It's funny that you say 0.2.2 is broken. Apart from this one glitch, it works fine for me (although from memory I think we had to do a little bit of fiddling at install time to get it to work).
I don't know a whole lot about the problem, but fetch_all_rows() is missing, and for whatever reason that breaks my methods. If it's working for you, then great. I don't guess that helps you with your problem though. Did you have this problem with 2.1.4? _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
I should point out that 0.2.2 has not been fully released yet. There's a bug I have to iron out of it first. Probably next week. Also, for general MySQLdb questions, send them to andy-python@dustman.net (mailing list). Or subscribe, it's low-volume and won't fatten your mailbox. On Fri, 30 Jun 2000, Michael Blewett wrote:
It's funny that you say 0.2.2 is broken. Apart from this one glitch, it works fine for me (although from memory I think we had to do a little bit of fiddling at install time to get it to work).
It's a matter of perspective. I prefer to think of it as 0.2.2 breaks ZMySQLDA. This is because ZMySQLDA uses _mysql, and _mysql prior to 0.2.2 implemented fetch_row(), fetch_rows(), fetch_all_rows(), and the ..._as_dict() variants. This was just too much code duplication to put up with, and the new interface is fetch([n[,how]]) where n is the maximum number of rows to fetch (default 1), and how is how to return the result (0: as tuple, 1: as dictionary, other codes may be added at some point). To fix ZMySQLDA, change fetch_all_rows() to fetch(maxrows) in the query routine. One other place (to get the tables) uses fetch_all_rows(). If you insist on using 0.2.2 at this point, change that as well. 0.2.2 is marked Alpha for a reason... And when I release it, I'll include a very small patch to make things right with ZMySQLDA again (I'm treating mordred's as the official one at this point).
At 00:12 30/06/2000 -0400, Ron Bickers wrote:
Why do you say you "must" use the AS statement? Do you get an error otherwise?
MySQLdb 0.2.2 is broken when used with ZMySQLDA. But 0.2.1 works okay for me. Andy Dustman (author of the MySQLdb) said he will fix it and hinted that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe). Since that one now uses his MySQLdb, he will likely kill off his own DA.
Quite true. Now for the AS statement: If you are joining tables with identically-named columns in common, you should use AS in your SQL to rename them. Otherwise it will return two columns with the same name to Zope, which is bad. You do not, however, need to do this:
Just need someone to put me straight here. When I use Select statements in an SQL Method I cannot simply :
Select Column1, Column2 etc from TableA.
Instead I must use the AS statement ie
Select Column1 AS Column1, Column2 AS Column2 from TableA.
- Michael
This is just gratuitious renaming to the original name, which is not renaming at all. Here's an example of what I'm talking about. SELECT TableA.Col, TableB.Col from TableA, TableB # bad SELECT TableA.Col AS ColA, TableB.Col AS ColB from TableA, TableB # good Note that the first SELECT returned two columns named Col, which will confuse Zope, and the second returns ColA (TableA.Col) and ColB (TableB.Col). Additional compatibility note: One of the 0.2.2 changes has been to synchronize with MySQL-3.23 evolution (now at 3.23.19-beta, and GPL'd), and the big feature here is: Transactions! Yes, as of 3.23.15, transactions are in MySQL, if you use BDB tables. And yes, BDB does mean Berkeley DB, specificially the Sleepycat DB v3, which supports transactions (but probably not columns, which I presume MySQL is imposing on the database). For ZMySQLDA developers out there; MySQL-3.23 defaults to autocommit. However, there is a BEGIN SQL statement which starts a transaction and similarly for COMMIT and ROLLBACK statements. But remember: All this transactional stuff ONLY works on BDB tables. If you try to BEGIN a transaction (or turn off autocommit) on another table type, well, I'm not sure what happens. Maybe you get an error, and maybe you don't. Not sure what the solution is for this. It may be necessary to have the ZMySQLDA package include another DA: ZMySQLTDA for transactions. The standard DA would not be based on TM, and the transactional one could simply subclass the other one: # ZMySQLTDA/db.py from Shared.DC.ZRDB.TM import TM class DB(ZMySQLDA.DB, TM): def _begin(self, *ignored): self.db.query("BEGIN") def _finish(self, *ignored): self.db.query("COMMIT") def _abort(self, *ignored): self.db.query("ROLLBACK") Obviously the application designer will have to use the right DA for the right table type... -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!"
-----Original Message----- From: Andy Dustman [mailto:adustman@comstar.net] Sent: Friday, June 30, 2000 11:42 AM To: Michael Blewett Cc: Ron Bickers; zope@zope.org; andy-python@dustman.net Subject: RE: [Zope] MySQL Select Statements
It's a matter of perspective. I prefer to think of it as 0.2.2 breaks ZMySQLDA. This is because ZMySQLDA uses _mysql, and _mysql prior to 0.2.2 implemented fetch_row(), fetch_rows(), fetch_all_rows(), and the ..._as_dict() variants.
Thanks for clarifying that. I hope the 1.2.0 DA continues to follow your (Andy's) changes to the MySQLdb so that we can have a nice, working, stable db/DA combination.
what the solution is for this. It may be necessary to have the ZMySQLDA package include another DA: ZMySQLTDA for transactions. The standard DA
Please not two DAs just for the difference in transaction support.
Obviously the application designer will have to use the right DA for the right table type...
It should be as simple as the application designer actually knowing what they're doing by knowing whether or not they're working with a table that supports transactions or not, and just not using BEGIN/COMMIT/ROLLBACK when they're not. Or is it too much to ask that designers know what they're doing? :-) _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
On Fri, 30 Jun 2000, Ron Bickers wrote:
It should be as simple as the application designer actually knowing what they're doing by knowing whether or not they're working with a table that supports transactions or not, and just not using BEGIN/COMMIT/ROLLBACK when they're not. Or is it too much to ask that designers know what they're doing? :-)
That's the problem: They don't get control over when BEGIN/COMMIT/ROLLBACK are used. Zope does that if you are using the Transaction Manager. And the only way to control use of the Transaction Manager is by selection of DA. That's why I think it may be necessary to have two ZMySQLDAs: One which is transactional and one which is not. However, they can both be part of the same distribution, and the transactional one can simply subclass the non-transactional one (mixing in the TM base and adding the required methods), so there is not a lot of code duplication. In addition, a lot of people are still going to be using MySQL-3.22, which does NOT support transactions, or even the BEGIN/COMMIT/ROLLBACK statements (these produce syntax errors). The only way to detect this at runtime is to look at the server's version. Anyway, more experimenting is needed. -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!"
-----Original Message----- From: Andy Dustman [mailto:adustman@comstar.net] Sent: Friday, June 30, 2000 1:25 PM To: Ron Bickers Cc: Michael Blewett; zope@zope.org; andy-python@dustman.net Subject: RE: [Zope] MySQL Select Statements
That's the problem: They don't get control over when BEGIN/COMMIT/ROLLBACK are used. Zope does that if you are using the Transaction Manager. And the only way to control use of the Transaction Manager is by selection of DA.
That's why I think it may be necessary to have two ZMySQLDAs: One which is transactional and one which is not. However, they can both be part of the same distribution, and the transactional one can simply subclass the non-transactional one (mixing in the TM base and adding the required methods), so there is not a lot of code duplication.
I see. So how would this affect use of transaction and non-transaction supporting tables? Would two separate database connections be required where the SQLMethod must use the correct connection depending on transaction support? Making assumptions is bad, but suppose MySQL 3.23 does something elegant with BEGIN/COMMIT/ROLLBACK attempts on non-transaction supporting tables (like return a "table does not support transactions" or simply ignores that statement). Could we then just have one DA for 3.22 and another for 3.23, where the 3.23 DA handles transactions, but silently ignores them when dealing with non-transaction tables? _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
On Fri, 30 Jun 2000, Ron Bickers wrote:
That's the problem: They don't get control over when BEGIN/COMMIT/ROLLBACK are used. Zope does that if you are using the Transaction Manager. And the only way to control use of the Transaction Manager is by selection of DA.
That's why I think it may be necessary to have two ZMySQLDAs: One which is transactional and one which is not. However, they can both be part of the same distribution, and the transactional one can simply subclass the non-transactional one (mixing in the TM base and adding the required methods), so there is not a lot of code duplication.
I see. So how would this affect use of transaction and non-transaction supporting tables? Would two separate database connections be required where the SQLMethod must use the correct connection depending on transaction support?
Making assumptions is bad, but suppose MySQL 3.23 does something elegant with BEGIN/COMMIT/ROLLBACK attempts on non-transaction supporting tables (like return a "table does not support transactions" or simply ignores that statement). Could we then just have one DA for 3.22 and another for 3.23, where the 3.23 DA handles transactions, but silently ignores them when dealing with non-transaction tables?
These are good questions. But I don't have good answers yet. I tried BEGIN and COMMIT when I didn't even have a database selected, and it took it. It may silently accept ROLLBACK when rollback is impossible, or it may raise an error. I haven't done that much testing with MySQL-3.23 yet, and none with BDB tables. But it may be possible to break things down as having a DA for 3.22 and one for 3.23. Which is, in practice, may not be any different than having one that is not transactional (3.22 never is) and one that is transactional (3.23 sometimes is). -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!"
-----Original Message----- From: Andy Dustman [mailto:adustman@comstar.net] Sent: Friday, June 30, 2000 4:08 PM To: Ron Bickers Cc: zope@zope.org; andy-python@dustman.net Subject: RE: [Zope] MySQL transaction support (was: MySQL Select Statements)
with BDB tables. But it may be possible to break things down as having a DA for 3.22 and one for 3.23. Which is, in practice, may not be any different than having one that is not transactional (3.22 never is) and one that is transactional (3.23 sometimes is).
What I want to avoid is having to create two different "MySQL database connections" where I have to choose the "correct" one based on whether or not my SQL method is using transactions or not. I'll most likely only be using one version of MySQL at a time, but if I'm using transactions at all, there's a good chance not all of my tables will support them. I guess we'll just have to do some testing. _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
Why not have one ZmySQLDA that attempts to determine whether it is transactional or not when the connect method is executed. That way you could have a DA connect to your 3.22 database, upgrade your database to
3.23.15 and not see any problems. A combination of try-except:ing an attempted "begin rollback" block and a test for database type should do the trick, no? If it's 3.22 or earlier, the begin-rollback should throw the synax error Andy mentioned. If that's ok, a test for database type (not sure about this one) to see if it's BDB? If all of this is wrapped in the connect method, we should be able to make it 'do the right thing'
Thoughts? Monty
with BDB tables. But it may be possible to break things down as having a DA for 3.22 and one for 3.23. Which is, in practice, may not be any different than having one that is not transactional (3.22 never is) and one that is transactional (3.23 sometimes is).
What I want to avoid is having to create two different "MySQL database connections" where I have to choose the "correct" one based on whether or not my SQL method is using transactions or not. I'll most likely only be using one version of MySQL at a time, but if I'm using transactions at all, there's a good chance not all of my tables will support them.
I guess we'll just have to do some testing. _______________________
Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Mon, 3 Jul 2000, Monty Taylor wrote:
Why not have one ZmySQLDA that attempts to determine whether it is transactional or not when the connect method is executed. That way you could have a DA connect to your 3.22 database, upgrade your database to
3.23.15 and not see any problems. A combination of try-except:ing an attempted "begin rollback" block and a test for database type should do the trick, no? If it's 3.22 or earlier, the begin-rollback should throw the synax error Andy mentioned. If that's ok, a test for database type (not sure about this one) to see if it's BDB? If all of this is wrapped in the connect method, we should be able to make it 'do the right thing'
There are two factors which determine whether or not transactions can be supported. Actually, there's only one, but it can't be directly tested for: 1) If the server version (easily obtained upon establishing the connection) is < 3.23.15, transactions are definitely not supported. 2) If the database in use employs BDB tables, transactions are definitely supported. The problem is: Support for the transaction manager is done with a MixIN class, so the decision on whether or not transactions should be supported in the DA comes before the connection object is created. Which raises an interesting possibility: On the connection object's Properties tab, add a checkbox which indicates that transactional semantics are required. The status of this checkbox determines which class is used for the database connection. This might be workable, not really sure. -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!"
Andy Dustman wrote:
There are two factors which determine whether or not transactions can be supported. Actually, there's only one, but it can't be directly tested for:
1) If the server version (easily obtained upon establishing the connection) is < 3.23.15, transactions are definitely not supported.
2) If the database in use employs BDB tables, transactions are definitely supported.
AFAIK the transaction support is enabled on table-by-table basis, so there is no way to know for sure if transactions are supported/partially-supported/unsupported for a particular set of queries
The problem is: Support for the transaction manager is done with a MixIN class, so the decision on whether or not transactions should be supported in the DA comes before the connection object is created.
But after the connection is established you could probably change the mixin class, at least if it is really a proxy wrapper of some other class. Python is _very_ dynamic language. ------------ Hannu
On Tue, 4 Jul 2000, Hannu Krosing wrote:
AFAIK the transaction support is enabled on table-by-table basis, so there is no way to know for sure if transactions are supported/partially-supported/unsupported for a particular set of queries
True enough. There are several problems here: 1) We must avoid using BEGIN/COMMIT/ROLLBACK on servers < 3.23.15 because it will result in syntax errors. 2) We must raise exceptions whenever the DA attempts to use transacation semantics and the database cannot fulfill this. Silently allowing the transaction manager to believe ROLLBACK has succeeded when it is actually doing nothing is a bad thing. 3) Even though the server may support transactions, the tables may not. Calling ROLLBACK hopefully produces an error, which is a good thing. 4) Calling ROLLBACK in AUTOCOMMIT mode probably does not produce an error. This is bad, but easily avoided by using BEGIN to force a transaction. Does this cause MySQL to produce an error if the tables don't support it? I don't know. Probably not. IMHO, the only good way to avoid these problems is to effectively have two DAs: A transactional one and a non-transactional one, perhaps determined by a Zope property. The application designer will have to choose which DA to use for a given application. Note also: With non-transactional databases, you would expect to get better performance by having several open connections which are not shared. In the case of MySQL, each connection has it's own server thread, and this can exploit CPUs, if available. However, connections should not be shared on transactional databases, because transactions are tied to a connection, and not a cursor. Even if there is a mutex to keep multiple threads from executing queries simultaneously on the same connection, the transactions are going to be whacky. -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!"
>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 7/4/00, 12:47:07 AM, Andy Dustman <adustman@comstar.net> wrote regarding Re: [Zope] MySQL transaction support (was: MySQL Select Statements):
Note also: With non-transactional databases, you would expect to get better performance by having several open connections which are not shared. In the case of MySQL, each connection has it's own server thread, and this can exploit CPUs, if available. However, connections should not be shared on transactional databases, because transactions are tied to a connection, and not a cursor. Even if there is a mutex to keep multiple threads from executing queries simultaneously on the same connection, the transactions are going to be whacky.
Oracle is obviously transactional. Does anyone know how it handles connection pooling? I'll look and see it I can figure it out, but I'd love it if someone already knew... Monty
participants (6)
-
Andy Dustman -
Curtis Maloney -
Hannu Krosing -
Michael Blewett -
Monty Taylor -
Ron Bickers