7.59.1 FETCH Parameters
Specifies either the field(s) that are to be fetched from the file or the name of a group that specifies the field(s) to be fetched. Alternatively, an expandable group expression can be entered in this parameter. For more details, refer to Expandable Groups.
The following special values can be used:
-
*ALL specifies that all fields from the currently active file be fetched.
-
*ALL_REAL specifies that all real fields from the currently active file be fetched.
-
*ALL_VIRT specifies that all virtual fields from the currently active file be fetched.
-
*EXCLUDING specifies that fields following this special value must be excluded from the field list.
-
*INCLUDING specifies that fields following this special value must be included in the field list. This special value is only required after an *EXCLUDING entry has caused the field list to be in exclusion mode.
When all fields are fetched from a logical file maintained by OTHER, all the fields from the based-on physical file are included in the field list.
It is strongly recommended that the special values *ALL, *ALL_REAL or *ALL_VIRT be used sparingly and only when required. Fetching fields which are not needed causes the function to retrieve and map fields unnecessarily, invalidates cross-reference details (shows fields which are not used in the function) and increases the Crude Entity Complexity Rating of the function pointlessly.
Note that when BLOB or CLOB data is retrieved, it is either *SQLNULL or a filename. If a filename, the data from the database file has been copied into the file.
Warning: It is time-consuming to retrieve BLOB or CLOB fields from a file.
Recommended Database Design When Using BLOB and CLOB Fields
The recommended design when using BLOB and CLOB fields is to put them in a separate file from the rest of the fields using the same key as the main file. This forces programmers to do separate IOs to access the BLOB and CLOB data, thus reducing impact on database performance from indiscriminate use of this data. It is also the most portable design ensuring that the non-BLOB and non-CLOB data can be quickly accessed at all times.
Refer to Specifying File Names in I/O commands.
Refer to Specifying Conditions and Expressions and Specifying WHERE Parameter in I/O Commands.
After a fetch utilizing a where condition that results in a record not found, the contents of the fields are unpredictable.
Refer to Specifying File Key Lists in I/O Commands.
Specifies the name of a field that is to receive the "return code" that results from the I/O operation.
If the default value of *STATUS is used the return code is placed into a special field called #IO$STS which can be referenced in the RDML program just like any other field.
If a user field is nominated to receive the return code it must be alphanumeric with a length of 2. Even if a user field is nominated the special field #IO$STS is still updated.
For details of I/O operation return code values, refer to I/O Return Codes.
Specifies what action is to be taken if an I/O error occurs when the command is executed.
An I/O error is considered to be a "fatal" error. Some examples are file not found, file is damaged, file cannot be allocated. These types of errors stop the function from performing any processing at all with the file involved.
If the default value of *ABORT is used the function will abort with error message(s) that indicate the nature of the I/O error.
*NEXT indicates that control should be passed to the next command.
*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.
If none of the previous values are used you must nominate a valid command label to which control should be passed.
Specifies the action to be taken if a validation error was detected by the command.
A validation error occurs when information that is to be added, updated or deleted from the file does not pass the FILE or DICTIONARY level validation checks associated with fields in the file.
If the default value *LASTDIS is used control will be passed back to the last display screen used. The field(s) that failed the associated validation checks will be displayed in reverse image and the cursor positioned to the first field in error on the screen.
*NEXT indicates that control should be passed to the next command.
*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.
If none of the previous values are used you must nominate a valid command label to which control should be passed.
The *LASTDIS is valid even if there is no "last display" (such as in batch functions). In this case the function will abort with the appropriate error message(s).
When using *LASTDIS the "Last Display" must be at the same level as the database command (INSERT, UPDATE, DELETE, FETCH and SELECT). If they are at different levels e.g. the database command is specified in a SUBROUTINE, but the "Last Display" is a caller routine or the mainline, the function will abort with the appropriate error message(s).
The same does NOT apply to the use of event routines and method routines in Visual LANSA. In these cases, control will be returned to the calling routine. The fields will display in error with messages returned to the first status bar encountered in the parent chain of forms, or if none exist, the first form with a status bar encountered in the execution stack (for example, a reusable part that inherits from PRIM_OBJT).
Specifies what is to happen if no record is found in the file that has a key matching the key nominated in the WITH_KEY parameter.
*NEXT indicates that control should be passed to the next command.
*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.
If none of the previous values are used you must nominate a valid command label to which control should be passed.
Specifies whether a "not found" message is to be automatically issued or not.
The default value is *NO which indicates that no message should be issued.
The only other allowable value is *YES which indicates that a message should be automatically issued. The message will appear on line 22/24 of the next screen format presented to the user or on the job log of a batch job.
Specifies whether or not the record should be locked when it is read.
*NO, which is the default value, indicates that the record should not be locked.
*YES indicates the record should be locked. It is the responsibility of the user to ensure that the record is released at some future time.
Note: LOCK(*YES) performs a record level lock. It may exhibit intra and inter operating system behavioural variations (e.g. commitment control locking multiple records; default wait times). User's are advised to investigate the development of proper and complete "user object" locking protocol by using the LOCK_OBJECT Built-In Function.
Portability Considerations |
Not supported and should not be used in portable applications. A build warning will be generated when used in Visual LANSA. |
Specifies the name of a field that contains the relative record number (for relative record file processing) of the record which is to be fetched. The WITH_RRN parameter cannot be used if the WITH_KEY or WHERE parameters are used.
Any field nominated in this parameter must be defined within the function or the LANSA data dictionary and must be numeric.
Using the WITH_RRN parameter to FETCH, DELETE or UPDATE records is faster than any other form of database access.
The actual database file being accessed can not be a logical file when using the WITH_RRN parameter.
For information, refer also to Load Other File in the Visual LANSA Developers Guide.
Specifies the name of a field in which the relative record number of the record just fetched should be returned.
Any field nominated in this parameter must be defined within the function or the LANSA data dictionary and must be numeric.
For further information refer also to Load Other File in the Visual LANSA Developers Guide.
Specifies that details of the last "n" FETCH operations be kept within the compiled RDML program. Whenever a FETCH command is executed the details of the last "n" FETCH operations are searched first. If the required details are found within the program, no database I/O operation actually occurs. This can dramatically improve the performance of RDML programs.
*NONE, which is the default value, indicates that no details of previous FETCH operations are to be kept. Every FETCH performed will result in I/O to the associated database file.
An integer in the range 1 to 9999 may be specified. This indicates the number of previous FETCH operations for which details should be kept. The number specified reflects the maximum number of different FETCH operations that could be reasonably expected.
For instance, if the FETCH is made to a company file (by key company number) to get a company name, and only 15 companies exist, a value of 15 would be correct. Specifying 500 will waste storage in the executing RDML program and may actually degrade its performance.
Note that the number specified does not limit the number of different FETCH operations allowed. If no space is available within the RDML program for a FETCH's details to be stored, the oldest FETCH details are overwritten.
When the KEEP_LAST parameter is specified, the WITH_KEY parameter must be specified and the WHERE parameter must not be specified.
Note also that since previous FETCH details are stored within the RDML program, it is possible for the actual database file details to be changed without the change being reflected in the RDML program.