9.219 TRANSFORM_LIST
Transforms the current contents of one or more working lists into a disk file.
It is designed to facilitate the transfer of information between Visual LANSA applications and other products (e.g: spreadsheets).
See also
Arguments
No | Type | Req/ Opt | Description | Min Len | Max Len | Min Dec | Max Dec |
|---|---|---|---|---|---|---|---|
1 | L | Req | Name of the primary working list that is to be transformed into a disk file. Note: If this list contains fields of type Binary or VarBinary, the Built-In Function will end in error. | ||||
2 | A | Req | Name of file to be replaced or created by this Built-In Function. | 1 | 256 | ||
3 | A | Opt | Output File Format. B - DBF File. (Not available on IBM i.) C - Columnized File (Numeric Fields Have Leading Signs) with signs. D - Columnized File (Numeric Fields do NOT have Signs) without signs. S – Comma Delimited File: The same format as type O files except that there are no blanks for invalid characters if the Method of handling invalid characters is R. T - Horizontal Tab Delimited Files. Output File Formats A, C, D, O, S and T support UTF-8 format. This is indicated by appending a 'U' to this argument (i.e. Format). For example, UTF-8 output for format O would have an Output File Format of 'OU'. Note: CU and DU column widths are double the field width in order to accommodate the extra bytes that UTF-8 may require. The default value is 'A'. | 1 | 3 | ||
4 | A | Opt | Method of handling invalid characters encountered within alphanumeric fields. 'B' - Replace by blank character. 'I' - Ignore. Include character. 'R' - Remove from output. The default value is 'B'. | 1 | 1 | ||
5 | A | Opt | Include Carriage Return at the end of each record. 'N'- Do not include carriage return. 'T' - Include carriage return and also truncate all blank data from the end of the record. 'Y'- Include carriage return. The default is 'Y'. | 1 | 1 | ||
6 | A | Opt | Decimal Point to be used . The allowable values are: 'R'- Remove the decimal point from all numeric representations. This will shorten the length of numeric fields that have decimal positions by 1 character. Only valid with file formats C and D. other - The value to be used as a decimal point character. The default is the currently defined system decimal point (i.e. '.' or ','). Note: The use of European style ',' decimal points may create problems in files formats that also use commas to delimit fields. | 1 | 1 | ||
7 | A | Opt | 'Y'- Close the file at completion. 'N'- Do not close the file at completion. The default is 'Y'. | 1 | 1 | ||
8 - 17 | L | Opt | Allows up to 10 Appendage Working Lists to be specified. Refer to the following notes for more details. Valid only if the primary working list is an RDML list. |
Return Values
No | Type | Req/ Opt | Description | Min Len | Max Len | Min Dec | Max Dec |
|---|---|---|---|---|---|---|---|
1 | A | Req | Return Code. OK - File Created. | 2 | 2 |
Technical Notes - TRANSFORM_LIST
SQLNULL Handling
When a field is SQLNULL, the *NULL equivalent is output.
Special Handling for BLOB and CLOB value
The full BLOB/CLOB file name will be saved in the output file. The BLOB/CLOB file itself will be duplicated in a subdirectory under the output file directory. The name of this subdirectory is <output file name>_LOB. For example if the output file is:
C:\Root\Data\Transformed1.dat,
and the original CLOB file is C:\XYZ\ CLOBNumber1.txt
then the duplicated CLOB file is
C:\Root\Data\Transformed1_LOB\CLOBNumber1.txt
and the CLOB value in the Transformed1.dat will be:
C:\XYZ\ CLOBNumber1.txt
If Transformed1.dat is moved (or copied ) into another system, move or copy the sub directory Transformed1_LOB and all its contents as well.
Example
The following outline function can save the contents of an existing SQL table to a disk file, or insert the contents of disk file into an SQL table (i.e. An SQL table Save/Restore function) .....
def_list #list fields(....) listcount(#count) type(*working) entrys(100)
request fields (#option and name of disk file involved)
if (#option = SAVE)
select fields(...) from_file(...)
add_entry #list
if (#count = 100)
use TRANSFORM_LIST #list (with "do not close" option)
clr_list #list
endif
endselect
use TRANSFORM_LIST #list (with "close" option)
else (#option was RESTORE)
dowhile (#retcode *ne EF)
use TRANSFORM_FILE into #list (with "do not close" option)
execute insertlist
endwhile
execute insertlist
endif
subroutine insertlist
selectlist #list
insert fields(...) to_file(....)
endselect
clr_list #list
endroutine
By adding the CONNECT_SERVER and CONNECT_FILE Built-In Functions, this function could be very simply expanded to support the following table of "data transfers":
Data Target <- - - - - - - - - - - - Data Source - - - - - - - - - - - - - >
PC SQL Table | PC Disk File | IBM i File | |
PC SQL Table | N/A | Yes | Yes |
PC Disk File | Yes | N/A | Yes |
IBM i File | Yes | Yes | N/A |