To use the WHERE constraint from COBOL

Restriction: This topic applies only when a Database Connectors license has been installed via the Micro Focus License Management System.

The following steps are required for using the WHERE constraint.

  1. Declare an External Variable

    To make use of WHERE constraints from COBOL, the application must declare an external variable for communication with the Database Connectors interface. This variable is declared as follows:

    77  a4gl-where-constraint  pic x(32750) external.
  2. Modify Your COBOL Procedures

    Your COBOL application should move the information that you want added to the WHERE clause to the new external variable before a COBOL positioning operation such as START or READ is performed. The additional constraint is then applied to any SQL read query performed on that file until a new positioning operation is performed.

    The additional query information is also stored in the Connector's cursor cache, so that if the same read conditions occur in later processing, the existing closed cursor can be reused with new bind variables instead of being regenerated.

    Be sure to fill the external variable before a positioning operation (START or READ). The WHERE constraint affects only READ NEXT operations preceded by a positioning operation. The WHERE constraint does not affect a READ NEXT that was not preceded by a positioning operation (such as a READ NEXT without a START immediately after opening a file).

Example

In your COBOL program you include this statement:

Move "ftest_key > 3 and ftest_key < 6" to
   A4GL_WHERE_CONSTRAINT.
Inspect A4GL_WHERE_CONSTRAINT replacing trailing spaces by
   low-values.
START FTEST-FILE KEY NOT LESS FTEST-KEY.

These results occur:

CURSOR 0: 
SELECT *,ROWID FROM ftest WHERE (ftest_key1_seg1 = ? AND 
ftest_key1_seg2 >= ?) AND (ftest_key > 3 and ftest_key < 6) 
ORDER BY ftest_key1_seg1 ASC, ftest_key1_seg2 ASC;
CURSOR 1: 
SELECT *,ROWID FROM ftest WHERE (ftest_key1_seg1 > ?) AND 
(ftest_key > 3 and ftest_key < 6) ORDER BY ftest_key1_seg1 ASC, 
ftest_key1_seg2 ASC;

Limitations