Application Development Guide

Embedding SQL Statements

Embedded SQL statements consist of the following three elements:

Element

Correct COBOL Syntax

Keyword pair

EXEC SQL

Statement string

Any valid SQL statement

Statement terminator

END-EXEC.

For example:

     EXEC SQL SELECT col INTO :hostvar FROM table END-EXEC.

The following rules apply to embedded SQL statements:

• Executable SQL statements must be placed in the PROCEDURE DIVISION. The SQL statements can be preceded by a paragraph name just as a COBOL statement.

• SQL statements can begin in either Area A (columns 8 through 11) or Area B (columns 12 through 72).

• Start each SQL statement with EXEC SQL and end it with END-EXEC. The SQL precompiler includes each SQL statement as a comment in the modified source file.

• You must use the SQL statement terminator. If you do not use it, the precompiler will continue to the next terminator in the application. This may cause indeterminate errors.

• SQL comments are allowed on any line that is part of an embedded SQL statement. These comments are not allowed in dynamically executed statements. The format for an SQL comment is a double dash (--) followed by a string of zero or more characters and terminated by a line end. Do not place SQL comments after the SQL statement terminator as they will cause compilation errors because they would appear to be part of the COBOL language.

• COBOL comments are allowed almost anywhere within an embedded SQL statement. The exceptions are:

  • Comments are not allowed between EXEC and SQL.

  • Comments are not allowed in dynamically executed statements.

• SQL statements follow the same line continuation rules as the COBOL language. However, do not split the EXEC SQL keyword pair between lines.

• Do not use the COBOL COPY statement to include files containing SQL statements. SQL statements are precompiled before the module is compiled. The precompiler will ignore the COBOL COPY statement. Instead, use the SQL INCLUDE statement to include these files.

  To locate the INCLUDE file, the DB2 COBOL precompiler searches the current directory first, then the directories specified by the DB2INCLUDE environment variable. Consider the following examples:

  • EXEC SQL INCLUDE payroll END-EXEC.

    If the file specified in the INCLUDE statement is not enclosed in quotation marks, as above, the precompiler searches for payroll.sqb, then payroll.cpy, then payroll.cbl, in each directory in which it looks.

  • EXEC SQL INCLUDE 'pay/payroll.cbl' END-EXEC.

    If the file name is enclosed in quotation marks, as above, no extension is added to the name.

    If the file name in quotation marks does not contain an absolute path, then the contents of DB2INCLUDE are used to search for the file, prepended to whatever path is specified in the INCLUDE file name. For example, with DB2 for AIX, if DB2INCLUDE is set to '/disk2:myfiles/cobol', the precompiler searches for './pay/payroll.cbl', then '/disk2/pay/payroll.cbl', and finally './myfiles/cobol/pay/payroll.cbl'. The path where the file is actually found is displayed in the precompiler messages. On OS/2 and Windows platforms, substitute back slashes (\) for the forward slashes in the above example.

  Note:

  The setting of DB2INCLUDE is cached by the DB2 Command Line Processor. To change the setting of DB2INCLUDE after any CLP commands have been issued, enter the TERMINATE command, then reconnect to the database and precompile as usual.

• To continue a string constant to the next line, column 7 of the continuing line must contain a '-' and column 12 or beyond must contain a string delimiter.

• SQL arithmetic operators must be delimited by blanks.

• Full-line COBOL comments can occur anywhere in the program, including within SQL statements.

• Use host variables exactly as declared when referencing host variables within an SQL statement.

• Substitution of white space characters such as end-of-line and TAB characters occur as follows:
  • When they occur outside quotation marks (but inside SQL statements), end-of-lines and TABs are substituted by a single space.
  • When they occur inside quotation marks, the end-of-line characters disappear, provided the string is continued properly for a COBOL program. TABs are not modified.

  Note that the actual characters used for end-of-line and TAB vary from platform to platform. For example, OS/2 uses Carriage Return/Line Feed for end-of-line, whereas UNIX-based systems use just a Line Feed.

[ DB2 List of Books | Search the DB2 Books ]