HP 3000 Manuals

HP ALLBASE/4GL Developer Self-Paced Training Guide

Defining the SQL Logic Block Details 

You can now create the details for the SQL logic block.

Menu Path 

To access this screen from the main menu: 

   1.  Select the Logic option.

   2.  Select SQL Logic Blocks.

   3.  Choose Details.

Screen Description 

This screen contains a free format text entry area that allows you to
enter the SQL commands that make up the SQL logic block.


          SQL Logic Block Details Screen 

Entering the Details 

You can use the normal terminal keyboard keys to enter and edit the
commands in this text entry area.

The find_prod SQL logic block contains a single SQL SELECT command as
follows: 

SELECT :product FROM sqlgrp.product
 WHERE product_no = :S-product_no.product_scrn
 FOR UPDATE OF description, supplier_no, lead_time;

To enter the command: 

   1.  Enter the command in the text area.

   2.  Make sure that you include the colon (:)  before the term product 
       in the first line, and the term S-product_no.product_scrn in the
       second line.

   3.  Terminate the command with a semicolon (;) at the end of the last
       line of the command.

       It doesn't matter which lines you enter the commands on, or
       whether you indent lines, as long as the commands are in the
       correct sequence.

   4.  When you have entered the command, press the  Commit Data  
       function key.

The SELECT Command 

HP ALLBASE/4GL modifies the syntax of the SQL SELECT command slightly.
In SQL logic blocks, you must enter the 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.  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.

Host Variables 

You can include references to the following data items in SQL logic
blocks.

   *   File record fields and work area fields.

   *   Variables and calculated items.

   *   Numeric and alphanumeric constants.

   *   Screen fields.

This is referred to as using host variables.  Note that you cannot use
host variables to replace the name of an SQL table.

References to HP ALLBASE/4GL data items in SQL logic blocks must be
prefixed with a colon (:).  The reference must be in the format:

     :data_ref 

where data_ref is the full reference to the item.

For example, the term :S-product_no.product_scrn in the SQL logic block
you have just created is a reference to the product_no field on the
product_scrn screen.  (Note that references to screen fields in SQL logic
blocks must be fully qualified references by name.)  Refer to the HP 
ALLBASE/4GL Developer Reference Manual for more information about the
referencing rules.

Generating an SQL Logic Block 

You can now generate the find_prod SQL logic block.

   1.  Start the generation process by pressing the  Generate SQL  
       function key.

HP ALLBASE/4GL calls the generate program, and then connects to the
database environment for the application.

During SQL logic block generation, HP ALLBASE/4GL validates and resolves
references to data items.  The generation process also converts the SQL
commands in the SQL block to an executable form and stores them in the
database environment as stored sections in a database module.

In its generated form, the prod_find command is equivalent to the
following SQL DECLARE CURSOR command:

DECLARE find_prod CURSOR FOR
  SELECT * FROM sqlgrp.product
  WHERE product_no = :S-product_no.product_scrn;

At run-time HP ALLBASE/4GL executes this command as a DECLARE CURSOR
command, followed by an OPEN CURSOR command.

The effect of this command is to declare and open a cursor on the product 
table.  The active set for the cursor is the set of rows in the table
that match the search condition specified by the WHERE clause in the
SELECT command.  In this particular case, the active set consists of a
maximum of one record because the product_no field is defined as a unique
index to the table.  If no record matches the search condition, the
active set for the cursor is empty.

Generation Errors.   

If HP ALLBASE/4GL detects any errors in an SQL logic block during
generation, it displays a generate error screen.

The generate error screen may specify a step number and a character
position for the error.  For SQL logic blocks, this position refers to
the cursor position at or near the cause of the error.  (Most HP
terminals display the current cursor position in terms of the screen line
number and column number between the two blocks of function key labels.)

If you receive an error message when you generate this SQL block, check
that you have entered it correctly.  Make sure that you have terminated
the command with a semicolon, and that you have included the colons where
they are required.

SQL Logic Block Limitations 

The find_prod SQL logic block introduces a number of features and
limitations that apply to all SQL logic blocks.

The next few paragraphs describe these items.

   *   Some SQL commands are not permitted in SQL logic blocks.  The HP 
       ALLBASE/4GL Developer Reference Manual contains a full list of the
       commands you cannot use.

   *   If an SQL logic block contains a SELECT command, this command must
       be the only command in the SQL logic block.

   *   If an SQL logic block doesn't contain a SELECT command, it can
       contain up to eight SQL commands.

   *   Each command in an SQL logic block must be terminated with a
       semicolon (;).

MPE/iX 5.0 Documentation