The following steps are required for using the WHERE constraint.
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.
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).
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;