File
File
is used to declare the tables that can be used in a script.
Local File FILEDEC_LISTFile FILEDEC_LIST
FILEDEC_LIST
is a list of FILEDEC
separated by commas.
FILEDEC
can have one of the following syntaxes:
FIL_NAMEFIL_NAME Order By ORDER_LISTFIL_NAME Where CONDITIONFIL_NAME Where CONDITION Order By ORDER_LIST
FIL_NAME
can have one of the following syntax:FILE_NAMEFILE_NAME [CLASS][CLASS]( VARIABLE_LIST ) From System STRING_EXPRESSION As [CLASS](VARIA_LIST) From Variable ARRAY_LIST As [CLASS]
FILE_NAME
can be:
FOLDER_NAME.FILE_NAME
.SERVER@FOLDER.FILE_NAME
.=STRING_EXPRESSION
.CLASS
is the identifier of a class (up to eight characters). When only [CLASS] is given in the previous syntax list, the table must already be declared, and no Where clause is allowed.
FOLDER_NAME
is the identifier of a folder. By default, it is the current folder known by the variable nomap.
STRING_EXPRESSION
is a valid expression whose evaluation returns a character string value.
VARIABLE_LIST
is a list of valid variable names separated by commas, which indicates the name of the column returned by the System order.
SERVER
is the name or the IP address of a server in which a runtime of the Sage X3 engine has been installed and in which a folder is installed. SERVER can be followed by the service number if it is not the same as the current one (the syntax becomes SERVER:SERVICE@FOLDER.FILE_NAME
).
CONDITION
is an evaluated condition that can include constants, variables, columns from the table, and functions and operators to filter only some lines on the table. For more information about how the CONDITION
is managed by the engine, see the Where documentation.
ORDER_LIST
is a list of elements that defines an Order By in the SQL sentence. For more information, see the Order By documentation.
VARIA_LIST
is a list of VARIA
separated by commas.
VARIA
can have one of the following syntaxes:
DECLARATION
VARNAME
.DECLARATION
VARNAME
( INDEX_SEP
).DECLARATION
is a declaration keyword that can be Char, Shortint, Integer, Date, TinyInt, Decimal, Clbfile, Blbfile, Datetime, or Uuident. If DECLARATION
is Char, VARNAME
must be followed by (N)
where N
is the maximum size of the string, before a possible list of (INDEX_SEP
).
VARNAME
is a variable name starting with a letter, and followed by letters, digits, or underscores.
INDEX_SEP
is a list of DIMENSION
or DIMENSION_RANGE
separated by commas. If N commas are given, it describes an array of dimension N+1. The number of commas is therefore limited to three.
DIMENSION_RANGE
has the following syntax : DIMENSION
..DIMENSION
.
DIMENSION
is an expression returning a numeric dimension.
ARRAY_LIST
is a list of ARRAY_VARIABLE
that contains the values of the different columns value for the records of a "pseudo-table" in memory. ARRAY_VARIABLE
has the following syntax : VARNAME
(DIMENSION_RANGE
). All the arrays presented must have the same dimension; the number of elements in the list must correspond to the number of elements in VARIA_LIST
.
# Declaration of two tables: SALESORDER with its default abbreviation, and CUSTOMER with [CST] as abbreviation.Local File SALESORDER, CUSTOMER [CST]# Use of 2 account tables from 2 accounting folder ACCOUNTING1 and ACCOUNTING2. # my_server.my_domain is the network name of the server where ACCOUNTING1 folder is installed,# the second folder is installed on the current application server.Local File "myserver.mydomain@ACCOUNTING1.ACCOUNT" [ACC1], "ACCOUNTING2.ACCCOUNT" [ACC2]# Declaration of one table twice with different abbreviations.# This can be useful to perform joins within the same tableLocal File ACCOUNT [GACC1], ACCOUNT [GACC2]# Declaration of 5 already opened tables.Local File [A1],[A2],[A3],[A4],[A5]# ITEMS table with a filer and an order by clauseLocal File ITEMS [ITM] Where [ITM]ITMKEY >= "fzzz" Order By Key A = [ITM]ITMCATEG Desc# Let's compute the global size of files belonging to the "sage" group present in a directory on UNIXLocal Integer TOTAL_SIZELocal File (D,L,P,G,T) From System "ls -l" As [SYS] Where [SYS]G = "sage"For [SYS] : [L]TOTAL_SIZE += val([F:SYS]T) : Next# Let's create a table in memory and use itLocal Char MY_KEY(20)(1..200)Local Integer MY_VALUE(1..200)Local Date MY_DATE(1..200)Local Integer NB_RECGosub FILL_TABLE : # Fills MY_KEY, MY_VALUE, MY_DATE arrays and returns NB_REC as the number of recordsLocal File (Char THEKEY(20), Integer AMOUNT, Date POSTING_DATE)& From Variable MY_KEY(1..NB_REC), MY_VALUE(1..NB_REC), MY_DATE(1..NB_REC)& As [MEM] Order By POSTING_DATEFor [MEM]# For every loop, we have [MEM]THEKEY, [MEM]AMOUNT, [MEM]POSTING_DATE available (found in MY_* arrays)# This type of table is usable only in read modeNext
File is used to declare the tables used in a routine. A table can be:
A database table (located on the same server and the current folder by default):
File
declaration. Additional filters can be added on the table by a Filter instruction, and finally a Where clause can also be added on the For syntax. These conditions are combined by the 'and' operator. For more information about the operators available, see the Where documentation.The result of a system command. Every line is split into words separated by spaces or tabs and every word is assigned to the fields on the list. Two consecutive spaces are considered as a unique one. When the list is full, the remaining characters of the lines are ignored and the next line is considered. If the end of the line is encountered before all the fields on the list have been considered, the remaining fields are returned empty. The end of line character can be either LF (line feed) or CR+LF (carriage return+line feed). The fields declared on the list are therefore all character strings with a maximum length of 255. This type of table has some usage restrictions:
The content of arrays stored in memory. This is possible only in read mode.
The result of the execution of this instruction is the following:
* It creates variable classes [F] and [G] and updates the variables [S]fileabre and [S]filename.
* It updates the list of tables opened. The table that is first in the command becomes the default table. This can be changed by the Default File instruction.
* The maximum number of tables that can be opened simultaneously is limited by the system variable [S]adxmto
. The value of this variable is normally defaulted by the supervisor (assigned in APL.ini configuration file). A common value for adxmto is 200.
A File
command closes all the tables opened by an earlier File
(or Trbegin) instruction. The use of this syntax is therefore deprecated in the application code. You must use Local File
to open the tables in a routine to avoid conflicts on the calling context.
The Local File
instruction is used to open files locally for a routine or subprogram and temporarily. This declaration does not close files previously opened by a File or Local File command, but adds to the list of open tables. The first table opened this way becomes the new default table. After the Close Local File command (or the end of the subprogram), the list of tables is as it was before the instruction, as are the current records.
A locality level is attached to each Local file. Therefore, using Local File and the same abbreviation, you can reopen a file already opened elsewhere, but this cannot be done in the same level of the nesting call.
You can open a file with Local File
within a transaction, but files opened in write mode by Trbegin will no longer be accessible – even if they are not physically closed before the end of the transaction.
In version 6, opening a table is costly if the table has not been opened, or if a Close instruction closed the table previously. This is the reason that in version 6 programming style, tests are performed by using clalev to open a table only if it is not previously opened. The main issue with this programming technique is that it creates conflicts (for example, filters can be inherited from a previous declaration). Therefore, a new instruction called LogicClose file has been implemented in version 7. It performs as a Close instruction, but does not release all the resources and is therefore cost effective when the table is reopened. As these instructions are managed by the supervisor layers, the best practice for version 7 is to declare systematically with the LocalFile
all the tables used in a routine called by Call, Func, or fmet instructions.
Temporary files created for a File command (sort file or System file) are stored in the '/tmp' folder, unless the environment variable TMPDIR contains the name of another folder. Their names are randomly generated. They are only accessible from the current procedure and are physically deleted at the end of their use or at the end of the procedure.
A Close Local File or LogicClose Local File can be done within a transaction, but it will be taken into account only at the end of the transaction (instructions Commit or Rollback).
The nbrecord function is available to perform a count(*)
on a table. It assumes that the table has been previously declared by File
, and uses the abbreviation of the table as an argument. This count does not consider the filter that can be added by any Where clause. The instruction rowcount performs the count on the filtered lines.
Local File CUSTOMER [CUST] Where COUNTRY="USA"Call MY_ROUTINERead [CUST]CODE First : # This will return the first customer having COUNTRY equal to the USACall MY_OTHER_ROUTINE...EndSubprog MY_ROUTINERead [CUST]CODE First : # Table has not been declared, previous condition applies: the first US customer code is readLocal File CUSTOMER Where COUNTRY<>"USA" : # The opposite conditionRead [CUST]CODE First : # Previous condition applies, now the first non US customer code is readFilter [CUST] Where CITY<>"PARIS"Read [CUST]CODE First : # Filter added to previous condition: first non US customer with a city not equal to PARIS is readEndSubprog MY_OTHER_ROUTINELocal File [CUST] Where COUNTRY<>"USA" : # The abbreviation refers to the previous file instruction, the condition is addedRead [CUST]CODE First : # No record will be returned, because the condition is COUNTRY="USA" and CONTRY<>"USA"End
Code | Description |
---|---|
7 | Abbreviation not found. |
10 | The expression that contains the table name does not return a character string. |
20 | Table not found. |
27 | Table access error. |
28 | Table opened twice. |
29 | Too many tables opened. |
Trbegin, Where, Order By, adxmto, clalev, Default, Close, Local, LogicClose, nbrecord, Filter, For, Link, filename, fileabre.