A sample COBOL program at the end of this section selects multiple rows from a table called EMPLOYEE, inserts a row into that table, then deletes the row just inserted. The example is intended to illustrate dynamic statements, using the three dynamic mechanisms that are available.
In the example, SQL statements are coded directly into working storage. The application could also read the statements from a terminal, from a file, or by some other method.
The SQLDA used for the DELETE statement has been partially assigned values in the WORKING STORAGE SECTION.
SQLDA field assignments also can be made in the PROCEDURE DIVISION.
The EMPLOYEE table is shown in Embedded SQL Examples
Note the following about the example:
- SELECT—SELECT is a data returning statement (potentially). Therefore, the statement is executed using a dynamic cursor.
The PREPARE readies the statement in the SQL string SEL-STMT for execution.
Notice that the program initializes the SqlDAID, SqlDABC and SqlN fields prior to executing the DESCRIBE request. SqlDABC is set by PP2 runtime. SqlN indicates to the DESCRIBE the number of repeating element groups available to contain field information. DESCRIBE returns information about the data coming back into the user-defined SQLDA (SQLDA)
The OPEN statement specifies the host variable whose value will replace the question mark found in the SELECT statement. OPEN executes the select, returning an indication of success or failure to the application, as well as the number of rows which are returned if the statement is successfully executed
The FETCH loop returns the data into the specified host variables through the SQLDA structure until no more data is found (+100 in the SQLCODE) or an error occurs (negative SQLCODE).
CLOSE terminates the cursor and performs the cleanup related to the SELECT statement.
- INSERT—INSERT does not return data, nor does it require input host variables.
This facility allows the statement to execute using an EXECUTE IMMEDIATE.
- DELETE—DELETE does not return data, but requires an input host variable.
This process requires the EXECUTE statement.
The PREPARE readies the statement in the SQL string for execution.
A DESCRIBE is not required for the DELETE statement because no data is returned.
The EXECUTE submits DELETE to the database for processing, passing the value of the host variable indicated in the SQLDA (DELSQLDA) for null.
The success or failure of the transaction is reflected in the SQLCODE.
The number of rows deleted, if any, appears in the first SQLERRD element.
The <comp> specification in the following example is COMP-5 for MF COBOL and COMP for all other COBOL compilers.
IDENTIFICATION DIVISION. PROGRAM-ID. LAB8. AUTHOR. WHR. ENVIRONMENT DIVISION. DATA DIVISION. ******************************************************* WORKING-STORAGE SECTION ******************************************************* 01 LOGON-STRING. 49 FILLER PIC S9(4) <comp> VALUE +12. 49 FILLER PIC X(12) VALUE ’tdp/user,psw’. 01 EMPLOYEE-RECORD. 02 EMPNUM PIC S9(9) <comp>. 02 MANNUM PIC S9(9) <comp>. 01 EMPOUT PIC -(15)9. 01 MANOUT PIC -(15)9. 01 EMPIND PIC S9(4) <comp>. 01 MANIND PIC S9(4) <comp>. 01 SQL-STATEMENT. 49 STMT-LEN PIC S9(4) <comp> VALUE +87. 49 FILLER PIC X(24) VALUE ’SELECT EMPLOYEE_NUMBER, ’. 49 FILLER PIC X(24) VALUE ’MANAGER_EMPLOYEE_NUMBER ’. 49 FILLER PIC X(14) VALUE ’FROM EMPLOYEE ’. 49 FILLER PIC X(25) VALUE ’WHERE EMPLOYEE_NUMBER = ?’. 01 INS-STATEMENT. 49 STMT-LEN PIC S9(4) <comp> VALUE +90. 49 FILLER PIC X(24) VALUE ’INSERT EMPLOYEE VALUES (’. 49 FILLER PIC X(37) VALUE’2010,1003,2216,8201,’’JONES’’,’’FREDDY’’,. 49 FILLER PIC X(29) VALUE ’’’20/06/14’’,’’19/05/26’’,200000)’. 01 DEL-STATEMENT. 49 STMT-LEN PIC S9(4) <comp> VALUE +46. 49 FILLER PIC X(46) VALUE ’DELETE FROM EMPLOYEE WHERE EMPLOYEE_NUMBER = ?’. 01 SQLDA. 02 SQLDAID PIC X(8) VALUE ’SQLDA ’. 02 SQLDABC PIC S9(9) <comp> VALUE 0. 02 SQLN PIC S9(4) <comp> VALUE +2. 02 SQLD PIC S9(4) <comp>. 02 SQLVAR OCCURS 2 TIMES. 03 SQLTYPE PIC S9(4) <comp>. 03 SQLLEN PIC S9(4) <comp>. 03 SQLDATA PIC X(4). 03 SQLIND PIC X(4). 03 SQLNAME PIC X(32). 01 DELSQLDA. 02 DELDAID PIC X(8). 02 DELDABC PIC S9(9) <comp> VALUE +60. 02 DELN PIC S9(4) <comp> VALUE +1. 02 DELD PIC S9(4) <comp> VALUE +1. 02 DELVAR. 03 DELTYPE PIC S9(4) <comp> VALUE +496. 03 DELLEN PIC S9(4) <comp> VALUE +4. 03 DELDATA PIC X(4). 03 DELIND PIC X(4). 03 DELNAME PIC X(32). 01 ERROR-MSG. 49 FILLER PIC S9(4) <comp>. 49 ERROR-TXT PIC X(255). 01 ERROR-CODE PIC S9(9) <comp>. 01 MAX-LENGTH PIC S9(4) <comp> VALUE +255. 01 OUT-CODE PIC -(15)9. 01 REQUEST-TYPE PIC X(8). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL DECLARE EMPCUR CURSOR FOR EMPSTMT END-EXEC. ******************************************************* PROCEDURE DIVISION ******************************************************* DISPLAY ’EXECUTING SAMPLE...’. DISPLAY ’ ’. ******************************************************* ***** LOGON ******************************************************* MOVE ’LOGON’ TO REQUEST-TYPE. EXEC SQL LOGON :LOGON-STRING END-EXEC. PERFORM ERROR-CHECK. IF (SQLCODE NOT = 0) THEN GOBACK. ******************************************************* ***** PREPARE ******************************************************* MOVE ’PREPARE’ TO REQUEST-TYPE. EXEC SQL PREPARE EMPSTMT FROM :SQL-STATEMENT END-EXEC. PERFORM ERROR-CHECK. IF (SQLCODE < 0) THEN PERFORM LOGOFF. ******************************************************* ***** DESCRIBE ******************************************************* MOVE ’DESCRIBE’ TO REQUEST-TYPE. EXEC SQL DESCRIBE EMPSTMT INTO SQLDA END-EXEC. PERFORM ERROR-CHECK. IF (SQLCODE = 0) THEN CALL ’DBCHSAD’ USING SQL-RETCODE, SQLDATA(1), EMPNUM CALL ’DBCHSAD’ USING SQL-RETCODE, SQLIND(1), EMPIND CALL ’DBCHSAD’ USING SQL-RETCODE, SQLDATA(2), MANNUM CALL ’DBCHSAD’ USING SQL-RETCODE, SQLIND(2), MANIND. IF (SQLCODE < 0) THEN PERFORM LOGOFF. ******************************************************* ***** OPEN ******************************************************* MOVE ’OPEN’ TO REQUEST-TYPE. MOVE 1001 TO EMPNUM. EXEC SQL OPEN EMPCUR USING :EMPNUM END-EXEC. PERFORM ERROR-CHECK. IF (SQLCODE = 0) THEN PERFORM FETCH-EMPCUR UNTIL SQLCODE NOT = 0. ******************************************************* ***** CLOSE ******************************************************* MOVE ’CLOSE’ TO REQUEST-TYPE. EXEC SQL CLOSE EMPCUR END-EXEC. PERFORM ERROR-CHECK. ******************************************************* ***** EXECUTE IMMEDIATE ******************************************************* MOVE ’EXECUTE’ TO REQUEST-TYPE. EXEC SQL EXECUTE IMMEDIATE :INS-STATEMENT END-EXEC. PERFORM ERROR-CHECK. ******************************************************* ***** PREPARE ******************************************************* MOVE ’PREPARE’ TO REQUEST-TYPE. EXEC SQL PREPARE DELSTMT FROM :DEL-STATEMENT END-EXEC. PERFORM ERROR-CHECK. IF (SQLCODE < 0) THEN PERFORM LOGOFF. ******************************************************* ***** EXECUTE ******************************************************* MOVE ’EXECUTE’ TO REQUEST-TYPE. CALL ’DBCHSAD’ USING SQL-RETCODE, DELDATA, EMPNUM. MOVE +2010 TO EMPNUM. MOVE LOW-VALUES TO DELIND. EXEC SQL EXECUTE DELSTMT USING DESCRIPTOR DELSQLDA END-EXEC. PERFORM ERROR-CHECK. ******************************************************* ***** LOGOFF ******************************************************* LOGOFF. MOVE ’LOGOFF’ TO REQUEST-TYPE. EXEC SQL LOGOFF END-EXEC. PERFORM ERROR-CHECK. GOBACK. ******************************************************* ***** FETCH ******************************************************* FETCH-EMPCUR. MOVE ’FETCH’ TO REQUEST-TYPE. EXEC SQL FETCH EMPCUR USING DESCRIPTOR SQLDA END-EXEC. PERFORM ERROR-CHECK. IF (SQLCODE = 0) THEN MOVE EMPNUM TO EMPOUT MOVE MANNUM TO MANOUT DISPLAY ’ ’ DISPLAY ’EMPLOYEE NUMBER : ’, EMPOUT DISPLAY ’MANAGER NUMBER : ’, MANOUT. ******************************************************* ***** ERROR CHECK ******************************************************* ERROR-CHECK. IF (SQLCODE NOT = 0) THEN MOVE SPACES TO ERROR-TXT CALL ’PPRTEXT’ USING SQL-RDTRTCON, ERROR-CODE, ERROR-MSG, MAX-LENGTH MOVE ERROR-CODE TO OUT-CODE DISPLAY ’ ’ DISPLAY ’ERROR/WARNING DETECTED IN ’, REQUEST-TYPE DISPLAY ’ CODE: ’, OUT-CODE DISPLAY ’ MSG : ’, ERROR-TXT.