I'm trying to do a "like" query in a database... I've created a ZSQL thing like this select foo,bar from xxx <dtml-sqlgroup where> <dtml-sqltest ABC op=like type=string> </dtml-sqlgroup> order by foo Now, in the documentation, it claims that the dtml-sqltest operation "like" puts a % at the end of the input string. This is false. So I'm trying to force-feed a % to it to no avail... Putting a % into any DTML seems to indicate an end of file character. Nice. So just how does one go about actually doing a non-exact string match from Zope against a relational DB? Also, I had tried: <ul> <dtml-in expr="zsqlthingy(ABC=Blah)"> <li> <dtml-var login> <dtml-var phone> </li> </dtml-in> </ul> And it tells me "global name Blah is not found"... I had to go create a DTML Method called Blah that contains just the line "<dtml-return "Blah">"... is there some way to just pass a string value (or numerical value) where a string (or number) are expected as input? Finally, if I've got this structure: ROOT ---------------------> (ZSQL scripts folder) |--------------------->(Some folder containing some DTML stuff) How can I call the ZSQL scripts from their folder? They're not being acquired because they are not above the DTML stuff in the heirarchy. Thanks. LL+P, Stephan
First of all, the LIKE comparison happens in the ZSQL method, for example (postgresql): SELECT * FROM myTable WHERE field ~* '.*&dtml-thisfield;.*' ; Now, with regards to passing the var thisfield, you can do it one of two ways. One, use default values in the ZSQL method's argument list, for example: thisfield:string="" (will default to empty but existing var) thisfield:string="squid" (will default to "squid") OR you can just say <dtml-call "mySQLmethodhere(thisfield='squid')"> Hope this helps. Liz -----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org] On Behalf Of Stephan Vladimir Bugaj Sent: Wednesday, May 29, 2002 10:12 PM To: zope list Subject: [Zope] Accessing a Database I'm trying to do a "like" query in a database... I've created a ZSQL thing like this select foo,bar from xxx <dtml-sqlgroup where> <dtml-sqltest ABC op=like type=string> </dtml-sqlgroup> order by foo Now, in the documentation, it claims that the dtml-sqltest operation "like" puts a % at the end of the input string. This is false. So I'm trying to force-feed a % to it to no avail... Putting a % into any DTML seems to indicate an end of file character. Nice. So just how does one go about actually doing a non-exact string match from Zope against a relational DB? Also, I had tried: <ul> <dtml-in expr="zsqlthingy(ABC=Blah)"> <li> <dtml-var login> <dtml-var phone> </li> </dtml-in> </ul> And it tells me "global name Blah is not found"... I had to go create a DTML Method called Blah that contains just the line "<dtml-return "Blah">"... is there some way to just pass a string value (or numerical value) where a string (or number) are expected as input? Finally, if I've got this structure: ROOT ---------------------> (ZSQL scripts folder) |--------------------->(Some folder containing some DTML stuff) How can I call the ZSQL scripts from their folder? They're not being acquired because they are not above the DTML stuff in the heirarchy. Thanks. LL+P, Stephan _______________________________________________ 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 ) -----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org] On Behalf Of Stephan Vladimir Bugaj Sent: Wednesday, May 29, 2002 10:12 PM To: zope list Subject: [Zope] Accessing a Database I'm trying to do a "like" query in a database... I've created a ZSQL thing like this select foo,bar from xxx <dtml-sqlgroup where> <dtml-sqltest ABC op=like type=string> </dtml-sqlgroup> order by foo Now, in the documentation, it claims that the dtml-sqltest operation "like" puts a % at the end of the input string. This is false. So I'm trying to force-feed a % to it to no avail... Putting a % into any DTML seems to indicate an end of file character. Nice. So just how does one go about actually doing a non-exact string match from Zope against a relational DB? Also, I had tried: <ul> <dtml-in expr="zsqlthingy(ABC=Blah)"> <li> <dtml-var login> <dtml-var phone> </li> </dtml-in> </ul> And it tells me "global name Blah is not found"... I had to go create a DTML Method called Blah that contains just the line "<dtml-return "Blah">"... is there some way to just pass a string value (or numerical value) where a string (or number) are expected as input? Finally, if I've got this structure: ROOT ---------------------> (ZSQL scripts folder) |--------------------->(Some folder containing some DTML stuff) How can I call the ZSQL scripts from their folder? They're not being acquired because they are not above the DTML stuff in the heirarchy. Thanks. LL+P, Stephan _______________________________________________ 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 )
On Wed, May 29, 2002 at 07:12:16PM -0700, Stephan Vladimir Bugaj wrote:
I'm trying to do a "like" query in a database...
I've created a ZSQL thing like this select foo,bar from xxx <dtml-sqlgroup where> <dtml-sqltest ABC op=like type=string> </dtml-sqlgroup> order by foo
simplify, simplify, and safety. First, an actual working snippet. select * from brands where brand like '<dtml-var brand sql_quote>%' Note the sql_quote. It is urgent, if you ever have any, even accidental exposure to the Internet. ALWAYS use sqlvar or sql_quote. (Yeah, I can think of some exceptions, but they just pass the work off into a pre-validation routine.) Now, this has not been tested, but should work fine: select foo,bar from xxx <dtml-if bah> where baz like '<dtml-var bah sql_quote>%' </dtml-if> order by foo Jim Penny
Stephan Vladimir Bugaj
... Also, I had tried: <ul> <dtml-in expr="zsqlthingy(ABC=Blah)"> <li> <dtml-var login> <dtml-var phone> </li> </dtml-in> </ul>
And it tells me "global name Blah is not found"... I had to go create a DTML Method called Blah that contains just the line "<dtml-return "Blah">"... is there some way to just pass a string value (or numerical value) where a string (or number) are expected as input?
If Blah is some kind of callable object, then "<dtml-return "Blah"> returns the object reference without calling it. You would want <dtml-return "Blah()">. If Blah is supposed to be a string, then you want <dtml-return " 'Blah' ">, where I have added spaces to show the single and double quotes more clearly. In othe words, Blah is treated as an object name, Blah() causes the object named Blah to be called, and 'Blah' is treated as a string. Cheers, Tom P
On Wed, 2002-05-29 at 21:12, Stephan Vladimir Bugaj wrote:
I'm trying to do a "like" query in a database...
I've created a ZSQL thing like this select foo,bar from xxx <dtml-sqlgroup where> <dtml-sqltest ABC op=like type=string> </dtml-sqlgroup> order by foo
I run SQL a little different. First I set my variables. <dtml-call expr="REQUEST.set('uid',AUTHENTICATED_USER)"> Then I call the SQL <dtml-in sqlQuery_sql> <dtml-var results> </dtml-in> The SQL contains: select * from Software_Problem_Reports where Problem_Summary like "<dtml-var Problem_Summary>%" I don't know if this is correct but it works for me.
Finally, if I've got this structure:
ROOT ---------------------> (ZSQL scripts folder) |--------------------->(Some folder containing some DTML stuff)
How can I call the ZSQL scripts from their folder? They're not being acquired because they are not above the DTML stuff in the heirarchy.
Using <dtml-with> maybe? -Matt
participants (5)
-
Jim Penny -
Liz Pelletier -
Matt Standish -
Stephan Vladimir Bugaj -
Thomas B. Passin