*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
Breck Carter
Last modified: June 13, 1996
mail to: bcarter@bcarter.com
See also The Dreaded Meta-Tilde (II)
"If three DropDownListBoxes are used to pick the DataWindow column name, comparison operator and search value, how should SetFilter be called?"
Such an innocent question! Little did I know that it would turn into a roller coaster ride of tildes, metacharacters and DataWindow function calls in a long conversation on CompuServe. It got started with a straightforward answer, just call SetFilter ( ddlb_name.text + ddlb_operator.text + ddlb_value.text ).
Folks who have been down this road before will cry out, "That won't work!" They are both right and wrong. The technique does have limitations but in fact it will work for simple numbers. Figure 1 shows that the string 'Total<=100' can be passed to SetFilter without error. The row with Key = ddd has been excluded because its Total column is larger than 100.
"When I try your suggestion I get 'Expression is not valid' or 'Expecting NUMBER expression'. What's going on?"
Here we go, the roller coaster is moving now. If the column is not numeric, just passing something like "Key=aaa" causes SetFilter to choke. For string columns the argument must have embedded quotes surrounding the value as in "Key='aaa'". But if you try that with a numeric column like "Total='100'" it will die.
SetFilter will work if you get the value right, even for a computed field and even if that field appears in the header band (strange but true), but it will collapse if you reference something that doesn't appear in the DataWindow at all. And all of these errors will manifest themselves as an annoying MessageBox that cannot be suppressed at runtime.
These messages can be confusing. I've often seen "Expecting NUMBER expression" for something like "Key=100" and blurted out, "But it IS a number!" Yes, 100 is a number, but Key isn't. Listing 1 shows what's works and what doesn't for three vastly different data types.
number_column=10 OK
string_column='bbb' OK
string_column="O'Day" OK
string_column='one~~' OK
time_column=time('1') OK
number_column='10' Expecting NUMBER
number_column=bbb Expression not valid
string_column=10 Expecting NUMBER
string_column=bbb Expression not valid
string_column='O'Day' Expression not valid
string_column='one~' Expression not valid
time_column=1 Expecting NUMBER
time_column='1' Expecting NUMBER
unknown_column=1 Expression not valid
We could try telling our users to be more careful: If they want to filter on a numeric column, just type in the value, but if it's a string it has to be surrounded by quotes. Oh, and by the way, if the string contains an embedded single quote you'd better use double quotes around the whole thing like in "O'Day", and if it's a tilde you want you have to type two of them. If the search string contains both kinds of quotes, as in Edward "Ted" O'Day, then you need lots of tildes; just one at a time, though, no pairs. Unless, of course, the string is supposed to contain a tilde.
Case insensitive searching is even more interesting; one approach is ask the user to type the value in lowercase and then put the column name inside a call to lower() to make the whole thing look like lower ( string_column ) = 'car lot'.
If it's a time column, the rules are different: the value not only has to be quoted but also placed inside a call to time(), and if it's minutes you want don't forget to include a zero and a colon like time ( '0:1' ) for one minute.
Yeah, sure. And pigs can fly. What our programs must do is let the users type sensible things like string_column = car lot, or time_column = 1. Maybe even drop the underscores, type blanks between the words that form a column name.
Listing 2 shows what a sensible interpretation of user input might be implemented. Leading and trailing blanks are trimmed from both the column name and value, and the name is translated to the actual DataWindow column name. Everything is converted to lowercase for case-insensitive searching; both the SetFilter call and the choose case statement need this.
integer li_RC
string ls_name
string ls_value
long ll_tilde_pos
long ll_quote_pos
long il_colon_pos
ls_name = trim ( lower ( ddlb_name.text ) )
ls_value = trim ( lower ( ddlb_value.text ) )
choose case ls_name
case 'string column'
ls_name = 'lower(string_column)'
ll_tilde_pos &
= pos ( ls_value, '~~' ) // 1 tilde
do while ll_tilde_pos > 0
ls_value = replace &
( ls_value, &
ll_tilde_pos, &
1, &
'~~~~' ) // 2 tildes
ll_tilde_pos = pos &
( ls_value, &
'~~', &
ll_tilde_pos + 2 )
loop
ll_quote_pos = pos ( ls_value, '"' )
do while ll_quote_pos > 0
ls_value = replace &
( ls_value, &
ll_quote_pos, &
1, &
'~~"' ) // becomes ~"
ll_quote_pos = pos &
( ls_value, &
'"', &
ll_quote_pos + 2 )
loop
ls_value = '"' + ls_value + '"'
case 'time column'
ls_name = 'time_column'
il_colon_pos = pos ( ls_value, ':' )
if il_colon_pos = 0 then
ls_value = '0:' + ls_value
else
if pos ( ls_value, &
':', &
il_colon_pos + 1 ) = 0 then
ls_value = '0:' + ls_value
end if
end if
if not IsTime ( ls_value ) then
MessageBox ( 'Error', 'Sorry!' )
return
end if
ls_value = "time('" + ls_value + "')"
end choose
li_RC = dw_groups.SetFilter &
( ls_name &
+ ddlb_operator.text &
+ ls_value )
if li_RC <> 1 then
MessageBox ( 'Error', 'SetFilter failed' )
return
end if
li_RC = dw_groups.Filter()
if li_RC <> 1 then
MessageBox ( 'Error', 'Filter failed' )
return
end if
For the string column, embedded tilde characters are changed to pairs of tildes, and then embedded double quotes are changed to append a leading tilde; the order of these operations is important. Finally, the string value is surrounded by double quotes for the SetFilter call. All this lets the user type something like string column = edward "ted" o'day and have it work.
For the time column, a zero and a colon is appended in front of the value if no colon is already present. This is also done if there is exactly one colon already present, to implement the assumption that the user will enter times in minutes and/or seconds unless the full hh:mm:ss value is specified. If the result is not valid as a time, an error message is displayed. When all this is done, the value is surrounded by single quotes and a call to time() before calling SetFilter. Now the user can type time column = 15 to match 00:15:00, or 15:59 to match 00:15:59.
Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.