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:

The Dreaded Meta-Tilde (I)

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.

Figure 1: Simple SetFilter DDLBs

"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.

Listing 1: What's Hot, What's Not

   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.

Listing 2: Sensible SetFilter

   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 )
         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 )
         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
            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!' )
         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' )
   end if

   li_RC = dw_groups.Filter()
   if li_RC <> 1 then
      MessageBox ( 'Error', 'Filter failed' )
   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.

See also: The Dreaded Meta-Tilde (II)

Breck Carter can be reached by phone at (416) 763-5200 or via email at