[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