To use the WHERE constraint from COBOL

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(300) 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

• WHERE constraints added by the COBOL program may not be portable between databases.
• The application may specify conditions of such complexity that they confuse the database query optimizer, resulting in poor performance. Be sure to examine the results of the optimizer trace facilities to ensure optimal performance.
• Verify the COBOL application is not sending information that results in a syntax error that is not detected until run time.
• The WHERE constraint should reference only columns in the primary table.
• If users have different configurations at different sites, so that some files can be vision files at one site, but Acu4GL tables at another site, then the WHERE-CONSTRAINT can be difficult to use. This happens because after the WHERE-CONSTRAINT is used, it is marked empty so it won't be used again. However, if a file is a vision file, the WHERE-CONSTRAINT is not used, and might be used for the next file. For example: At site 1, files AA and AB are both SQL Server tables. At site 2, file AA is a vision file, and AB is a SQL Server table. If the COBOL program sets a WHERE-CONSTRAINT that would work with file AA, then at site 2, that WHERE-CONSTRAINT will instead be used for file AB, which will fail.