Check SQL Syntax at Compile Time

Overview

Implementation of Enhancement Requests #1836 and #1837 in v13.12.26 and later versions of the Elastic COBOL compiler optionally check embedded SQL statements against a database at compile time.  Also, static cursor declaration can be forced on DECLARE CURSOR statements appearing in the PROCEDURE DIVISION of a program.  

The syntax check also will automatically apply any necessary SQL TYPE IS clauses to your working storage and linkage section elements and groups that are referenced as host variables. This applies to both "get" (host variables used in WHERE clauses) and "set" (host variables used in FETCH statements or SELECT INTO clauses).

The compiler directives introduced as part of #1836 and #1837 are described in the Elastic COBOL Programmers Guide and ecobol Options Forum article. 

Compile Time Checking

The following compile-time directives trigger embedded SQL checking:

• -sql:url jdbc:postgresql://localhost/mydb -- use JDBC to connect to this URL
• -sql:user myuserid -- use this JDBC user id when connecting to the database
• -sql:password mypassword -- use this JDBC password when connecting to the database
• -sql:driver org.postgresql.Driver -- use this JDBC driver class name when connecting (optional for many JDBC v3.0 and later drivers)

Compiler directives can be placed in any of these locations:

1. On the ecobol or ecobol.exe command line
2. In the ecobol.dir file referenced by another directive to the compiler
3. In the Additional Settings location of the SQL tab within the COBOL Compile Options dialog box for the Elastic COBOL project within Eclipse
4. Within the program being compiled with the $SET directive where $ is in column 7, for example,
   $SET DIRECTIVE "-sql:user myuserid"

A JDBC Java program checks EXEC SQL ... END-EXEC statements within the program.  The checker is contained in the ecobol.jar run time library.  The CLASSPATH environment variable must contain the location of that jar (e.g., "/usr/local/lib/ecobol.jar") prior to running the compiler or starting Eclipse.  The appropriate database driver associated with the URL must also be listed in the colon-separate (Linux) or semicolon-separated (Windows)  CLASSPATH environment variable (e.g., "/usr/local/lib/postgresql-9.2-1003.jdbc4.jar").  The java command must also be available in the system PATH environment variable prior to compilation.  The database must be started and accessible with the URL, user and password supplied at compile time.

Any of the following warnings may be emitted, SQL checking is suspended, but compilation continues if the above conditions are not met:

• EC8067W SQL Check: Could not invoke JDBC SQL checker as requested by '-sql:url' directive. Check CLASSPATH for 'ecobol.jar' and PATH for 'java'.

• EC8067W SQL Check: Class not found on CLASSPATH - org.postgresql.Driver

• EC8067W SQL Check: No suitable driver found for jdbc:postgresql://localhost/tickets

• EC8067W SQL Check: Connection refused. Check that the hostname and port are correct and that the database is running

SQL statements are prepared with the database driver and it may issue error, warning or informational messages when SQL syntax or semantic errors are detected.  These messages are reflected in the Elastic COBOL compilation log and within the Eclipse IDE.  Compilation continues despite any warnings.  The following are examples of these two error types:

• EC8067W SQL Check: ERROR: syntax error at or near "value"
• EC8067W SQL Check: ERROR: relation "pbytea" does not exist

An informational message is generated when host variables referenced in a SQL statement are different from the declared data type:

• EC8068I 'SQL TYPE IS BYTEA' clause added to 'XBA OF REC'

Inferring the SQL TYPE IS clause will affect run time conversions of the host variable to the appropriate database type.  Some conversions (character to numeric) may still not be possible at run time depending upon the data within the host variables.

Note:  You may eliminate these information messages by changing your program to explicitly place the SQL TYPE IS clause on your groups and elements.  Subsequent compiles would then not require compile-time SQL syntax checking.

Checking SQL statements at compile time does not eliminate the possibility of run time SQL errors.  Also, checking may be incomplete when tables are created or altered within the application itself.  Unlike mainframe DB2 that relies on execution plans to be created and packages of statements to be bound at compile time, JDBC relies on dynamic execution of SQL statements and thus the conditions at compile time may not be the same as when the application is executed.

Static Declarations of Cursors

By default Elastic COBOL treats DECLARE CURSOR statements within the DATA DIVISION to be static and those within the PROCEDURE DIVISION to be dynamic.  Dynamic cursor declaration allows for different variables to be bound based on programmatic decisions.  For example, in this snippet of COBOL code:

IF QUERY-CUST-NAME = 'Y' THEN
    EXEC SQL
        DECLARE C1 CURSOR FOR SELECT * FROM CUST WHERE NAME=:WS-NAME
    END-EXEC
ELSE
    EXEC SQL
        DECLARE C1 CURSOR FOR SELECT * FROM CUST WHERE NUMBER=:WS-CUST-NUM
    END-EXEC
END-IF

Mainframe DB2 on the other hand treats all cursor declarations as static -- as if they appear in the DATA DIVISION.  In order to execute this behavior within Elastic COBOL set the first compiler directive:

• -sql:declarestatic -- declare all cursors as static
• -sql:declaredynamic -- declare cursors within procedure division as dynamic (default)

Using -sql:declarestatic allows cursors to be declared lexically after they are referenced in an OPEN or FETCH statement or in a paragraph that is not executed.