[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