Purpose

SQL and ENDSQL are directives that make it possible to directly embed an SQL statement in PROIV logic. The statement is preceded by the word SQL and terminated with ENDSQL.

Syntax

SQL

        SQL-statement

ENDSQL

Syntax Elements

SQL-statement is any SQL statement. This is free-format except that quoted text may not span logic lines.

Remarks

Any type of SQL statement may be included and may reference 'host' (PROIV) variables using the normal SQL syntax.  The value of the host variable is passed between PROIV and the DBMS at run time.

The DBMS-specific SQL status code is available in the system variable @STDERR.

More than one SQL statement may be included within the logic provided that SQL and ENDSQL delimit each one

Example 1

SQL

        DELETE FROM CUSTTABLE WHERE CUSTCODE = :$CUSTCODE

ENDSQL

This uses a Full Function mode statement to delete those rows from CUSTTABLE where the column CUSTCODE has the value currently contained in the PROIV scratch variable $CUSTCODE.

Example 2

SQL

        SELECT FROM ADDRTABL

        WHERE TOWN = 'LONDON'

        ORDER BY POSTCODE

ENDSQL

This uses a Full Function mode Type 1 SELECT statement.  When the defined ADDRTABL is read, the SQL SELECT statement is used to retrieve a set of data.  This might be used in the default logic of a paging screen, for example.  Note that the column names are omitted (from between the SELECT and the FROM) as PROIV inserts them from the appropriate file definition for ADDRTABL.  If required, they can be explicitly specified, but they must match the file definition in number and type.