HP 3000 Manuals HP 3000 Manuals
FILE Command (HP ALLBASE/SQL)
The file command allows you to perform various operations on application
data files. You can use the file command to perform operations on HP
ALLBASE/SQL tables and select lists, HP TurboIMAGE/iX data sets, KSAM
data files, and serial data files.
This description covers the use of the FILE command to perform operations
on SQL tables and select lists.
Also refer to chapter 9 for more information about working with HP
ALLBASE/SQL databases from within HP ALLBASE/4GL.
Formats
{*BUFFER}
{*CLOSE }
FILE {*DELETE} file_ref [;command]
{*INSERT}
{*NEXT }
Window
Click here to view figure.
Parameters
file_ref
Specifies the HP ALLBASE/4GL name of the HP ALLBASE/SQL table or select
list on which the operation is to be performed. You must use the HP
ALLBASE/4GL dictionary names for HP ALLBASE/SQL tables with this command.
command
A command to be executed if the requested file operation fails. You can
use any of the following HP ALLBASE/4GL logic commands:
* ENTER
* EXIT
* EXTERNAL
* MESSAGE
* PROCEED
* SERIES
* TOP
* VISIT
* ZIP
Description
The FILE command allows you to perform various operations on HP
ALLBASE/SQL tables and select lists.
Before you can use the FILE *NEXT, FILE *DELETE, and FILE *CLOSE forms of
the FILE command to manipulate HP ALLBASE/SQL data, you must execute a
SELECT command in an SQL logic block. This command implicitly declares
and opens an HP ALLBASE/SQL cursor.
You must specify any base tables that are to be updated by the FILE
command in the current HP ALLBASE/4GL process by using a MODE command
with the *WRITE argument. Any files not specified in the MODE command
cannot be updated or modified from HP ALLBASE/4GL logic and are available
for reference purposes only. (Refer to the MODE command.)
Error Handling
If the FILE command contains an optional error command, HP ALLBASE/4GL
executes the command if an error occurs as a result of the file access.
For example, when performing a *NEXT operation HP ALLBASE/4GL executes
the optional command if the end of the file is encountered.
When an error occurs, the HP ALLBASE/4GL data manager writes an error
code into the communication area field *IOSTATUS. Appendix C lists the
data manager error codes. The value in *IOSTATUS is zero if the file
command is successful.
If you include the optional error command, the data manager error
messages will not be displayed on the terminal screen.
File Errors
The following table summarizes the most common file error values returned
to the communication area field *IOSTATUS. Refer to appendix C for a
complete list of the HP ALLBASE/SQL file error return values.
*IOSTATUS
Value Condition
60000 No SQL data base has been defined for the application.
60010 HP ALLBASE/4GL is unable to open the HP ALLBASE/SQL message
catalog.
60110 Beginning or end of file encountered by a FILE *NEXT
command.
60112 A record has not been read for the file. You cannot update
or delete a record with the HP ALLBASE/4GL FILE command, or
use the WHERE CURRENT OF clause in an SQL logic block unless
you read a record to position the cursor.
60150 The requested file operation cannot be performed on an SQL
select list. You cannot insert a new record into a select
list.
60151 The requested FILE *DELETE operation is out of sequence.
You must use a SELECT command in an SQL logic block to open
a cursor, and a FILE *NEXT command to position the cursor
before you can delete a record.
60153 The requested FILE *NEXT operation is out of sequence. You
must use a SELECT command in an SQL logic block to open a
cursor before you can use the FILE *NEXT command to read a
record.
102509 \Index uniqueness violation. You are attempting to insert a
new record that causes duplicate values in a column defined
as a unique index.
File Record Buffers
HP ALLBASE/4GL maintains a separate file record buffer for each HP
ALLBASE/SQL table and each select list. Note that you cannot use
multiple record layouts with HP ALLBASE/SQL tables.
HP ALLBASE/4GL clears the file record buffer automatically when a file is
first accessed, or at the start of any process.
HP ALLBASE/SQL Table Locking
All HP ALLBASE/SQL transactions initiated from HP ALLBASE/4GL are subject
to the HP ALLBASE/SQL locking mechanisms. Refer to the HP ALLBASE/SQL
Reference Manual for more information.
File Operations
Operations that may be performed on base tables or select lists are:
*BUFFER. Sets all fields in the specified file record buffer to nulls.
The buffer cleared is defined by file_ref. The *BUFFER operation on an
SQL table does not release any locks.
*CLOSE. Closes the nominated file. This command performs the same
function as the HP ALLBASE/SQL CLOSE CURSOR command, closing the cursor
for a base table or a select list. The COMMIT WORK command in an SQL
logic block also closes all open HP ALLBASE/SQL cursors.
You may wish to use the *CLOSE operation to release the read locks held
on a table before you complete a transaction. To do this you must start
the transaction with a BEGIN WORK CS (cursor stability) command.
In cursor stability mode, page level read locks are released as the
cursor moves off the page. By using this approach, you can use tables
for reading without locking other users out of the same tables while you
keep your transaction active.
Since the locks are released when the cursor is closed, do not use the
data read from that cursor as a basis for a later update. This can
introduce inconsistencies in the database. Refer to the HP ALLBASE/SQL
Reference Manual for more information about cursor stability and its
implications.
*DELETE. Deletes a record from a base table. You can only use the FILE
*DELETE command on base table cursors, or on a select list that only
accesses a single table, and does not use a GROUP BY clause. To use this
command, you must declare and open a cursor with a SELECT command in an
SQL logic block, and then position the cursor with a FILE *NEXT command
in an HP ALLBASE/4GL logic block.
*INSERT. Adds the current file buffer contents to a base table as a new
record. This operation will cause an error if you attempt to insert a
new record which would result in duplicate values in an column defined as
a unique index. If an index uniqueness error occurs, the value returned
to *IOSTATUS is 102509.
*NEXT. Reads the next record in the active set for the base table cursor
or select list cursor. This command performs the same function as the HP
ALLBASE/SQL FETCH command. If a FILE *NEXT command encounters the end of
file, the value returned to *IOSTATUS is 60110. To use this command, you
must first declare and open a cursor with a SELECT command in an SQL
logic block.
Using HP ALLBASE/SQL Data
In combination, the FILE command and HP ALLBASE/SQL commands in SQL logic
blocks allow you to retrieve records from HP ALLBASE/SQL tables and
views, insert new records in base tables, modify existing base table
records, and delete base table records.
Refer to chapter 9 for a description and examples of the use of the FILE
command to manipulate SQL data.