HP 3000 Manuals HP 3000 Manuals
SQL Logic Blocks
SQL logic blocks are the mechanism for passing SQL commands to HP
ALLBASE/SQL from HP ALLBASE/4GL. SQL logic blocks consist of a header and
the SQL logic block details. The header defines the name of the SQL
logic block, and the SQL logic block details contains the SQL commands.
The commands contained in an SQL logic block are invoked by a command
(the SQL command) in an HP ALLBASE/4GL logic block.
You must generate an SQL logic block before it can be used in an
application.
The following descriptions of HP ALLBASE/4GL SQL logic blocks assume that
you are familiar with the basic syntax and usage of SQL commands. Refer
to the HP ALLBASE/SQL Reference Manual for more information about SQL
commands.
SQL Block Commands
HP ALLBASE/4GL imposes some limitations on the SQL commands you can use
in an SQL logic block.
You cannot use the following commands in an HP ALLBASE/4GL SQL logic
block:
BEGIN DECLARE SECTION and END DECLARE SECTION
BULK
CLOSE cursorname
CONNECT TO DBEnvironmentname
DECLARE cursorname
DESCRIBE commandname INTO areaname
EXECUTE IMMEDIATE
FETCH
INCLUDE SQLCA or SQLDA
INTO clause with SELECT command
OPEN cursorname
PREPARE
RELEASE
START DBE and STOP DBE
WHENEVER
If an SQL logic block contains a SELECT command, it must be the only
command in the SQL logic block.
In general, you can use any other valid SQL commands in an SQL logic
block, subject to the limitations of SQL itself. If an SQL logic block
does not contain a SELECT command, it can contain up to eight SQL
commands that generate into stored database sections. The SQL logic
block can also contain additional SQL commands that do not generate into
stored database sections. The SQL commands that do not generate into
stored database sections are:
BEGIN WORK.
COMMIT WORK.
ROLLBACK WORK.
SAVEPOINT.
Host Variable Referencing
SQL logic blocks can contain references to HP ALLBASE/4GL file record
fields, work area fields, variables and calculated items, and constants.
NOTE You cannot use references to scratch-pad fields or communication
area fields in SQL logic blocks, and you cannot use host variable
referencing to replace the name of a table.
SQL logic blocks can also contain references to screen fields, provided
the reference is a fully qualified reference by name. References to
screen fields must be in the form:
S-field_name.screen_name
You cannot use substrings in references to screen field names.
You must precede a reference to an HP ALLBASE/4GL item with a colon (:).
That is, you must express the reference in the format:
:data_ref
where data_ref is the HP ALLBASE/4GL name, including the item type
prefix, for the HP ALLBASE/4GL item.
References to host variables cannot include null indicator variables.
However, HP ALLBASE/4GL does support reading and writing null values, so
you can use the *NULL argument for the IF command to test for null status
in HP ALLBASE/4GL. You can also use the *NULL argument for the MOVE
command to set the HP ALLBASE/4GL indicator variable associated with an
HP ALLBASE/SQL table or select list buffer field to the null status.
The SELECT Command
HP ALLBASE/4GL requires a slightly modified format for the SELECT command
in SQL logic blocks. You must use the SELECT command in the following
format:
SELECT {:table_name } FROM clauses [other clauses]
{:select_list}
[FOR UPDATE OF column_name [,column_name ...]];
In this expression, :table_name is an HP ALLBASE/SQL table name that has
been defined on the file/SQL table definition screen in the dictionary,
and :select_list is a select list name that has been defined in the
dictionary. A host variable reference cannot be used to specify the name
of the table or select list.
You cannot use the SELECT * FROM ... form of the SELECT command in an
SQL logic block, and you cannot use the INTO clause in a SELECT command.
At generate time, HP ALLBASE/4GL converts this command to a DECLARE
CURSOR command in the format:
DECLARE SQL_block_name CURSOR FOR select_command
FOR UPDATE of ...;
where SQL_block_name is the name of the SQL logic block containing the
SELECT command.
If a SELECT command contains a FOR UPDATE OF clause, it cannot contain a
GROUP BY clause, or an ORDER BY clause.