15.10 - Dynamic Statement Example - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

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.