To declare select and bind SQLDAs, you can code them into your program using the sample select and bind SQLDAs shown in Figure 11-1. You can modify the table dimensions to suit your needs.

Figure 11-1 Sample Pro*COBOL SQLDA Descriptors and Data Buffers (64 bit)

Description of "Figure 11-1 Sample Pro*COBOL SQLDA Descriptors and Data Buffers (64 bit)"

You can store the SQLDAs in files (named SELDSC and BNDDSC, for example), and then copy the files into your program with the INCLUDE statement as follows:

     EXEC SQL INCLUDE SELDSC END-EXEC.
     EXEC SQL INCLUDE BNDDSC END-EXEC.

Figure 11-2 shows whether variables are set by SQLADR calls, DESCRIBE commands, FETCH commands, or program assignments.

Figure 11-2 How Variables Are Set

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

You must call the library subroutine SQLADR to get the addresses of data buffers that store input and output values. You store the addresses in a bind or select SQLDA so that Oracle knows where to read bind-variable values or write select-list values.

Call SQLADR using the syntax

     CALL "SQLADR" USING BUFFER, ADDRESS.

where:

BUFFER

Is a data buffer that stores the value or name of a select-list item, bind variable, or indicator variable.

ADDRESS

Is an integer variable that returns the address of the data buffer.

A call to SQLADR stores the address of BUFFER in ADDRESS. The next example uses SQLADR to initialize the select descriptor tables SELDV, SELDH-VNAME, and SELDI. Their elements address data buffers for select-list values, select-list names, and indicator values.

 PROCEDURE DIVISION.
     ... 
     PERFORM INIT-SELDSC 
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
     ... 
 INIT-SELDSC.
     CALL "SQLADR" USING SEL-DV(J), SELDV(J).
     CALL "SQLADR" USING SEL-DH-VNAME(J), SELDH-VNAME(J).
     CALL "SQLADR" USING SEL-DI(J), SELDI(J).

This section provides more detail about the datatype descriptor table. In host programs that use neither datatype equivalencing nor dynamic SQL Method 4, the conversion between internal and external datatypes is determined at precompile time. By default, Pro*COBOL assigns a specific external datatype to each host variable. For example, Pro*COBOL assigns the INTEGER external datatype to host variables of type PIC S9(n) COMP.

However, Method 4 lets you control data conversion and formatting. You specify conversions by setting datatype codes in the datatype descriptor table.

Internal Datatypes

Internal datatypes specify the formats used by Oracle to store column values in database tables to represent pseudocolumn values.

When you issue a DESCRIBE SELECT LIST command, Oracle returns the internal datatype code for each select-list item to the SELDVTYP (datatype) descriptor table. For example, the datatype code for the Jth select-list item is returned to SELDVTYP(J).

Table 11-1 shows the internal datatypes and their codes:

External Datatypes

External datatypes specify the formats used to store values in input and output host variables.

The DESCRIBE BIND VARIABLES command sets the BNDDVTYP table of datatype codes to zeros. Therefore, you must reset the codes before issuing the OPEN command. The codes tell Oracle which external datatypes to expect for the various bind variables. For the Jth bind variable, reset BNDDVTYP(J) to the external datatype you want.

The following table shows the external datatypes and their codes, as well as the corresponding COBOL datatypes:

Notes:

1. For use in an EXEC SQL VAR statement only.

2. Include the n-byte length field.

For more information about the datatypes and their formats, see “The Oracle Database Datatypes”.

PL/SQL Datatypes

PL/SQL provides a variety of predefined scalar and composite datatypes. A scalar type has no internal components. A composite type has internal components that can be manipulated individually. “Initialize the Descriptors” shows the predefined PL/SQL scalar datatypes and their internal datatype equivalence

For a select descriptor, DESCRIBE SELECT LIST can return any of the internal datatypes. Often, as in the case of character data, the internal datatype corresponds exactly to the external datatype you want to use. However, a few internal datatypes map to external datatypes that can be difficult to handle. Thus, you might want to reset some elements in the SELDVTYP descriptor table.

For example, you might want to reset NUMBER values to FLOAT values, which correspond to PIC S9(n)V9(n) COMP-1 values in COBOL. Oracle does any necessary conversion between internal and external datatypes at FETCH time. Be sure to reset the datatypes after the DESCRIBE SELECT LIST but before the FETCH.

For a bind descriptor, DESCRIBE BIND VARIABLES does not return the datatypes of bind variables, only their number and names. Therefore, you must explicitly set the BNDDVTYP table of datatype codes to tell Oracle the external datatype of each bind variable. Oracle does any necessary conversion between external and internal datatypes at OPEN time.

When you reset datatype codes in the SELDVTYP or BNDDVTYP descriptor table, you are "coercing datatypes." For example, to coerce the Jth select-list value to VARCHAR2, use the following statement:

*    Coerce select-list value to VARCHAR2. 
     MOVE 1 TO SELDVTYP(J). 

When coercing a NUMBER select-list value to VARCHAR2 for display purposes, you must also extract the precision and scale bytes of the value and use them to compute a maximum display length. Then, before the FETCH, you must reset the appropriate element of the SELDVLN (length) descriptor table to tell Oracle the buffer length to use. To specify the length of the Jth select-list value, set SELDVLN(J) to the length you need.

For example, if DESCRIBE SELECT LIST finds that the Jth select-list item is of type NUMBER, and you want to store the returned value in a COBOL variable declared as PIC S9(n)V9(n) COMP-1, simply set SELDVTYP(J) to 4 and SELDVLN(J) to the length of COMP-1 numbers on your system.

Exceptions

In some cases, the internal datatypes that DESCRIBE SELECT LIST returns might not suit your purposes. Two examples of this are DATE and NUMBER. When you DESCRIBE a DATE select-list item, Oracle returns the datatype code 12 to the SELDVTYP table. Unless you reset the code before the FETCH, the date value is returned in its 7-byte internal format. To get the date in its default character format, you must change the datatype code from 12 to 1 (VARCHAR2) and increase the SELDVLN value from 7 to 9.

Similarly, when you DESCRIBE a NUMBER select-list item, Oracle returns the datatype code 2 to the SELDVTYP table. Unless you reset the code before the FETCH, the numeric value is returned in its internal format, which is probably not desired. Therefore, change the code from 2 to 1 (VARCHAR2), 3 (INTEGER), 4 (FLOAT), or some other appropriate datatype.

Extracting Precision and Scale

The library subroutine SQLPRC extracts precision and scale. Normally, it is used after the DESCRIBE SELECT LIST, and its first parameter is SELDVLN(J). To call SQLPRC, use the following syntax

     CALL "SQLPRC" USING LENGTH, PRECISION, SCALE.

where:

The following example shows how SQLPRC is used to compute maximum display lengths for NUMBER values that will be coerced to VARCHAR2:

 WORKING-STORAGE SECTION. 
 01  PRECISION       PIC S9(9) COMP. 
 01  SCALE           PIC S9(9) COMP. 
 01  DISPLAY-LENGTH  PIC S9(9) COMP. 
 01  MAX-LENGTH      PIC S9(9) COMP VALUE 80. 
     ... 
 PROCEDURE DIVISION. 
     ... 
     PERFORM ADJUST-LENGTH 
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC. 
 ADJUST-LENGTH. 
*    If datatype is NUMBER, extract precision and scale. 
     IF SELDVTYP(J) = 2 
         CALL "SQLPRC" USING SELDVLN(J), PRECISION, SCALE. 
     MOVE 0 TO DISPLAY-LENGTH. 
*    Precision is set to zero if the select-list item 
*    refers to a NUMBER of unspecified size.  We allow for 
*    a maximum precision of 10. 
     IF SELDVTYP(J) = 2 AND PRECISION = 0 
         MOVE 10 TO DISPLAY-LENGTH. 
*    Allow for possible decimal point and sign. 
     IF SELDVTYP(J) = 2 AND PRECISION > 0 
         ADD 2 TO PRECISION 
         MOVE PRECISION TO DISPLAY-LENGTH. 
     ... 

Notice that the first parameter in the subroutine call is the Jth element in the table of select-list lengths.

The SQLPRC procedure, defined in the SQLLIB runtime library, returns zero as the precision and scale values for certain SQL datatypes. The SQLPR2 procedure is similar to SQLPRC in that it has the same syntax and returns the same binary values, except for the datatypes shown in this table:

For every select-list column (not expression), DESCRIBE SELECT LIST returns a NULL/not NULL indication in the datatype table of the select descriptor. If the Jth select-list column is constrained to be not NULL, the high-order bit of SELDVTYP(J) datatype variable is clear; otherwise, it is set.

Before using the datatype in an OPEN or FETCH statement, if the NULL status bit is set, you must clear it. Never set the bit.

You can use the library routine SQLNUL to find out if a column allows NULL datatypes and to clear the datatype's NULL status bit. You call SQLNUL using the syntax

     CALL "SQLNUL" USING VALUE-TYPE, TYPE-CODE, NULL-STATUS.

where:

The following example shows how to use SQLNUL:

 WORKING-STORAGE SECTION.
     ... 
*    Declare variable for subroutine call.
     01  NULL-STATUS  PIC S9(9) COMP.
     ... 
 PROCEDURE DIVISION.
 MAIN.
     EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC.
     ...
     PERFORM HANDLE-NULLS
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
     ...
 HANDLE-NULLS.
*    Find out if column is NOT NULL, and clear high-order bit.
     CALL "SQLNUL" USING SELDVTYP(J), SELDVTYP(J), NULL-STATUS.
*    If NULL-STATUS = 1, NULLs are allowed.

Notice that the first and second parameters in the subroutine call are the same. Respectively, they are the datatype variable before and after its NULL status bit is cleared.

Your program needs a host variable to store the text of the dynamic SQL statement. The host variable (SELECTSTMT in our example) must be declared as a character string:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
     ... 
 01  SELECTSTMT  PIC X(120). 
     EXEC SQL END DECLARE SECTION END-EXEC. 

Because the query in our example might contain an unknown number of select-list items or place-holders, you must declare select and bind descriptors. Instead of hard-coding the SQLDAs, you use INCLUDE to copy them into your program, as follows:

     EXEC SQL INCLUDE SELDSC END-EXEC. 
     EXEC SQL INCLUDE BNDDSC END-EXEC. 

For reference, the INCLUDEd declaration of SELDSC follows:

 WORKING-STORAGE SECTION. 
     ... 
 01  SELDSC. 
         05  SQLDNUM              PIC S9(9) COMP. 
         05  SQLDFND              PIC S9(9) COMP. 
         05  SELDVAR              OCCURS 3 TIMES. 
             10 SELDV             PIC S9(9) COMP. 
             10 SELDFMT           PIC S9(9) COMP. 
             10 SELDVLN           PIC S9(9) COMP. 
             10 SELDFMTL          PIC S9(4) COMP. 
             10 SELDVTYP          PIC S9(4) COMP. 
             10 SELDI             PIC S9(9) COMP. 
             10 SELDH-VNAME       PIC S9(9) COMP. 
             10 SELDH-MAX-VNAMEL  PIC S9(4) COMP. 
             10 SELDH-CUR-VNAMEL  PIC S9(4) COMP. 
             10 SELDI-VNAME       PIC S9(9) COMP. 
             10 SELDI-MAX-VNAMEL  PIC S9(4) COMP. 
             10 SELDI-CUR-VNAMEL  PIC S9(4) COMP. 
             10 SELDFCLP          PIC S9(9) COMP. 
             10 SELDFCRCP         PIC S9(9) COMP. 

 01  XSELDI. 
     05  SEL-DI        OCCURS 3 TIMES PIC S9(9) COMP. 
 01  XSELDIVNAME. 
         05  SEL-DI-VNAME  OCCURS 3 TIMES PIC X(5). 
 01  XSELDV. 
         05  SEL-DV        OCCURS 3 TIMES PIC X(10). 
 01  XSELDHVNAME. 
         05  SEL-DH-VNAME  OCCURS 3 TIMES PIC X(5).  

You next set the maximum number of select-list items or place-holders that can be described, as follows:

     MOVE 3 TO SQLDNUM IN SELDSC. 
     MOVE 3 TO SQLDNUM IN BNDDSC. 

You must initialize several descriptor variables. Some require the library subroutine SQLADR.

In our example, you store the maximum lengths of name buffers in the SELDH-MAX-VNAMEL, BNDDH-MAX-VNAMEL, and BNDDI-MAX-VNAMEL tables, and use SQLADR to store the addresses of value and name buffers in the SELDV, SELDI, BNDDV, BNDDI, SELDH-VNAME, BNDDH-VNAME, and BNDDI-VNAME tables.

 PROCEDURE DIVISION. 
     ... 
     PERFORM INIT-SELDSC 
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC. 
     PERFORM INIT-BNDDSC 
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN BNDDSC. 
     ... 
 INIT-SELDSC. 
     MOVE SPACES TO SEL-DV(J). 
     MOVE SPACES TO SEL-DH-VNAME(J). 
     MOVE 5 TO SELDH-MAX-VNAMEL(J). 
     CALL "SQLADR" USING SEL-DV(J), SELDV(J). 
     CALL "SQLADR" USING SEL-DH-VNAME(J), SELDH-VNAME(J). 
     CALL "SQLADR" USING SEL-DI(J), SELDI(J). 
     ... 
 INIT-BNDDSC. 
     MOVE SPACES TO BND-DV(J). 
     MOVE SPACES TO BND-DH-VNAME(J). 
     MOVE SPACES TO BND-DI-VNAME(J). 
     MOVE 5 TO BNDDH-MAX-VNAMEL(J). 
     MOVE 5 TO BNDDI-MAX-VNAMEL(J). 
     CALL "SQLADR" USING BND-DV(J), BNDDV(J). 
     CALL "SQLADR" USING BND-DH-VNAME(J), BNDDH-VNAME(J). 
     CALL "SQLADR" USING BND-DI(J), BNDDI(J). 
     CALL "SQLADR" USING BND-DI-VNAME(J), BNDDI-VNAME(J).
     ...

Figure 11-3 and Figure 11-4 represent the resulting descriptors.

Figure 11-3 Initialized Select Descriptor

Description of "Figure 11-3 Initialized Select Descriptor"

Figure 11-4 Initialized Bind Descriptor

Description of "Figure 11-4 Initialized Bind Descriptor"

Next, you prompt the user for a SQL statement, then store the input string in SELECTSTMT as follows:

     DISPLAY "Enter a SELECT statement: " WITH NO ADVANCING. 
     ACCEPT SELECTSTMT. 

We assume the user entered the following string:

     SELECT ENAME, EMPNO, COMM FROM EMP WHERE COMM < :BONUS

PREPARE parses the SQL statement and gives it a name. In our example, PREPARE parses the host string SELECTSTMT and gives it the name SQLSTMT, as follows:

     EXEC SQL PREPARE SQLSTMT FROM :SELECTSTMT END-EXEC. 

DECLARE CURSOR defines a cursor by giving it a name and associating it with a specific SELECT statement.

To declare a cursor for static queries, use the following syntax:

     EXEC SQL DECLARE cursor_name CURSOR FOR SELECT ... 

To declare a cursor for dynamic queries, the statement name given to the dynamic query by PREPARE replaces the static query. In our example, DECLARE CURSOR defines a cursor named EMPCURSOR and associates it with SQLSTMT, as follows:

     EXEC SQL DECLARE EMPCURSOR CURSOR FOR SQLSTMT END-EXEC.

DESCRIBE BIND VARIABLES puts descriptions of bind variables into a bind descriptor. In our example, DESCRIBE readies BNDDSC as follows:

     EXEC SQL 
         DESCRIBE BIND VARIABLES FOR SQLSTMT 
         INTO BNDDSC 
     END-EXEC. 

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 11-5 shows the bind descriptor in our example after the DESCRIBE. Notice that DESCRIBE has set SQLDFND to the actual number of place-holders found in the processed SQL statement.

Figure 11-5 Bind Descriptor after the DESCRIBE

Description of "Figure 11-5 Bind Descriptor after the DESCRIBE"

Next, you must reset the maximum number of place-holders to the number actually found by DESCRIBE, as follows:

     IF SQLDFND IN BNDDSC < 0 
         DISPLAY "Too many bind variables" 
         GOTO ROLL-BACK 
     ELSE 
         MOVE SQLDFND IN BNDDSC TO SQLDNUM IN BNDDSC
     END-IF. 

Your program must get values for the bind variables in the SQL statement. How the program gets the values is up to you. For example, they can be hard-coded, read from a file, or entered interactively.

In our example, a value must be assigned to the bind variable that replaces the place-holder BONUS in the query WHERE clause. Prompt the user for the value, then process it, as follows:

 PROCEDURE DIVISION. 
     ... 
     PERFORM GET-INPUT-VAR 
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN BNDDSC. 
     ... 
 GET-INPUT-VAR. 
     ... 
*    Replace the 0 DESCRIBEd into the datatype table 
*    with a 1 to avoid an "invalid datatype" Oracle error. 
     MOVE 1 TO BNDDVTYP(J). 
*    Get value of bind variable. 
     DISPLAY "Enter value of ", BND-DH-VNAME(J). 
     ACCEPT INPUT-STRING. 
     UNSTRING INPUT-STRING DELIMITED BY "  " 
         INTO BND-DV(J) COUNT IN BNDDVLN(J). 

Assuming that the user supplied a value of 625 for BONUS, the next table shows the resulting bind descriptor.

Figure 11-6 Bind Descriptor after Assigning Values

Description of "Figure 11-6 Bind Descriptor after Assigning Values"

The OPEN statement for dynamic queries is similar to the one for static queries, except the cursor is associated with a bind descriptor. Values determined at run time and stored in buffers addressed by elements of the bind descriptor tables are used to evaluate the SQL statement. With queries, the values are also used to identify the active set.

In our example, OPEN associates EMPCURSOR with BNDDSC as follows:

     EXEC SQL 
         OPEN EMPCUR USING DESCRIPTOR BNDDSC 
     END-EXEC. 

Remember, BNDDSC must not be prefixed with a colon.

Then, OPEN executes the SQL statement. With queries, OPEN also identifies the active set and positions the cursor at the first row.

If the dynamic SQL statement is a query, the DESCRIBE SELECT LIST statement must follow the OPEN statement but precede the FETCH statement.

DESCRIBE SELECT LIST puts descriptions of select-list items into a select descriptor. In our example, DESCRIBE readies SELDSC as follows:

     EXEC SQL 
         DESCRIBE SELECT LIST FOR SQLSTMT INTO SELDSC 
     END-EXEC. 

Accessing the data dictionary, DESCRIBE sets the length and datatype of each select-list value.

Figure 11-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 select list. If the SQL statement is not a query, SQLDFND is set to zero. Also notice that the NUMBER lengths are not usable yet. For columns defined as NUMBER, you must use the library subroutine SQLPRC to extract precision and scale. See the section “Coercing Datatypes”.

Figure 11-7 Select Descriptor after the DESCR

Description of "Figure 11-7 Select Descriptor after the DESCR"

Next, you must reset the maximum number of select-list items to the number actually found by DESCRIBE, as follows:

     MOVE SQLDFND IN SELDSC TO SQLDNUM IN SELDSC. 

Before fetching the select-list values, the example resets some elements in the length and datatype tables for display purposes.

 PROCEDURE DIVISION. 
     ... 
     PERFORM COERCE-COLUMN-TYPE 
         VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC. 
     ... 
 COERCE-COLUMN-TYPE. 
*    Clear NULL bit. 
     CALL "SQLNUL" USING SELDVTYP(J), SELDVTYP(J), NULL-STATUS. 

*    If datatype is DATE, lengthen to 9 characters. 
     IF SELDVTYP(J) = 12 
         MOVE 9 TO SELDVLN(J). 

*    If datatype is NUMBER, extract precision and scale. 
     MOVE 0 TO DISPLAY-LENGTH. 
     IF SELDVTYP(J) = 2 AND PRECISION = 0 
         MOVE 10 TO DISPLAY-LENGTH. 
     IF SELDVTYP(J) = 2 AND PRECISION > 0 
         ADD 2 TO PRECISION 
         MOVE PRECISION TO DISPLAY-LENGTH. 
     IF SELDVTYP(J) = 2 
         IF DISPLAY-LENGTH > MAX-LENGTH 
             DISPLAY "Column value too large for data buffer." 
             GO TO END-PROGRAM 
         ELSE 
             MOVE DISPLAY-LENGTH TO SELDVLN(J). 

*    Coerce datatypes to VARCHAR2. 
     MOVE 1 TO SELDVTYP(J). 

Figure 11-8 shows the resulting select descriptor. Notice that the NUMBER lengths are now usable and that all the datatypes are VARCHAR2. The lengths in SELDVLN(2) and SELDVLN(3) are 6 and 9 because we increased the DESCRIBEd lengths of 4 and 7 by 2 to allow for a possible sign and decimal point.

Figure 11-8 Select Descriptor before the FETCH

Description of "Figure 11-8 Select Descriptor before the FETCH"

FETCH returns a row from the active set, stores select-list values in the data buffers, and advances the cursor to the next row in the active set. If there are no more rows, FETCH sets SQLCODE in the SQLCA, the SQLCODE variable, or the SQLSTATE variable to the "no data found" error code. In the following example, FETCH returns the values of columns ENAME, EMPNO, and COMM to SELDSC:

     EXEC SQL 
         FETCH EMPCURSOR USING DESCRIPTOR SELDSC 
     END-EXEC. 

“Select Descriptor after the FETCH” shows the select descriptor in our example after the FETCH. Notice that Oracle has stored the select-list and indicator values in the data buffers addressed by the elements of SELDV and SELDI.

For output buffers of datatype 1, Oracle, using the lengths stored in SELDVLN, left-justifies CHAR or VARCHAR2 data, and right-justifies NUMBER data.

The value MARTIN was retrieved from a VARCHAR2(10) column in the EMP table. Using the length in SELDVLN(1), Oracle left-justifies the value in a 10-byte field, filling the buffer.

The value 7654 was retrieved from a NUMBER(4) column and coerced to 7654. However, the length in SELDVLN(2) was increased by two to allow for a possible sign and decimal point, so Oracle right-justifies the value in a 6-byte field.

The value 482.50 was retrieved from a NUMBER(7,2) column and coerced to 482.50. Again, the length in SELDVLN(3) was increased by two, so Oracle right-justifies the value in a 9-byte field.

After the FETCH, your program can process the select-list values returned by FETCH. In our example, values for columns ENAME, EMPNO, and COMM are processed.

CLOSE disables the cursor. In our example, CLOSE disables EMPCURSOR as follows:

     EXEC SQL CLOSE EMPCURSOR END-EXEC

Figure 11-9 Select Descriptor after the FETCH

Description of "Figure 11-9 Select Descriptor after the FETCH"