HP 3000 Manuals HP 3000 Manuals
Preprocessing of Dynamic Queries
Preprocessing 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 8-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.
![[]](../../pic3k/M011754/FFN1033c50.gif)
Figure 8-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 query as follows:
* Define a host variable (or a string) 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 section 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 Chapter 6). 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 C preprocessor parses this command, it inserts a type
declaration for this data structure into the modified source code file:
# if 0
EXEC SQL INCLUDE SQLDA;
# endif
sqlda_type sqlda;
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:
typedef struct {
char sqldaid[8]; reserved for ALLBASE/SQL
int sqldabe; reserved for ALLBASE/SQL
int sqln; number of format array records
int sqld; number of columns
sqlformat_type *sqlfmtarr; format array address
int sqlnrow; number of rows to FETCH
int sqlrrow; number of rows fetched
int sqlrowlen; bytes in each row
int sqlbuflen; bytes in data buffer
int sqlrowbuf; data buffer address
} sqlda_type;
Values are assigned to SQLDA fields by you or by ALLBASE/SQL, as
summarized in Table 8-1.
Table 8-1. SQLDA Fields
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| FIELD | FIELD | C | YOU SET | YOU SET | ALLBASE/ | ALLBASE/ |
| NAME | DESCRIPTION | DATA | BEFORE | BEFORE | SQL | SQL |
| | | TYPE | DESCRIBE | FETCH | SETS AT | SETS AT |
| | | | | | DESCRIBE | FETCH |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqldaid | reserved | char[8] | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqldabc | reserved | int | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqln | number of format | int | X | | | |
| | array records | | | | | |
| | (one record | | | | | |
| | (column) per | | | | | |
| | select list item) | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqld | number of columns | int | | | X | |
| | in query result | | | | | |
| | (0 if non-query) | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlfmtarr | address of format | sqlformat_type* | X | | | |
| | array | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlnrow | number of rows to | int | | X | | |
| | FETCH into the | | | | | |
| | data buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlrrow | number of rows | int | | | | X |
| | put into the data | | | | | |
| | buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlrowlen | number of bytes | int | | | X | |
| | in each row | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlbuflen | number of bytes | int | | X | | |
| | in the data | | | | | |
| | buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
| | | | | | | |
| sqlrowbuf | address of data | int | | X | | |
| | buffer | | | | | |
| | | | | | | |
----------------------------------------------------------------------------------------------------------
Setting Up the Format Array
You declare the format array as an array of records having the type
SqlFormat_Type:
sqlformat_type sqlfmts[NbrFmtRecords];
You 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:
#define 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:
#define NbrFmtRecords 6;
The definition for the type SqlFormat_Type appears in the full
preprocessor generated type include file:
typedef struct {
short sqlnty;
short sqltype;
short sqlprec;
short sqlscale;
int sqltotallen;
int sqlvallen;
int sqlindlen;
int sqlvof;
int sqlnof;
char sqlname[20];
} sqlformat_type;
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 8-2 explains the
meaning of each field in a format array record.
Table 8-2. Fields in a Format Array Record
-----------------------------------------------------------------------------------------------
| | | |
| FIELD NAME | MEANING OF FIELD | C DATA TYPE |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlnty | reserved; always set to 110 | short |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqltype | data type of column: | short |
| | | |
| | 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* | |
| | | |
| | | |
| | * 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 and Date/Time/Interval | |
| | character types are compatible with regular character | |
| | types. | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlprec | precision of DECIMAL data | short |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlscale | scale of DECIMAL data | short |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqltotallen | byte sum of sqlvallen, sqlindlen, indicator alignment | int |
| | bytes, and next data value alignment bytes | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlvallen | number of bytes in data value, including a 4-byte | int |
| | prefix containing actual length of VARCHAR data | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlindlen | number of bytes null indicator occupies in the data | short |
| | buffer: | |
| | | |
| | 0 bytes: column defined NOT NULL | |
| | 2 bytes: column allows null values | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlvof | byte offset of value from the beginning of a row | int |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlnof | byte offset of null indicator from the beginning of a | int |
| | row, dependent on the value of sqlindlen | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlname | defined name of column or, for computed expression, | char[30] |
| | EXPR | |
| | | |
-----------------------------------------------------------------------------------------------
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:
#define MaxDataBuff 2500 /* bytes in data buffer */
.
.
.
char DataBuffer[MaxDataBuff]; /* the data buffer */
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:
sqlda.sqlnrow = ((sqlbuflen) / (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:
struct {
int strlen_col1; /* string length of column1 */
char column1[20];
short column2;
int column3;
short column3Ind; /* indicator variable */
} databuffer[MaxDataBuff];
When a column contains a VARCHAR data type, a 4 byte integer must be
declared immediately before the variable for that column to hold the
string length. The application itself needs to properly place the string
terminator, ASCII 0, into the string.
struct SQLVarChar {
int length; /* actual length of VARCHAR value */
char VarCharCol[MaxColSize]; /* VARCHAR value */
};
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:
* 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:
#define MaxDataBuff 1000 /* bytes in data buffer */
char DataBuffer[MaxDataBuff]; /* the data buffer */
When the select list is known, you can define the data buffer as
an array of records having the expected format:
#define MaxNbrRows 200
char DataBuffer[MaxNbrRows];
struct {
Column1DataType Column1;
Column2DataType Column2;
} DataBuffer[MaxNbrRows];
* You 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.
#define NbrFmtRecords 1024 /* Columns expected */
.
.
sqlformat_type sqlfmts[NbrFmtRecords]; /* sqlfmts is the format array */
* Use a host variable for the SELECT command, and pass it to
ALLBASE/SQL in the PREPARE command:
EXEC SQL BEGIN DECLARE SECTION;
char DynamicCommand[2048];
EXEC SQL END DECLARE SECTION;
.
.
.
EXEC SQL PREPARE Cmd1 FROM :DynamicCommand;
* Initialize two sqlda fields, sqln and sqlfmtarr. sqln is set to
the size of the format array, and sqlfmtarr is set to its address.
.
sqlda.sqln = NbrFmtRecords; /* Maximum select list elements*/
sqlda.sqlfmtarr = sqlfmts; /* Format array address */
* 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
sqlda.sqlbuflen = sizeof(DataBuffer); /* bytes in data buffer */
sqlda.sqlrowbuf = int(DataBuffer); /* data buffer address */
/* cast as INT */
sqlda.sqlnrow = ((sqlda.sqlbuflen) / (sqlda.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 C 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 8-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. The
figure illustrates the relationships between column definitions
and the layout of data in the data buffer.
![[]](../../pic3k/M011754/FFN1043c50.gif)
Figure 8-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.
do {
EXEC SQL FETCH Cursor1 USING DESCRIPTOR sqlda;
DisplayRow();
} while (sqlca.sqlcode !=100)
* 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 cex10a. The listing is found in Figure 8-9 in the following
section, "cex10a: Program Using 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.
* 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 cex10a. The data buffer
shown is for the first query executed in the dialog in Figure 8-8.
![[]](../../pic3k/M011754/FFN1053c50.gif)
Figure 8-6. Parsing the Data Buffer in cex10a
Program cex10a 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 + sqlfmts[i].SqlNOf;
To move a data value into a variant record, cex10a uses the following
statement:
StrMove(sqlfmts[1].(SqlValLen, DataBuffer,
CurrentOffset + sqlfmts[i].SqlVOf, OneColumn.CharData, 0);