5 Implementing Dynamic SQL Method 4

What Makes Method 4 Special?

Unlike Methods 1, 2, and 3, dynamic SQL Method 4 lets your program

• accept or build dynamic SQL statements that contain an unknown number of select-list items or placeholders, and

• take explicit control over datatype conversion

To add this flexibility to your program, you must provide additional information to the Oracle runtime library.

What Information Does Oracle Need?

The Pro*PL/1 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

• the number of bind variables and select-list items

• the length of each bind variable and item

• the datatype of each bind variable and item

• the address of the each bind variable and program variable that will hold a received select-list item

Where Is the Information Stored?

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.

How is the Information Obtained?

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.

Introducing the PL/1 SQLDA

The SQLDA is a PL/1 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/1 POINTER type, is used to achieve compatibility among different implementations of PL/1. 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.

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.

Declaring a SQLDA

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.

Figure 5-1 shows a SQLDA named SELDSC. It is a select descriptor. A bind descriptor is identical.

Figure 5-1 The SQL Descriptor Area

Description of "Figure 5-1 The SQL Descriptor Area"

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.

shows whether variables are set by calls to SQLADR, DESCRIBE commands, FETCH commands, or program assignments.

Figure 5-2 How Variables Are Set

Description of "Figure 5-2 How Variables Are Set"

Multiple SQLDAs

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.)

The SQLDA Variables

This section explains the purpose and use of each element in the SQLDA structure.

EXEC SQL FETCH ... USING DESCRIPTOR SELDSC;

EXEC SQL OPEN ... USING DESCRIPTOR BNDDSC;

Datatypes in the SQLDA

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/1 variable, the type of the PL/1 variable determines the conversion. For example, if you SELECT data from an Oracle column having the type NUMBER into a PL/1 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:

• set the datatype code for each bind variable in the bind descriptor

• change some of the datatype codes that Oracle returns when you DESCRIBE a select list into a select descriptor

CALL SQLPRC(LENGTH, PRECISION, SCALE);

/* 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);

Handling NULL/NOT NULL Datatypes

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.'); 

SELDSC.SQLDSC(J).SQLDVTYP = TYPE_CODE;

Declare a Host String

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;

Set the Size of the Descriptors

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.

Declare the SQLDAs

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 */ 

Declare the Data Buffers

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:

• names of select-list items (SEL_DH_VNAME) or bind variables (BND_DH_VNAME)

• data retrieved from the query (SEL_DV)

• values of bind variables (BND_DV)

• values of indicator variables (SEL_DI and BND_DI)

• names of indicator variables used with bind variables (BND_DI_VNAME)

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.

Initialize the Descriptors

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 preceeding example code). 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.

Figure 5-3 Initialized Select Descriptor

Description of "Figure 5-3 Initialized Select Descriptor"

Figure 5-4 Initialized Bind Descriptor

Description of "Figure 5-4 Initialized Bind Descriptor"

Get the Query Text into the Host String

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 the Query from the Host String

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 a Cursor

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;

DESCRIBE the Bind Variables

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.

Figure 5-5 Bind Descriptor After the DESCRIBE

Description of "Figure 5-5 Bind Descriptor After the DESCRIBE"

VariablesReset Maximum Number of Bind

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;

Get Values for Bind Variables

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.

Figure 5-6 Bind Descriptor After Assigning Values

Description of "Figure 5-6 Bind Descriptor After Assigning Values"

OPEN the Cursor

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.

DESCRIBE the SelectList

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.

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.

Figure 5-7 Select Descriptor After the DESCRIBE

Description of "Figure 5-7 Select Descriptor After the DESCRIBE"

Adjust the Select Descriptor Values

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;

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 preceeding DO-loop 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).

Figure 5-8 Select Descriptor Before the FETCH

Description of "Figure 5-8 Select Descriptor Before the FETCH"

FETCH A Row from the Active Set

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:
...

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.

Figure 5-9 Select Descriptor After the FETCH

Description of "Figure 5-9 Select Descriptor After the FETCH"

Process the Select-List Items

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 the Cursor

CLOSE disables the cursor. In our example, CLOSE disables EMP_CURSORas follows:

EXEC SQL CLOSE EMP_CURSOR;