[Zope] Z SQL and python script question

[Thomas M G Bennett]

This response is totally based on if I really understood your question correctly. ;-)

We use this in a search page which I've shortened for example purpose.  For this 
example we have two text boxes and a selection box between the two text boxes.  The 
selection box has a choice of 'and' or 'or'. I think this would work for what you are trying 
to do or something similar to this rather than use a python script to join the text.

description is a field in table accession
keybool is 'and' or 'or'
and if I remember correctly ~* does a case insensitive match

ZSQL Method:
ARGUMENTS: keyword keyword2 keyword3

Select * from accession where (lower (description)~*<dtml-sqlvar keyword type="string">)
 <dtml-if keyword2> <dtml-var keybool> (lower (description)~*<dtml-sqlvar keyword2 
type="string">)</dtml-if>
 <dtml-if keyword3> <dtml-var keybool2> (lower (description)~*<dtml-sqlvar keyword3 
type="string">)</dtml-if>
 order by accnum ASC


Or if you know there is text in both boxes, why not just :

Select * from my_table where (lower (description)~*'<dtml-sqlvar keyword type="string"> 
<dtml-sqlvar keyword2 type="string">')



Below was what I was using before I found out about dtml-sqlvar to put a / escape 
character in front of apostophe for entering into a postgresql database if one was found 
within the text submitted from a form.  Otherwise the text was truncated at the first 
apostrophe it would find.  Actually looking at the script, I think this is the original one that I 
wrote for escaping " for MySQL


ZSQL Method
ARGUMENTS: username email question subject

INSERT INTO askref VALUES
('<dtml-var username>',
 '<dtml-var email>',
 '<dtml-var "qf(question)">',
 '<dtml-var "qf(subject)">'
 )


In the example below you could just as easily pass 'text' and 'text2' and join them for your 
purpose.

PYTHON SCRIPT: qf.py

#! /usr/local/bin/python
import string
def qf(self,text):
   text2=""
   for line in text:
      line = string.replace(line,"\'","\\\'")
      text2 = text2 + string.join(line,"")
  return text2



7/29/2003 11:09:27 AM, Rex McKanry <rmckanry@yahoo.com> wrote:

>Hi,
>I'm trying to join or add two text boxes from a Zope
>web page to use in a postgresql database search. I
>have written a python script that adds the two strings
>I'm passing for me and am passing the to strings to
>the sql query but am unsure how to call the python
>script from the sql statement. Does any one have any
>ideas??
>
>Thanks,
>Rex
>
>__________________________________
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>http://sitebuilder.yahoo.com
>
>_______________________________________________
>Zope maillist  -  Zope@zope.org
>http://mail.zope.org/mailman/listinfo/zope
>**   No cross posts or HTML encoding!  **
>(Related lists - 
> http://mail.zope.org/mailman/listinfo/zope-announce
> http://mail.zope.org/mailman/listinfo/zope-dev )
>

-----------------------------------------------------------------
Thomas McMillan Grant Bennett      Appalachian State University
Computer Consultant III            University Library
Voice:  828 262 6587	           FAX:    828 262 2797

"Windows: A 32-bit GUI on top of a 16-bit wrapper around an 8-bit 
interpretation of a 4-bit operating system written by a 2-bit 
company."   -David Simmons