Dynamic Statement Example - Teradata Preprocessor2

Teradata® Preprocessor2 for Embedded SQL Programmer Guide - 20.00

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Teradata Preprocessor2
Release Number
20.00
Published
October 2023
ft:locale
en-US
ft:lastEdition
2023-11-20
dita:mapPath
nyr1691484085721.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
xfi1470440464166
Product Category
Teradata Tools and Utilities

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 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 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(22) INIT ('TDSU/SM122284,SM122284');
        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_NUMBER, ' ||
                   'DEPARTMENT_NUMBER, ' ||
                   'JOB_CODE, ' ||
                   'LAST_NAME, ' ||
                   'FIRST_NAME, ' ||
                   'HIRE_DATE, ' ||
                   'BIRTHDATE, ' ||
                   'SALARY_AMOUNT ' ||
                   'FROM EMPLOYEE ' ||
                   'WHERE EMPLOYEE_NUMBER = ?'));
        DCL INS_STATEMENT CHAR(91) VAR
            INIT (('INSERT EMPLOYEE ' ||
                   'VALUES (' ||
                   '2010,' ||
                   '1003,' ||
                   '2216,' ||
                   '8201,' ||
                   '''JONES'',' ||
                   '''FREDDY'',' ||
                   '''20/06/14'',' ||
                   '''19/05/26'',' ||
                   '200000)'));
        DCL DEL_STATEMENT CHAR(41) VAR
            INIT ('DELETE EMPLOYEE WHERE EMPLOYEE_NUMBER = ?');
        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 SQLDA;
        CALL ERROR_CHECK ();
        IF SQLCODE = 0
        THEN DO;
            SQLDA.SQLDATA(1) = ADDR(EMPNUM);
            SQLDA.SQLIND(1)  = ADDR(EMPIND);
            SQLDA.SQLDATA(2) = ADDR(MANNUM);
            SQLDA.SQLIND(2)  = ADDR(MANIND);
            SQLDA.SQLDATA(3) = ADDR(DPTNUM);
            SQLDA.SQLIND(3)  = ADDR(DPTIND);
            SQLDA.SQLDATA(4) = ADDR(JOBNUM);
            SQLDA.SQLIND(4)  = ADDR(JOBIND);
            SQLDA.SQLDATA(5) = ADDR(LSTNAM);
            SQLDA.SQLIND(5)  = ADDR(LSTIND);
            SQLDA.SQLDATA(6) = ADDR(FSTNAM);
            SQLDA.SQLIND(6)  = ADDR(FSTIND);
            SQLDA.SQLDATA(7) = ADDR(HIRDAT);
            SQLDA.SQLIND(7)  = ADDR(HIRIND);
            SQLDA.SQLDATA(8) = ADDR(BRTDAT);
            SQLDA.SQLIND(8)  = ADDR(BRTIND);
            SQLDA.SQLDATA(9) = ADDR(SALARY);
            SQLDA.SQLIND(9)  = ADDR(SALIND);
            SQLDA.SQLTYPE(7) = 496;
            SQLDA.SQLTYPE(8) = 496;
            SQLDA.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 = ' ';
            PUT SKIP EDIT (' ') (A);
            CALL PPRTEXT (SQL_RDTRTCON,
            ERROR_CODE,
            ERROR_MSG,
            MAX_LENGTH);
            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;