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:[EMAIL PROTECTED]]
Sent: Friday, 20 April 2001 15:21
To: [EMAIL PROTECTED]
Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL
sucks)
* Adrian Hungate ([EMAIL PROTECTED]) [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/
[EMAIL PROTECTED] KF6VNC
