Using COPYROW_SUB (Functions/Subroutines/Programs) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Using COPYROW_SUB (Functions/Subroutines/Programs) ======

======   ======

==== Created at 14 NOV 1996 03:00PM ====




**COPYROW_SUB**

**COPYROW_SUB(sourceTable, sourceIds, targetTable, targetIds, options )**

** Using COPYROW_SUB**

Copies rows from one table to another, to the same table under a new name, or to or from **DOS **files (operating system files, including **OS/2)**.

** sourceTable**

The source table for the rows to be copied. The table must be attached. To copy from the dictionary, use the prefix "**DICT**.". To copy a **DOS **file, use "**DOS**" as the //sourceTable//. (For more information about copying **DOS **files, see "Copying to and from **DOS **files" below.)

** sourceIds**

An array (**@FM**-delimited) of row keys to be copied. To copy all the rows in a table, use an asterisk (*) to mean "all rows". If a select list is active, leave s//ourceIds// //// null and the subroutine will copy only the rows represented in the select list (any entries in //sourceIds// //// override the select list).

If a select list is active when you call **COPYROW_SUB**, but you list explicit rows to copy in //sourceIds//, the select list is preserved (until you return to **TCL**).

If you want to copy a **DOS **file, use the **DOS **file name as the //sourceId//, including the path.

** targetTable**

The name of the table into which the source rows should be copied. The table must be attached. To copy to the same table, leave t//argetTable// //// null.

** targetIds**

To rename rows as they are being copied, provide an array (**@FM**-delimited) in //targetIds//. Rows are renamed one-to-one with keys in //sourceIds// //// the first key in //sourceIds// //// is assigned the first key in //targetIds//, the second key in //sourceIds// //// is assigned the second key in //targetIds//, etc.

If there are more keys in //sourceIds// //// than in //targetIds//, the extra rows are not assigned new names. If there are more keys in //targetIds// //// than in //sourceIds//, the extra keys are ignored. If the list of rows to be copied is implied in a select list or because //sourceIds// //// is "*" for "all" new keys are still assigned one-for-one with the list in //targetIds//.

If a key in //targetIds// //// already exists in //targetTable//, you must use the "O" or "N" options (to overwrite the existing row), or **COPYROW_SUB** will generate an error.

When copying **DOS **files into an Advanced Revelation table, it's a good idea to include a //targetId// //// for the file, because unless you use the "R" option, the full path name will be retained as the //targetId//. See "Copying to and from **DOS** files" below for more details.

** Options**

|**Type**            |**Option**|**Meaning**                                                                                                                                                                                                                                                      |
|Process\\ \\ Control|D         |(Delete) Deletes the source row after copying it.                                                                                                                                                                                                                |
|                    |N         |(New record inhibit) The row is copied only if a row of the same name already exists in the target table.                                                                                                                                                        |
|                    |O         |(Overwrite) Overwrites rows in the target table if they exist.                                                                                                                                                                                                   |
|                    |L         |(Lock) Locks the source and target rows before copying.                                                                                                                                                                                                          |
|                    |B         |(Batch) Puts a transaction around the entire copy process. If any errors occur, all changes are rolled back, and no copies are made.                                                                                                                             |
|Formatting          |I         |(Interchange format) Converts the row to **ASCII** "text" format field marks (**@FM**) are converted to carriage return/line feeds, and a **DOS **end-of-file character (**ASCII** 26) is put at the end of the file.                                            |
|                    |R         |(Revelation format) Convert the row from **ASCII** "text" format to Advanced Revelation format carriage return/line feeds are converted to field marks (**@FM**), the **DOS** end-of-file character (**ASCII** 26) is stripped, and tabs are converted to spaces.|
|                    |X         |(Hex format) Convert the row to hex representation as it is being copied.                                                                                                                                                                                        |
|//(continued)//     |// //     |// //                                                                                                                                                                                                                                                            |

// //

// //

|**Type**|**Option**|**Meaning**                                                                                                                    |
|Display |A         |(Announce) Displays a message in the status line showing the key for each row being copied, along with the percentage complete.|
|        |S         |(Suppress messages) Show no messages at all in the status line.                                                                |
|        |U         |(User message) Display a custom message in the status bar along with a "gas gauge" showing percentage complete.\\ \\           |

See "Notes" below for further information on using these options.

+ Unlike the **TCL COPYROW **command, **COPYROW_SUB** does not include options to copy rows to the printer or to the screen.

** Values returned**

The return status of **COPYROW_SUB** is indicated by the system variable //@file.error// as indicated in the following table. 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.

|**Error**|**Meaning**                         |**Add'l**** Info.**            |
|S805     |Some or all rows copied successfully|<2> row count                  |
|401      |Invalid source or destination table.|<2> table                      |
|B403     |Unable to write row.                |<2,1> key\\ \\ <2,2> table     |
|S100     |Target row is too long.             |<2,1> row\\ \\ <2,2> max length|
|W106     |User quit while waiting for a lock  |<2> key\\ \\ <3> table         |
|W107     |Source row does not exist.          |<2,1> key\\ \\ <2,2> table     |
|W179     |Target row already exists.          |<2,1> key\\ \\ <2,2> table     |
|W180     |Target row does not exist.          |<2,1> key\\ \\ <2,2> table     |

In addition to these, any errors that **COPYROW_SUB** encounters while attempting to read or delete a row are passed through directly.

Under some circumstances, **COPYROW_SUB** sets these values in //status(// //)//:

|**status( )**|**Meaning**                                              |
|-2           |Copy successful.                                         |
|-1           |User aborted the process. Some rows may have been copied.|
|1            |An error occurred.\\ \\ ** **                            |

**Notes**

** Creating new records and overwriting existing ones**

By default, **COPYROW_SUB** checks each row as it copies it. If a row already exists under the same in the target table, the row is not copied and error W179 is generated. If there is no row of that name in the target table, it is created by the copy process.

The "O" option overwrites records without warning. The "N" option checks to see if the target row already exists; if not, the row is not copied and error W180 is generated. The "N" option is therefore a "copy only if exists" option. You can use it to ensure that new rows are not inadvertently created by misspelled or incorrect row keys.

+ The "O" option copies rows fastest, because it does not have to read the target row before writing to it.

** Displaying messages while copying**

By default (without the "S", "A", "U" options), **COPYROW_SUB** displays a "gas gauge" in the status line indicating how far along the copy process is. The percentage complete is displayed in the center of the status line. 

The default display changes under two circumstances. If there are fewer than three rows to copy, no message is displayed. If there are more than 100 rows to copy, the status line display is updated only every 10th row.

The "A" option substitutes a text message showing the percentage complete as text (no gas gauge), and displays the source and target row keys. The status line is updated for any number of rows, and for each row even after the 100th one.

To suppress all messages, use the "S" option.

You can also substitute your own message using the "U" option. The text you supply is displayed in the status line in the middle of a "gas gauge". To use this option, add the text to display immediately after the "U" in the option list. You can use spaces and punctuation in your text (no parentheses). To include the percentage complete, use the %P% token:

copyrow_sub("TABLE1","","TABLE2","","UCopying, %P%% done")

If you use it, the "U" option must be the last option in the option list. If you use both the "A" and the "U" options, the "A" option takes precedence.

** Copying to and from DOS files**

You can use the //sourceTable// //// and //targetTable// //// name of "**DOS**" to copy **DOS** files. The maximum size of a **DOS** file you can copy with **COPYROW_SUB** is 64K (65,536 bytes).

The "I" and "R" options allow you to exchange between **DOS** "text" format and Advanced Revelation format. (See the table above for details.) It is not a requirement that you change format as you copy you can copy a Revelation-formatted row to a **DOS** file and vice versa. You might copy an Advanced Revelation row to a **DOS** file without reformatting, for example, if you wanted to put the program on diskette to send to someone.

The "R" option automatically attempts to convert each tab (**ASCII** 9) to seven spaces in the target row. However, if the resulting row would be greater than 65,536 bytes, the row is copied without the substitution, the row appears with tab characters in it, and **COPYROW_SUB** generates error S100.

The "R" option also causes **COPYROW_SUB** to strip path name from the key if no target key is provided. For example, in this sample:

copyrow_sub( "DOS", "C:\TEMP\DOSFILE.TXT", "CUSTOMERS",   

 "", "R" )

the row appears in the table **CUSTOMERS** as **DOSFILE.TXT**. Path name information is not stripped if you do not use the "R" option, even if you are copying from one **DOS** file name to another.

** Locking**

The "L" option causes **COPYROW_SUB** to attempt to lock each row in the source and target tables before beginning to copy it. If the lock fails, **COPYROW_SUB** enters a loop, checking each second or so to see if the row is available.

While **COPYROW_SUB** is in its locking loop, the user can press [Esc] to stop the attempt to lock the record. In that case, **COPYROW_SUB **skips that record and generates error message W106 to tell you that row could not be locked.

** Hex conversion**

The "X option converts each character in the source row to its corresponding hex representation. If the resulting row would exceed 65,536 bytes, the row is truncated to this size. No error message warns you that this has happened.

** Correct use of COPYROW_SUB**

source = "SAMPLE_CUSTOMERS"

sourceKeys = "1,5,6,11"

convert "," to @fm in sourceKeys

 

target = "TRANSFER_CUSTOMERS"

targetKeys = "" /* copies to new table with same ids */

 

options = "AL" /* Announce copy, Lock rows */

call copyrow_sub( source, sourceKeys, target, targetKeys, options )

if status() = -2 then

 /* process was successful */

 text = @file.error<2> : " records copied!"

 call msg( text )

end else

 call fsmsg()

end

//Copies rows 1, 5, 6, and 11 from the SAMPLE_CUSTOMERS table to the TRANSFER_CUSTOMERS table. A message is posted in the status line for each row being copied.//

declare subroutine msg

perform "SELECT SAMPLE_INVOICES WITH DATE < '01 JAN 1992' "

if @list.active ne 3 then

 msg("no records to archive!") ; stop

end

reccount = @rec.count

 

source = "sample_invoices"

target = "invoices_history"

 

options = "DBOU -- archiving, %P% percent compete --"

call copyrow_sub( source, sids, target, "", options )

 

 

//(continued)//

// //

begin case

 case status() = -1

 msg("You quit the process before it was done. Please retry.")

 case status() = -2

 totalCopied = @file.error<2>

 if totalCopied = reccount then

 text = "The archive process completed sucessfully."

 text<-1> = "%1% rows were copied successfully."

 msg( text, "", "", reccount )

 end else

 text = "An error has occurred. %1% rows were selected,"

 text<-1> = "but only %2% were copied."

 msg( text, "", "", recCount : @FM : totalCopied )

 end

 case 1

 call fsmsg()

end case

stop

//Selected rows are copied from SAMPLE_INVOICES to INVOICES_HISTORY. The rows are deleted from SAMPLE_INVOICES after being copied. The entire copy process is a single transaction; if any one row is not copied, all are restored as they were. To speed the process, and to guarantee that the most recent rows are on file in INVOICES_HISTORY, the target rows are overwritten. A custom message informs the user that the archive process is underway.//