[Zope] How does dtml-sqlgroup really work?

Jorge O. Martinez jmartinez@eMediaMillWorks.com
Sat, 08 Jun 2002 15:14:25 -0400


Steve:

Thanks! I'll check out more carefully those chapters as it seems I am missing 
important info.

Regards,

Jorge M.

Steve Spicklemire wrote:
> Oh... I should also point out that there are a lot of similar examples 
> in chapter 12 (and all these examples are downloadable from 
> www.zopeonarope.com). The queries in chapter 12 are all generated 
> automatically from the ZClass definitions of the objects whose instance 
> data are stored in the relational database. These queries happen to be 
> used in the context of several ZPatterns based applications, but the 
> queries themselves have no explicit dependence on ZPatterns.
> 
> good luck!
> -steve
> 
> On Saturday, June 8, 2002, at 01:32 PM, Steve Spicklemire wrote:
> 
>> Hi Jorge,
>>
>> Yup.. it's chapter 13, all about LoginManager and 
>> Security/Authentication.
>>
>> take care,
>> -steve
>>
>> On Saturday, June 8, 2002, at 12:08 PM, Jorge O. Martinez wrote:
>>
>>> 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
>>
> 
> 
> 
> _______________________________________________
> 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