I am using ZODBC talking to an MS-SQL7 database (Yack spit), so the syntax is a little different. As for the unchangeable defaults, I use defaults a lot, and I don't think I've ever seen your problem. What you appear to be trying to do with the ZSQL Method in your example strikes me as 'A Bad Idea'(tm). Having the schema of the returned data change depending on the value of a parameter seems to be asking for problems (IMHO). If you look back at the my last example, and replace the PARAMS with: dDate="2001/03/31" sOffsetSize="month" nOffset="1" This certainly does work, I just tested it. Interestingly, so does: PARAMS: testval:int=0 <dtml-if testval> select count(shipped_date) cnt <dtml-else> select count(shipped_date)/2 halfcnt </dtml-if> from order_ If you call it with testval=1, you get a column called cnt, with testval=0 you get a column called halfcnt. Isn't this just what you are saying doesn't work? Adrian... -- Adrian Hungate Manager, European I.S. Acucorp UK Limited -----Original Message----- From: The Doctor What [mailto:docwhat@gerf.org] Sent: Friday, 20 April 2001 15:21 To: zope-dev@zope.org Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks) * Adrian Hungate (ahungate@acucorp.com) [010420 05:06]:

May be I am misunderstanding your problem here, but are you suggesting that

SELECT tab1.col1 col1, tab2.col1 col2 FROM ... etc ...

Does not expose 'col1' and 'col2' in the namespace for you? This is not my experience. Ok, I only have about 50 ZSQL methods on my intranet but they are all complex queries with column naming clashes. If it failed to work as you are suggesting, I would not have continued using Zope - Database connectivity was one of my criteria.

I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier. Perhaps like so: <dtml-var name='tab1.col1'> (note single quotes)

The normality (or abnormality) of your databases should make no difference, (Except to how complex your individual SQL statements become). What database are you running against? Maybe you have hit a database connector bug?

Postgresql. I've tried all three of the DAs: Psyco, Popy and PostresDA [Nice Example Snipped] What DB was that? The syntax isn't what I expect from Postgres, Tandem and MySQL.

As far as the 'foo=0' example, if you set foo to 0, and do nothing in between to change it, of course a test for not equal to 0 will fail, what would you expect?

Here is the example again, but better: <param>foo:int=0</param> <dtml-if expr="foo <> 0">Not Zero<dtml-else>Zero</dtml-if> If called like so: <dtml-var expr="zsql_example(foo=200)"> It will ALWAYS print 'Zero'. This makes it hard to do something with a param that defaults to nothing like: ---------------------------------------- <param>category_id=""</param> SELECT url.id, url.name <dtml-if category_id>, xref_url_category.categoryid as category_id, </dtml-if> FROM url <dtml-if category_id>, xref_url_category </dtml-if> <dtml-sqlgroup where> <dtml-if category_id> <dtml-sqltest category_id column="xref_url_category.categoryid" type="int"> <dtml-and> xref_url_category.urlid = url.id </dtml-if> </dtml-sqlgroup> ---------------------------------------- You can't even replace the param with "category_id:int=0" and then check for category_id <= 0 in the dtml-ifs. I finished my project, so I'm happy for the moment. I have another ZSQL project (a DVD, Comic Book and Video tracker for my Manga and Anime), so I'll be back. Thanks to everyone who spent time to help out. Ciao! -- Who are you going to believe, me or your own eyes? -- Groucho Marx The Doctor What: Guru to the Gods http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC