[Zope] How does dtml-sqlgroup really work?

[Christian Theune]

Sorry, for the late reply,

seems like not many ppl really use sqltest.

The sqgroup/sqltest combo has some things you didn't find out:

sqltest works like the if, when you add the optional attribute, like this:

    <dtml-sqltest ticker type="nb" optional> (op=eq is the default)

the sqlgroup provides you with some additional tags, the "and" and "or" tag,
so it looks for itself when to set the `and' and 'or' operators, depending if
the previous sqltest did something or didn't create a statement, due to the `optional' tag.

This could be your complete query: 

 SELECT
    company_name,ticker,primary_exchange
 FROM
    company_index
 <dtml-sqlgroup where>
  <dtml-sqltest ticker type=nb optional>
  <dtml-and>
    <dtml-sqltest company_name op=like type=nb optional>
  <dtml-and>
    <dtml-sqltest industry_group type=nb optional>
  <dtml-and>
    <dtml-sqltest industry_subgroup type=nb optional>
  <dtml-and>
    <dtml-sqltest market_cap type=nb optional>
  <dtml-and>
    <dtml-sqltest market_sector type=nb optional>
  <dtml-and>
    <dtml-sqltest primary_exchange type=nb optional>
</dtml-sqlgroup>


Regards

Christian

* Jorge O. Martinez <jmartinez@eMediaMillWorks.com> [020607 18:02]:
> 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 )

-- 
Christian Theune - ct@gocept.com
gocept gmbh & co.kg - schalaunische strasse 6 - 06366 koethen/anhalt
tel.+49 3496 3099112 - fax.+49 3496 3099118 mob. - 0178 48 33 981

reduce(lambda x,y:x+y,[chr(ord(x)^42) for x in 'zS^BED\nX_FOY\x0b'])