Fixed-List SELECT

Fixed-List SELECT

Until now, you have been unable to retrieve rows from DB2 tables using dynamic SQL. The next two classes of dynamic SQL provide this capability. The first and simplest is fixed-list SELECT .

You can use a fixed-list SELECT statement to explicitly prepare and execute SQL SELECT statements when the columns to be retrieved by the application program are known and unchanging. You need to do so to create the proper working-storage declaration for host variables in your program. If you do not know in advance the columns that will be accessed, you must use a varying-list SELECT statement.

Listing 12.4 shows a fixed-list SELECT statement. This example formulates a SELECT statement in the application program and moves it to a host variable. Next, a cursor is declared and the SELECT statement is prepared. The cursor then is opened and a loop to FETCH rows is invoked. When the program is finished, the cursor is closed.

Listing 12.4. Fixed-List SELECT Dynamic SQL

 SQL to execute:     SELECT  PROJNO, PROJNAME, RESPEMP     FROM    DSN8810.PROJ     WHERE   PROJNO   = ?     AND     PRSTDATE = ?  Move the "SQL to execute" to STRING-VARIABLE  EXEC SQL DECLARE CSR2 CURSOR FOR FLSQL;     EXEC SQL PREPARE FLSQL FROM :STRING-VARIABLE;     EXEC SQL OPEN CSR2 USING :TVAL1, :TVAL2;  Loop until no more rows to FETCH  EXEC SQL         FETCH CSR2 INTO :PROJNO, :PROJNAME, :RESPEMP;     EXEC SQL CLOSE CSR2; 

This example is simple because the SQL statement does not change. The benefit of dynamic SQL is its capability to modify the SQL statement. For example, you could move the SQL statement

 SELECT  PROJNO, PROJNAME, RESPEMP FROM    DSN8810.PROJ WHERE   RESPEMP  = ? AND     PRENDATE = ? 

to the STRING-VARIABLE as shown in Listing 12.4 without modifying the OPEN or FETCH logic. Note that the second column of the predicate is different from the SQL statement as presented in Listing 12.4 ( PRENDATE instead of PRSTDATE ). Because both are the same data type ( DATE ), however, you can use TVAL2 for both if necessary. The host variables passed as parameters in the OPEN statement must have the same data type and length as the columns in the WHERE clause. If the data type and length of the columns in the WHERE clause change, the OPEN statement must be recoded with new USING parameters.

If parameter markers are not used in the SELECT statements, the markers could be eliminated and values could be substituted in the SQL statement to be executed. No parameters would be passed in the OPEN statement.

You can recode the OPEN statement also to pass parameters using an SQLDA (SQL Descriptor Area). The SQLDA would contain value descriptors and pointers to these values. You can recode the OPEN statement as follows :

 EXEC-SQL     OPEN CSR2 USING DESCRIPTOR :TVAL3; END_EXEC. 

DB2 uses the SQLDA to communicate information about dynamic SQL to an application program. The SQLDA sends information such as the type of SQL statement being executed and the number and data type of columns being returned by a SELECT statement. It can be used by fixed-list SELECT and varying-list SELECT dynamic SQL. The following code illustrates the fields of the SQLDA :

 ******************************************************* ***    SQLDA: SQL DESCRIPTOR AREA FOR COBOL II      *** ******************************************************* 01  SQLDA.     05 SQLDAID               PIC X(8)   VALUE 'SQLDA'.     05 SQLDABC          COMP PIC S9(8)  VALUE 13216.     05 SQLN             COMP PIC S9(4)  VALUE 750.     05 SQLD             COMP PIC S9(4)  VALUE 0.     05 SQLVAR OCCURS 1 TO 750 TIMES DEPENDING ON SQLN.         10 SQLTYPE      COMP PIC S9(4).             88 SQLTYPE-BLOB             VALUE 404 405.             88 SQLTYPE-CLOB             VALUE 408 409.             88 SQLTYPE-DBCLOB           VALUE 412 413.             88 SQLTYPE-FLOAT            VALUE 480 481.             88 SQLTYPE-DECIMAL          VALUE 484 485.             88 SQLTYPE-SMALLINT         VALUE 500 501.             88 SQLTYPE-INTEGER          VALUE 496 497.             88 SQLTYPE-DATE             VALUE 384 385.             88 SQLTYPE-TIME             VALUE 388 389.             88 SQLTYPE-TIMESTAMP        VALUE 392 393.             88 SQLTYPE-CHAR             VALUE 452 453.             88 SQLTYPE-VARCHAR          VALUE 448 449.             88 SQLTYPE-LONG-VARCHAR     VALUE 456 457.             88 SQLTYPE-VAR-ONUL-CHAR    VALUE 460 461.             88 SQLTYPE-GRAPHIC          VALUE 468 469.             88 SQLTYPE-VARGRAPH         VALUE 464 465.             88 SQLTYPE-LONG-VARGRAPH    VALUE 472 473.             88 SQLTYPE-ROWID            VALUE 904 905.             88 SQLTYPE-BLOB-LOC         VALUE 961 962.             88 SQLTYPE-CLOB-LOC         VALUE 964 965.             88 SQLTYPE-DBCLOB-LOC       VALUE 968 969.         10 SQLLEN       COMP PIC S9(4).         10 SQLDATA           POINTER.         10 SQLIND            POINTER.         10 SQLNAME.             15 SQLNAMEL COMP PIC S9(4).             15 SQLNAMEC COMP PIC X(30). 

A description of the contents of the SQLDA fields is in the discussion of the next class of dynamic SQL, which relies heavily on the SQLDA .

Quite a bit of flexibility is offered by fixed-list SELECT dynamic SQL. Fixed-list dynamic SQL provides many of the same benefits for the SELECT statement as non- SELECT dynamic SQL provides for other SQL verbs. An SQL SELECT statement can be prepared once and then fetched from a loop. The columns to be retrieved must be static, however. If you need the additional flexibility of changing the columns to be accessed while executing, use a varying-list SELECT .

CAUTION

For fixed-list SELECT dynamic SQL, you cannot code the SQLDA in a VS/COBOL program. You will need to use LE COBOL.

Fixed-List SELECT Guidelines

Follow the guidelines provided in this section when developing fixed-list SELECT dynamic SQL programs.

Use As Many Parameter Markers As Necessary

A prepared statement can contain more than one parameter marker. Use as many as necessary to ease development.

Issue Prepared Statements Multiple Times in a Unit of Work

After a statement is prepared, you can execute it many times in one unit of work without issuing another PREPARE .