RE: [Zope] MySQL Select Statements

2000-06-30 Thread Andy Dustman

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 [EMAIL PROTECTED] (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!"


___
Zope maillist  -  [EMAIL PROTECTED]
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 )




RE: [Zope] MySQL Select Statements

2000-06-30 Thread Ron Bickers

 -Original Message-
 From: Andy Dustman [mailto:[EMAIL PROTECTED]]
 Sent: Friday, June 30, 2000 11:42 AM
 To: Michael Blewett
 Cc: Ron Bickers; [EMAIL PROTECTED]; [EMAIL PROTECTED]
 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.
[EMAIL PROTECTED]


___
Zope maillist  -  [EMAIL PROTECTED]
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 )




Re: [Zope] MySQL Select Statements

2000-06-30 Thread Dieter Maurer

Michael Blewett writes:
  Select Column1, Column2 etc
  from TableA.
  
  Instead I must use the AS statement ie
  
  Select Column1 AS Column1, Column2 AS Column2
  from TableA.
Zope should not be responsible for this strange behaviour.

It simply executes the SQL statement and asks the result
for the result column names. It then wraps each result
row in an object that allows to access a column value
as attribute under the respective column name.

You could try:
dtml-let SQLresult=SQLMethod
  dtml-var "SQLresult.names()"
/dtml-in
to get the list of column names your Z SQL Method
"SQLMethod" returns.

This may give you a hint how your database names result columns.


I recently found out that Oracle returns column names
converted into all uppercase. I needed about 2 hours
to analyse this weird behaviour.



Dieter

___
Zope maillist  -  [EMAIL PROTECTED]
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 )




Re: [Zope] MySQL Select Statements

2000-06-29 Thread Curtis Maloney

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

___
Zope maillist  -  [EMAIL PROTECTED]
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 )




RE: [Zope] MySQL Select Statements

2000-06-29 Thread Ron Bickers

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.
[EMAIL PROTECTED]


 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
 Michael Blewett
 Sent: Thursday, June 29, 2000 10:53 PM
 To: [EMAIL PROTECTED]
 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  -  [EMAIL PROTECTED]
 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 )




___
Zope maillist  -  [EMAIL PROTECTED]
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 )




RE: [Zope] MySQL Select Statements

2000-06-29 Thread Michael Blewett

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.
[EMAIL PROTECTED]


  -Original Message-
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
  Michael Blewett
  Sent: Thursday, June 29, 2000 10:53 PM
  To: [EMAIL PROTECTED]
  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  -  [EMAIL PROTECTED]
  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? "


___
Zope maillist  -  [EMAIL PROTECTED]
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 )




Re: [Zope] MySQL Select Statements

2000-06-29 Thread Michael Blewett

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


___
Zope maillist  -  [EMAIL PROTECTED]
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 )




RE: [Zope] MySQL Select Statements

2000-06-29 Thread Ron Bickers


 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.
[EMAIL PROTECTED]


___
Zope maillist  -  [EMAIL PROTECTED]
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 )