[Zope] How does dtml-sqlgroup really work?

Jorge O. Martinez jmartinez@eMediaMillWorks.com
Sat, 08 Jun 2002 13:08:18 -0400


Steve:

Thanks, I'll try this approach. BTW, I got your book and it's really cool (App 
Dev and Cont. Man). Is this in the book and I missed it? Guess I'll have to dig 
deeper into it ;-).

Regards,

Jorge M.

Steve Spicklemire wrote:
> Hi Jorge,
> 
> Here is an example from www.zopeonarope.com, notice that there is only 
> one sqlgroup with a 'where'. The other sqlgroup tags lie between 
> dtml-and tags producing "and" logic. The idea is that you can supply 
> various parameters, and depending which ones are not the empty string, 
> different renderings of the template will result with the correct logic.
> 
>                 01  selectSQL:
>                 02
>                 03  <params>personID="" passwd="" roles="" 
> domains=""</params>
>                 04   select personID, passwd, roles, domains from 
> Passwords_Basic
>                 05   <dtml-sqlgroup where>
>                 06
>                 07   <dtml-sqlgroup>
>                 08   <dtml-if personID>
>                 09   <dtml-sqltest personID type=string>
>                 10   </dtml-if>
>                 11   </dtml-sqlgroup>
>                 12   <dtml-and>
>                 13   <dtml-sqlgroup>
>                 14   <dtml-if passwd >
>                 15   <dtml-sqltest passwd type=string>
>                 16   </dtml-if>
>                 17   </dtml-sqlgroup>
>                 18   <dtml-and>
>                 19   <dtml-sqlgroup>
>                 20   <dtml-if roles >
>                 21   <dtml-sqltest roles type=string>
>                 22   </dtml-if>
>                 23   </dtml-sqlgroup>
>                 24   <dtml-and>
>                 25   <dtml-sqlgroup>
>                 26   <dtml-if domains >
>                 27   <dtml-sqltest domains type=string>
>                 28   </dtml-if>
>                 29   </dtml-sqlgroup>
>                 30
>                 31   </dtml-sqlgroup>
> 
> Maybe give that a try?
> 
> take care,
> -steve
> 
> 
> On Friday, June 7, 2002, at 12:53 PM, Jorge O. Martinez wrote:
> 
>> Hi all:
>>
>> Thanks for your help. In the end I had to use this ugly thing, but it 
>> works, slightly dissapointed I could not get sqltest & sqlgroup to 
>> work....maybe I'll have to time to dig deeper later, and understand it 
>> better when I increase my Zope Zen ;-). Thanks!
>>
>> select company_name,ticker,primary_exchange
>>  from
>> company_index
>>
>> <dtml-if company_name>
>> <dtml-sqlgroup where>
>> <dtml-sqltest company_name op=like type=nb optional>
>> </dtml-sqlgroup>
>> </dtml-if>
>>
>> <dtml-if industry_group>
>> <dtml-sqlgroup where>
>> <dtml-sqltest industry_group op=like type=nb optional>
>> </dtml-sqlgroup>
>> </dtml-if>
>>
>> <dtml-if industry_subgroup>
>> <dtml-sqlgroup where>
>> <dtml-sqltest industry_subgroup op=like type=nb optional>
>> </dtml-sqlgroup>
>> </dtml-if>
>>
>> <dtml-if primary_exchange>
>> <dtml-sqlgroup where>
>> <dtml-sqltest primary_exchange op=like type=nb optional>
>> </dtml-sqlgroup>
>> </dtml-if>
>>
>> <dtml-if market_cap>
>> <dtml-sqlgroup where>
>> <dtml-sqltest market_cap op=like type=nb optional>
>> </dtml-sqlgroup>
>> </dtml-if>
>>
>> <dtml-if market_sector>
>> <dtml-sqlgroup where>
>> <dtml-sqltest market_sector op=like type=nb optional>
>> </dtml-sqlgroup>
>> </dtml-if>
>>
>> order by company_name
>>
>> Charlie Reiman wrote:
>>
>>> You need some 'and's. Try
>>> <dtml-sqltest ticker op=eq type=nb>
>>> <dtml-and>
>>> <dtml-sqltest...>
>>> Be happy it was an easy problem.
>>>
>>>> -----Original Message-----
>>>> From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of 
>>>> Jorge
>>>> O. Martinez
>>>> Sent: Friday, June 07, 2002 7:41 AM
>>>> To: zope@zope.org
>>>> Subject: [Zope] How does dtml-sqlgroup really work?
>>>>
>>>>
>>>> Hi:
>>>>
>>>> Not sure if this is a DB question, or DTML question, so I'm
>>>> sending it to the
>>>> general Zope list.
>>>>
>>>> I've been banging my head over this relatively simple query, which 
>>>> (if my
>>>> understanding of the dtml-sqltest tag is correct), should be
>>>> fine. The idea is
>>>> simple, get 3 columns from company_index, and display the
>>>> results, if there's
>>>> further constrains set (like ticker, company_name, etc.), taylor
>>>> the query
>>>> accordingly:
>>>>
>>>> select company_name,ticker,primary_exchange
>>>>  from
>>>> company_index
>>>> <dtml-sqlgroup where>
>>>> <dtml-sqltest ticker op=eq type=nb>
>>>> <dtml-sqltest company_name op=like type=nb>
>>>> <dtml-sqltest industry_group op=eq type=nb>
>>>> <dtml-sqltest industry_subgroup op=eq type=nb>
>>>> <dtml-sqltest market_cap op=eq type=nb>
>>>> <dtml-sqltest market_sector op=eq type=nb>
>>>> <dtml-sqltest primary_exchange op=eq type=nb>
>>>> </dtml-sqlgroup>
>>>>
>>>> but it's not happy. It keeps displaying errors like this:
>>>>
>>>> *************
>>>>
>>>> Zope has encountered an error while publishing this resource.
>>>>
>>>> Error Type: KeyError
>>>> Error Value: industry_group
>>>>
>>>> Troubleshooting Suggestions
>>>>
>>>>     * This resource may be trying to reference a nonexistent object or
>>>> variable industry_group.
>>>>     * The URL may be incorrect.
>>>>     * The parameters passed to this resource may be incorrect.
>>>>     * A resource that this resource relies on may be
>>>> encountering an error.
>>>>
>>>> For more detailed information about the error, please refer to
>>>> the HTML source
>>>> for this page.
>>>>
>>>> If the error persists please contact the site maintainer. Thank
>>>> you for your
>>>> patience.
>>>>
>>>> **************
>>>>
>>>> Which in my opinion does not make sense. I intedended for only
>>>> one var to be
>>>> set, and leave the others non-existing alone. I get this error
>>>> when I only set
>>>> the company_name, and I expect my query to be:
>>>>
>>>> select company_name,ticker,primary_exchange
>>>>  from
>>>> company_index
>>>> where company_index like 'Value I passed%'
>>>>
>>>> Shouldn't sqltest should recognize that, and not raise an error?
>>>>
>>>> -------------------------
>>>>
>>>> I used a variation, which I don't think should be used because 
>>>> supposedly
>>>> sqltest's job is to test if a var is set, and include it in my
>>>> 'where' if it
>>>> is. As you can see, I check with a <dtml-if ..> if the var is
>>>> set, but I think
>>>> I should not have to do it since, I think based on docs I've read, that
>>>> <dtml-sqltest...> should do that.
>>>>
>>>> select company_name,ticker,primary_exchange
>>>>  from
>>>> company_index
>>>> <dtml-sqlgroup where>
>>>> <dtml-if ticker>
>>>> <dtml-sqltest ticker op=eq type=nb>
>>>> </dtml-if>
>>>> <dtml-if company_name>
>>>> <dtml-sqltest company_name op=like type=nb>
>>>> </dtml-if>
>>>> <dtml-if industry_group>
>>>> <dtml-sqltest industry_group op=eq type=nb>
>>>> </dtml-if>
>>>> <dtml-if industry_subgroup>
>>>> <dtml-sqltest industry_subgroup op=eq type=nb>
>>>> </dtml-if>
>>>> <dtml-if market_cap>
>>>> <dtml-sqltest market_cap op=eq type=nb>
>>>> </dtml-if>
>>>> <dtml-if market_sector>
>>>> <dtml-sqltest market_sector op=eq type=nb>
>>>> </dtml-if>
>>>> <dtml-if primary_exchange>
>>>> <dtml-sqltest primary_exchange op=eq type=nb>
>>>> </dtml-if>
>>>> </dtml-sqlgroup>
>>>>
>>>> By the way, this way does not work either, I also get an error
>>>> using the above
>>>> syntax:
>>>>
>>>> ****************
>>>>
>>>> Zope Error
>>>>
>>>> Zope has encountered an error while publishing this resource.
>>>>
>>>> Error Type: DatabaseError
>>>> Error Value: (933, 'ORA-00933: SQL command not properly ended')
>>>>
>>>> Troubleshooting Suggestions
>>>>
>>>>     * The URL may be incorrect.
>>>>     * The parameters passed to this resource may be incorrect.
>>>>     * A resource that this resource relies on may be
>>>> encountering an error.
>>>>
>>>> For more detailed information about the error, please refer to
>>>> the HTML source
>>>> for this page.
>>>>
>>>> If the error persists please contact the site maintainer. Thank
>>>> you for your
>>>> patience.
>>>>
>>>> ****************
>>>>
>>>> Any hints are gretly appreciated.
>>>>
>>>> Regards,
>>>>
>>>> Jorge M.
>>>>
>>>> -- 
>>>> Jorge O. Martinez
>>>> MIS Senior Associate
>>>> eMediaMillWorks
>>>> 1100 Mercantile Lane, Suite 119
>>>> Largo, MD 20774
>>>> E-mail  => jmartinez@eMediaMillWorks.com
>>>> Phone   => (301)883-2482 ext. 105
>>>> Fax     => (301)883-9754
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Zope maillist  -  Zope@zope.org
>>>> 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 )
>>>>
>>
>>
>>
>> -- Jorge O. Martinez
>> MIS Senior Associate
>> eMediaMillWorks
>> 1100 Mercantile Lane, Suite 119
>> Largo, MD 20774
>> E-mail  => jmartinez@eMediaMillWorks.com
>> Phone   => (301)883-2482 ext. 105
>> Fax     => (301)883-9754
>>
>>
>>
>> _______________________________________________
>> Zope maillist  -  Zope@zope.org
>> 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 )
> 
> 



-- 
Jorge O. Martinez
MIS Senior Associate
eMediaMillWorks
1100 Mercantile Lane, Suite 119
Largo, MD 20774
E-mail  => jmartinez@eMediaMillWorks.com
Phone   => (301)883-2482 ext. 105
Fax     => (301)883-9754