Using COPYROW_SUB (Functions/Subroutines/Programs)

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 sourceIds 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 targetTable 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

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.

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( ):

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.