9 219 TRANSFORM

You are here:

9.219 TRANSFORM_LIST

Note: Built-In Function Rules Usage Options

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).

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. A - Normal Delimited File. 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. O - Comma Delimited File. 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 A would have an Output File Format of 'AU'. 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	Close Output File Option. '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

Type

Req/ Opt

Description

Min Len

Max Len

Min Dec

Max Dec

Req

Return Code.

OK - File Created.
ER - Error when opening file. Refer to Return Code - Error Handling and Error Activity .

Technical Notes - TRANSFORM_LIST

9.219.1 Output File Formats

9.219.2 Other Parameters

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