This problem has been bugging for a long time now and I can't seem to find the answer. I have a database that is searchable with SQL methods. in simple mode the SQL method and the Search Interface work well. Now the problem is when I have a form, which (i thought) would have to be extensible. So I thought to use record-based input forms, like this <searchitem.searchterm:records> # record 1 <searchitem.connector:records (AND/OR/NOT)> <searchitem.searchterm:records> # record 2 <searchitem.connector:records (AND/OR/NOT)> <searchitem.searchterm:records> # record 3 . . . At the moment this has only two fields, but it could also be extended to have a choice of other search fields (Author, Title, Publishing year....), but let's keep it simple. In theory this would be extensible ad nauseam The SQL Method then has the following code to build a query string <dtml-in searchitem> <dtml-if sequence-start>TITEL LIKE <dtml-var expr="'%s%s%s%s%s' %(chr(39), '%', 'searchterm', '%', chr(39))"> <dtml-else><dtml-var connector> TITEL LIKE <dtml-var expr="'%s%s%s%s%s' %(chr(39), '%', searchterm, '%', chr(39))"> </dtml-if> </dtml-in> Whatever I try, this fails with a KeyError for a Key 0 I tracked this down to the dependance of SQL Methods the regular REQUEST keys. If this is right, it means the 'searchterm' and 'connector' of the example would not be available which would explain the KeyError. I found this particularly hard to find out, as using SQL Methods is indirect and database errors may also get in the way. A few questions: - is my analysis correct, or am I way off track - if it is correct, why does it work this way - how can I make this work (I can come with workarounds myself, the point is they all lack flexibility) Rik
Hello Rik, it's good to know that I am not the only one with this problem. I posted some days ago but didn't get any answer. I can't even get "normal" REQUEST variables to evaluate correct. I tried an argument arg:int=0 and in SQL SELECT * FROM DATA <dtml-sqlgroup where> <dtml-if arg> <dtm-sqltest arg type=int> </dtml-if> </dtml-sqlgroup> and I always get SELECT * FROM DATA WHERE ARG=0 Shouldn't that be impossible? To your problem: Did you try searchitem.searchterm? Although "searchitem." shouldn't be needed. Maybe you need to use REQUEST.searchitem or did you define searchitem as argument in the ZSQL form? So long Ulli -- ----------------- Die Website Effizienzer ------------------ luna-park Bravo Sanchez, Vollmert, Wisser GbR Ulrich Wisser mailto:u.wisser@luna-park.de Alter Schlachthof, Immenburgstr. 20 Tel +49-228-9654055 D-53121 Bonn Fax +49-228-9654057 ------------------http://www.luna-park.de ------------------
Hello Rik,
it's good to know that I am not the only one with this problem. I posted some days ago but didn't get any answer. I can't even get "normal" REQUEST variables to evaluate correct.
Perhaps I could help you a bit with your question
I tried an argument arg:int=0 and in SQL
Where did you put the arg:int=0? I can't think of a place where this would be valid as such. What is your intention with this method? I you have a form you could set the argument in a hidden field (<input type="hidden" name="arg:int" value="0">) you will be able to access this easily in a SQL Method. Obviously it will alway be evaluated as true in the following method
SELECT * FROM DATA <dtml-sqlgroup where> <dtml-if arg> <dtm-sqltest arg type=int> </dtml-if> </dtml-sqlgroup>
and I always get
SELECT * FROM DATA WHERE ARG=0
Shouldn't that be impossible?
Not per se.
To your problem:
Did you try searchitem.searchterm? Although "searchitem." shouldn't be needed. Maybe you need to use REQUEST.searchitem or did you define searchitem as argument in the ZSQL form?
The records addition makes the searchsitem available as a list of records with fields. It may be iterated with a dtml-in. In this case that would be <dtml-in searchitem>. The sequence-item then has the 'fields' of the record as its attributes, so that in this case you can access them with <dtml-var connector> and <dtml-var searchterm>. This works nicely in a normal DTML Method. The problem seems to be that the SQL Method is only able to retrieve items from the REQUEST.form dictionary, and the records list is in the REQUEST object itself. This seems not to be directly accessibly from the SQL Method, because it gets its values from the stated input fields (the ones you define as tokens in a SQL Method). But I'm not completely sure. Rik
The records addition makes the searchsitem available as a list of records with fields. It may be iterated with a dtml-in. In this case that would be <dtml-in searchitem>. The sequence-item then has the 'fields' of the record as its attributes, so that in this case you can access them with <dtml-var connector> and <dtml-var searchterm>. This works nicely in a normal DTML Method.
The problem seems to be that the SQL Method is only able to retrieve items from the REQUEST.form dictionary, and the records list is in the REQUEST object itself. This seems not to be directly accessibly from the SQL Method, because it gets its values from the stated input fields (the ones you define as tokens in a SQL Method). But I'm not completely sure.
OK, forget about this. I was completely off track. Somehow on of the methods on the way apparently got messed up. The proposed way of including a search worked as I had intended and in exactly that way. Sorry to have posted misleading messages. Please ignore them Rik
Hi Rik,
Perhaps I could help you a bit with your question
I tried an argument arg:int=0 and in SQL
Where did you put the arg:int=0? I can't think of a place where this would be valid as such. What is your intention with this method?
in the argument list of the ZSQL method. So the method should import arg from REQUEST and if there isn't any "arg" value in the Request arg should be set to 0.
SELECT * FROM DATA <dtml-sqlgroup where> <dtml-if arg> <dtm-sqltest arg type=int> </dtml-if> </dtml-sqlgroup>
and I always get
SELECT * FROM DATA WHERE ARG=0
Shouldn't that be impossible?
Not per se.
When arg is 0, how can <dtm-if arg> evaluate it to true? So long Ulli -- ----------------- Die Website Effizienzer ------------------ luna-park Bravo Sanchez, Vollmert, Wisser GbR Ulrich Wisser mailto:u.wisser@luna-park.de Alter Schlachthof, Immenburgstr. 20 Tel +49-228-9654055 D-53121 Bonn Fax +49-228-9654057 ------------------http://www.luna-park.de ------------------
Ulrich Wisser wrote:
Hi Rik,
Perhaps I could help you a bit with your question
I tried an argument arg:int=0 and in SQL
Where did you put the arg:int=0? I can't think of a place where this would be valid as such. What is your intention with this method?
in the argument list of the ZSQL method. So the method should import arg from REQUEST and if there isn't any "arg" value in the Request arg should be set to 0.
SELECT * FROM DATA <dtml-sqlgroup where> <dtml-if arg>
If you want to exclude arg if it is 0 this tag should read <dtml-if "arg and arg<>0"> if you want to leave arg unset, just don't pass it to you SQL method (either in your form or otherwise)
<dtm-sqltest arg type=int>
according to the sql documentation sqltest is used for testing against sql values not against variables in the current namespace, though I've neve used it myself, so I may be mistaken (the documentation is not overly clear)
</dtml-if> </dtml-sqlgroup>
and I always get
SELECT * FROM DATA WHERE ARG=0
Shouldn't that be impossible?
Not per se.
When arg is 0, how can <dtm-if arg> evaluate it to true?
Because there is a variable arg and its value is 0 ;-) 0 does not mean false in DTML or Python. Does this make it clearer? Rik
participants (3)
-
Rik Hoekstra -
Rik Hoekstra -
Ulrich Wisser