Release 1.8
Part Number A87540-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Pro*PL/I Supplement to the Oracle Precompilers Guide Release 1.8 Part Number A87540-01 |
|
This chapter provides the information you need to implement Dynamic SQL Method 4 in your Pro*PL/I application. You learn the following things:
Note: For a discussion of dynamic SQL Methods 1, 2, and 3, and an overview of Method 4, see Chapter 9 of the Programmer's Guide to the Oracle Precompilers.
Before looking into the requirements of Method 4, you should feel comfortable with the terms select-list item and placeholder. Select-list items are the columns or expressions following the keyword SELECT in a query. For example, the following dynamic query contains three select-list items:
'SELECT ename, job, sal + comm FROM emp WHERE deptno = 20'
Placeholders are dummy bind variables that hold places in a SQL statement for actual bind variables. You do not declare placeholders, and can name them anything you like.
Placeholders for bind variables are most often used in the SET, VALUES, and WHERE clauses. For example, the following dynamic SQL statements each contain two placeholders:
'INSERT INTO emp (empno, deptno) VALUES (:E, :D)' 'DELETE FROM dept WHERE deptno = :NUM AND loc = :LOC'
Placeholders, such as bind variables, cannot reference table or column names.
Unlike Methods 1, 2, and 3, dynamic SQL Method 4 lets your program
To add this flexibility to your program, you must provide additional information to the Oracle runtime library.
The Pro*PL/I Precompiler generates calls to Oracle for all executable dynamic SQL statements. If a dynamic SQL statement contains no select-list items or placeholders, Oracle needs no additional information to execute the statement. The following DELETE statement falls into this category:
/* Dynamic SQL statement. */ STMT = 'DELETE FROM emp WHERE deptno = 30';
However, most dynamic SQL statements contain select-list items or placeholders, as does the following UPDATE statement:
/* Dynamic SQL statement with placeholders. */ STMT = 'UPDATE emp SET comm = :C WHERE empno = :E';
To execute a dynamic SQL statement that contains placeholders for bind variables or select-list items, Oracle needs information about the program variables that hold the input (bind) values, and that will hold the FETCHed values when a query is executed. The information needed by Oracle is
All the information Oracle needs about select-list items or placeholders for bind variables, except their actual values, is stored in a program data structure called the SQL Descriptor Area (SQLDA).
Descriptions of select-list items are stored in a select descriptor, and descriptions of bind variables are stored in a bind descriptor.
The values of select-list items are stored in output variables; the values of bind variables are stored in input variables. You store the addresses of these variables in a select or bind SQLDA so that Oracle knows where to write output values and read input values.
How do values get stored in these variables? Output values are FETCHed using a cursor, and input values are typically filled in by the program, often from information entered interactively by the user.
DESCRIBE helps you provide the information Oracle needs by storing descriptions of select-list items or placeholders in a SQLDA.
You use the DESCRIBE statement to help obtain the information Oracle needs.
The DESCRIBE SELECT LIST statement examines each select-list item and determines its name, datatype, constraints, length, scale, and precision. It then stores this information in the select SQLDA, and in program variables pointed to by fields in the SQLDA. The total number of select-list items is also stored in the SQLDA by the DESCRIBE statement.
The DESCRIBE BIND VARIABLES statement obtains the number of placeholders in the SQL statement, and the names and lengths of each placeholder. The program must then fill in the datatype and length of the associated bind variables in the SQLDA, and obtain the bind variable values, which are stored in the program variables pointed to by fields in the SQLDA.
See the section "The Basic Steps" later in this chapter for a complete description of the steps that you perform to declare, allocate, and use the SQLDA.
This section describes the SQL Descriptor Area in detail. You learn what elements the descriptor structure contains, how they should be initialized, and how they are used in your program.
The SQLDA is a PL/I structure that contains two top-level elements and an array of substructures. Each substructure contains information about a single input or output variable. You declare a separate SQLDA major structure for the select-list items, and for the bind (or input) variables. These are called the select descriptor and the bind descriptor.
All SQLDA elements that hold an address are declared as FIXED BINARY (31). This datatype, rather than the more natural PL/I POINTER type, is used to achieve compatibility among different implementations of PL/I. You initialize these elements using the SQLADR procedure. The syntax of this procedure call is
CALL SQLADR(YOUR_BUFFER_ADDRESS, SQLDA_ELEMENT_ADDRESS);
as shown in the following example:
DCL SELECT_DATA_VALUE CHARACTER (10); DCL SQLADR EXTERNAL ENTRY(PTR VALUE, PTR VALUE); ... CALL SQLADR(ADDR(SELECT_DATA_VALUE), ADDR(SELDSC.SQLDSC(1).SQLDV));
In this example, the address of the buffer SELECT_DATA_VALUE is stored in the SQLDV element in the first substructure of the array SQLDSC.
Note: Under IBM operating systems (MVS and VM/CMS), there is an alternate form of SQLADR called SQ3ADR. With SQ3ADR, the arguments are passed without using the PL/I ADDR built-in function. See the section "Sample 9: Dynamic SQL Method 4 Program" later in this chapter to see how to use SQ3ADR.
The SQLDVLN and SQLDVTYP elements contain the length and the datatype code for the select-list item or the bind variable. For a select descriptor, these elements are set when the SQL DESCRIBE statement is executed. You may reset them before actually fetching the values. For more information, see the later section "The SQLDA Variables." For a bind descriptor, you must set the length and datatype.
To declare a SQLDA, copy it into your program with the statement
EXEC SQL INCLUDE DESCRIPTOR_NAME;
where "DESCRIPTOR_NAME" is the name of the file containing the text of the descriptor declaration. Or hardcode it as shown in Figure 5-1.
Note that the example below shows a SQLDA named SELDSC. It is a select descriptor. A bind descriptor is identical.
In the examples used in this section, the structures are named SELDSC for the select SQLDA, and BNDDSC for the bind SQLDA. These structures are identical, except for their names.
Figure 5-2 shows whether variables are set by calls to SQLADR, DESCRIBE commands, FETCH commands, or program assignments.
If your program has more than one active dynamic SQL statement, each statement must have its own SQLDA(s). You can declare any number of SQLDAs with different names. For example, you might declare three select SQLDAs named SEL1, SEL2, and SEL3, so that you can FETCH from three concurrently OPEN cursors. (However, non-concurrent cursors can reuse SQLDAs.)
This section explains the purpose and use of each element in the SQLDA structure.
The name of the structure. The name is arbitrary; however, the examples in this manual use BNDDSC for a bind descriptor and SELDSC for a select descriptor. The names of the structure elements are not arbitrary; they should not be changed.
Contains the number of bind variables or select-list items that can be DESCRIBEd. Before issuing a DESCRIBE statement, your program must set this variable to the maximum size of the SQLDSC substructure array. After the DESCRIBE, SQLDNUM must be reset to the actual number of variables described, which is contained in SQLDFND.
Set when Oracle performs a DESCRIBE statement. It contains the number of bind variables or select-list items DESCRIBEd. If after a DESCRIBE, SQLDFND is less than 0, there were more bind variables or select-list items than specified in SQLDNUM. In this case, SQLDFND is set to the negative of the number of variables DESCRIBEd. For example, if SQLDNUM is 10, and SQLDFND contains the value -11 after a DESCRIBE, 11 select-list items or bind variables were found. If this happens, you cannot process the statement.
Defines an array of N substructures. In a select descriptor, each substructure holds information about a select-list item, and the addresses of the buffers that will hold the data retrieved from the item. In a bind descriptor, each substructure holds information about a bind variable, and the address of the data buffer that contains the value of the bind variable.
You must set N before compiling your program. It should be large enough to handle the maximum number of expected bind variables (for the bind descriptor) or the maximum number of expected select-list items (for the select descriptor).
Holds the address of the buffer that contains the value for this bind variable, or that will hold the data retrieved from the select-list item.
Your program must place the address in this element using the SQLADR procedure. Set this element in a select descriptor before doing a FETCH. In a bind descriptor, set it before doing the OPEN.
Holds the address of a format conversion string, used only with decimal datatypes (FIXED DECIMAL(P,S) or FIXED DECIMAL(P)). They have the Oracle external datatype code 7. The format conversion string is of the form 'PP.+SS' or 'PP.-SS', where PP is the precision of the decimal number, and SS is the scale. The '+' and '-' signs are mandatory. For example, a FIXED DECIMAL(6,2) format string would be '06.+02'. Refer to the "Coercing Datatypes after DESCRIBE" section later in this chapter for more information on the use of precision and scale for Oracle NUMBER data.
Set this element using SQLADR. Set it before a FETCH in a select descriptor, and before an OPEN in a bind descriptor. You must also set the length of the format conversion string in the SQLDFMTL element.
Oracle sets this element when it executes the DESCRIBE statement. It contains the length of the select-list value. The format of the length differs among Oracle datatypes. For character datatypes (VARCHAR2 and CHAR), SQLDVLN is set to the number of bytes in the select-list item. For NUMBER datatypes, the scale is returned in the low-order byte of the variable, and the precision in the next highest-order byte.
If, for a NUMBER datatype, SQLDVLN is set to zero, this means that the column in the table was established with no explicit precision and scale, or the precision and scale for the select-list expression was not determined. In this case, you must decide on an appropriate value (between 0 and 38) and put it in SQLDVLN before the FETCH.
For maximum portability, use the SQLPRC or SQLPR2 library procedures (described in the "Datatypes in the SQLDA" section later in this chapter) to retrieve the precision and scale values from SQLDVLN.
You must reset SQLDVLN to the required length of the data buffer before executing the FETCH statement. For example, if you coerce a described NUMBER to a PL/I CHARACTER string, set SQLDVLN to the precision of the number, and add two to account for the sign and decimal point.
If you coerce a NUMBER to a PL/I FLOAT, set SQLDVLN to the length in bytes of the appropriate FLOAT type in your implementation of PL/I (usually 4 for a FLOAT(7) or less, and 8 for a FLOAT(8) or greater). See the "Datatypes in the SQLDA" section later in this chapter for more information about the lengths of coerced datatypes.
Bind Descriptor
Your program must set the correct SQLDVLN value, which is the length in bytes of the bind variable data buffer that SQLDV points to. Set the length before executing the OPEN statement.
Contains the length of the decimal format conversion string pointed to by the SQLDFMT element. Set it to zero if SQLDFMT does not point
to a format conversion string. Set it before the FETCH in a select descriptor, and before the OPEN in a bind descriptor.
Oracle sets this element when it performs the DESCRIBE statement. It contains the datatype code for the select-list value. This datatype code determines how the Oracle data will be converted into the PL/I data buffer or variable that SQLDV points to. This topic is covered in detail in the "Datatypes in the SQLDA" section later in this chapter.
Note: In a select descriptor, the high-order bit of this element is set to indicate the NULL/NOT NULL status of the field. You should retrieve the datatype code using the SQLNUL procedure. See the "Handling NULL/NOT NULL Datatypes" section later in this chapter for a description of this procedure.
The DESCRIBE statement sets this element to zero. You must set the datatype code in this element before executing the OPEN statement. The code indicates the external PL/I type of the buffer or variable that SQLDV points to. Refer to the section "Datatypes in the SQLDA" later in this chapter for more information on the datatype codes.
Holds the address of an indicator variable, declared in your program. The indicator variable must be a FIXED BIN(15).
You put the address in this element using the SQLADR procedure.
You must initialize this element before doing a FETCH. Oracle sets the indicator values after fetching select-list items.
For select descriptors, when Oracle executes the statement
EXEC SQL FETCH ... USING DESCRIPTOR SELDSC;
if the Jth returned select-list item is NULL, the indicator-variable value to which SELDSC.SQLDSC(J).SQLDI points is set to -1. If not NULL, it is set to zero or a positive integer.
You must initialize this element and set the variable values before doing an OPEN.
For bind descriptors, when Oracle executes the statement
EXEC SQL OPEN ... USING DESCRIPTOR BNDDSC;
the indicator-variable value to which BNDDSC.SQLDSC(J).SQLDI points determines whether the Jth bind variable is NULL. If the value of an indicator variable is -1, the value of its associated variable is NULL.
Holds the address of a buffer used to store the name of a select-list item (for select descriptors) or the name of a bind variable (for bind descriptors). After a DESCRIBE, the appropriate names will be placed in the strings pointed to by the SQLDH_VNAME elements.
Your host program code must initialize the SQLDH_VNAME elements with the addresses of the strings you have declared before executing the DESCRIBE statement. Use the SQLADR procedure to do this initialization.
Contains the maximum length of the buffer pointed to by SQLDH_VNAME. Your program must set this value before issuing a DESCRIBE statement. A value placed in the string pointed to by SQLDH_VNAME will be truncated to this length if necessary.
Contains the actual number of characters in the string pointed to by SQLDH_VNAME after a DESCRIBE.
Holds the address of a string that stores the name of a bind indicator variable. It is set using SQLADR. It must be set before the DESCRIBE statement is executed. This element is not used in a select descriptor.
Contains the maximum number of characters in the SQLDI_VNAME string. Your program must initialize this value before performing a DESCRIBE statement. A value placed in the string pointed to by SQLDI_VNAME will be truncated to this length if necessary. This element is not used in a select descriptor.
Contains the number of characters contained in the string pointed to by SQLDI_VNAME after the DESCRIBE statement. This element is not used in a select descriptor.
Unused element; reserved by Oracle. It must be set to zero when your program starts.
Unused element; reserved by Oracle. It must be set to zero when your program starts.
This section provides more information about using the SQLDVTYP datatype element in the SQLDA. In host programs that do not use datatype equivalencing or Dynamic SQL Method 4, the precompiler predefines how to convert between Oracle data and your program host variables. When you SELECT data in a table into a PL/I variable, the type of the PL/I variable determines the conversion. For example, if you SELECT data from an Oracle column having the type NUMBER into a PL/I CHARACTER array, the numeric data is converted to a character (ASCII or EBCDIC) representation. If you select the same numeric data into an integer (FIXED BINARY) variable, the data is converted to a binary integer representation. When you INSERT or UPDATE data, the data in the host variable is converted to the correct type for the column in the table.
But, when you write a Dynamic Method 4 program, you must specify the conversion by doing the following:
Oracle defines a number of datatypes, and for each datatype, there is a datatype code. There is a distinction between internal datatypes and external datatypes. Internal datatypes are the types that you can assign to an Oracle column in a table, and that Oracle can return from a table. The internal datatypes are CHAR, VARCHAR2, NUMBER, DATE, LONG, RAW, and LONG RAW. There are additional entities that you can SELECT from a table, such as LEVEL, CURRVAL, NEXTVAL, ROWNUM, UID, USER, and SYSDATE, but these entities are always returned as one of the basic internal types. LEVEL, CURRVAL, NEXTVAL, UID, and ROWNUM are NUMBERs; SYSDATE is a DATE type; and USER is a CHAR.
External datatypes include the internal datatypes, and in addition provide extended conversion capabilities. For example, when you DESCRIBE a select list that contains a LONG RAW item, the length of the value is not returned in the SQLDVLN element. You can coerce the LONG RAW internal type to a VARRAW external type, by placing an external datatype code for VARRAW in the SQLDVTYP element after doing the DESCRIBE, but before doing the FETCH. The data returned on the FETCH will then include the length of the LONG RAW item in the first two bytes of the output buffer.
In some cases, the internal datatype codes that a DESCRIBE operation returns in SQLDVTYP might not be the ones you want for your program's purposes. Two examples of this are DATE and NUMBER. When you DESCRIBE a select list containing a DATE item, the datatype code 12 for DATE is returned in the SQLDVTYP element. If you do not change this before the FETCH, the date value is returned as 7 binary bytes that represent the date in the Oracle internal format. To receive the date in a character format (DD-MON-YY), you must change the 12 datatype code to 1 (VARCHAR2), and increase the SQLDVLN (length) value, which was returned as 7, to 9.
Similarly, when you DESCRIBE a select list that contains a NUMBER item, the datatype code 2 is returned in SQLDVTYP. If you do not change this before the FETCH, the numeric value is returned as an array representing the number in its Oracle internal format, which is probably not what you want. So, change the 2 code to 1 (VARCHAR2), 3 (INTEGER), 4 (FLOAT), or some other appropriate type.
When coercing NUMBER (2) values to VARCHAR2 (1) for display purposes, you also have to extract the precision and scale bytes from the value that the DESCRIBE statement places in the SQLDVLN variable. You then use the precision and scale to compute a maximum length for the conversion into a PL/I CHARACTER string, placing the value back into the SQLDVLN element before the FETCH.
The library procedure SQLPRC extracts precision and scale. You call SQLPRC using the syntax
CALL SQLPRC(LENGTH, PRECISION, SCALE);
where:
/* include a select descriptor SQLDA */ EXEC SQL INCLUDE SELDSC; /* Declare variables for the function call. */ DCL (J, PRECISION, SCALE) FIXED BIN(31), /* Declare some data buffers. */ SEL_DV(3) CHARACTER (10) VARYING, /* Declare library function. */ SQLPRC EXTERNAL ENTRY(ANY, ANY, ANY); ... /* Extract precision and scale. */ CALL SQLPRC(SELDSC.SQLDSC(J).SQLDVLN, PRECISION, SCALE); /* Set the desired precision if 0 is returned . Note that size of the buffer is 10. */ IF PRECISION = 0 THEN PRECISION = 6; /* Allow for possible decimal point and sign. */ SELDSC.SQLDSC(J).SQLDVLN = PRECISION + 2; /* Increase SQLDVLN if scale is negative. */ IF SCALE < 0 THEN SELDSC.SQLDSC(J).SQLDVLN = SELDSC.SQLDSC(J).SQLDVLN + (-SCALE);
Notice that the first parameter in the SQLPRC procedure call points to the SQLDVLN element in the Jth minor structure of the SQLDSC array, and that the precision and scale parameters must be 4 bytes in size.
The SQLPRC procedure returns zero as the precision and scale values for certain SQL datatypes. The SQLPR2 procedure is similar to SQLPRC, having the same syntax, and returning the same binary values, except for these datatypes:
| SQL Datatype | Binary Precision | Scale |
|
FLOAT |
126 |
-127 |
|
FLOAT (N) |
N (range is 1 to 126) |
-127 |
|
REAL |
63 |
-127 |
|
DOUBLE PRECISION |
126 |
-127 |
The table below lists the datatypes and datatype codes, as well as the type of host variable normally used for that external type. See the Programmer's Guide to the Oracle Precompilers for detailed information about the format of the external datatypes.
The datatype codes listed in the table above are the ones that you should set in the SQLDVTYP element of the SQLDA for data conversion.
DESCRIBE returns a NULL/NOT NULL indication in the SQLDVTYP element of the select descriptor, defined as a FIXED BINARY (15). If a column is declared to be NOT NULL, the high-order bit of the variable is clear; otherwise, it is set.
Before using the datatype in an OPEN or FETCH statement, if the NULL/NOT NULL bit is set, you must clear it. (Never set the bit.) You can use the library procedure SQLNUL to find out whether a column allows NULLs, and to clear the datatype's NULL/NOT NULL bit. You call SQLNUL using the syntax
CALL SQLNUL(TYPE_VALUE, TYPE_CODE, NULL_STATUS);
where:
The following example shows how to use SQLNUL:
/* Declare variables for the function call. */ DCL (NULL_OK, TYPE_CODE) FIXED BIN (15), SQLNUL EXTERNAL ENTRY(ANY, ANY, ANY); /* Find out whether column is NOT NULL. */ CALL SQLNUL(SELDSC.SQLDSC(J).SQLDVTYP, TYPE_CODE, NULL_OK); IF NULL_OK ^= 0 THEN PUT LIST ('Nulls OK for this column.');
Note: After SQLNUL returns, the second parameter contains the type code with the NULL bit cleared. This is the value you must use when checking for an Oracle internal datatype code. You should also make sure to reset the SQLDVTYP element in the SQLDA (before the FETCH) with a datatype code that has the high-order bit cleared. For example
SELDSC.SQLDSC(J).SQLDVTYP = TYPE_CODE;
Method 4 can be used to process any dynamic SQL statement. In this example, a query is processed so you can see how both input and output variables are handled. Steps that are common to all embedded SQL programs, such as connecting to Oracle and including the SQLCA, are not described here.
To process a dynamic query using Method 4, our example program takes the following steps:
Note: If the dynamic SQL statement is not a query or contains a known number of select-list items or placeholders, then some of the above steps are unnecessary.
With Method 4, you use the following sequence of embedded SQL statements:
EXEC SQL PREPARE statement_name FROM { :host_string | string_literal }; EXEC SQL DECLARE cursor_name CURSORFOR statement_name; EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name INTO bind_descriptor_name; EXEC SQL OPEN cursor_name [USING DESCRIPTOR descriptor_name]; EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name INTO select_descriptor_name; EXEC SQL FETCH cursor_name USING DESCRIPTOR select_descriptor_name; EXEC SQL CLOSE cursor_name;
Note that if the number of select-list items is known, you can omit DESCRIBE SELECT LIST and use the following Method 3
FETCH statement:
EXEC SQL FETCH emp_cursor INTO host_variable_list;
If the number of bind variables is known, you can omit DESCRIBE BIND VARIABLES and use the following Method 3 OPEN statement:
EXEC SQL OPEN cursor_name [USING host_variable_list];
The following sections show how these statements allow your host program to accept and process a dynamic query using descriptors.
Your program needs a variable to store the text of the dynamic query. The variable (SELECT_STMT in our example) must be declared as a character string.
EXEC SQL BEGIN DECLARE SECTION; .. . DCL SELECT_STMT CHARACTER (120); EXEC SQL END DECLARE SECTION;
Before you include the files that contain the select and bind descriptor declarations, you should set the size of the descriptor in each file. This is set by changing the N variable for the SQLDSC array of substructures.
You normally set this to a value high enough to accommodate the maximum number of select-list items and bind variables that you expect to have to process. The program will not be able to process the SQL statement if there are more select-list items or bind variables than the number of substructures. In our example, a low number of three is used so that the structures can be easily illustrated.
Use INCLUDE to copy the files containing the SQLDA declarations into your program, as follows:
EXEC SQL INCLUDE SELDSC; /* select descriptor */ EXEC SQL INCLUDE BNDDSC; /* bind descriptor */
You must declare data buffers to hold the bind variables and the returned select-list items. In our examples, arbitrary names are used for the buffers used to hold the following:
DCL SEL_DH_VNAME (3) CHARACTER (5), BND_DH_VNAME (3) CHARACTER (5), SEL_DV (3) CHARACTER (10), BND_DV (3) CHARACTER (10) SEL_DI (3) FIXED BIN (15), BND_DI (3) FIXED BIN (15), BND_DI_VNAME (3) CHARACTER (5);
Note that an array of data buffers is declared, and the dimension of the array (3) is the same as the number N of substructures (SQLDSC(N)) in each descriptor area.
You must initialize several elements in each descriptor. Some are simply set to numeric values; some require the library procedure SQLADR to place an address in the element.
In our example, you first initialize the select descriptor. Set SQLDNUM to the number of substructures (3). Then, in each substructure, set the SQLDH_MAX_VNAMEL element to the length (5) of the name data buffer (SEL_DH_VNAME). Set the SQLDVLN element to the length (10) of the value data buffer (SEL_DV). Put the addresses of the data buffers in the SQLDH_VNAME, SQLDV, and SQLDI elements using SQLADR. Finally, set the reserved and unused elements to zero.
SELDSC.SQLDNUM = 3; DO J = 1 TO SELDSC.SQLDNUM; SELDSC.SQLDSC(J).SQLDH_MAX_VNAMEL = 5; SELDSC.SQLDSC(J).SQLDVLN = 10; /* setup the pointers */ CALL SQLADR(ADDR(SEL_DH_VNAME(J)), ADDR(SELDSC.SQLDSC(J).SQLDH_VNAME)); CALL SQLADR(ADDR(SEL_DV(J)), ADDR(SELDSC.SQLDSC(J).SQLDV)); CALL SQLADR(ADDR(SEL_DI(J)), ADDR(SELDSC.SQLDSC(J).SQLDI)); /* initialize unused elements to 0 */ SEL_DI(J) = 0; SELDSC.SQLDSC(J).SQLDFMT = 0; SELDSC.SQLDSC(J).SQLDFCLP = 0; SELDSC.SQLDSC(J).SQLDFCRCP = 0; END;
The bind descriptor is initialized in almost the same way. The difference is that SQLDI_MAX_VNAMEL must also be initialized.
BNDDSC.SQLDNUM = 3; DO J = 1 TO BNDDSC.SQLDNUM; BNDDSC.SQLDSC(J).SQLDH_MAX_VNAMEL = 5; BNDDSC.SQLDSC(J).SQLDVLN = 10; /* length of indicator variable name */ BNDDSC.SQLDSC(J).SQLDI_MAX_VNAMEL = 5; /* setup the pointers */ CALL SQLADR(ADDR(BND_DH_VNAME(J)), ADDR(BNDDSC.SQLDSC(J).SQLDH_VNAME)); /* address of indicator variable name */ CALL SQLADR(ADDR(BND_DI_VNAME(J)), ADDR(BNDDSC.SQLDSC(J).SQLDI_VNAME)); CALL SQLADR(ADDR(BND_DV(J)), ADDR(BNDDSC.SQLDSC(J).SQLDV)); CALL SQLADR(ADDR(BND_DI(J)), ADDR(BNDDSC.SQLDSC(J).SQLDI)); /* set unused elements to 0 */ BND_DI(J) = 0; BNDDSC.SQLDSC(J).SQLDFMT = 0; BNDDSC.SQLDSC(J).SQLDFCLP = 0; BNDDSC.SQLDSC(J).SQLDFCRCP = 0; END;
The descriptors that result after the initialization are shown in Figure 5-3 and Figure 5-4. In these pictures, the left-hand box represents the descriptor structure, and the boxes on the right represent the data buffers (such as SEL_DV) that you declared in your program. The arrows represent pointers, showing which data buffers the SQLDA elements point to.
The data buffers are empty after initialization (except SEL_DI and BND_DI, which were set to zero in the example code above). As our example progresses, and the DESCRIBE or FETCH statements begin to fill in the data buffers, the values will be shown in later figures. Whenever these boxes are empty, it indicates that the variable is either uninitialized or was not filled in by a DESCRIBE or FETCH statement. Unused or reserved fields in the descriptors (SQLDFMT, SQLDFMTL, SQLDFCLP, and SQLDFCRCP) are not shown in these figures.
Note: To save space, the SQLDA element names in the left hand columns of Figures 5-3 through 5-9 are abbreviated. Each structure element name must be preceded by the structure and substructure names. For example, S2.SQLDV must be written as SELDSC.SQLDSC(2).SQLDV in the PL/I code. B3.SQLDVTYP stands for BNDDSC.SQLDSC(3).SQLDVTYP.
Continuing our example, you prompt the user for a SQL statement, and then store the input in SELECT_STMT.
PUT LIST ('Enter SQL statement: '); GET EDIT (SELECT_STMT) (A(120));
In this example, it is assumed that the user typed the string
'SELECT ENAME, EMPNO, COMM FROM EMP WHERE COMM < :BONUS'
PREPARE parses the query and gives it a name. In our example, PREPARE parses the host string SELECT_STMT and gives it the name SQL_STMT, as follows:
EXEC SQL PREPARE SQL_STMT FROM :SELECT_STMT;
DECLARE CURSOR defines a cursor by giving it a name and associating it with a specific query. When declaring a cursor for static queries, you use the following syntax:
EXEC SQL DECLARE CURSOR_NAME CURSOR FOR SELECT ...
When declaring a cursor for dynamic queries, the statement name given to the dynamic query by PREPARE is substituted for the static query. In our example, DECLARE CURSOR defines a cursor named EMP_CURSOR and associates it with SQL_STMT, as follows:
EXEC SQL DECLARE EMP_CURSOR CURSOR FOR SQL_STMT;
Note: You must declare a cursor for all Dynamic SQL statements, not just queries. For non-query statements, opening the cursor executes the statement.
DESCRIBE BIND VARIABLES fills in fields in a bind descriptor that describe the bind variables in the SQL statement. In our example, DESCRIBE fills in a bind descriptor named BNDDSC. The DESCRIBE statement is
EXEC SQL DESCRIBE BIND VARIABLES FOR SQL_STMT INTO BNDDSC;
Note that BNDDSC must not be prefixed with a colon.
The DESCRIBE BIND VARIABLES statement must follow the PREPARE statement but precede the OPEN statement.
Figure 5-5 shows the bind descriptor in our example after the DESCRIBE. Notice that DESCRIBE has set SQLDFND to the actual number of input bind variables found in the query's WHERE clause.
Next, you must test the actual number of bind variables found by DESCRIBE, as follows:
IF BNDDSC.SQLDFND <0 THEN DO; PUT LIST ('Too many input variables were described.'); GOTO NEXT_SQL_STMT; /* try again */ END; /* Set number of bind variables DESCRIBEd. */ BNDDSC.SQLDNUM = BNDDSC.SQLDFND;
Your program must get values for any bind variables found in the SQL statement. In our example, a value must be assigned to the placeholder BONUS in the query's WHERE clause. So, you prompt the user for the value, and then process it as follows:
DCL (BN, BV) POINTER, BNAME CHARACTER (10) BASED(BN), BVAL CHARACTER (10) BASED(BV); ... PUT SKIP LIST ('Enter values of bind variables'); DO J = 1 TO BNDDSC.SQLDNUM; /* Display the name. Use UNSPEC to get an integer (really a pointer) into the buffer pointer. */ UNSPEC(BN) = UNSPEC(BNDDSC.SQLDSC(J).SQLDH_VNAME); PUT SKIP EDIT (BN->BNAME, ': ') (A(BNDDSC.SQLDSC(J).SQLDH_CUR_VNAMEL),A(2)); /* Get bind variable value from user. */ UNSPEC(BV) = UNSPEC(BNDDSC.SQLDSC(J).SQLDV); GET LIST (BV->BVAL); /* Set the length. */ BNDDSC.SQLDSC(J).SQLDVLN = LENGTH(BV->BVAL); /* Make the datatype VARCHAR2. */ BNDDSC.SQLDSC(J).SQLDVTYP = 1; END;
Assuming that the user supplied a value of 625 for BONUS, Figure 5-6 shows the resulting bind descriptor.
The OPEN statement used for dynamic queries is similar to that used for static queries except that the cursor is associated with a bind descriptor. Values determined at runtime and stored in the bind descriptor are used to evaluate the query and identify its active set.
In our example, OPEN associates EMP_CURSOR with BNDDSC, as follows:
EXEC SQL OPEN EMP_CURSOR USING DESCRIPTOR BNDDSC;
Remember, BNDDSC must not be prefixed with a colon.
The OPEN executes the query, identifies its active set, and positions the cursor at the first row.
The DESCRIBE SELECT LIST statement must follow the OPEN statement but precede the FETCH statement.
DESCRIBE SELECT LIST fills in a select descriptor to hold descriptions of items in the query's select list. In our example, DESCRIBE fills in a select descriptor named SELDSC, as follows:
EXEC SQL DESCRIBE SELECT LIST FOR SQL_STMT INTO SELDSC;
SELDSC must not be prefixed with a colon.
Accessing the data dictionary, DESCRIBE sets the length and datatype of each select-list item.
Figure 5-7 shows the select descriptor in our example after the DESCRIBE. Notice that DESCRIBE has set SQLDFND to the actual number of items found in the query's select list.
Also notice that the NUMBER lengths in the second and third SQLDVLN elements are not usable yet. For select-list items defined as NUMBER, you should use the library procedure SQLPRC to extract precision and scale, as explained in the next section.
First you must check the SELDSC.SQLDFND variable that was set by the DESCRIBE statement. If it is negative, too many select-list items were described. If it is not, set SQLDNUM to the number of select-list items described, as follows:
IF SQLDFND <0 THEN DO; PUT LIST ('Too many select-list items. Try again.'); GOTO NEXT_STMT; END; ELSE SELDSC.SQLDNUM = SELDSC.SQLDFND;
In our example, before FETCHing the select-list values, you reset some length elements for display purposes. You also reset the datatype value to avoid dealing with Oracle datatypes.
... DCL J FIXED BIN(15), (SCALE, PRECISION) FIXED BIN(31); DCL SQLPRC EXTERNAL ENTRY(ANY, ANY, ANY); ... /* Process each field value */ DO J = 1 TO SELDSC.SQLDNUM; /* If the datatype is NUMBER (datatype code 2) extra processing is required. */ IF SELDSC.SQLDSC(J).SQLDVTYP = 2 THEN DO; /* get precision and scale */ CALL SQLPRC(SELDSC.SQLDSC(J).SQLDVLN, PRECISION, SCALE); /* Allow for the size of a number with no precision in the table. */ IF PRECISION = 0 THEN PRECISION = 6; SELDSC.SQLDSC(J).SQLDVLN = PRECISION + 2; IF SCALE <0 THEN SELDSC.SQLDSC(J).SQLDVLN = SELDSC.SQLDSC(J).SQLDVLN + (-SCALE); END; /* If datatype is a DATE (datatype code 12) set length to 9. */ IF SELDSC.SQLDSC(J).SQLDVTYP = 12 THEN SELDSC.SQLDSC(J).SQLDVLN = 9; /* Coerce all datatypes to VARCHAR2. */ SELDSC.SQLDSC(J).SQLDVTYP = 1; END;
Figure 5-8 shows the resulting select descriptor. Notice that the lengths for the buffers that will hold the EMPNO and COMM fields are set to 6 and 9. These values were set in the DO-loop above from the EMP table column lengths of 4 and 7 by the statement that adds 2 to PRECISION (for possible minus sign and decimal point). Notice also that the datatypes are set to 1 (VARCHAR2).
Note: When the datatype code returned on a DESCRIBE is 2 (Oracle internal number) it must be coerced to a PL/I type that a NUMBER can be converted to; this does not have to be CHARACTER. You could also coerce a NUMBER to a PL/I FLOAT, in which case you would put the datatype code number 4 in the SQLDVTYP element, and put the length (size of a PL/I float in bytes) in the SQLDVLN element.
FETCH returns a row from the active set, stores select-list values in the select descriptor, and advances the cursor to the next row in the active set. If there are no more rows, FETCH sets SQLCA.SQLCODE, the SQLCODE variable, or the SQLSTATE variable to the "no data found" Oracle error code. In our example, FETCH returns the values of columns ENAME, EMPNO, and COMM to the data buffers pointed to by the SQLDV elements:
... EXEC SQL WHENEVER NOT FOUND GOTO N_FND; NXT_ROW: EXEC SQL FETCH EMP_CURSOR USING DESCRIPTOR SELDSC; CALL PRINT_ROW(SELDSC); /* proc. to print values */ GOTO NXT_ROW; N_FND: ...
Figure 5-9 shows the select descriptor in our example after the FETCH. Notice that Oracle has stored the column and indicator-variable values in the data buffers pointed to by the descriptor.
After the FETCH, your program can scan the select descriptor for the select-list values returned by FETCH and process them. In our example, values for columns ENAME, EMPNO, and COMM can be processed.
Note how Oracle converts these values into the SEL_DV data buffers. The select-list value `MARTIN' is from an Oracle VARCHAR2 column. It is left justified in the 10-byte field of SEL_DV(1).
The EMPNO value is a NUMBER(4) in the Oracle table. The program adds 2 to this (for decimal point and possible sign), resulting in a length of 6. Since EMPNO is a NUMBER, the value '7654' is right justified in a 6-byte field in the output buffer.
The COMM column has a length of 7. The program adds 2 (for decimal point and sign) for a total length of 9. The value '482.50' is right justified on conversion into the 9-byte field of SEL_DV(3).
CLOSE disables the cursor. In our example, CLOSE disables EMP_CURSORas follows:
EXEC SQL CLOSE EMP_CURSOR;
To use input or output host arrays with Method 4, you must use the optional FOR clause of the EXECUTE statement to tell Oracle the size of the host array. (For more information about the FOR clause, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.)
First, you set up a descriptor for the host arrays. Set each SQLDV element to point to the start of the array, the SQLDVLN element to contain the length of each member of the array, and the SQLDVTYP element to contain the type of the members.
Then, you use a FOR clause in the EXECUTE or FETCH statement (whichever is appropriate) to tell Oracle the number of array elements you want to process. This is necessary because Oracle has no other way of knowing the size of your host array. Note that EXECUTE can be used for non-queries with Method 4. In the following program, three input host arrays are used to add data to the emp table:
HSTARRS: PROCEDURE OPTIONS(MAIN); /* Using the FOR clause with Method 4 */ EXEC SQL BEGIN DECLARE SECTION; DCL USR CHARACTER (21) VARYING INIT('SCOTT'), PWD CHARACTER (21) VARYING INIT('TIGER'), ARRAY_SIZE FIXED BIN(31) INIT(5), SQL_STMT CHARACTER (120) VARYING INIT('INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (:E, :N, :D)'); EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; /* SQLCAPLI on IBM systems */ /* Declare a bind descriptor. */ EXEC SQL INCLUDE BNDDSC; DCL NAMES(5) CHARACTER (15), NUMBERS(5) FIXED BIN(31), DEPTS(5) FIXED BIN(31); EXEC SQL WHENEVER SQLERROR GOTO ERROR; EXEC SQL CONNECT :USR IDENTIFIED BY :PWD; PUT SKIP LIST ('Connected to Oracle.'); /* Set up the descriptors. */ BNDDSC.SQLDNUM = 3; EXEC SQL PREPARE S FROM :SQL_STMT; EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO BNDDSC; /* Initialize the descriptors . Use SQ3ADR on IBM systems. */ CALL SQLADR(ADDR(NUMBERS(1)), ADDR(BNDDSC.SQLDSC(1).SQLDV)); BNDDSC.SQLDSC(1).SQLDVLN = 4; /* 4-byte... */ BNDDSC.SQLDSC(1).SQLDVTYP = 3; /* ...integers */ CALL SQLADR(ADDR(NAMES(1)), ADDR(BNDDSC.SQLDSC(2).SQLDV)); BNDDSC.SQLDSC(2).SQLDVLN = 15; /* 15... */ BNDDSC.SQLDSC(2).SQLDVTYP = 1; /* char arrays */ CALL SQLADR(ADDR(DEPTS(1)), ADDR(BNDDSC.SQLDSC(3).SQLDV)); BNDDSC.SQLDSC(3).SQLDVLN = 4; /* 4-byte... */ BNDDSC.SQLDSC(3).SQLDVTYP = 3; /* ...integers */ /* Now initialize the data buffers. */ NAMES(1) = 'TRUSDALE'; NUMBERS(1) = 1010; DEPTS(1) = 30; NAMES(2) = 'WILKES'; NUMBERS(2) = 1020; DEPTS(2) = 30; NAMES(3) = 'BERNSTEIN'; NUMBERS(3) = 1030; DEPTS(3) = 30; NAMES(4) = 'FRAZIER'; NUMBERS(4) = 1040; DEPTS(4) = 30; NAMES(5) = 'MCCOMB'; NUMBERS(5) = 1050; DEPTS(5) = 30; PUT SKIP LIST ('Adding to the Sales force...'); EXEC SQL FOR :ARRAY_SIZE EXECUTE S USING DESCRIPTOR BNDDSC; EXEC SQL COMMIT RELEASE; PUT SKIP EDIT (SQLCA.SQLERRD(3), ' new salespeople added.') (F(4), A); RETURN; ERROR: PUT SKIP EDIT (SQLERRM)(A(70)); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK RELEASE; RETURN; END HSTARRS;
This section presents a complete program that illustrates the steps required to use Dynamic SQL Method 4. These steps were outlined in the "Basic Steps" section earlier in this chapter, and were discussed in greater detail in the sections following.
In this demo program, each step as outlined in "The Basic Steps" section earlier in this chapter is noted in comments in the source code. (Note that because of the block structure of PL/I, the steps do not follow in order.)
This program is available online as SAMPLE10.PPL.
DYN4DEM: PROCEDURE OPTIONS(MAIN); /* On IBM systems you must call SQ3ADR rather than SQLADR. This is set up here. */ EXEC Oracle IFDEF CMS; EXEC Oracle DEFINE SQ3LIB; EXEC Oracle ENDIF; EXEC Oracle IFDEF MVS; EXEC Oracle DEFINE SQ3LIB; EXEC Oracle ENDIF; /* * * STEP 1 -- Declare a host string * (Host variables for the logon process * must also be declared in the SQL Declare Section) */ EXEC SQL BEGIN DECLARE SECTION; /* host string */ DCL STMT CHARACTER (1000) VAR, /* strings for logon */ (USR, PWD) CHARACTER (40)<N>VAR; EXEC SQL END DECLARE SECTION; EXEC Oracle IFDEF SQ3LIB; EXEC SQL INCLUDE SQLCAPLI; EXEC Oracle ELSE; EXEC SQL INCLUDE SQLCA; EXEC Oracle ENDIF; /* * STEP 3 -- INCLUDE the bind and select descriptors * (Remember STEP 2, and set N in SQLDSC * in the INCLUDEd files before the INCLUDE.) */ EXEC SQL INCLUDE BNDDSC; EXEC SQL INCLUDE SELDSC; /* * STEP 4 -- Declare the data buffers for input and output * variables, and the buffers for names. * (NOTE: these are *not* host variables!) */ DCL BND_DI(20) FIXED BINARY (15), BND_DI_VNAME(20) CHARACTER(80), BND_DV(20) CHARACTER(80), BND_DH_VNAME(20) CHARACTER(80), SEL_DI(20) FIXED BINARY (15), SEL_DV(20) CHARACTER(80), SEL_DH_VNAME(20) CHARACTER(80), /* index variable and flags */ (I, TRY, DONE) FIXED BIN(15); /* Local procedures */ /* * STEP 6 -- Get a SQL Statement from the user. */ GET_SQL: PROCEDURE (S); DCL S CHARACTER (*) VAR, L FIXED BINARY (15), LINE CHARACTER (80) VAR, DONE FIXED BINARY (15); S = ''; L = 1; PUT SKIP LIST ('DSQL>> '); /* A statement can occur over multiple lines. Keep getting input until the terminating ';' */ DONE = 0; DO UNTIL (DONE ^= 0); GET EDIT (LINE) (A (80)); DO WHILE (SUBSTR(LINE,LENGTH(LINE),1) = ' '); LINE = SUBSTR(LINE,1,LENGTH(LINE)-1); END; S = S || LINE || ' '; IF SUBSTR(LINE,LENGTH(LINE),1) = ';' THEN DO; /* Set "all done" flag; Throw away terminating '; '. */ DONE = 1; S = SUBSTR(S,1,LENGTH(S)-2); END; ELSE DO; L = L + 1; PUT EDIT ( L, ' ') ( F(5), A(2) ); END; END; END GET_SQL; /* * STEP 11 -- Get values for the bind variables */ GET_BIND_VARS: PROCEDURE (BNDDSC); EXEC SQL INCLUDE BNDDSC; DCL BN POINTER, BNAME CHARACTER(80) BASED (BN), BV POINTER, BVAL CHARACTER(80) BASED (BV), I FIXED BINARY (15); PUT SKIP LIST ( 'Please enter values for Bind Vars.' ); PUT SKIP; DO I = 1 TO BNDDSC.SQLDNUM; /* Display bind variable name. Use UNSPEC to get an integer into a pointer. */ UNSPEC(BN) = UNSPEC(BNDDSC.SQLDSC(I).SQLDH_VNAME); PUT EDIT ( BN ->> BNAME, ': ' ) (A(BNDDSC.SQLDSC(I).SQLDH_CUR_VNAMEL), A(2)); /* Get value for this bind variable. */ UNSPEC(BV) = UNSPEC(BNDDSC.SQLDSC(I).SQLDV); GET LIST ( BV ->> BVAL ); /* Declare the bind variable to be type VARCHAR2. */ BNDDSC.SQLDSC(I).SQLDVTYP = 1; END; END GET_BIND_VARS; /* * This procedure prints column headings for select-list items. */ PRINT_COL_HEADINGS: PROCEDURE (SELDSC); EXEC SQL INCLUDE SELDSC; DCL (I,J) FIXED BINARY (15), LINE CHARACTER (132) BASED (P), BLANKS FIXED BINARY (15), P POINTER; /* * STEP 14 -- Readjust TYPE and LENGTH elements in the SQLDA * Output column names as column headings. */ PUT SKIP(2); DO I = 1 TO SELDSC.SQLDNUM; UNSPEC(P) = UNSPEC(SELDSC.SQLDSC(I).SQLDH_VNAME); IF SELDSC.SQLDSC(I).SQLDVTYP = 1 THEN DO; /* Have Oracle VARCHAR2 type. Left justify. Compute number of blanks required for padding. */ BLANKS = MAX(SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL, SELDSC.SQLDSC(I).SQLDVLN) - SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL; PUT EDIT ( P ->> LINE, ' ' ) (A (SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL), X(BLANKS), A(1)); END; ELSE DO; /* Assume we have Oracle NUMBER type. Right-justify. Also, force column width to be the maximum of column heading and 9. */ SELDSC.SQLDSC(I).SQLDVLN = MAX(SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL, 9); BLANKS = SELDSC.SQLDSC(I).SQLDVLN - SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL; PUT EDIT ( P ->> LINE, ' ' ) ( X (BLANKS), A(SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL), A(1)); /* Coerce select-list names to type VARCHAR2 to simplify printing. */ SELDSC.SQLDSC(I).SQLDVTYP = 1; END; END; /* Underline the column headings. */ PUT SKIP; DO I = 1 TO SELDSC.SQLDNUM; IF I >> 1 THEN PUT EDIT (' ') (A(1)); IF SELDSC.SQLDSC(I).SQLDVTYP = 1 THEN DO J = 1 TO MAX(SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL, SELDSC.SQLDSC(I).SQLDVLN); PUT EDIT ('-') (A (1)); END; ELSE DO J = 1 TO MAX(SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL, 9); PUT EDIT ('-') (A(1)); END; END; PUT SKIP; END PRINT_COL_HEADINGS; /* * Print out some help for the user at * program start-up. */ PRINT_HELP: PROCEDURE; PUT SKIP; PUT SKIP LIST ( 'Terminate all SQL stmts w/'';''(semi-colon).'); PUT SKIP LIST ( 'Type ''/EXIT''; to exit DSQL' ); PUT SKIP; END PRINT_HELP; /* * STEP 16 -- Process the select-list items. */ PRINT_ROW: PROCEDURE (SELDSC); EXEC SQL INCLUDE SELDSC; DCL BLANKS FIXED BINARY (15), DI POINTER, I FIXED BINARY (15), LINE CHARACTER(132) BASED(P), P POINTER, SELDI FIXED BINARY (15) BASED(DI); DO I = 1 TO SELDSC.SQLDNUM; /* Check if the select-list item is NULL. */ UNSPEC(DI) = UNSPEC(SELDSC.SQLDSC(I).SQLDI); IF DI ->> SELDI << 0 THEN /* This item is NULL. Set the length of buf to zero so output spacing (blank pad) comes out correctly. */ SELDSC.SQLDSC(I).SQLDVLN = 0; /* Compute number of required blanks for appropriate spacing. */ BLANKS = MAX( MAX(SELDSC.SQLDSC(I).SQLDH_CUR_VNAMEL, SELDSC.SQLDSC(I).SQLDVLN), 9) - SELDSC.SQLDSC(I).SQLDVLN; /* Print column value, with blank padding. */ UNSPEC(P) = UNSPEC(SELDSC.SQLDSC(I).SQLDV); PUT EDIT ( P ->> LINE, ' ') (A(SELDSC.SQLDSC(I).SQLDVLN), X(BLANKS), A(1)); END; PUT SKIP; END PRINT_ROW; /* * Begin the MAIN program here. * */ EXEC SQL WHENEVER SQLERROR GOTO LOGERR; TRY = 0; DO UNTIL (TRY = 3); TRY = TRY + 1; PUT SKIP LIST ( 'Username: ' ); GET EDIT (USR) (A(8)); PUT SKIP LIST ( 'Password: ' ); GET EDIT (PWD) (A(8)); EXEC SQL CONNECT :USR IDENTIFIED BY :PWD; GOTO CONNECTED_OK; LOGERR: PUT SKIP; PUT SKIP LIST ( SQLCA.SQLERRM ); PUT SKIP; END; /* DO UNTIL */ PUT SKIP LIST ( 'Aborting login after 3 attempts.' ); RETURN; CONNECTED_OK: /* Initialization. */ CALL PRINT_HELP; /* * STEP 5 -- Initialize the select and bind descriptors. */ DO I = 1 TO 20; SELDSC.SQLDSC(I).SQLDH_MAX_VNAMEL = 80; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR (SEL_DH_VNAME(I),SELDSC.SQLDSC(I).SQLDH_VNAME); EXEC Oracle ELSE; CALL SQLADR (ADDR(SEL_DH_VNAME(I)), ADDR(SELDSC.SQLDSC(I).SQLDH_VNAME)); EXEC Oracle ENDIF; SELDSC.SQLDSC(I).SQLDI_MAX_VNAMEL = 80; SELDSC.SQLDSC(I).SQLDVLN = 80; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR(SEL_DV(I),SELDSC.SQLDSC(I).SQLDV); EXEC Oracle ELSE; CALL SQLADR (ADDR(SEL_DV(I)), ADDR(SELDSC.SQLDSC(I).SQLDV)); EXEC Oracle ENDIF; SEL_DI(I) = 0; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR(SEL_DI(I),SELDSC.SQLDSC(I).SQLDI); EXEC Oracle ELSE; CALL SQLADR (ADDR(SEL_DI(I)), ADDR(SELDSC.SQLDSC(I).SQLDI)); EXEC Oracle ENDIF; SELDSC.SQLDSC(I).SQLDFMT = 0; SELDSC.SQLDSC(I).SQLDFCLP = 0; SELDSC.SQLDSC(I).SQLDFCRCP = 0; END; DO I = 1 TO 20; BNDDSC.SQLDSC(I).SQLDH_MAX_VNAMEL = 80; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR (BND_DH_VNAME(I),BNDDSC.SQLDSC(I).SQLDH_VNAME); EXEC Oracle ELSE; CALL SQLADR (ADDR(BND_DH_VNAME(I)), ADDR(BNDDSC.SQLDSC(I).SQLDH_VNAME)); EXEC Oracle ENDIF; BNDDSC.SQLDSC(I).SQLDI_MAX_VNAMEL = 80; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR (BND_DI_VNAME(I),BNDDSC.SQLDSC(I).SQLDI_VNAME); EXEC Oracle ELSE; CALL SQLADR (ADDR(BND_DI_VNAME(I)), ADDR(BNDDSC.SQLDSC(I).SQLDI_VNAME)); EXEC Oracle ENDIF; BNDDSC.SQLDSC(I).SQLDVLN = 80; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR(BND_DV(I),BNDDSC.SQLDSC(I).SQLDV); EXEC Oracle ELSE; CALL SQLADR (ADDR(BND_DV(I)), ADDR(BNDDSC.SQLDSC(I).SQLDV)); EXEC Oracle ENDIF; BND_DI(I) = 0; EXEC Oracle IFDEF SQ3LIB; CALL SQ3ADR(BND_DI(I),BNDDSC.SQLDSC(I).SQLDI); EXEC Oracle ELSE; CALL SQLADR (ADDR(BND_DI(I)), ADDR(BNDDSC.SQLDSC(I).SQLDI)); EXEC Oracle ENDIF; BNDDSC.SQLDSC(I).SQLDFMT = 0; BNDDSC.SQLDSC(I).SQLDFCLP = 0; BNDDSC.SQLDSC(I).SQLDFCRCP = 0; END; /* Main Executive Loop: Get and execute SQL statement. */ DONE = 0; DO UNTIL ( DONE ^= 0 ); EXEC SQL WHENEVER SQLERROR GOTO SQLERR; /* * Call routine to do STEP 6. */ CALL GET_SQL(STMT); IF STMT = '/EXIT' | STMT = '/exit' THEN DONE = 1; ELSE DO; /* * STEPS 7 & 8 - Prepare the SQL statement and * declare a cursor for it. */ EXEC SQL PREPARE S FROM :STMT; EXEC SQL DECLARE C CURSOR FOR S; /* * STEP 9 -- Describe the bind variables * in this SQL statement. */ BNDDSC.SQLDNUM = 20; EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO BNDDSC; IF BNDDSC.SQLDFND << 0 THEN DO; PUT SKIP LIST ('Too many Bind Vars in this SQL stmt.'); PUT LIST (' Try again...'); GOTO NXT_STM; END; /* * STEP 10 -- Reset N of bind variables. */ BNDDSC.SQLDNUM = BNDDSC.SQLDFND; IF BNDDSC.SQLDNUM ^= 0 THEN CALL GET_BIND_VARS (BNDDSC); /* do STEP 11 */ /* * STEP 12 -- Open the cursor using the bind descriptor. */ EXEC SQL OPEN C USING DESCRIPTOR BNDDSC; SELDSC.SQLDNUM = 20; /* * STEP 13 -- Describe the select list. */ EXEC SQL DESCRIBE SELECT LIST FOR S INTO SELDSC; IF SELDSC.SQLDFND << 0 THEN DO; PUT SKIP LIST ('Too many Select Vars in this SQL stmt.'); PUT LIST (' Try again...'); GOTO NXT_STM; END; SELDSC.SQLDNUM = SELDSC.SQLDFND; /* If this is a SELECT statement, then display rows. Else, all done... */ IF SELDSC.SQLDNUM ^= 0 THEN DO; CALL PRINT_COL_HEADINGS (SELDSC); /* Fetch each row, and print it. */ EXEC SQL WHENEVER NOT FOUND GOTO N_FND; NXT_ROW: /* * STEP 15 -- Fetch the data into the buffers * (buffers are pointed to by SELDSC). */ EXEC SQL FETCH C USING DESCRIPTOR SELDSC; CALL PRINT_ROW (SELDSC); /* do STEP 16 */ GOTO NXT_ROW; N_FND: END; IF SQLCA.SQLERRD(3) ^= 0 THEN DO; PUT EDIT ( SQLCA.SQLERRD(3), ' Row(s) processed.' ) ( SKIP(1), F(4), A ); PUT SKIP; END; /* * STEP 17 -- Close the cursor. */ EXEC SQL CLOSE C; END; GOTO NXT_STM; SQLERR: PUT SKIP LIST ( SQLCA.SQLERRM ); PUT SKIP; NXT_STM: END; /* DO UNTIL */ EXEC SQL COMMIT WORK RELEASE; RETURN; /* exit program */ END DYN4DEM;
|
 Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
|