[Zope] How does dtml-sqlgroup really work?
Jorge O. Martinez
jmartinez@eMediaMillWorks.com
Fri, 07 Jun 2002 13:53:42 -0400
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