Trail: PROIV Documentation > Administration > Environment Guide > Transparent Mode

In Transparent Mode, PROIV automatically generates the necessary SQL statements for SQL table data access/manipulation for the access modes allowed.  In order to do this it uses the PROIV SQL File Definitions referenced in the function. The statements are executed according to the normal PROIV Timing Cycle, that is, reading from and writing to an SQL table occurs at the same point in the PROIV execution cycle as for all PROISAM files.

The following sections describe how, and in what format, SQL statements are automatically generated for access of the Primary File (first name specified in the File Information section of a function definition) and a Secondary File (any subsequent name specified in the FILE INFORMATION section).

Primary Files

PROIV generates a 'default' SELECT statement for each SQL file definition in a function, with the selection criteria based upon the K (key) type fields for that file.  This SELECT statement:

• Retrieves only those columns referenced in the PROIV Function.

• Can result in zero, one or more rows being returned. Selection criteria that generate a multiple row response where only a single row can be processed should be avoided because the results are unpredictable.

• Never specifies a join of SQL tables.

• Can be controlled by SEL-ONLY, SEL-PARTIAL and SEL-RANGE commands in default logic.  These commands operate in exactly the same way as for standard file types, with the exception of SEL-RANGE acting on a file with more than one KEY type field.  For non-SQL file types, SEL-RANGE sets a start and end point, but does not restrict access on any record within that range.  For SQL files, SEL-RANGE sets a start and end point for each of the key fields in the file definition.  However, SEL-RANGE can be made to operate as for ISAM file types.  (See Transaction Management for more information on SEL-RANGE.)

• PROIV uses the BETWEEN operator on affected key fields whenever a SEL-RANGE or SEL-PARTIAL operation is specified.

Paging Cycle

For the primary file of a Paging Cycle:

• The standard SELECT statement is executed once each time the user repositions to a new page when the primary file is read.

• For an auto-sequenced file, database cursor repositioning involves re-issuing the SELECT with an additional criterion containing a >= operator on the record to be displayed, (the SELECT must still include the ORDER BY clause).

• For an indexed Paging File, repositioning involves re-issuing the original SELECT and then issuing FETCH statements until the required record number is reached. However, this operation is optimised only if the last key part can vary so that the new page can be selected directly without repositioning being required.  The need to re-issue the SELECT for auto-sequenced and indexed paging files is independent of whether the user wishes to go forward or backward through the file.

• The SELECT statement is not reissued in transparent mode when paging forward by exactly one page in a cycle which is not the outermost cycle with files, and a COMMIT() or ROLLBACK() has not been performed by the application.

• During the READ phase of the PROIV Timing Cycle, PROIV builds and issues a SELECT statement as described above, and then retrieves the set of returned rows by issuing FETCH commands.

• For a file in Change or Delete mode (during the READ phase), PROIV issues another SELECT that retrieves each individual row for update. Using a predicate containing the key field(s) and their value(s) together with a FOR UPDATE OF clause specifying all referenced non-key fields.  PROIV then FETCHes the row and issues an UPDATE/DELETE statement with a predicate consisting of a WHERE CURRENT OF CURSOR clause (or equivalent for the relevant 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 an auto-sequenced file, PROIV maintains the integrity of the record sequence when DELETEs or INSERTs are requested in a paging LS, in exactly the same way as it does for non-SQL auto-sequenced files.

Non-Paging Cycle

For the primary file of a non-Paging Cycle:

• During the READ phase of the PROIV Timing Cycle, PROIV builds and issues a SELECT statement as described above, 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 required row for update.

• 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 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.

Non-One Time Report and Update Cycles

For the primary file of a non-one time Report and Update Cycles:

• During the READ phase of the PROIV Timing Cycle, PROIV builds and issues a SELECT statement as described above, PROIV then processes the set of returned rows by issuing FETCH commands.

• For a file in Change or Delete mode, during the READ phase, PROIV issues this SELECT to retrieve each individual row for update using a predicate containing the key field(s) and their value(s) together with a FOR UPDATE OF clause specifying all non-key fields.  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 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.

One Time Report and Update Cycles

For the primary file of a one time Report and Update Cycles:

• During the READ phase of the PROIV timing cycle, PROIV builds and issues a SELECT statement as described above, 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.

• 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 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

PROIV automatically constructs an SQL SELECT statement for all SQL secondary files in a function, retrieving all columns referenced in the PROIV File Definition. The SELECT statement includes a WHERE clause specifying a value for each key field defined in the PROIV File Definition using the equal to (=) relational operator.

• During the READ phase of the PROIV timing cycle, PROIV builds and issues a SELECT statement as described above. 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.

• 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 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.

SQL Statements Generated

This section shows the typical format of the SQL statements generated in various situations.

Read File - Single Row

Where only one row is expected (primary file in a non-Paging Cycle, one-time report or update, all secondary files), a statement of the form:

SELECT key-column-name, key-column-name ... column-name, column-name ...
FROM table-name
WHERE key-column-name = ?, key-column-name = ? ...
FOR UPDATE OF column-name ...

is generated.  (Note that the statement format may vary between the different supported RDBMSs). The FOR UPDATE clause is only appended if the file mode is change or delete (that is, there is update intent).  The table-name is taken from the FILE NAME or ALTERNATE FILE NAME field defined in the PROIV file definition.  The col-name and key-col-name are taken from the PROIV file definition VARIABLE NAME or ALTERNATE fields.

The substitution parameters (the question marks) designate where run-time values are to be assigned to column variables during statement execution.  The actual values passed depend on the current values of the PROIV variables at run-time.

Read File - Multiple Row

Where multiple rows are expected (Paging Cycle, screen Cycle Update, many-time report or update), a typical statement of the form:

SELECT key-col-name, key-col-name ... col-name, col-name ...
FROM table-name
WHERE key-col-name between ? and ?,
      key-col-name between ? and ? ...
ORDER BY key-col-name, key-col-name ...

is generated. The order of the key-col-names in the ORDER BY clause is as specified in the PROIV File Definition.

It is not generally possible to specify FOR UPDATE with an ORDER BY clause, and so PROIV never attempts this.  If there is update intent, a second statement is generated as for single row reads, with a FOR UPDATE OF clause appended using the data retrieved by the above SELECT statement.

The values of the substitution parameters (the question marks) depend on the SEL-xxx statements that have been executed (if any) or if the file is auto-sequenced.

Update File

Rows are normally updated a row at a time except when clearing a file.

A statement of the form:

UPDATE table-name

WHERE CURRENT OF cursor-name

SET (col-name = ?, col-name = ? ...)

Or:

DELETE FROM table-name

WHERE CURRENT OF cursor-name

Or:

INSERT INTO table-name

SET (key-col-name, key-col-name ... col-name, col-name ...)

VALUES (?, ? ...)

is generated according to the operation required.  (Note that the statement format may vary between the different supported DBMS's - see Oracle SQL Statement Generation for details). The cursor names are internal to PROIV.

Comment on this topic

Topic ID: 720261