HP 3000 Manuals HP 3000 Manuals
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.
Click here to view figure.
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 | FIELD | Pascal | YOU SET | YOU SET | ALLBASE/ | ALLBASE/ |
| NAME | DESCRIPTION | DATA | BEFORE | BEFORE | SQL | SQL |
| | | TYPE | DESCRIBE | FETCH | SETS AT | SETS AT |
| | | | | | DESCRIBE | FETCH |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqldaid | reserved | Packed Array | | | | |
| | | [1..8] of char | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqldabc | reserved | Integer | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqln | number of format | Integer | X | | | |
| | array records | | | | | |
| | (one record | | | | | |
| | (column) per | | | | | |
| | select list item) | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqld | number of columns | Integer | | | X | |
| | in query result | | | | | |
| | (0 if non-query) | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlfmtarr | address of format | Integer | X | | | |
| | array | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlnrow | number of rows to | Integer | | X | | |
| | FETCH into the | | | | | |
| | data buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlrrow | number of rows | Integer | | | | X |
| | put into the data | | | | | |
| | buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlrowlen | number of bytes | Integer | | | X | |
| | in each row | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlbuflen | number of bytes | Integer | | X | | |
| | in the data | | | | | |
| | buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlrowbuf | address of data | SmallInt | | X | | |
| | buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
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 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: | SmallInt |
| | | |
| | 0 = SMALLINT or INTEGER | |
| | 1 = BINARY* | |
| | 2 = CHAR* | |
| | 3 = VARCHAR* | |
| | 4 = FLOAT | |
| | 5 = DECIMAL * | |
| | 8 = NATIVE CHAR * | |
| | 9 = NATIVE VARCHAR * | |
| | 10 = DATE* | |
| | 11 = TIME* | |
| | 12 = DATETIME* | |
| | 13 = INTERVAL* | |
| | 14 = VARBINARY* | |
| | 15 = LONG BINARY * | |
| | 16 = LONG VARBINARY* | |
| | | |
| | | |
| | * 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. | |
| | * 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. | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlprec | precision of DECIMAL data | SmallInt |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlscale | scale of DECIMAL data | SmallInt |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqltotallen | byte sum of sqlvallen, sqlindlen, indicator alignment | Integer |
| | bytes, and next data value alignment bytes | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlvallen | number of bytes in data value, including a 4-byte | Integer |
| | prefix containing actual length of VARCHAR data | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlindlen | number of bytes null indicator occupies in the data | SmallInt |
| | buffer: | |
| | | |
| | 0 bytes: column defined NOT NULL | |
| | 2 bytes: column allows null values | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlvof | byte offset of value from the beginning of a row | Integer |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlnof | byte offset of null indicator from the beginning of a | Integer |
| | row, dependent on the value of sqlindlen | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlname | defined name of column or, for computed expression, | Packed Array |
| | EXPR | [1..20] of char |
| | | |
-----------------------------------------------------------------------------------------------
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; (*columns 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. The figure illustrates the
relationships between column definitions and the layout of data in
the data buffer.
Click here to view figure.
Figure 10-5. Format of the Data Buffer
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.
Click here to view figure.
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);