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: May 9, 1996
mail to: bcarter@bcarter.com



Encapsulated DataWindow Searching

Many commercial applications have facilities to aid the power-typist. One popular feature is "searching and scrolling while you type a search string." My favorite example comes from Lotus Magellan for DOS; this ancient program was once called "The Fifth Killer App" (after word processing, spreadsheets, databases and communications.)

Magellan lets you search for text in files across a whole network by just typing the search string and hitting the plus and minus keys to find the next and previous occurrences. You don't have to open a "Find window" or hit any special key to start the search... you just start typing and Magellan knows what to do.

Dealing With Those 1,000-Row Result Sets

You can do the same thing with any DataWindow by calling dwFind every time the user types another character in a SingleLineEdit control. There is no need to make the user tell your program "I want to start searching now."

Figure 1 shows a DataWindow that has automatically scrolled to row 1450 as the user typed "travel - t". With a result set of this size, performance falls just slightly short of instantaneous but is still quite snappy on a 486/66 workstation. All the data is in memory, and although dwFind has been called ten times (once for each keystroke) the response is perfectly acceptable.

For larger result sets performance may suffer. It can be said, however, that a user who truly needs to deal with 10,000 rows also truly needs this kind of search facility, and will be willing to put up with slight delays.

Figure 1: Find-And-Scroll On The Fly

Figure 2 shows a more sophisticated example, albeit with a much smaller result set. Three different SingleLineEdit fields are available for searching on employee name, department and phone number. Here the user has typed in the first four characters of an employee name and the DataWindow has scrolled to line 9. Then the user switched to the department name search field and typed a single letter "r", causing the DataWindow to back up to the top and scroll to line 8.

Each of these search fields is a separate instance of a Standard SingleLineEdit User Object called u_sle_find_string. After placing these sle's on the window, exactly two lines of PowerScript code are needed to make each one work: one line to tell the sle which DataWindow it is supposed to search, and another line to tell it which column to search on. This code may be put in the Window Open event as shown in Listing 1. There is absolutely no need to add or modify code in the DataWindow itself since the SingleLineEdit object contains all the logic for searching.

Figure 2: Multiple Instances of u_sle_find_string

Listing 1: Enabling the SingleLineEdits in the Window Open script

// Initialize search parameters. This is
// all you HAVE to do to use u_sle_find_string.

sle_employee  .idw_search    = dw_phone_numbers
sle_employee  .is_search_col = "emp_name"
sle_department.idw_search    = dw_phone_numbers
sle_department.is_search_col = "dept_name"
sle_phone     .idw_search    = dw_phone_numbers
sle_phone     .is_search_col = "emp_phone"

The variables idw_search and is_search_col can be thought of as "attributes" of the u_sle_find_string object. They are implemented as public instance variables along with a number of others that provide these special features (see Listing 2):

Listing 2: Attributes of u_sle_find_string

public:
DataWindow idw_search     // dw to search
string is_search_col      // column to search
boolean ib_beep           // OK to beep once?
boolean ib_repeat_beep    // OK to keep beeping?
boolean ib_case_sensitive // When comparing?
KeyCode ikc_find_next     // Hotkeys
KeyCode ikc_find_previous
KeyCode ikc_clear_find
KeyCode ikc_scroll_next_page
KeyCode ikc_scroll_next_row
KeyCode ikc_scroll_prior_page
KeyCode ikc_scroll_prior_row

All these attributes are initialized in the constructor event shown in Listing 3. The DataWindow and column attributes (idw_search and is_search_col) are given special "invalid" values so they can be checked later to make sure the program has given them proper values. The other attributes are given valid initial values which may be overridden by the program or even changed on the fly. The "hotkey" features may be disabled by setting the attributes to Null, as in SetNull ( sle_employee.ikc_find_next ).

Listing 3: Initializing Attributes in the u_sle_find_string Constructor Event

// Assign "invalid" initial values.
SetNull ( this.idw_search )
this.is_search_col = ""

// Assign valid initial values.
this.ib_case_sensitive = false
this.ib_beep           = true
this.ib_repeat_beep    = true
this.ikc_find_next         = KeyAdd!
this.ikc_find_previous     = KeySubtract!
this.ikc_clear_find        = KeyEscape!
this.ikc_scroll_next_page  = KeyPageDown!
this.ikc_scroll_next_row   = KeyDownArrow!
this.ikc_scroll_prior_page = KeyPageUp!
this.ikc_scroll_prior_row  = KeyUpArrow!

The heart of u_sle_find_string lies in the pbm_enChange script shown in Listing 4. This PowerBuilder user-defined event corresponds to the Windows EN_CHANGE event, which "is sent when the user has taken an action that may have altered text in an edit control" according to Microsoft.

This is more suited to our purposes than the standard Modified! event which only fires after the user presses Enter or changes focus. It is also better than the Windows EN_UPDATE event (pbm_enUpdate) which is fired just before the user's change is displayed. With pbm_enChange the user gets to see each character appear in the SingleLineEdit before the DataWindow search is executed.

Listing 4: Scrolling on the Fly with a pbm_enChange Script

// Try to find matching row, starting
// from the top.
this.PostEvent ( "ue_find_new" )

There are actually three kinds of searches in u_sle_find_string: start a new search from the top, find next and find previous. These have been implemented as custom user-defined events shown in Listing 5.

When pbm_enChange is executed, it cannot be assumed that the user is searching forward from the current row. For example, the user could have backspaced and deleted a character to cause the DataWindow to effectively "scroll up". For this reason pbm_enChange always causes the search to start over by posting ue_find_new. As they say in the textbooks, I leave it as "an exercise for the reader" to make the obvious performance improvements here.

Listing 5: User-Defined Events for Searching and Scrolling


ue_find_new Event

// Try to find matching row, starting
// from the top.

integer li_RC
long    il_found_row

if this.is_search_val <> this.text then

   // Store new value.

   li_RC = this.of_store_search_val()

   if li_RC = 1 then

      // Try to find matching row, starting
      // from the top every time.
      il_found_row = this.of_find &
          ( 1, this.idw_search.RowCount() )

      // Scroll to the matching row, if one
      // was found and it is different.
      this.of_scroll ( il_found_row )

   end if
end if

ue_find_next Event

long ll_row
long ll_row_count
long il_found_row

// Try to find matching row, looking
// forward from the current row.

ll_row    = this.idw_search.GetRow() + 1
ll_row_count = this.idw_search.RowCount()
if ll_row <= ll_row_count then
   il_found_row = this.of_find &
      ( ll_row, ll_row_count )
else
   il_found_row = -1 // force error
end if

// Scroll to the matching row, if one
// was found and it is different.

this.of_scroll ( il_found_row )

ue_find_previous Event

long ll_row
long il_found_row

// Try to find matching row, looking
// backward from the current row.

ll_row = this.idw_search.GetRow() - 1
if ll_row >= 1 then
   il_found_row = this.of_find ( ll_row, 1 )
else
   il_found_row = -1 // force error
end if

// Scroll to the matching row, if one
// was found and it is different.

this.of_scroll ( il_found_row )

The user-defined events that implement scrolling and searching do so by calling the User Object Functions shown in Listing 6. The of_store_search_val function handles the problem of special characters which cannot be passed directly to dwFind: the tilde and single and double quotes. While most users won't type tildes too often, it is certainly possible for someone to search for something like "Mom's "Big Eats" Diner." This function also handles the problem of case-insensitive matching, and accomplishes all of that by translating the original sle.text value into an edited value suitable for dwFind. Listing 7 shows the private instance variables used by these functions.

Listing 6: User Object Functions for Searching and Scrolling


of_store_search_val User Object Function

// function integer of_store_search_val()
// Save this.text in both original and
// edited formats.

string  ls_temp_val
integer li_pos

// Save original format.
this.is_search_val = this.text

// Convert to lowercase if case-insensitive
// matching is requested.
if this.ib_case_sensitive then
   this.is_edited_val = this.is_search_val
else
   this.is_edited_val &
      = Lower ( this.is_search_val )
end if

// Check to see if further processing
// is necessary (an optimization).

if ( Pos ( this.is_search_val, "~~") > 0 ) &
or ( Pos ( this.is_search_val, "'" ) > 0 ) &
or ( Pos ( this.is_search_val, '"' ) > 0 ) then

   // Put tildes in front of special
   // characters ~'".
   ls_temp_val = this.is_edited_val
   for li_pos = len ( this.is_search_val ) &
            to 1 step -1
      choose case mid ( this.is_search_val, &
                        li_pos, 1 )
         case "~~"
            ls_temp_val &
               = replace ( ls_temp_val, &
                           li_pos, 1, "~~~~" )
         case "'"
            ls_temp_val &
               = replace ( ls_temp_val, &
                           li_pos, 1, "~~'" )
         case "'"
            ls_temp_val &
               = replace ( ls_temp_val, &
                           li_pos, 1, '~~"' )
      end choose
   next
   this.is_edited_val = ls_temp_val
end if
return 1

of_find  User Object Function

// function long of_find()
//    ( long al_from_row, &
//      long al_to_row )

// Call dwFind to match leading
// characters of is_search_col against
// is_edited_val.

long ll_search_val_len
long ll_row

ll_search_val_len = Len ( this.is_search_val )

if this.ib_case_sensitive then
   ll_row = this.idw_search.dwFind &
      ( "mid ( " &
           + this.is_search_col &
           + ", 1, " &
           + String ( ll_search_val_len ) &
           + " ) = '" &
           + this.is_edited_val &
           + "'", &
        al_from_row, &
        al_to_row )
else
   ll_row = this.idw_search.dwFind &
      ( "lower ( mid ( " &
           + this.is_search_col &
           + ", 1, " &
           + string ( ll_search_val_len ) &
           + " ) ) = '" &
           + this.is_edited_val &
           + "'", &
        al_from_row, &
        al_to_row )
end if
return ll_row

of_scroll User Object Function

// subroutine of_scroll &
//    ( long al_found_row )

// Scroll to the matching row, if one
// was found and it is different.

if al_found_row > 0 then
   if this.idw_search.GetRow() &
         <> al_found_row then
      this.idw_search.ScrollToRow &
         ( al_found_row )
   end if
   this.ib_has_beeped = false
else
   if  ( this.ib_beep ) &
   and (    ( not this.ib_has_beeped ) &
         or ( this.ib_repeat_beep  ) ) then
      this.ib_has_beeped = true
      Beep ( 1 )
   end if
end if
return

Listing 7: Private Instance Variables

private:
string is_search_val = "" // raw user input
string is_edited_val = "" // version for dwFind
boolean ib_has_beeped = false
boolean ib_hot_key_pressed = false
boolean ib_has_focus = false

Other Event Considered Very Scary

I'd like it to be otherwise, but I could find no better home for the special keystroke handling than the Other event. Some of my worst errors have been committed in this event and u_sle_find_string is no exception. Never mind rebooting, I had to use Norton Disk Doctor after one mistake (hot tip: don't call MessageBox() from an Other script.)

The Other event is used because it's the only place where you can set Message.Processed to true after processing a keystroke... or at least that's what the documentation says. This is necessary because I wanted to let the user press the plus and minus keys on the numeric keypad (KeyAdd! and KeySubtract!) to do a find next and find previous without having "+" and "-" show up in the sle.text value. They can still press the normal plus and minus keys if they want to search on those characters.

One problem with Other is that it can occur even for a Windows control that doesn't have focus, especially if that control is first in the tab order. This wreaked havoc when a second instance of u_sle_find_string was added to the window: the string in sle_employee was being used even when the user was pressing "+" on sle_department.

The solution is shown in Listing 8: the Other event is completely ignored if the object doesn't currently have focus. Of course, calling GetFocus() in the Other event is another big No-No (it might have no value), so an instance variable ib_has_focus was used. This variable is turned on and off in the GetFocus and LoseFocus events shown in Listing 9.

The GetFocus event also checks the validity of the two required attributes (idw_search and is_search_col), and repeats the search automatically if the Text attribute already contains a search value. This last feature is known as "I've wandered, now take be home!"

Listing 8: Careful Keystroke Handling in the Other Event

if not this.ib_has_focus then
   Return
end if

// Process hot key if pressed.
if KeyDown ( this.ikc_find_next ) then
   // Process each keypress only once.
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true
      // Try to find matching row, looking
      // forward from the current row.
      this.TriggerEvent ( "ue_find_next" )
   end if
   // Prevent key from affecting this.text.
   message.processed = true
   return

elseif KeyDown ( this.ikc_find_previous ) then
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true

      // Try to find matching row, looking
      // backward from the current row.
      this.TriggerEvent &
         ( "ue_find_previous" )
   end if
   Message.Processed = true
   Return

elseif KeyDown ( this.ikc_clear_find ) then
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true
      this.text = ""
   end if
   // Note: Setting Message.Processed = true
   // seems to suppress the assignment of
   // this.text = "" above. Therefore, the
   // "Clear Find" hot key should be
   // carefully chosen to be one that
   // won't affect the contents of
   // this.text itself.  Posting an event
   // and calling a function didn't help.
   return

elseif KeyDown &
          ( this.ikc_scroll_next_page ) then
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true
      this.idw_search.ScrollNextPage()
   end if
   message.processed = true
   return

elseif KeyDown &
          ( this.ikc_scroll_next_row ) then
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true
      this.idw_search.ScrollNextRow()
   end if
   message.processed = true
   return

elseif KeyDown &
          ( this.ikc_scroll_prior_page ) then
   // Process each keypress only once.
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true
      this.idw_search.ScrollPriorPage()
   end if
   message.processed = true
   return

elseif KeyDown &
          ( this.ikc_scroll_prior_row ) then
   if not this.ib_hot_key_pressed then
      this.ib_hot_key_pressed = true
      this.idw_search.ScrollPriorRow()
   end if
   message.processed = true
   return

else
   // Now it's OK to accept
   // another key press.
   this.ib_hot_key_pressed = false
end if

Listing 9: Safety Features in GetFocus and LoseFocus


GetFocus Event

integer li_RC

// Allow processing of Other events.
this.ib_has_focus = true

// Check validity of idw_search and
// is_search_col.
li_RC = this.of_check_column()

if li_RC = 1 then

   // This search hasn't failed... yet.
   this.ib_has_beeped = false

   // If something is present in this.text
   // from a previous use, force it to be
   // used this time to look for a match.
   this.is_search_val = ""
   this.PostEvent ( "ue_find_new" )
end if

LoseFocus Event

// Prevent processing of Other events.
this.ib_has_focus = false

of_check_column User Object Function

// function integer of_check_column()

// Check validity of idw_search and
// is_search_col.

string ls_col_type

if IsNull ( this.idw_search ) then
   MessageBox ( "Error", &
      "idw_search is null" )
   return -1
end if

if Len ( this.is_search_col ) = 0 then
   MessageBox ( "Error", &
      "is_search_col is empty" )
   return -1
end if

ls_col_type = this.idw_search.dwDescribe &
              ( this.is_search_col &
                 + ".Coltype" )

if ls_col_type = "!" then
   MessageBox ( "Error", &
      "is_search_col '" &
         + this.is_search_col &
         + "' not in DataWindow" )
   return -1
end if

if mid ( ls_col_type, 1, 5 ) <> "char(" then
   MessageBox ( "Error", &
      "is_search_col '" &
         + this.is_search_col &
         + "' not string column" )
   return -1
end if
return 1

The u_sle_find_string object stands on its own as an easy-to-use black box. As a Standard User Object it is a lot easier to customize by changing its size, font and other attributes than a Custom User Object (an oxymoron if there ever was one.)


See also: Scroll-While-You-Type Program Code

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