Preprocessing of Dynamic Queries

Processing of dynamic queries requires setting up a buffer to receive the query result and extracting the items you want from the buffer. For these operations, you use three special data structures:

SQL Description Area (SQLDA). The SQLDA is a record used to pass information on the location and contents of the other two dynamic data structures, the format array and the data buffer. You set some fields in the SQLDA and pass them to ALLBASE/SQL; and ALLBASE/SQL passes values back to you in other fields.
SQL Format Array. The format array is an array of records with one record for each select list item (column). The attributes of a column in the query result are described in a format array record. When you do not know the format of a query result at programming time, you use format array information to identify where in the data buffer to find each column value and how to interpret it.
Data Buffer. The data buffer is an array for holding rows in a query result. ALLBASE/SQL puts rows into the data buffer each time you execute the FETCH command.

Figure 10-4 summarizes the relationships among the special data structures and when data is assigned to them. Note that status checking information for each SQL command can be found in the sqlca data structure. See the chapter "Runtime Status Checking and the SQLCA" for more details.

Figure 10-4 Dynamic Query Data Structures and Data Assignment

Though some specific details differ depending on the query type, in general you handle all types of dynamic queries as follows:

A host variable (a string) is defined to hold the SELECT statement to be used by the PREPARE command.
The PREPARE command dynamically preprocesses the query. ALLBASE/SQL defines a temporary section, which includes a run tree for the SELECT command specified in the PREPARE command:
EXEC SQL PREPARE MyQuery FROM :DynamicCommand;
The DESCRIBE command makes available to your program information about each column in a query result:
EXEC SQL DESCRIBE MyQuery INTO SQLDA
The DECLARE CURSOR command maps the temporary section to a cursor so that the other cursor manipulation commands can be used:
EXEC SQL DECLARE DynamicCursor CURSOR FOR MyQuery;
The OPEN command allocates ALLBASE/SQL buffer space for holding qualifying rows and defines the active set:
EXEC SQL OPEN DynamicCursor;
The FETCH command evaluates any predicates in the query and transfers rows from the ALLBASE/SQL buffer into host variables:
EXEC SQL FETCH DynamicCursor USING DESCRIPTOR SQLDA;
The USING DESCRIPTOR clause indicates to ALLBASE/SQL that rows should be formatted in accord with a format array identified in the SQLDA and returned to a data buffer identified in the SQLDA. The SQLDA, the format array, and the data buffer are discussed later in this chapter under "Using the Dynamic Query Data Structures."
Although you can fetch multiple rows with each execution of the FETCH command, you do not specify the BULK option when fetching rows that qualify for dynamic queries. Instead, you set a field in the SQLDA as shown later in this chapter to communicate to ALLBASE/SQL how many rows to fetch. You can repeatedly execute the FETCH command until ALLBASE/SQL sets sqlca.sqlcode to 100.
The CLOSE command closes the cursor and frees previously allocated buffer space:
EXEC SQL CLOSE DynamicCursor;
The COMMIT WORK and ROLLBACK WORK commands also close any open cursors, unless you are using the KEEP CURSOR option of the OPEN command (see the chapter "Processing with Cursors"). In addition, these commands release locks obtained to execute the dynamic query. Therefore, to improve concurrency when repeatedly preparing dynamic queries, issue one of these commands before executing the PREPARE command for the second and each subsequent time.

Dynamically Updating and Deleting Data

You have the option of dynamically updating or deleting a row in conjunction with a dynamic FETCH statement. Any dynamic UPDATE WHERE CURRENT or DELETE WHERE CURRENT statement must be hard coded in your program just as you would code it for a non-dynamic FETCH statement. The statements cannot be defined at run time and prepared.

Whether your SELECT statement is completely user specified at run time, supplied by your program based on related user input, or completely defined by your program, here are some things to keep in mind:

If you are using a dynamic cursor to update, be sure your SELECT statement contains a FOR UPDATE OF clause.
An UPDATE WHERE CURRENT command must map to an appropriate SELECT statement. Be sure all of the columns you might possibly want to update are specified in the FOR UPDATE OF clause.
For example, if the host variable or string from which you prepare contains the following statement, you can use the UPDATE WHERE CURRENT command to change the content of all the columns in qualifying rows of PurchDB.Parts.
SELECT PartNumber FROM PurchDB.Parts WHERE PartNumber BETWEEN 9000 AND 9999 FOR UPDATE OF PartNumber, PartName, SalesPrice
However, if your prepared command is based on a host variable or string containing the following statement, you will only be able to use UPDATE WHERE CURRENT to change column SalesPrice in any qualifying rows of PurchDB.Parts.
SELECT PartNumber FROM PurchDB.Parts WHERE PartNumber BETWEEN 9000 AND 9999 FOR UPDATE OF SalesPrice
Your error checking strategy might include routines to parse user input for an acceptable SELECT statement and/or routines to test specific sqlca field values and invoke SQLEXPLAIN. This error checking strategy may need to be modified, if the syntax of the SELECT statement has changed for a particular ALLBASE/SQL release.

Setting Up the SQLDA

You use the INCLUDE command to declare the SQLDA in the declaration section of your program:

   EXEC SQL INCLUDE SQLDA;

When the Pascal preprocessor parses this command, it inserts a type declaration for this data structure into the modified source code file:



   $Skip_Text ON$

     EXEC SQL INCLUDE SQLDA;

   $Skip_Text OFF$

   sqlda: Sqlda_Type;

Alternatively, you can include the above type declaration in your source file and omit the INCLUDE command.

The Sqlda_Type record is defined as follows in the full preprocessor generated include file named SQLTYPE:



   Sqlda_Type = Record

     SqldaId  : Packed Array[1..8] of Char; reserved for ALLBASE/SQL

     Sqldabc  : Integer;                    reserved for ALLBASE/SQL

     Sqln     : Integer;                    number of format array records

     Sqld     : Integer;                    number of columns

     SqlFmtArr: Integer;                    format array address

     SqlNRow  : Integer;                    number of rows to FETCH

     SqlRRow  : Integer;                    number of rows fetched

     SqlRowLen: Integer;                    bytes in each row

     SqlBufLen: Integer;                    bytes in data buffer

     SqlRowBuf: Integer;                    data buffer address

     end;

Values are assigned to SQLDA fields by you or by ALLBASE/SQL, as summarized in Table 10-1.

Table 10-1 SQLDA Fields

FIELD NAME	FIELD DESCRIPTION	Pascal DATA TYPE	YOU SET BEFORE DESCRIBE	YOU SET BEFORE FETCH	ALLBASE/SQL SETS AT DESCRIBE	ALLBASE/SQL SETS AT FETCH
sqldaid	reserved	Packed Array [1..8] of char
sqldabc	reserved	Integer
sqln	number of format array records (one record (column) per select list item)	Integer	X
sqld	number of columns in query result (0 if non-query)	Integer			X
sqlfmtarr	address of format array	Integer	X
sqlnrow	number of rows to FETCH into the data buffer	Integer		X
sqlrrow	number of rows put into the data buffer	Integer				X
sqlrowlen	number of bytes in each row	Integer			X
sqlbuflen	number of bytes in the data buffer	Integer		X
sqlrowbuf	address of data buffer	SmallInt		X

Setting Up the Format Array

You declare the format array as an array of records having the type SqlFormat_Type:



   var

     SqlFmts   : Array[1..NbrFmtRecords] of SqlFormat_Type;

Set the number of records in the format array (NbrFmtRecords in this example) to the largest number of select list items you expect. If you do not know this value at programming time, you can allow for as many as 1024 records, since 1024 is the maximum number of columns any query result can contain, as follows:



   const

     NbrFmtRecords = 1024;

On the other hand, if you know at programming time the maximum number of columns to expect, you may be able to declare a smaller format array:



   const

     NbrFmtRecords = 6;

The definition for the type SqlFormat_Type appears in the full preprocessor generated type include file:



   SqlFormat_Type = Packed Record

     SqLnty, SqlType, SqlPrec, SqlScale: SmallInt;

     SqlTotalLen, SqlValLen, SqlIndLen: integer;

     SqlVof, SqlNof: integer;

     SqlName: Packed Array [1..20] of Char;

     end;

Each record in the format array describes one of the columns in the query result. The first record describes the first column, the second record describes the second column, and so forth. Table 10-2 “Fields in a Format Array Record” explains the meaning of each field in a format array record. Under "MEANING OF FIELD" for the sqltype field in the table, DATE, TIME, DATETIME, and INTERVAL each have different code numbers, but they all are formatted with an sqltype of code 2, CHAR, externally.

Table 10-2 Fields in a Format Array Record

FIELD NAME MEANING OF FIELD Pascal DATA TYPE

sqlnty reserved; always set to 111 SmallInt

sqltype

data type of column:

       0 = SMALLINT or INTEGER

       1 = BINARY ^[1]

       2 = CHAR ^[1]

       3 = VARCHAR ^[1]

       4 = FLOAT

       5 = DECIMAL ^[2]

       8 = NATIVE CHAR ^[1]

       9 = NATIVE VARCHAR ^[1]

      10 = DATE ^[1]

      11 = TIME ^[1]

      12 = DATETIME ^[1]

      13 = INTERVAL ^[1]

      14 = VARBINARY ^[1]

      15 = LONG BINARY ^[1]

      16 = LONG VARBINARY ^[1]

SmallInt

sqlprec precision of DECIMAL data SmallInt

sqlscale scale of DECIMAL data SmallInt

sqltotallen byte sum of sqlvallen, sqlindlen, indicator alignment bytes, and next data value alignment bytes Integer

sqlvallen number of bytes in data value, including a 4-byte prefix containing actual length of VARCHAR data Integer

sqlindlen

number of bytes null indicator occupies in the data buffer:

     0 bytes:  column defined NOT NULL

     2 bytes:  column allows null values

SmallInt

sqlvof byte offset of value from the beginning of a row Integer

sqlnof byte offset of null indicator from the beginning of a row, dependent on the value of sqlindlen Integer

sqlname defined name of column or, for computed expression, EXPR Packed Array [1..20] of char

^[1]Native CHAR or VARCHAR is what SQLCore uses internally when a CHAR or VARCHAR column is defined with a LANG = ColumnLanguageName clause. They possess the same characteristics as the related types CHAR and VARCHAR, except that data stored in native columns will be sorted, compared, or truncated using local language rules. Native, character, and Date/Time types are compatible with regular character types.

^[2]When you use the DECIMAL data type with BULK processing or with format array record in dynamic processing, you must convert the DECIMAL value to its ASCII representation. Refer to the routine BCDToString in the program cex10a later in this chapter.

Setting up the Data Buffer

You use different approaches to setting up the data buffer depending on whether your dynamic query result has an unknown format or a known format. If the query result has an unknown format, you may not know the number of columns or their data types. If the query result has a known format, you know in advance the number of columns in the query result and the data type of each column.

Setting up a Buffer for Query Results of Unknown Format

For query results of unknown format, you declare the data buffer as a character array:

   const

     MaxDataBuff = 2500;

   .

   .

   .

   var

     DataBuffer : packed array[1..MaxDataBuff] of char;

The data buffer must be large enough to hold all the rows ALLBASE/SQL retrieves each time you execute the FETCH command, i.e., the number of rows you specify in SQLDA.SqlNRow. The data buffer defined above can hold as many as 2500 bytes of data.

Although the data buffer above can hold 2500 bytes, it would not be able to hold 2500 bytes of column values if any of the values were null and/or VARCHAR:

If a column can contain null values, ALLBASE/SQL appends a 2-byte suffix to the data value when it puts the data into the data buffer. This suffix, referred to as a null indicator, contains a 0 when the data value is not null and a negative number when the value is null. You use the sqlindlen field of the format array record to determine whether ALLBASE/SQL returned this suffix with the data.
When ALLBASE/SQL puts VARCHAR data into the data buffer, it prefixes the data with 4 bytes containing the actual length of the VARCHAR string. You use the sqltype field of the format array record to identify VARCHAR values. This field is set to 3 when data returned to the data buffer has this prefix.

You can use the SQLDA.SqlRowLen value to compute how many rows will fit into the data buffer. Dividing SQLDA.SqlRowLen into SQLDA.SqlBufLen gives you the number of rows, including any VARCHAR prefixes and null indicator suffixes accompanying data values in the row:

   SqlNRow := SqlBufLen DIV SqlRowLen

The data buffer declaration shown above is an array of char, because the format of the query result is unknown at programming time.

Setting up a Buffer for Query Results of Known Format

When you know the query result format in advance, you can declare a data buffer as an array of records having the expected format. When a column can contain null values, you must declare a 2-byte indicator variable, immediately following the variable for that column. The indicator variable will hold the 2-byte suffix ALLBASE/SQL returns with the data value. In the following example, Column3Ind is an indicator variable for Column3.

 DataBuffer  : Packed Array[1..MaxDataBuff] of Packed Record

                 Column1      : String[20]; (* for VARCHAR data *)

                 Column2      : SmallInt;

                 Column3      : Integer;

                 Column3Ind   : SmallInt;   (* indicator variable *)

                 Column4      : Packed Array[1..60] of Char; (* for CHAR data *)

               End;

When a column contains a VARCHAR data type, you use a string data type for the variable length data, as shown above. The string data type includes a 4-byte prefix for the length of the data.

The data types you declare for a query result of known format need not be equivalent to the data types of their corresponding columns, but they should be compatible. (DATE, TIME, DATETIME, and INTERVAL values are treated like CHAR values.) Refer to the ALLBASE/SQL Reference Manual for the rules governing data type compatibility and conversion for complete information on this topic. The ALLBASE/SQL Reference Manual also addresses type conversion that may occur when a select list item is an expression containing data of different types. When you expect truncation, the column must allow nulls in order to detect the truncation.

Using the Dynamic Query Data Structures

You use the SQLDA, the format array, and the data buffer in the following sequence of operations:

Include the SQLDA at the beginning of your program with an INCLUDE statement:
EXEC SQL INCLUDE SQLDA;

Declare a data buffer to hold the query result. This may be structured or not, depending on whether you know the format of the query result in advance. The following is unstructured:

   const

     MaxDataBuff = 2500;

   .

   .

   .

   var  

     DataBuffer : packed array[1..MaxDataBuff] of char;

When the select list is known, you can define the data buffer as an array of records having the expected format:

 

   var

     DataBuffer: packed array[1..MaxNbrRows] of packed record

                   column1  : Column1DataType;

                   column2  : Column2DataType;

                 end;

Declare a format array as sqlformat_type. This type is defined for you in the preprocessor generated type include file. The number of records in the format array in this example is 1024, which allows for the maximum size query result of 1024 columns.
const NbrFmtRecords = 1024; (*column expected*) var (SQLFmts is the format array SQLFmts : array[1..NbrFmtRecords] of SQLFormat_Type;

Use a host variable for the SELECT command, and pass it to ALLBASE/SQL in the PREPARE command:

   EXEC SQL BEGIN DECLARE SECTION;

   DynamicCommand        : string[1024];

   EXEC SQL END DECLARE SECTION;

   .

   .

   .

   EXEC SQL PREPARE Cmd1 FROM :DynamicCommand;

Initialize two SQLDA fields, sqln and sqlfmtarr. sqln is set to the number of records of the format array, and sqlfmtarr is set to its address.

   with SQLDA do

     begin

       sqln      := NbrFmtRecords;       (* columns expected*)

       sqlfmtarr := waddress(SQLFmts);   (* format array address*)

     end;

Execute the DESCRIBE command:

   EXEC SQL DESCRIBE Cmd1 INTO SQLDA;

During the execution of the DESCRIBE command, ALLBASE/SQL returns to the format array and to the SQLDA the information you need later to parse and handle the query result. You use format array information to parse the data buffer when you do not know in advance the format of a query result.




	NOTE: When you know the format of the query result in advance, you can define a data buffer having the format you expect, and you do not need to use format array information to parse it. However, you still need to declare the format array.

Declare and open a cursor for the prepared query:

   EXEC SQL DECLARE Cursor1 CURSOR FOR Cmd1;

   EXEC SQL OPEN Cursor1;

Before retrieving rows into the data buffer, initialize three SQLDA fields. These fields identify your data buffer and specify how many rows you want retrieved into the data buffer each time the FETCH command is executed:

   with SQLDA do

     begin

       sqlbuflen := sizeof(DataBuffer);      (* bytes in data buffer *)

       sqlrowbuf := waddress(DataBuffer);    (* data buffer address *)

       sqlnrow   := sqlbuflen DIV sqlrowlen; (* number of rows to FETCH *)

     end;

Execute the FETCH command. ALLBASE/SQL packs the data buffer with as many rows from the active set as you specified in SQLDA.SqlNRow. ALLBASE/SQL puts the first select list value into the data buffer, starting at the first byte of the format array and including any VARCHAR prefixes, ALLBASE/SQL null indicators for columns that can contain null values, and any alignment bytes provided by the Pascal compiler. Then ALLBASE/SQL writes the second through last select list values for the first row. If the query result contains another row, the first through last select list values in that row are written to the data buffer. Data values are thus concatenated in the data buffer until the last row has been fetched. When the last row in the active set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100.
In Figure 10-5, two columns are selected from the vendors table in the sample database. Column VendorNumber is defined in the table as an INTEGER that cannot contain a null value. Column VendorRemarks is defined in the table as a VARCHAR that can contain a null value. Since the VendorRemarks column can contain a null value, a two byte null indicator needs to be provided immediately following this VARCHAR data column. Note the two byte filler that completes the VendorRemarks column definition. It is needed by the compiler for byte alignment purposes; data is aligned on 4 byte boundaries.
NOTE: 300/400 Character array data is aligned on 2 byte boundaries in Series 300 and 400 systems.
The figure illustrates the relationships between column definitions and the layout of data in the data buffer for a Series 800 system.
Figure 10-5 Format of the Data Buffer for a Series 800

Note that the number of rows to retrieve with each execution of the FETCH command is specified in SQLDA.SqlNRow. As shown in the above example, you can calculate the number of rows that will fit into the data buffer by dividing the row length (in bytes) into the number of bytes in the data buffer. Sqlrowlen, one of the SQLDA fields set by ALLBASE/SQL when you execute the DESCRIBE command, contains the number of bytes in each row.
while SQLCA.SQLCODE = 0 do begin EXEC SQL FETCH Cursor1 USING DESCRIPTOR SQLDA; DisplayRow; end;
If the query result is of unknown format, parse rows out of the data buffer after each execution of the FETCH command. The technique for parsing is shown in detail in the next section.

Parsing the Data Buffer

The technique for parsing the data buffer and assigning its contents to variables of appropriate types is illustrated in function DisplaySelect of program pasex10a. The listing is found in Figure 10-9 in the following section, "Program pasex10a: Dynamic Commands of Unknown Format." Essentially, you initialize an offset variable for the data buffer, then execute a loop for each row retrieved with the FETCH statement. For each column in the loop, you do the following:

Check for null values, taking appropriate action when one is found.
Examine the data type and length of the data element itself, assigning it to an appropriate variable of the corresponding size. A dynamically preprocessed PREPARE statement with an output data buffer requires you code Pascal statements yourself to convert Binary Coded Decimal (BCD) representation to character representation. If you use an input buffer with dynamic preprocessing, you must write code that converts the character representation to BCD format before the data is placed in the input buffer.
Increment the offset variable by the value of SQLDA.SqlRowLen (the length of a complete row).

The following diagram summarizes the arithmetic used to parse the data buffer in function DisplaySelect in program pasex10a. The data buffer shown is for the first query executed in the dialog in Figure 10-6.

Figure 10-6 Parsing the Data Buffer in Program pasex10a

Program pasex10a uses the following assignment to set the start of a row:

   CurrentOffset := CurrentOffset + SqlRowLen;

To find a null indicator, the program uses the following assignment:

   NullIndOffset := CurrentOffset + SqlNOf;

To move a data value into a variant record, pasex10a uses the following statement:

   StrMove(SqlValLen, DataBuffer,

   CurrentOffset + SqlVOf, OneColumn.CharData, 1);