785.TXT
=====================================================================
SEARCH AND REPLACE
=====================================================================
PRODUCT: R:BASE VERSION: 4.5+ or Higher
=====================================================================
CATALOG: Programming in R:BASE AREA : Logic & Data Manipulation
=====================================================================
R:BASE 4.5 Plus! added a new SuperMath function, SRPL, to do string
search and replace. This function lets you search for and replace
just part of a field; you no longer need to replace an entire TEXT
or NOTE field. The following program demonstrates the use of the
SRPL function. In addition to using the SRPL function, the program
queries system tables to present only TEXT or NOTE columns for
selection. It prompts the user for the old string and for the
replacement string. The user can then opt to change all rows that
contain the old string, or choose to verify each change.
The program builds an UPDATE command, which is then executed as an
ampersand variable to change all rows. For more information about
using ampersand variables, see "Working With Variables" in the
Sept/Oct 1993 Exchange, Fax doc #738. A DECLARE CURSOR is used to
verify each change. For more information about DECLARE CURSOR, see
"Using Cursors" in this issue of the Exchange.
*(SEARCH.CMD - Search and replace program)
CLS
CLEAR ALL VAR
SET ERROR VAR VsEvar
SET MESSAGES OFF
SET ERROR MESSAGES OFF
CLS
LABEL toploop
CLS FROM 3,42 TO 7,78 BLACK
CLS FROM 2,40 TO 6,77 CYAN
WRITE ' RB:Search' AT 3,52 YELLOW ON CYAN
WRITE ' Text Search and Replace Program.' +
AT 5,42 YELLOW ON CYAN
CHOOSE VsTblview FROM #TBLVIEWS AT 5 12
IF VsEvar <> 0 THEN
PAUSE 2 USING 'No tables or views available.'
GOTO alldone
ENDIF
IF VsTblview = '[Esc]' THEN
GOTO alldone
ENDIF
SET VAR VsTabid = sys_table_id FROM sys_tables +
WHERE sys_table_name = .VsTblview
SELECT COUNT(*) INTO VsEdiCols FROM sys_columns +
WHERE sys_table_id = .VsTabid AND +
(sys_data_type = 3 OR sys_data_type = 8)
IF VsEdiCols = 0 THEN
PAUSE 2 USING +
'This table has no Text or Note columns to modify.' +
AT CENTER,CENTER DEFAULT
GOTO toploop
ENDIF
CHOOSE VsColumn FROM #VALUES FOR sys_column_name +
FROM sys_columns WHERE sys_table_id = .VsTabid AND +
(sys_data_type = 3 OR sys_data_type = 8) CASCADE
IF VsColumn = '[Esc]' THEN
GOTO toploop
ENDIF
SET VAR VsColLen INT = sys_length FROM sys_columns +
WHERE sys_column_name = .VsColumn
IF VsColLen > 40 THEN
SET VAR VsColLen = 40
ENDIF
CLS FROM 10 12 TO 18 72 BLACK
CLS FROM 9 10 TO 17 71 GREEN
SET VAR VsOldString TEXT, VsNewString TEXT
WRITE 'Enter the old text string to search for.' +
AT 10 14 BLACK ON GREEN
FILLIN VsOldString = .VsColLen AT 11 14 YELLOW ON GREEN
IF VsOldString IS NULL THEN
GOTO toploop
ENDIF
WRITE 'Enter the new replacement string.' +
AT 12 14 BLACK ON GREEN
FILLIN VsNewString = .VsColLen AT 13 14 YELLOW ON GREEN
IF VsNewString IS NULL THEN
GOTO toploop
ENDIF
SNAP search.$$$ FROM 1,1 TO 25,80
SELECT COUNT(*) INTO VsNumRows FROM &VsTblview +
WHERE &VsColumn CONTAINS .VsOldString
IF VsNumRows = 0 THEN
PAUSE 2 USING 'No matching rows found.' +
AT CENTER,CENTER DEFAULT
GOTO toploop
ENDIF
WRITE 'ALL rows containing the old string +
will be replaced.' AT 15 14 RED ON GREEN
WRITE .VsNumRows, 'rows will be updated.' +
AT 16 24 RED ON GREEN
DIALOG 'Confirm each change?' +
VsConfirm, VsKey YES AT 19
IF VsConfirm = 'NO' THEN
DIALOG 'Continue with update of all rows?' +
VsOk VsEnd YES AT 19
IF VsOk = 'NO' THEN
GOTO alldone
ELSE
SET VAR VsUpdate = +
('UPDATE' & .VsTblview & 'SET' & .VsColumn + +
'=' + '(SRPL(' + .VsColumn + ',' + '''' + +
.VsOldString + '''' + ',' + '''' + .VsNewString + +
'''' + ',0))' & 'WHERE' & .VsColumn & + 'LIKE' & +
'''%' + .VsOldString + '%''')
&VsUpdate
CLS FROM 15 12 TO 17 68 GREEN
IF VsEvar = 0 THEN
WRITE 'Update successful,' .VsNumRows, +
'rows updated.' AT 16 14 RED ON GREEN
PAUSE 2
ELSE
WRITE 'Update did not complete.' +
AT 16 14 RED ON GREEN
PAUSE 2
ENDIF
ENDIF
ELSE
SET VAR VsErrCnt INT = 0, VsUpdCnt INT = 0, +
VsOld TEXT, VsNew TEXT
SET VAR VsWhereString = ('%'+.VsOldString+'%')
DROP CURSOR c1
DECLARE c1 CURSOR FOR SELECT &VsColumn +
FROM &VsTblview +
WHERE &VsColumn LIKE .VsWhereString
OPEN c1
FETCH c1 INTO VsOld VsInd1
WHILE SQLCODE <> 100 THEN
SET VAR VsNew = +
(SRPL(.VsOld,.VsOldString,.VsNewString,0))
CLS FROM 16 9 TO 19 73 BLACK
CLS FROM 15 8 TO 18 72 CYAN
WRITE 'Old row value: ', .VsOld AT 16 10 +
BLACK ON CYAN
WRITE 'New row value: ', .VsNew AT 17 10 +
RED ON CYAN
DIALOG 'Change this row?' VsOk VsEnd YES AT 19
IF VsOk = 'yes' THEN
UPDATE &VsTblview SET &VsColumn = .VsNew +
WHERE CURRENT OF c1
IF VsEvar <> 0 THEN
SET VAR VsErrCnt = -1
ELSE
SET VAR VsUpdCnt = (.VsUpdCnt + 1)
ENDIF
ENDIF
FETCH c1 INTO VsOld VsInd1
ENDWHILE
DROP CURSOR c1
DISPLAY search.$$$ AT 1
IF VsErrCnt >= 0 THEN
SET VAR VsMsg = +
( 'Update successful,' & CTXT(.VsUpdCnt) & +
'rows updated.' )
PAUSE 2 USING .VsMsg AT 16 DEFAULT
ELSE
PAUSE 2 USING 'Update did not complete.' +
AT 16 DEFAULT
ENDIF
ENDIF
CLS
*(reset)
LABEL alldone
CLEAR VAR Vs%
SET ERROR VAR OFF
CLS
SET MESSAGES ON
SET ERROR MESSAGES ON
RETURN
display information screen
select a table or view
Retrieve the table id number from the system table, SYS_TABLES. The
table id number is used to retrieve the columns. A listing of all
system tables and columns is located in Appendix D of the Startup &
New Features Guide
Make sure there are TEXT or NOTE columns in the selected table. The
system table, SYS_COLUMNS, is queried using the table id - a TEXT
column has a datatype number of 3 a NOTE column has a datatype number
of 8
The system table, SYS_COLUMNS, is queried using the table id. A menu
of TEXT and NOTE columns from the selected table is displayed. Select
a column from the menu
Find the length of the selected column by querying the system table
SYS_COLUMNS. If the column length is more than 40, the text entered
is limited to 40. It is limited for display purposes, i.e. to fit
in the designated display area on the screen. To let users enter
longer values, one option is to use a FILLIN box. Refer to the
Command Dictionary for information on using a FILLIN box
Prompt for the old and new values If no string is entered the program
aborts and returns to the select a table menu
Take a snapshot of the screen for displaying messages when the update
is complete
Find out how many rows meet the condition. If no matching rows are
found, return to the tables menu
If yes to confirm each change, a DECLARE CURSOR is used to step
through each matching row. If no, an UPDATE command is built to change
all rows
Let the user abort the operation
Build the UPDATE command.
The keywords of the UPDATE command are combined with the user
selected values. The four quotes together tell R:BASE to put a
single quote in the result. The LIKE operator is used so that if the
selected column is indexed, the index will be used in the WHERE
clause to make the search faster. The resulting variable value looks
like this:
Variable = Value Type
-------- ---------------------------- -----
VsUpdate = UPDATE employee SET TEXT
empaddress=(SRPL(empaddress,'D
rive','Dr.',0)) WHERE
empaddress LIKE '%Drive%'
Notice that the contents of the variable look just like the UPDATE
command as you would enter it at the R> prompt.
Execute the UPDATE command
Set up the WHERE clause for the DECLARE CURSOR to do row by row updates
Define a cursor to point to all the matching rows
Display the old and new values and prompt to confirm the change
Execute an UPDATE command to change the row the cursor is currently
pointing at
The two count variables indicate if the update failed on any row
All variables in this command file start with Vs to make it easy to
clear them when done