Dynamic Statement Example
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 Appendix C: “Embedded SQL Examples.”
Note the following about the example:
1 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.
2 INSERT—INSERT does not return data, nor does it require input host variables.
This facility allows the statement to execute using an EXECUTE IMMEDIATE.
3 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 Teradata 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.
Note: 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.