HP 3000 Manuals

HP ALLBASE/4GL Developer Reference Manual Vol. 1

Retrieving HP ALLBASE/SQL Data 

HP ALLBASE/4GL performs all data retrieval from HP ALLBASE/SQL databases
via cursors.

To retrieve data from an HP ALLBASE/SQL database, you must use a SELECT
command in an SQL logic block, followed by a FILE *NEXT command in an HP
ALLBASE/4GL logic block.  These commands take the form:

     SQL sql_block_name [;command]
     FILE *NEXT file_name [;command]
       .
       .
       .

The SQL command calls the SQL logic block sql_block_name.  To retrieve
data from an HP ALLBASE/SQL database, this SQL logic block must contain a
SELECT command as the only command.

The SELECT command must be 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.  If later commands in the HP ALLBASE/4GL
logic block update the HP ALLBASE/SQL table or select list, you must
include the FOR UPDATE OF clause in the SELECT command.

At generate time, HP ALLBASE/4GL converts the SELECT command to a DECLARE
CURSOR for SELECT command.  At run-time, the SELECT command performs the
same function as an OPEN CURSOR command in HP ALLBASE/SQL.

The FILE *NEXT command retrieves the first record in the active set for
the cursor defined by the SELECT command.  In this command, file_name is
the name of the table or select list in the SELECT command.  The record
is retrieved into the record buffer for the select list or table.  The
FILE *NEXT command performs the same function as an SQL FETCH command.

Further commands in the HP ALLBASE/4GL logic block can then use the
retrieved data as required.  Subsequent FILE *NEXT commands retrieve
records in sequence from the active set for the cursor.

Example 1 - Retrieving HP ALLBASE/SQL Data 

     Function retrieve_titles 

          .
          .
      6 SQL select_titles
      7 FILE *NEXT titles
      8 SHOW *REFRESH
          .
          .

     SQL Block select_titles 

      SELECT :titles FROM library.titles
      WHERE index_no = :V- index_no;

This example shows the use of a SELECT command in an SQL logic block,
followed by a FILE *NEXT command in an HP ALLBASE/4GL logic block, to
retrieve a record from an HP ALLBASE/SQL table.  In this example, the SQL
logic block select_titles declares and opens a cursor on the HP
ALLBASE/SQL base table titles.  The FILE *NEXT command in the HP
ALLBASE/4GL function retrieve_titles retrieves the first record in the
active set for the SQL cursor.  The SHOW *REFRESH command following the
FILE command displays the details of the record retrieved by the FILE
command.

MPE/iX 5.0 Documentation