Title: RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)

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.

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?

I have the following query that our sales-reps use to check how much product we sold in the last 'n' days/weeks/months etc...

PARAMS: dDate, sOffsetSize, nOffset

       Order_.Shipped_Date Date,
       Region.Region_Name Region,
       Employee.Number_ ID,
       Territory.Territory_Name Territory,
       Currency_Exchange_Rate.From_Currency_Code Currency,
       SUM(Order_.Sub_Total - Order_.Order_Discount_Amount) Local,
       Currency_Exchange_Rate.Current_Exchange_Rate * SUM(Order_.Sub_Total - Order_.Order_Discount_Amount) Dollar
FROM Region
INNER JOIN Territory ON Region.Region_Id = Territory.Region_Id
INNER JOIN Company ON Territory.Territory_ID = Company.Territory_ID
INNER JOIN Order_ ON Company.Company_ID = Order_.Bill_To_Company_ID
INNER JOIN Currency_Exchange_Rate ON Order_.Currency_ID = Currency_Exchange_Rate.From_Currency_ID
INNER JOIN Employee ON Order_.Account_Manager = Employee.Employee_Id
<dtml-sqlgroup where>
(Currency_Exchange_Rate.To_Currency_Code = 'USD')
(Order_.Status IN ('Invoiced', 'Billed', 'Shipped'))
(Order_.Shipped_Date IS NOT NULL)
(Order_.Sub_Total <> 0)
(datediff(day, Order_.Shipped_Date, '<dtml-var dDate>') >= 0)
(datediff(<dtml-var sOffsetSize>, Order_.Shipped_Date, '<dtml-var dDate>') < <dtml-var nOffset>)
GROUP BY Order_.Shipped_Date, Region.Region_Name, Employee.Number_, Territory.Territory_Name, Currency_Exchange_Rate.From_Currency_Code, Currency_Exchange_Rate.Current_Exchange_Rate

ORDER BY Region.Region_Name, Employee.Number_, Territory.Territory_Name, Currency_Exchange_Rate.From_Currency_Code


This exposes 'Date', 'Region', 'ID', 'Territory', 'Currency', 'Local' and 'Dollar' to the calling script. I use this from a Python product, python scripts and from DTML all the time (10's or 100's of times per day at least).

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?


-----Original Message-----
From: The Doctor What [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 19 April 2001 23:22
To: Paul Erickson
Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL

* Paul Erickson ([EMAIL PROTECTED]) [010419 17:02]:
> The Doctor What wrote:
> > > * Loosing the variable between the form and dtml-if
> I don't understand this.  I'm assuming that you are losing values that
> are not in your argument list.  All you have to do is add the arguments.

That isn't what I mean.  Try this (typed in, so it may need to be
<dtml-if expr="foo != 0">Life is good</dtml-if>

You'll NEVER get Life is good to print out.  EVER.  This is because
ZSQL is setting foo to 0.  But dtml-var and dtml-sqlvar both work.

> > > * Inability to handle table.field names for variables
> > > * And enough flexibility to work around the above problems
> > >
> > > This makes ZSQL extreamely nasty, and impossible to use with a
> > > normalized database.
> Database normalization isn't really an issue.  It sounds like you're
> really just having problems with the syntax of joins.

Nope, can do joins.  Been doing joins (mainly inner).  Not the
problem.  The problem is that if I have tables like:

Table1ID   <-- Foreign Key thrown in for fun.

And I join them, then I MUST rename all the selects using AS:
  Table1.id as id1
  Table1.name as name1

Because I can't have zsql put the variables in the caller's
namespace as "Table1.id".  It puts them in as "id" (without the AS).

Fortunately, I found the column for sqltest (which is the other end
of ZSQL):
<dtml-sqltest id1 column="Table1.id" ....

> > >
> > > What's the point?  ZSQL sucks, how do I talk to the DB directly?
> Grab the Python database adapter and write some python classes or
> external methods to do what you need.

Can I use them from a python script?  If not, what's the point?  I
mean: External methods are nice to have when you have *no other
choice*, but they aren't something I'd want to debug and deal with
for object.

> > This makes things work MUCH better.  So there are work arounds.
> This isn't a work-around, it is the way that it is intended to be used.

I didn't see a single example of it.  The Zope Book didn't mention
this at all (except as a single line reference).  I would *never*
have gone looking for the word column.  Why? It's a field!

> Again, I think that it's a matter of understanding how ZSQL works,
> rather than a limitation of ZSQL.  If you have column name conflicts,
> you can always use something like:
> SELECT a.field as a_field, b.field as b_field FROM ...
> Then refer to them in dtml like &dtml-a_field; or <dtml-var
> name="a_field">

Right, but you still have to do extra work to get them back into a
ZSQL object.  column lets you do that.  It's got no examples to help
out with.  It's documented once.  I didn't see it.  Life is better

> > I still would love some examples.  Do people end up with 4 ZSQL
> > objects per thing they manipulate in their database?:
> > UPDATE, SELECT, INSERT and DELETE?  Or do they mix them somehow?
> I typically wind up with a ZSQL method for each of the CRUD operations.
> Sometimes I have separate selects methods for more complex joins, if the
> DTML code gets too ugly when I try to combine them into a single method.
> Example of a fairly typical join statement (for MySQL database):
> Arguments-
> begin_date:date end_date:date="2037-01-01"
> SELECT search_string, search_result,  search_date, result_category,
> legal_category.name
> FROM search_log LEFT JOIN legal_category ON
> search_log.result_category=legal_category.code
> <dtml-sqlgroup required where>
>   <dtml-sqltest name="begin_date" op="gt" type="nb"
>       column="search_date">
> <dtml-and>
>   <dtml-sqltest name="end_date" type="nb" op="lt"
>       column="search_date" optional>
> </dtml-sqlgroup>

Hey!  An example!

> Good Luck

Thanks.  So I'm still having trouble.  I can't get any of the list
examples to work.

I build a select multiple list, and then try to dtml-in on it.  It
doesn't seem to work.  REQUEST shows in it th other and form
namespaces, as a list, but I can't actually dtml-var it or anything.

q<dtml-var dalist>p
<dtml-in VirtualRootPhysicalPath>
d<dtml-var sequence-item>b
<dtml-var REQUEST>

If you call this with this URL (substitute as needed):

Which is the URL pattern formed by a <SELECT name="dalist:list"

Then you get errors on dalist.  The examples at

Don't work as exactl above.



Outside of a dog, a book is a man's best friend.  Inside a dog it's too dark to read.
        -- Groucho Marx

The Doctor What: Need I say more?                http://docwhat.gerf.org/
[EMAIL PROTECTED]                                                   KF6VNC

Reply via email to