Dynamic Statement Example
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;*