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 PL/I program follows that selects multiple rows from a table called EMPLOYEE, inserts a row into that table, then deletes the row just inserted. This procedure is performed using the three dynamic mechanisms which are available. This example is intended to illustrate dynamic statements.

The program has the SQL statements coded directly. The application could also read the statements from a terminal, a file or by some other method.

The EMPLOYEE table is shown in Appendix C: “Embedded SQL Examples.”

 

Sequence

Description

SELECT

SELECT is a data returning statement. Thus the statement is executed using a dynamic cursor.

The PREPARE readies the statement in the SQL string SQL_STATEMENT for execution.

The DESCRIBE returns information about the data coming back into the user defined SQLDA (SQLDA).

The program initializes the SqlDAID, SqlDABC and SqlN fields prior to executing the DESCRIBE request.

SqlDABC is set by PP2 runtime, while SqlN indicates to the DESCRIBE the number of repeating element groups available to contain field information.

The OPEN statement specifies the host variable whose value replaces the null 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 that 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

The insert statement does not return data, nor does it require input host variables.

This facility allows the statement to execute using an EXECUTE IMMEDIATE.

DELETE

The delete statement does not return data, but requires an input host variable, which requires an EXECUTE statement.

The PREPARE readies the statement in the SQL string for execution.

A DESCRIBE is not required for the delete because no data is returned.

EXECUTE submits the delete to the Teradata Database for processing, passing the value of the host variable indicated in the SQLDA (SQLDA) for the 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.

SAMPLE:PROC OPTIONS (MAIN);
/* DECLARATIONS OF BUILTIN FUNCTIONS */
   DCL PPRTEXT EXTERNAL ENTRY OPTIONS (ASM INTER);
   DCL ADDR BUILTIN;
   DCL NULL BUILTIN;
/* DECLARATION OF VARIABLES          */
   DCL LOGON_STRING      CHAR(12) INIT (’tdp/user,psw’);
   DCL 01   EMPLOYEE_RECORD,
         02 EMPNUM      BIN FIXED(31),
         02 MANNUM      BIN FIXED(31),
         02 DPTNUM      BIN FIXED(31),
         02 JOBNUM      BIN FIXED(31),
         02 LSTNAM      CHAR(20),
         02 FSTNAM      CHAR(30)       VAR,
         02 HIRDAT      BIN FIXED(31),
         02 BRTDAT      BIN FIXED(31),
         02 SALARY      DEC FIXED(15,2);
   DCL EMPIND  BIN FIXED(15);
   DCL MANIND  BIN FIXED(15);
   DCL DPTIND  BIN FIXED(15);
   DCL JOBIND  BIN FIXED(15);
   DCL LSTIND  BIN FIXED(15);
   DCL FSTIND  BIN FIXED(15);
   DCL HIRIND  BIN FIXED(15);
   DCL BRTIND  BIN FIXED(15);
   DCL SALIND  BIN FIXED(15);
   DCL SQL_STATEMENT     CHAR(176) VAR
      INIT (’SELECT EMPLOYEE_NUMBER, MANAGER_EMPLOYEE_N
UMBER, DEPARTMENT_NUMBER, JOB_CODE, LAST_NAME, FIRST_NA
ME, HIRE_DATE, BIRTHDATE, SALARY_AMOUNT FROM EMPLOYEE W
HERE EMPLOYEE_NUMBER = ?’);
   DCL INS_STATEMENT     CHAR(91) VAR
      INIT (’INSERT EMPLOYEE VALUES (2010,1003,2216,820
1,’’JONES’’,’’FREDDY’’,’’20/06/14’’,’’19/05/26’’,200000
)’);
   DCL 01   SQLDA BASED(SQLDAPTR),
         02 SQLDAID     CHAR(8),
         02 SQLDABC     BIN FIXED(31),
         02 SQLN        BIN FIXED(15),
         02 SQLD        BIN FIXED(15),
         02 SQLVAR (SQLSIZE REFER(SQLN)),
            03 SQLTYPE  BIN FIXED(15),
            03 SQLLEN   BIN FIXED(15),
            03 SQLDATA  PTR,
            03 SQLIND   PTR,
            03 SQLNAME  CHAR(30) VAR;
   DCL SQLSIZE    BIN FIXED(15);
   DCL SQLDAPTR   PTR;
   DCL 01   DELSQLDA,
         02 DELDAID   CHAR(8),
         02 DELDABC   BIN FIXED(31)  INIT(60),
         02 DELN      BIN FIXED(15)  INIT(1),
         02 DELD      BIN FIXED(15)  INIT(1),
         02 DELVAR,
               03 DELTYPE  BIN FIXED(15)  INIT(496),
               03 DELLEN   BIN FIXED(15)  INIT(4),
               03 DELDATA  PTR            INIT(ADDR(EMPNUM)),
               03 DELIND   PTR            INIT(NULL),
               03 DELNAME  CHAR(30) VAR;
   DCL ERROR_MSG  CHAR(255)      VAR;
   DCL ERROR_CODE    BIN FIXED(31);
   DCL MAX_LENGTH    BIN FIXED(15)  INIT (255);
   DCL REQUEST_TYPE      CHAR(8);
      EXEC SQL INCLUDE SQLCA;
      EXEC SQL
         DECLARE EMPCUR CURSOR FOR EMPSTMT;
   PUT SKIP EDIT(’EXECUTING SAMPLE...’) (A);
   PUT SKIP EDIT(’ ’) (A);
/*******************************************************/
/********** LOGON                                      */
/*******************************************************/
   REQUEST_TYPE = ’LOGON’;
   EXEC SQL
      LOGON :LOGON_STRING;
   CALL ERROR_CHECK ();
   IF SQLCODE ¬ = 0
      THEN
         STOP;
/*******************************************************/
/********** PREPARE                                    */
/*******************************************************/
REQUEST_TYPE = ’PREPARE’;
EXEC SQL
      PREPARE EMPSTMT FROM :SQL_STATEMENT;
CALL ERROR_CHECK ();
IF SQLCODE < 0
      THEN
         CALL LOGOFF;
/*******************************************************/
/********** DESCRIBE                                   */
/*******************************************************/
   REQUEST_TYPE = ’DESCRIBE’;
   SQLSIZE = 9;
   ALLOCATE SQLDA SET(SQLDAPTR);
   SQLDAID = ’SQLDA   ’;
   SQLDABC = 0.
   EXEC SQL
      DESCRIBE EMPSTMT INTO SQLSQLDA;
      CALL ERROR_CHECK ();
   IF SQLCODE = 0
      THEN DO;
         SQLDATA(1)  = ADDR(EMPNUM);
         SQLIND(1)   = ADDR(EMPIND);
         SQLDATA(2)  = ADDR(MANNUM);
         SQLIND(2)   = ADDR(MANIND);
         SQLDATA(3)  = ADDR(DPTNUM);
         SQLIND(3)   = ADDR(DPTIND);
         SQLDATA(4)  = ADDR(JOBNUM);
         SQLIND(4)   = ADDR(JOBIND);
         SQLDATA(5)  = ADDR(LSTNAM);
         SQLIND(5)   = ADDR(LSTIND);
         SQLDATA(6)  = ADDR(FSTNAM);
         SQLIND(6)   = ADDR(FSTIND);
         SQLDATA(7)  = ADDR(HIRDAT);
         SQLIND(7)   = ADDR(HIRIND);
         SQLDATA(8)  = ADDR(BRTDAT);
         SQLIND(8)   = ADDR(BRTIND);
         SQLDATA(9)  = ADDR(SALARY);
         SQLIND(9)   = ADDR(SALIND);
         SQLTYPE(7)  = 496;
         SQLTYPE(8)  = 496;
         SQLLEN(9)   = 3842;
      END;
   IF SQLCODE < 0
      THEN
      CALL LOGOFF;
/*******************************************************/
/********** OPEN                                       */
/*******************************************************/
   REQUEST_TYPE = ’OPEN’;
   EMPNUM = 1001;
   EXEC SQL
      OPEN EMPCUR USING :EMPNUM;
   CALL ERROR_CHECK ();
   IF SQLCODE = 0
      THEN DO UNTIL (SQLCODE ¬ = 0);
   CALL FETCH_EMPCUR ();
   END;
/*******************************************************/
/********** CLOSE                                      */
/*******************************************************/
   REQUEST_TYPE = ’CLOSE’;
   EXEC SQL
      CLOSE EMPCUR;
   CALL ERROR_CHECK ();
/*******************************************************/
/********** EXECUTE IMMEDIATE                          */
/*******************************************************/
   REQUEST_TYPE = ’EXECUTE’;
   EXEC SQL
      EXECUTE IMMEDIATE :INS_STATEMENT;
   CALL ERROR_CHECK ();
/*******************************************************/
/********** PREPARE                                    */
/*******************************************************/
   REQUEST_TYPE = ’PREPARE’;
   EXEC SQL
      PREPARE DELSTMT FROM :DEL_STATEMENT;
   CALL ERROR_CHECK ();
   IF SQLCODE < 0
      THEN
   CALL LOGOFF;
/*******************************************************/
/********** EXECUTE                                    */
/*******************************************************/
   REQUEST_TYPE = ’EXECUTE’;
   EMPNUM = 2010;
   EXEC SQL
      EXECUTE DELSTMT USING DESCRIPTOR DELSQLDA;
   CALL ERROR_CHECK ();
   CALL LOGOFF;
/*******************************************************/
/********** LOGOFF                                     */
/*******************************************************/
LOGOFF:PROC;
         REQUEST_TYPE = ’LOGOFF’;
         EXEC SQL
               LOGOFF;
         CALL ERROR_CHECK ();
END LOGOFF;
/*******************************************************/
/********** FETCH CURSOR                               */
/*******************************************************/
FETCH_EMPCUR:PROC;
      REQUEST_TYPE = ’FETCH’;
      EXEC SQL
         FETCH EMPCUR USING DESCRIPTOR SQLDA;
      CALL ERROR_CHECK ();
      IF SQLCODE = 0
         THEN DO;
            PUT SKIP EDIT  (’ ’) (A);
            PUT SKIP EDIT  (’EMPLOYEE NUMBER   :’,EMPNUM)
                           (A, F(15));
            PUT SKIP EDIT  (’MANAGER NUMBER    :’,MANNUM)
                           (A, F(15));
            PUT SKIP EDIT  (’DEPARTMENT NUMBER :’,DPTNUM)
                           (A, F(15));
            PUT SKIP EDIT  (’JOB CODE          :’,JOBNUM)
                           (A, F(15));
            PUT SKIP EDIT  (’LAST NAME         :’,LSTNAM)
                           (A, A);
            PUT SKIP EDIT  (’FIRST NAME        :’,FSTNAM)
                           (A, A);
            PUT SKIP EDIT  (’HIRE DATE         :’,HIRDAT)
                           (A, P’99/99/99’);
            PUT SKIP EDIT  (’BIRTH DATE        :’,BRTDAT)
                           (A, P’99/99/99’);
            PUT SKIP EDIT  (’SALARY AMOUNT     :’,SALARY)
                           (A, F(15,2));
      END;
END FETCH_EMPCUR;
ERROR_CHECK:PROC;
/*******************************************************/
/********** ERROR CHECK                                */
/*******************************************************/
      IF SQLCODE ¬ = 0
         THEN DO;
            ERROR_MSG = ’ ’;
            CALL PPRTEXT   (SQL_RDTRTCON,
                            ERROR_CODE,
                            ERROR_MSG,
                            MAX_LENGTH);
            PUT SKIP EDIT  (’ ’) (A);
            PUT SKIP EDIT  (’ERROR/WARNING DETECTED IN ’,
                            REQUEST_TYPE) (A, A);
            PUT SKIP EDIT  (’   CODE:’, ERROR_CODE)
                           (A, F(9));
            PUT SKIP EDIT  (’   MSG :’, ERROR_MSG)
                           (A, A);
            END;
END ERROR_CHECK;
END SAMPLE;*