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? "

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? "

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!"

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!"

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!"

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 )

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

...

...

...

...

...

>>>>>>>>>>>> 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