Using DELETEROW_SUB (Functions/Subroutines/Programs)
Created at 14 NOV 1996 02:51PM
DELETEROW_SUB
DELETEROW_SUB( tableName, rowIds, options )
Using DELETEROW_SUB
Permanently deletes one or more rows from an Advanced Revelation table.
tableName
The name of the table from which the row is to be deleted. The table must be attached.
rowIds
An array (@FM-delimited) of row keys to be deleted. To delete all the rows in a table, use an asterisk (*) to mean "all rows". If a select list is active, leave rowIds null and the subroutine will delete only the rows represented in the select list. If a select list is active when you call DELETEROW_SUB, but you list explicit rows to delete in rowIds, the select list is preserved (until you return to TCL).
If you want to delete a DOS file, use the DOS file name as the tableName, including the path.
options
| Option | Meaning |
| A | (Announce) Displays a message in the status line showing the key for each row being deleted, along with the percentage complete |
| S | (Suppress messages) Show no messages at all in the status line. |
| L | (Lock) Locks rows before deleting them. |
| B | (Batch) Puts a transaction around the entire delete process. If any errors occur, all changes are rolled back, and no rows are deleted. |
Values returned
The return status of DELETEROW_SUB is indicated by the system variable @file.error. Multiple errors are delimited with record marks (@RM). The error number appears in field 1 of each @file.error "record", and additional information about the error appears in field 2. Possible errors are:
| Error | Meaning | Add'l Info. |
| W189 | Rows successfully deleted. | <2> row count |
| 401 | Invalid or missing table name. | |
| 100 | The key does not exist in tableName (status( ) returns zero). | <2> key |
| 421 | User cancelled process. | |
| W106 | User quit while waiting for a lock; that row was skipped. | <2> key <3> table |
Under some circumstances, DELETEROW_SUB sets these values for status( ):
| status( ) | Meaning |
| -2 | Successful deletion. |
| -1 | User cancelled the process. Some rows may have been deleted. |
| 1 | An error occurred. |
| 0 | Non-critical error (for example, row to be deleted does not exist in the table). |
Notes
Displaying messages while deleting
By default (without the "S" or "A" options), DELETEROW_SUB displays a "gas gauge" in the status line indicating how far along the process is. The percentage complete is displayed in the center of the status line.
The "A" option substitutes a text message displaying the key of the row being deleted. To suppress all messages, use the "S" option.
Locking
The "L" option causes DELETEROW_SUB to attempt to lock each row in the table before beginning to delete it. If the lock fails, DELETEROW_SUB enters a loop, checking each second or so to see if the row is available.
While DELETEROW_SUB is in its locking loop, the user can press [Esc] to stop the attempt to lock the record. In that case, DELETEROW_SUB skips that record and generates error message W106 to tell you that row could not be locked.
Correct use of DELETEROW_SUB
table = "SAMPLE_CUSTOMERS"
rowIds = "100,101,102,103"
options = "AL"
convert "," to @FM in rowIds
call deleterow_sub( table, rowIds, options )
begin case
case status() eq -2
rowCount = @file.error<2>
call msg( "%1% rows deleted!", "", "", rowCount )
case status() eq -1
text = "You aborted the delete process!"
call msg( text )
case status() eq 0
/* there may have been non-critical errors */
text = "Deletion successful"
case status() eq 1
call fsmsg() ; * display normal errors
end case
stop