*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: May 9, 1996
mail to: bcarter@bcarter.com
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.
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
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.)
Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.