====== 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