[Zope] How do I create a cursor on a query in Zope

[Marten Quadland]

I'm having trouble getting simply the first option to work.

I'm used to calling sql calls with the -
<!--#in SQL_Statement size=60 start=query_start-->
<!--#in SQL_Statement2 size=60 start=query_start-->
<!--#/in-->
<!--#/in-->

Instead of:
<dtml-in SQL_get_names>
</dtml-in>

Is there a difference?  If I try your meathod, I can't tell if it is working
because it will not print anything to screen.

If I try your second meathod, I can't even add the SQL Meathod.  It doesn't
read the varaibles.

Lastly, if I try my syntax above it fails every time.

Thanks for the help though,
Quad
-----Original Message-----
From: Evan Simpson [mailto:evan@4-am.com]
Sent: Tuesday, August 24, 1999 6:57 PM
To: Marten Quadland
Cc: 'zope@zope.org'
Subject: Re: [Zope] How do I create a cursor on a query in Zope


Ah! Ok, the first approach which pops to mind is this:

Suppose SQL_get_names is the first query, and the others are SQL_get_count1,
..., SQL_get_countn.  You could then write:

<dtml-in SQL_get_names>
  <dtml-var NAME> <dtml-in SQL_get_count1><dtml-var count></dtml-in><dtml-in
SQL_get_count2>...</dtml-in>
</dtml-in>

Next approach; Suppose queries 1..n differ only in the table name and group
field.  Define SQL_get_count(table, gfield, NAME) as:

select <dtml-var gfield>, count(*) as rcount from <dtml-var table>
where <!--#sqltest name=NAME col=name type=string>
group by <dtml-var gfield>

and write:

<dtml-in SQL_get_names>
  <dtml-var NAME> <dtml-in "('table1', 'field1'), ('table2', 'field2'),...">
    <dtml-in "SQL_get_count(table=_['sequence-key'],
gfield=_['sequence-item'],
NAME=NAME)"><dtml-var rcount></dtml-in>
  </dtml-in>
</dtml-in>

Finally, if you want to minimize the number of queries, you could try
defining
SQL_get_count(table, gfield) as a join query, and use a dictionary and pair
of
lists to store results.  That gets pretty hairy to write in DTML, though.

Marten Quadland wrote:

> Sorry for the confusion,
>
> I'm trying to open a cursor on a query say:
>
> Open a cursor:
> select NAME from table
>
> Where I can read one name at a time and perform an action on that name.
For
> example:
>
> select blah, count(*) as table2Count from table2
> where table2.name = NAME
> group by blah
>
> select blah2, count(*) as table3Count from table3
> where table3.name = NAME
> group by blah2
>
> select blah3, count(*) as table4Count from table3
> where table3.name = NAME
> group by blah3
>
> Write a row(NAME, table2count, table3count, table4count)
>
> CLOSE THE CURSOR
>
> The end result would be something like:
>
> NAME            Count of Visits Count of Purchases      Count of Inquiries
> Joe                     1234                    4331
> 5965
> Jane                    3766                    3534
> 35
> Jeff                    3457                    6433
> 3653
> Harry                   5433                      88
> 2854
> Tim                       56                    2434
> 9865
> .
> .
> .
>
> The capitalized NAME is equal to the value of the first response from
> Query1.