- Full Function Mode

In Full Function mode, PROIV allows SQL statements to be coded directly in PROIV Logic. The SQL statements used can be divided into two categories, according to whether or not they are handled within the normal PROIV Timing Cycle:

SELECT statements that override the data records to be processed by influencing the selection criteria that would otherwise be made in the Transparency mode statement. In this case, records are still processed within the constraints of the PROIV Timing Cycle.
Most other SQL statements that are not supported through Transparency mode (for example, DDL statements). In this case, the statements are unaffected by the PROIV Timing Cycle and are executed immediately as they are encountered within logic.

Full Function mode statements must not include:oh
Transaction handling, for example, COMMIT() or ROLLBACK(). See PROIV's COMMIT() and ROLLBACK() logic commands in the PROIV Developer Logic Commands.
Cursor manipulation.
Database connection or disconnection.

Functions that make use of Full Function mode do not easily port to non-SQL environments. Restricting such statements to standard ANSI-SQL syntax does improve the ease of porting applications between different SQL environments.

The remaining sections discuss Full Function mode under the headings SELECT Statements, and Non-SELECT Statements.

SELECT Statements

In the case of a SELECT statement that references a PROIV File Definition (in place of the normal SQL table name), the defined SELECT statement replaces the Transparency mode SELECT statement that is normally generated automatically. This gives the flexibility to override the specific record(s) to be manipulated within the normal Timing Cycle, based upon criteria specified within the PROIV Logic. It is important to note that the execution of such a SELECT is deferred until the normal point within the PROIV Timing Cycle.

In Full Function mode, the SELECT statement to be used is specified in Default Logic (primary file in indexed Paging Cycle, or Update cycle, or non-one time cycle) or in Before Read logic (all other cases) at the screen where the file to be accessed is specified.

An SQL SELECT statement coded in PROIV logic can be one of two types, Type 1 or Type 2.

Type 1

SQL
   SELECT
   FROM PROIV-filename
   WHERE standard-SQL-syntax
   {ORDER BY standard-SQL-syntax}
ENDSQL

In this case PROIV automatically generates the list of columns to be retrieved using the PROIV File (table) Definition specified in the FROM clause. The list of columns will contain only those referenced by the function.

This SELECT statement can be termed the Outer SELECT and all identifiers used in it must be either PROIV File Variables, PROIV Scratch Variables, or PROIV File Names depending on their usage; the FROM clause must contain only a PROIV File Name; only the WHERE clause can contain PROIV Variables which must be used as host variables, otherwise SQL column names must be used.

Host variables are used to pass data from PROIV to SQL. (Host variables that pass data from SQL to PROIV are not supported.) They can be used anywhere an expression can be used provided they are prefixed by a colon (:), but cannot be used to specify SQL column or table/file names.
Exercise care when using file variables as host variables in Full Function SQL statements. The values of any file variables used by the SELECT are taken during the read cycle for the file, and not at the time of the logic execution. It is therefore recommended that file variables are used only if their values will remain static for the duration of their usage as SQL host variables, for example, they must not belong to any of the files in the current cycle, or Update or Report cycle.
Any variable appearing in an SQL statement that is acting as 'host variable' must be prefixed by a colon (:) as specified in the standard SQL syntax. When the variable is referenced outside an SQL statement, the colon must be omitted.

Type 2

A Type 2 statement is not associated with a PROIV File; it is an ad hoc SELECT from a distinct SQL table.

SQL
   SELECT DISTINCT SQL-column-list
    FROM SQL-tablename-list
   {WHERE standard-SQL-syntax}
   {ORDER BY standard-SQL-syntax}
   {GROUP BY standard-SQL-syntax}
   {HAVING standard-SQL-syntax}
ENDSQL

In this case, the list of columns to be retrieved must be specified (which may include the use of the built-in-functions AVG, SUM, MAX, MIN and COUNT, or any other valid SQL syntax) and a valid SQL table name from which the column data is to be extracted. Note that SQL-tablename-list must be a literal - it cannot be a PROIV Variable.

A Type 2 SELECT is associated with a PROIV File solely through the position of the logic in which it is coded, that is, in Default Logic for a primary file, and in the corresponding Before Read Logic for secondary files.

The PROIV File associated with a Type 2 SELECT must have the same number of File Variables as the number of columns to be retrieved, and the types of the variables must be suitable for receiving the corresponding column. PROIV will attempt to load the data for the first column into the first File Variable, the data for the second column into the second File Variable, and so on. Thus, the PROIV File Definition does not have to relate to a real table or view, it is purely a mechanism for receiving the data into PROIV, thus a Type 2 SELECT should only be used in Lookup mode.

Primary Files

Default logic may contain more than one Type 1 SELECT statement for the primary file, but the last Type 1 SELECT processed at execution time is the one used for accessing the file. For example:

IF@$COM1 = 'TEST'
    SQL
       SELECT
       FROM CUST.PRO
       WHERE CUST-ID = :$OLD-CUST
   ENDSQL

ELSE
    SQL
       SELECT
       FROM CUST.PRO
       WHERE CUST-ID = :$NEW-CUST
   ENDSQL
ENDIF

When a SELECT is specified for the primary file in a Paging Cycle, the file becomes an Indexed Paging file, and database cursor repositioning after terminal input must be handled in the same way as for Transparency mode.

Since PROIV has to reissue the original SELECT to reposition itself, the values in any host variables used in the SELECT statement must not change during the execution of the cycle, otherwise a different set of rows will be used, resulting in unpredictable paging operations.

For the primary file of a Paging Cycle, or the primary file of a Report or Update Cycle that is not one-time, during the READ phase of the PROIV Timing Cycle, PROIV issues the SELECT statement specified by the user; PROIV then processes the set of returned rows by issuing FETCH commands.

For a file in Add mode, PROIV always uses a Transparency mode SELECT to check if the record already exists, not any user-specified SELECT statement.

For a file in Change or Delete mode, during the WRITE phase, PROIV reverts to Transparency mode processing as follows. PROIV issues another SELECT to retrieve each individual row for update. Using a predicate containing the key field(s) and their value(s) from the row FETCHed in the READ phase, together with a FOR UPDATE OF clause specifying all referenced non-key fields. PROIV then FETCHes the first row of this set and issues an UPDATE/DELETE statement with a predicate consisting of a WHERE CURRENT OF CURSOR clause (or equivalent for the relevant SQL RDBMS).

For a file in Add mode, during the WRITE phase, PROIV issues an INSERT statement with values for every column specified in the file definition.

For the Primary file of a one time Report or Update Cycle, during the READ phase of the PROIV Timing Cycle, PROIV issues the user-specified SELECT statement, together with a FOR UPDATE OF clause specifying all referenced non-key fields (if the file is in Change or Delete mode); PROIV then FETCHes the first row of this set. Therefore, in Full Function, mode Type 1 SELECT statements, you should not include FOR UPDATE clauses.

In this situation, the user-specified SELECT is also used in Add mode to check if the record already exists.

For a file in Change or Delete mode, during the WRITE phase PROIV issues an UPDATE/DELETE statement with a predicate consisting of a WHERE CURRENT OF CURSOR clause (or equivalent for the relevant SQL RDBMS).

For a file in Add mode, during the WRITE phase, PROIV issues an INSERT statement with values for every column specified in the File Definition.

Secondary Files

A Type 1 SELECT is executed once for each record of the secondary file in the current cycle, or Update or Report cycle, according to PROIV rules of processing.

As with primary files, there may be multiple Type 1 SELECTs for the same file, but only the last one encountered at execution time is used for accessing the file.

For the Secondary file of any current cycle, or Update or Report cycle, during the READ phase of the PROIV Timing Cycle, PROIV issues either the user-specified or the Full Function mode SELECT statement (together with a FOR UPDATE OF clause specifying all referenced non-key fields if the file is in Change or Delete mode); PROIV then FETCHes the first row of this set. Therefore in Full Function mode, Type 1 SELECT statements, you should not include FOR UPDATE clauses.

In this situation, the user-specified SELECT is also used in Add mode to check if the record already exists.

For a file in Add mode, during the WRITE phase, PROIV issues an INSERT statement with values for every column specified in the File Definition.

Non-SELECT Statements

PROIV supports non-SELECT SQL statements coded in any PROIV Logic. Since the response from a non-SELECT statement is just the SQL return code and no data is returned, PROIV executes the SQL statement when encountered during execution of the logic, not during any file processing.

The statement must use the standard SQL syntax and must be delimited by SQL and ENDSQL statements. It may also include host variables, identified by a colon (:), and PROIV replaces these with their value at execution time. PROIV does not alter the SQL statement in any other way, so all other identifiers used in the statement must be true SQL identifiers.

Example:

SQL
   DELETE FROM SQLTBLA
   WHERE SQLCOLA IN(SELECT SQLCOLB
   FROM SQLTBLB
   WHERE SQLCOLC < :$VAR1)
ENDSQL

Embedded SQL statements may be explicitly defined, or dynamically built for execution at run time.

Full Function mode is not generally recommended for DML statements as it represents a return to 3GL programming practices.

No transaction processing (commit or rollback), cursor manipulation or database connection or disconnection statements are allowed. All of these functions must be handled by PROIV.

Support of Full Function Mode with Auto-Sequenced Files

Auto-sequenced files are designed such that PROIV has complete control of the keys. Full Function mode negates this functionality when used in Cycle mode logic and consequently cannot be supported.

The Full Function mode access to auto-sequence files does not work in Cycle mode logic. The data stream returned by Full Function mode cannot be accessed in this way for the following reasons:

All file accesses to an auto-sequenced file requires a full key defined.
The full key requirement interferes with the partial key as presented by the Full Function mode.
Cursors cannot be accessed by the key.

Support for Full Function Mode with NOWAIT Clause

Full Function mode does not support the NOWAIT clause because LOCKED_ROWS_RETURNED=Y adds at runtime NOWAIT, and as a consequence, the following error is generated: 'SELECT type 2 support with ORACLE, error ORA-00933 SQL command not properly ended (NOWAIT)'.

Comment on this topic

Topic ID: 720106