Varying-List SELECT

Varying-List SELECT

Varying-list SELECT is the last class of dynamic SQL. You use it to explicitly prepare and execute SQL SELECT statements when you do not know in advance which columns will be retrieved by an application program.

Varying-list SELECT provides the most flexibility for dynamic SELECT statements. You can change tables, columns, and predicates " on-the-fly ."

WARNING

Because everything about the query can change during one invocation of the program, the number and type of host variables needed to store the retrieved rows cannot be known beforehand. The lack of knowledge regarding what is being retrieved adds considerable complexity to your application programs. (Note that FORTRAN and VS/COBOL programs cannot perform varying-list SELECT dynamic SQL statements.)

The SQLDA , as I mentioned, is the vehicle for communicating information about dynamic SQL between DB2 and the application program. It contains information about the type of SQL statement to be executed, the data type of each column accessed, and the address of each host variable needed to retrieve the columns. The SQLDA must be hard-coded into the COBOL II program's WORKING-STORAGE area, as shown here:

 EXEC-SQL     INCLUDE SQLDA END_EXEC. 

Table 12.1 defines each item in the SQLDA when it is used with varying-list SELECT .

Table 12.1. SQLDA Data Element Definitions

SQLDA Field Name	Use in DESCRIBE or PREPARE Statement
SQLDAID	Descriptive only; usually set to the literal "SQLDA" to aid in program debugging
SQLDABC	Length of the SQLDA
SQLN	Number of occurrences of SQLVAR available
SQLD	Number of occurrences of SQLVAR used
SQLTYPE	Data type and indicator of whether NULL s are allowed for the column; for UDTs, SQLTYPE is set based on the base data type
SQLLEN	External length of the column value; for LOBs
SQLDATA	Address of a host variable for a specific column
SQLIND	Address of NULL indicator variable for the preceding host variable
SQLNAME	Name or label of the column

The steps needed to code varying-list SELECT dynamic SQL to your application program vary according to the amount of information known about the SQL beforehand. Listing 12.5 details the steps necessary when you know that the statement to be executed is a SELECT statement. The code differs from fixed-list SELECT in three ways: The PREPARE statement uses the SQLDA , the FETCH statement uses the SQLDA , and a step is added to store host variable addresses in the SQLDA .

Listing 12.5. Varying-List SELECT Dynamic SQL

 SQL to execute: SELECT PROJNO, PROJNAME, RESPEMP                 FROM DSN8810.PROJ                 WHERE PROJNO = 'A00'                 AND PRSTDATE = '1988-10-10';  Move the "SQL to execute" to STRING-VARIABLE  EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL; EXEC SQL     PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE; EXEC SQL OPEN CSR3;  Load storage addresses into the SQLDA   Loop until no more rows to FETCH  EXEC SQL FETCH CSR3 USING DESCRIPTOR SQLDA; EXEC SQL CLOSE CSR3; 

When PREPARE is executed, DB2 returns information about the columns being returned by the SELECT statement. This information is in the SQLVAR group item of the SQLDA . Of particular interest is the SQLTYPE field. For each column to be returned, this field indicates the data type and whether NULL s are permitted. Note that in the SQLDA layout presented previously, all possible values for SQLTYPE are coded as 88-level COBOL structures. They can be used in the logic of your application program to test for specific data types. The valid values for SQLTYPE are shown in Table 12.2.

Table 12.2. Valid Values for SQLTYPE

SQLTYPE Value
NULL Allowed	NULL Not Allowed	Data Type
384	385	DATE
388	389	TIME
392	393	TIMESTAMP
400	401	null- terminated graphic string
404	405	BLOB
408	409	CLOB
412	413	DBCLOB
448	449	Small VARCHAR
452	453	Fixed CHAR
456	457	Long VARCHAR
460	461	VARCHAR optionally null-terminated
464	465	Small VARGRAPHIC
468	469	Fixed GRAPHIC
472	473	Long VARGRAPHIC
480	481	FLOAT
484	485	DECIMAL
496	497	INTEGER
500	501	SMALLINT
904	905	ROWID
961	962	BLOB locator
964	965	CLOB locator
968	969	DBCLOB locator
972	973	result set locator
976	977	table locator

The first value listed is returned when NULL s are permitted; the second is returned when NULL s are not permitted. These two codes aid in the detection of the data type for each column. The application program issuing the dynamic SQL must interrogate the SQLDA , analyzing each occurrence of SQLVAR . This information is used to determine the address of a storage area of the proper size to accommodate each column returned. The address is stored in the SQLDATA field of the SQLDA . If the column can be NULL , the address of the NULL indicator is stored in the SQLIND field of the SQLDA . When this analysis is complete, data can be fetched using varying-list SELECT and the SQLDA information.

Note that the group item, SQLVAR , occurs 750 times. This number is the limit for the number of columns that can be returned by one SQL SELECT . You can modify the column limit number by changing the value of the SQLN field to a smaller number but not to a larger one. Coding a smaller number reduces the amount of storage required. If a greater number of columns is returned by the dynamic SELECT , the SQLVAR fields are not populated .

You can also code dynamic SQL without knowing anything about the statement to be executed. An example is a program that must read SQL statements from a terminal and execute them regardless of statement type. You can create this type of program by coding two SQLDA s: one full SQLDA and one minimal SQLDA (containing only the first 16 bytes of the full SQLDA ) that PREPARE s the statement and determines whether it is a SELECT . If the statement is not a SELECT , you can simply EXECUTE the non- SELECT statement. If it is a SELECT , PREPARE it a second time with a full SQLDA and follow the steps in Listing 12.6.

Listing 12.6. Varying-List SELECT Dynamic SQL with Minimum SQLDA

 EXEC SQL INCLUDE SQLDA EXEC SQL INCLUDE MINSQLDA  Read "SQL to execute" from external source   Move the "SQL to execute" to STRING-VARIABLE  EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL; EXEC SQL     PREPARE VLSQL INTO MINSQLDA FROM :STRING-VARIABLE; IF SQLD IN MINSQLDA = 0     EXECUTE IMMEDIATE  (SQL statement was not a SELECT)  FINISHED. EXEC SQL     PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE; EXEC SQL OPEN CSR3;  Load storage addresses into the SQLDA   Loop until no more rows to FETCH  EXEC SQL FETCH CSR3 USING DESCRIPTOR SQLDA; EXEC SQL CLOSE CSR3; 

In this section, I've provided a quick introduction to varying-list SELECT dynamic SQL. If you want to code parameter markers or need further information on acquiring storage or pointer variables, consult the appropriate compiler manuals and the following DB2 manuals:

DB2 Application Programming and SQL Guide

DB2 SQL Reference

Varying-List SELECT Guidelines

The following guidelines should be adhered to when developing varying-list SELECT dynamic SQL programs.

Use Varying-List SELECT with Care

Be sure that you understand the fundamental capabilities of varying-list SELECT dynamic SQL before trying to use it. You should understand completely the SQLDA , pointer variables, and how the language you're using implements pointers before proceeding.