HP 3000 Manuals HP 3000 Manuals
SQL Command
The SQL command executes an SQL logic block.
Formats
SQL {block_name} [; command]
{data_ref }
Parameters
block_name
The name of the SQL logic block to be executed.
data_ref
May be one of the following containing the name of the SQL block to be
executed.
* Constant.
* Variable or calculated item.
* Screen field name.
* Scratch-pad field name.
command
An optional command to be executed if a recoverable error occurs during
the execution of the SQL block. If you don't specify an error command,
and an error does occur, HP ALLBASE/4GL displays the text of the error
message on the screen and control passes to the next command in the logic
block.
You can use the following commands:
* ENTER
* EXIT
* EXTERNAL
* MESSAGE
* PROCEED
* SERIES
* TOP
* VISIT
* ZIP
Description
The SQL command executes an SQL logic block.
SQL logic blocks are used to pass SQL commands from HP ALLBASE/4GL to HP
ALLBASE/SQL. At run-time, the SQL command in an HP ALLBASE/4GL logic
block passes the HP ALLBASE/SQL commands in the SQL logic block to the HP
ALLBASE/4GL/HP ALLBASE/SQL interface. After the SQL block has been
executed, control passes to the next command in the logic block.
If an SQL logic block contains a SELECT command, this must be the only
command in the SQL logic block. Otherwise, the SQL block can contain up
to eight SQL commands that generate into stored database sections. You
must define the SQL logic block using the appropriate header and details
screens in the logic menu.
Refer to chapter 9 for further details of the SQL commands that are
permitted in an SQL logic block, and the usage of these commands.
Error Handling
If an HP ALLBASE/SQL error occurs during execution of an SQL logic block,
execution of the SQL logic block terminates. If you have specified the
optional error command on the SQL command, control passes to the error
command when an error is detected by HP ALLBASE/SQL. If you haven't
specified an error command, control passes to the next command in the HP
ALLBASE/4GL logic block when an HP ALLBASE/SQL error occurs.
When HP ALLBASE/SQL detects an error at run-time, HP ALLBASE/4GL places
the text of the error message in the communication area field *ERROR, and
places the error number, plus 100 000 in the communication area field
*IOSTATUS.
If the SQL command that caused the error does not contain an optional
error command, the text of the error message is displayed on the screen.
In some cases, a single SQL statement may cause more than one error
message. Under these conditions, the first error message and error
number are loaded into *ERROR and *IOSTATUS respectively at the time of
error detection. If the SQL command contains an optional error command,
the error handling logic can invoke the SQLEXPLAIN command in a further
SQL logic block to retrieve the text and number of subsequent errors.
The text of the message is returned to the *ERROR communication area
field, and the error number is returned to *IOSTATUS. (Note that you
cannot specify a host variable on the SQLEXPLAIN command in an SQL logic
block.) The SQLEXPLAIN command returns a null string after the last
error has been returned.
To determine the HP ALLBASE/SQL error number, subtract 100 000 from the
HP ALLBASE/4GL error message number. Refer to the HP ALLBASE/SQL
Messages Manual for the causes and suggested remedial actions for HP
ALLBASE/SQL error messages.
Example
1 SQL customer
2 FILE *NEXT namelist
3 SHOW *REFRESH
.
.
.
7 SQL customer_updt
In this logic block, the first SQL command executes the SQL logic block
customer. This SQL logic block typically would contain an HP ALLBASE/SQL
SELECT command to declare and open a cursor defined by the select list
namelist. The following FILE *NEXT command retrieves the first record
from the active set defined by the SELECT command.
The remaining commands in the logic block then use the record retrieved
by the FILE *NEXT command. The final SQL command executes the SQL logic
block customer_updt. This SQL logic block could contain an SQL UPDATE
command and a COMMIT WORK command.