[egenix-users] Help with Full-Text Query in SQL Method

[Programmer]

This is more of a Zope problem than a mxODBC problem but I thought I
might try bouncing it here. I have a table where TWO columns must be
full-text searchable (the reasoning is beyond me!). Additionally, there
is also several fixed value qualifiers. 
So to test the full-text query I came up with:
 
select ResultID, Synopsis from RESULT
where contains (Synopsis, '"text"')
or contains (Description, '"text"')
 
This query works fine but I run into trouble with Zopifying it. I can't
use dtml-sqlvar because it's quoting messes up the way text must be
quoted in this type of query. So I end up with this - yes it's ugly.
 
select ResultID, Synopsis from RESULT
where contains (Synopsis, '"<dtml-var search_text null="">"')
or contains (Description, '"<dtml-var search_text null="">"')
 
But it works. So now I add the qualifiers and the SQL-Method gets kinda
complicated:
 
select ResultID, AcronymID, Synopsis from RESULT
<dtml-sqlgroup where>
  <dtml-sqltest ClassificationID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest AcronymID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest ProcessID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest Priority op=eq type=int optional>
<dtml-and>
  <dtml-sqltest ResTypeID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest DepartmentID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest SoftwareID op=eq type=int optional>
<dtml-and>
contains (Synopsis, '"<dtml-var search_text null="">"')
<dtml-or>
  <dtml-sqltest ClassificationID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest AcronymID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest ProcessID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest Priority op=eq type=int optional>
<dtml-and>
  <dtml-sqltest ResTypeID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest DepartmentID op=eq type=int optional>
<dtml-and>
  <dtml-sqltest SoftwareID op=eq type=int optional>
<dtml-and>
contains (Description, '"<dtml-var search_text null="">"')
</dtml-sqlgroup>
 
Problem here is if I run this with only the full-text variable I get
this:
 
select ResultID, AcronymID, Synopsis from RESULT
where
(contains (Synopsis, '"certs"')
and contains (Description, '"certs"')
)

The OR gets replaced with AND. Any ideas what I'm missing here? Is there
perhaps a less ugly way to do this?
 
Thanks!
 
M. A. Ruberto (Programmer)
Every piece of knowledge must have a single, unambiguous, authoritative
representation within a system.
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: /mailman-archives/egenix-users/attachments/20070504/e0d26b23/attachment.htm