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;