1 INCLUDE Inserts declarations or code into a precompiled host language program. You can use the INCLUDE statement to insert: o Host language declarations for the SQL Communications Area (SQLCA) and a message vector o Host language declarations for the SQL Descriptor Areas (SQLDA and SQLDA2) o Host language source code o Host language declarations for repository record definitions 2 Environment You can use the INCLUDE statement in precompiled host language programs only. Programs must either use an INCLUDE SQLCA statement or explicitly declare an SQLCODE variable. The other forms of the INCLUDE statement are optional. 2 Format INCLUDE --+--> SQLCA --+-------------+-------------+-> | +-> EXTERNAL -+ | +--> SQLDA ------------------------------+ +--> SQLDA2 -----------------------------+ +--> ------------------------+ +--> FROM DICTIONARY -----+ | +--------------------------------+ | +-+----------------------------+-+ | +-> FIXED -----------------+-+ | | +-> NULL TERMINATED BYTES -+ | | +--------------------------------+ | +-+---------------+-----------------+ +-> AS --+ 2 Arguments 3 AS_name Specifies a name to override the structure name of the record from the repository. By default, the SQL precompiler takes the structure name from the repository record name. 3 EXTERNAL Declares an external reference to the SQLCA structure for SQL precompiled C programs. If you have multiple modules that use the INCLUDE SQLCA statement, you can add the EXTERNAL keyword to all but one of them. If your application shares the SQLCA among multiple images, one image must define the SQLCA while all other images must reference the SQLCA. Use the EXTERNAL keyword to reference the SQLCA. 3 file-spec The file specification for source code to be inserted into your program. The file specification must refer to a standard OpenVMS text file. SQL does not support the INCLUDE statement from text libraries (file extension .tlb). Use the SQL INCLUDE statement in either of these cases: o The source code to be included contains embedded SQL statements. o The source code to be included contains host language variable declarations to which embedded SQL statements in other parts of the program refer. If the source code contains neither SQL statements nor variables to which SQL statements refer, using the SQL INCLUDE statement is no different from using host language statements to include files. 3 FIXED The FIXED and NULL TERMINATED BYTES clauses tell the precompiler how to interpret C language CHAR fields. If you specify FIXED, the precompiler interprets CHAR fields from the repository as fixed character strings. 3 FROM_DICTIONARY Specifies the path name for a repository record definition. Because SQL treats the path name as a string literal, you should enclose it in single quotation marks. SQL declares a host structure corresponding to the repository record definition and gives it the same name. SQL statements embedded in the program can then refer to the host structure. Typically, programs use the FROM DICTIONARY argument as a convenient way to declare host structures that correspond to table definitions stored in the repository. SQL stores table definitions in the repository in the following cases only: o Both the CREATE DATABASE statement and the database declaration for the attach in which the table was defined specified the PATHNAME argument. o The database definitions were copied to the repository with an INTEGRATE statement. However, programs can use the FROM DICTIONARY argument to declare host structures for any CDD$RECORD repository object type, including those repository objects defined as part of the database. Using the INCLUDE statement does more than using a comparable host language statement that inserts a CDD$RECORD object into the program. The INCLUDE FROM DICTIONARY statement lets you refer to the repository record in an embedded SQL statement, while the host language statement does not. 3 NULL_TERMINATED_BYTES Specifies that CHAR fields from the repository are null- terminated. The module processor interprets the length field in the repository as the number of bytes in the string. If n is the length in the repository, then the number of data bytes is n-1, and the length of the string is n bytes. In other words, the precompiler assumes that the last character of the string is for the null terminator. Thus, a field that the repository lists as 10 characters can only hold a 9-character SQL field from the C precompiler. If you do not specify a character interpretation option, NULL TERMINATED BYTES is the default. For more information, see the NULL TERMINATED CHARACTERS argument in the SQL Module Language chapter of the Oracle Rdb SQL Reference Manual. 3 SQLCA Specifies that SQL inserts into the program the SQLCA and a message vector (RDB$MESSAGE_VECTOR) structure specific to supported database systems. Both the SQLCA and the message vector provide ways of handling error conditions: o The SQLCA is a collection of variables that SQL uses to provide information about the execution of SQL statements to application programs. The SQLCA shows if a statement was successful and, for some conditions, the particular error when a statement was not successful. o The message vector is also a collection of variables that SQL updates after SQL executes a statement. The message vector also lets programs check if a statement was successful, but provides more detail than the SQLCA about the type of error condition if a statement was not successful. For more information on the SQLCA and the message vector, see the Oracle Rdb SQL Reference Manual. 3 SQLDA Specifies that SQL inserts the SQLDA into the program. The SQLDA is a collection of variables used only in dynamic SQL. The SQLDA provides information about dynamic SQL statements to the program, and information about host language variables in the program to SQL. 3 SQLDA2 Specifies that SQL inserts the SQLDA2 into the program. The SQLDA2, like the SQLDA, is a collection of variables that provides information about dynamic SQL statements to the program and information about host language variables in the program to SQL. You should use the SQLDA2 in any dynamic statement where the column name used in a parameter marker or select list item is one of the date-time or interval data types. For more information on the SQLDA and SQLDA2, see the Oracle Rdb SQL Reference Manual. 2 Examples Example 1: Including a host structure declaration This simple COBOL program uses the INCLUDE FROM DICTIONARY statement to declare a host structure that corresponds to the EMPLOYEES table in the sample personnel database. The repository path name specifies the RDB$RELATIONS repository directory between the database directory and the table name. IDENTIFICATION DIVISION. PROGRAM-ID. INCLUDE_FROM_CDD. * * Illustrate how to use the INCLUDE FROM DICTIONARY * statement to declare a host structure corresponding to * the EMPLOYEES table: * DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL WHENEVER SQLERROR GOTO ERR END-EXEC. * * Include the SQLCA: EXEC SQL INCLUDE SQLCA END-EXEC. * * Declare the schema: * (Notice that declaring the alias with the * FILENAME qualifier would not have precluded * using the INCLUDE FROM DICTIONARY statement later.) EXEC SQL DECLARE PERS ALIAS FOR PATHNAME 'CDD$DEFAULT.PERSONNEL' END-EXEC. * * Create a host structure that corresponds to the * EMPLOYEES table with the INCLUDE FROM DICTIONARY * statement. The path name in the INCLUDE statement * must specify the RDB$RELATIONS directory before * the table name: EXEC SQL INCLUDE FROM DICTIONARY 'CDD$DEFAULT.PERSONNEL.RDB$RELATIONS.EMPLOYEES' END-EXEC. * * Declare an indicator structure for the host * structure created by the INCLUDE FROM DICTIONARY statement: 01 EMPLOYEES-IND. 02 EMP-IND OCCURS 12 TIMES PIC S9(4) COMP. EXEC SQL DECLARE E_CURSOR CURSOR FOR SELECT * FROM EMPLOYEES END-EXEC. PROCEDURE DIVISION. 0. DISPLAY "Display rows from EMPLOYEES:". EXEC SQL OPEN E_CURSOR END-EXEC. EXEC SQL FETCH E_CURSOR INTO :EMPLOYEES:EMP-IND END-EXEC. PERFORM UNTIL SQLCODE NOT = 0 DISPLAY EMPLOYEE_ID, FIRST_NAME, LAST_NAME EXEC SQL FETCH E_CURSOR INTO :EMPLOYEES:EMP-IND END-EXEC END-PERFORM. EXEC SQL CLOSE E_CURSOR END-EXEC. EXEC SQL ROLLBACK END-EXEC. EXIT PROGRAM. ERR. DISPLAY "unexpected error ", sqlcode with conversion. CALL "SQL$SIGNAL". Example 2: Including the SQLCA This fragment from a PL/I program shows the INCLUDE SQLCA statement and illustrates how an error-handling routine refers to the SQLCA. The program creates an intermediate result table, TMP, and copies the EMPLOYEES table from the personnel database into it. It then declares a cursor for TMP and displays the rows of the cursor on the terminal screen. /* Include the SQLCA: */ EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO ERROR_HANDLER; EXEC SQL DECLARE ALIAS FOR FILENAME personnel; DCL MANAGER_ID CHAR(5), LAST_NAME CHAR(20), DEPT_NAME CHAR(20); DCL COMMAND_STRING CHAR(256); EXEC SQL CREATE TABLE TMP (MANAGER_ID CHAR(5), LAST_NAME CHAR(20), DEPT_NAME CHAR(20)); COMMAND_STRING = 'INSERT INTO TMP SELECT E.LAST_NAME, E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.EMPLOYEE_ID = D.MANAGER_ID'; EXEC SQL EXECUTE IMMEDIATE :COMMAND_STRING; EXEC SQL DECLARE X CURSOR FOR SELECT * FROM TMP; EXEC SQL OPEN X; EXEC SQL FETCH X INTO MANAGER_ID, LAST_NAME, DEPT_NAME; DO WHILE (SQLCODE = 0); PUT SKIP EDIT (MANAGER_ID, ' ', LAST_NAME, ' ', DEPT_NAME) (A,A,A,A,A); EXEC SQL FETCH X INTO MANAGER_ID, LAST_NAME, DEPT_NAME; END; EXEC SQL ROLLBACK; PUT SKIP EDIT (' ALL OK') (A); RETURN; ERROR_HANDLER: /* Display the value of the SQLCODE field in the SQLCA: */ PUT SKIP EDIT ('UNEXPECTED SQLCODE VALUE ', SQLCODE) (A, F(9)); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK;