HP 3000 Manuals HP 3000 Manuals
Programming with Dynamic Parameters
Depending on the purpose of your application, there is a broad spectrum
of scenarios in which dynamic parameters could be useful. You might know
almost all the elements of a statement at coding time, including the
statement type and what dynamic parameters are required. At the opposite
extreme, a program might be required to handle a completely unknown SQL
statement containing dynamic parameters. Generally speaking, the less
you know about a dynamic statement at coding time, the more coding you
must do to verify the statement's content at run time.
The two basic methods of assigning dynamic parameter values involve use
of either host variables or ALLBASE/SQL data structures and a data
buffer. To use host variables, you must at least know the exact format
of your SQL statement, although you need not know the specific data
values of dynamic parameters. To use data structures and a data buffer,
you do not need to know the exact format of your SQL statement.
Table 3-2. Dynamic Parameter Functionality by Programming Language
-------------------------------------------------------------------------------------------------
| | | | |
| Language | Dynamic Parameter | Dynamic Parameter | Dynamic Parameters |
| | Data Assignment | Data Assignment | in a BULK INSERT |
| | via Host Variables | via a Data Buffer | Statement |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| C | yes | yes | yes |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COBOL | yes | no | yes (with host |
| | | | variables) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| FORTRAN | yes | no | no |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| Pascal | yes | yes | yes |
| | | | |
-------------------------------------------------------------------------------------------------
Host variables are available for C, COBOL, FORTRAN, and Pascal
applications. Data buffers are available for C and Pascal applications
only. In addition, dynamic parameters within a BULK INSERT statement
require special syntax and are discussed separately. BULK INSERT
functionality is available for C, COBOL, and Pascal applications. The
following subsections discuss each basic coding method:
* Using Host Variables to Process Dynamic Parameters.
* Using Data Structures and a Data Buffer to Process Dynamic
Parameters
* Using a BULK INSERT Statement with Dynamic Parameters.
Using Host Variables to Process Dynamic Parameters
When you know at coding time the data type and format of each dynamic
parameter in a dynamic statement, you have the choice of using either a
host variable or a data buffer to provide dynamic parameter input at run
time. This section details the use of host variables with non-bulk
statements. (The next section discusses the data buffer technique.) The
functionality described in this section is available for C, COBOL,
FORTRAN, and Pascal programs.
Suppose you are coding an interactive user application. It involves
mapping a user's menu choice to a partially known statement, then
prompting for and accepting dynamic parameter values for data whose
format is known at coding time. The following pseudocode illustrates
this scenario.
:
Accept a variable indicating which of a set of statements the user has chosen.
Prepare the dynamic command for this statement:
PREPARE CMD FROM 'UPDATE PurchDB.Parts SET SalesPrice = ? WHERE PartNumber = ?;'
Prompt the user for values for the SalesPrice and PartNumber columns.
Execute the dynamic command using host variables to provide dynamic parameter
values:
EXECUTE CMD USING :SalesPrice, :PartNumber;
You could now loop back to prompt the user for additional values for SalesPrice
and PartNumber. Note that the dynamic command does not have to be prepared again.
Using Data Structures and a Data Buffer to Process Dynamic Parameters
If at coding time you don't know the data types of all dynamic parameters
in the prepared statement, you must use two ALLBASE/SQL data structures
and a data buffer to obtain the default data types and pass dynamic
parameter input to the database. These data structures are the same as
those used for dynamic output processing:
* sqlda_type data structure.
* sqlfmts_type data structure.
* data buffer.
The following discussion points out how to use these structures for input
data. Here the term input data means dynamic parameter data, and output
data means select list data. When a prepared statement is described for
both input and output data, you must define one set of data structures
for input data and another set for output data. Refer to the chapter
"Using Dynamic Operations" in the ALLBASE/SQL C Application Programming
Guide or the ALLBASE/SQL Pascal Application Programming Guide for a
detailed description and example of how to use dynamic data structures
for output.
Using the SQLDA for Input.
To use an SQLDA structure for input, you prepare the dynamic command,
then use the INPUT option with the DESCRIBE statement:
DESCRIBE INPUT DynamicCommand INTO SQLDA
In place of SQLDA, you could name any data structure of type sqlda_type.
When the DESCRIBE statement executes, whether for input data or output
data, the values in a given format array must be consistent with the
values in its related data buffer. Refer to Table 3-5 and Table 3-6
for a detailed description of the format array.
One difference between the use of
the SQLDA for input data versus output data involves the Sqln and Sqld
fields. Sqln is set by your program prior to issuing the DESCRIBE
statement and represents the maximum number of 48 byte format array
records allowed by the program. When using the DESCRIBE OUTPUT
specification, you tell ALLBASE/SQL to load each format record with
information for each select list item in the currently prepared statement
(if it is a query). Using the DESCRIBE INPUT specification indicates
that you want ALLBASE/SQL to load each format array record with
information for each dynamic parameter in the currently prepared
statement. Therefore, following execution of the DESCRIBE statement,
Sqld represents either the number of select list items output or the
number of dynamic parameters input.
When you describe dynamic parameters for input, the Sqlindlen field in
the format array always equals two, even if it relates to a table column
that does not allow nulls. Therefore, you must allow two bytes for this
field in the corresponding input data buffer. By contrast, when you
describe output for a column that was defined as not null, the Sqlindlen
field is set to 0 to indicate no bytes are allocated in the corresponding
data buffer for information relating to null values.
CAUTION ALLBASE/SQL reads the data buffer based on its related format
array. When you have described input, be sure the data you load
into the data buffer corresponds to the information in its
related format array.
Using the Data Buffer for Input.
When you are describing data for input, it is your program's
responsibility to load the data buffer with input values for each dynamic
parameter based on information in the related format array. (This is
unlike describing data for output, where ALLBASE/SQL loads the specified
data buffer when data is fetched.) Following is a list of possible
coding steps:
1. Define any host variables to be used to pass a command string to
ALLBASE/SQL via the PREPARE and DESCRIBE statements.
2. Define any necessary sqlda_type structures (also called descriptor
areas) for holding information about a given command string.
Sqlda_type structures are used to communicate information
regarding a specific SQL statement between this program and the
database to which it is connected. Information is transferred
when the DESCRIBE statement executes and when the FETCH statement
executes.
Remember that the INCLUDE SQLDA statement generates one sqlda_type
data structure named sqlda. So, if you need more than one such
structure, you must code a declaration for each.
If you know nothing about an SQL statement until run time, define
an sqlda_type structure for output to determine if the statement
is a query or not. If it is a query, ALLBASE/SQL loads the
related sqlformat_type structure with the format of the query
result (one 48 byte element per select list item). You must also
define an sqlda_type structure for input in case the statement
contains dynamic parameters. In this case, ALLBASE/SQL loads the
related sqlformat_type structure with the format of the dynamic
parameters (one 48 byte element per dynamic parameter).
3. Define an sqlformat_type structure (also called a format array)
for each required data buffer.
Information is transferred to the format array when the DESCRIBE
statement executes.
4. Define any necessary data buffers.
Each data buffer must correspond to an sqlda_type structure and an
sqlformat_type structure.
If the statement is a query, your program needs a data buffer to
hold the query results generated by the FETCH statement. If the
statement contains dynamic parameters, your program needs a data
buffer into which it loads the values of those dynamic parameters.
The dynamic parameter values are transmitted to the database by
means of an OPEN or an EXECUTE statement.
5. Use the PREPARE statement to preprocess the dynamic statement.
6. Set the appropriate sqlda_type fields. See Table 3-3 and
Table 3-4 .
Remember, when you describe input for a non-bulk statement (a
statement that processes just one row), sqlnrow must always be
equal to one prior to issuing the OPEN or EXECUTE statement.
7. Use the DESCRIBE statement (with the optional OUTPUT
specification) to determine the statement type and its format if
it is a query. Information goes to the specified sqlda_type and
sqlformat_type data structures.
You must use DESCRIBE OUTPUT if, at coding time, the composition
of your prepared statement is completely unknown or if you know it
is a query but you do not know its exact format and content.
8. Use the DESCRIBE statement with the INPUT specification to
determine the number of dynamic parameters in the prepared
statement and the default data type and format of each. Your
application obtains this information via the specified sqlda_type
and sqlformat_type data structures. (The "Using Default Data
Types with Dynamic Parameters" section later in this chapter
contains detailed information about default data types and default
data formats for dynamic parameters.)
9. Load the input data buffer with dynamic parameter values based on
information provided by the DESCRIBE INPUT statement.
10. If the prepared statement is a query, use a DECLARE statement to
associate it with a cursor.
Use an OPEN statement to put qualifying rows of the query into the
data buffer you have defined for output. Specify the USING
DESCRIPTOR clause of the OPEN statement to pass in dynamic
parameter values.
In a loop, use a FETCH USING DESCRIPTOR statement to process each
row.
11. Close the cursor and commit work.
12. If the prepared statement is not a query, use the EXECUTE
statement with the USING clause to pass in dynamic parameter
values.
Table 3-3. Setting SQLDA Fields for Output and for Input in C
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| Field | Field | C | You Set | You Set | You Set | ALLBASE/ | ALLBASE/ |
| Name | Description | Data | Before | Before | Before | SQL | SQL |
| | | Type | DESCRIBE | OPEN or | FETCH | Sets at | Sets at |
| | | | | EXECUTE | | DESCRIBE | FETCH |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqldaid | reserved | char[8]| | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqldabc | reserved | int | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqln | number of format | int | X | | | | |
| | array elements | | | | | | |
| | (for output, one | | | | | | |
| | record per select | | | | | | |
| | list item to a | | | | | | |
| | maximum of 1024; | | | | | | |
| | for input, one | | | | | | |
| | record per | | | | | | |
| | dynamic parameter | | | | | | |
| | to a maximum of | | | | | | |
| | 255) | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqld | for output, | int | | | | X | |
| | number of columns | | | | | | |
| | in query result | | | | | | |
| | (0 if non-query); | | | | | | |
| | for input, number | | | | | | |
| | of "?"s in the | | | | | | |
| | prepared | | | | | | |
| | statement | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlfmtarr | address of format | int | X | | | X 4 | |
| | array | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlnrow | number of rows in | int | | X 1 | X 2 | | |
| | the data buffer 3 | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlrrow | number of rows | int | | | | | X 2 |
| | put into the data | | | | | | |
| | buffer (used for | | | | | | |
| | output only, not | | | | | | |
| | for input) | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlrowlen | number of bytes | int | | | | X | |
| | in each row | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlbuflen | number of bytes | int | | X 1 | X 2 | | |
| | in the data | | | | | | |
| | buffer | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlrowbuf | address of data | int | | X 1 | X 2 | | |
| | buffer | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| |
| 1 Used for input only. |
| |
| 2 Used for output only. |
| |
| 3 When you describe for output, use sqlnrow to specify the number of rows to fetch |
| into the data buffer. When you describe for input, use sqlnrow to specify the |
| number of rows you have loaded into the data buffer (Always set to one for a |
| non-bulk statement.). |
| |
| 4 Data is loaded into the format array when the DESCRIBE statement executes. |
| |
------------------------------------------------------------------------------------------
Table 3-4. Setting SQLDA Fields for Output and for Input in Pascal
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| Field | Field | Pascal | You Set | You Set | You Set | ALLBASE/ | ALLBASE/ |
| Name | Description | Data | Before | Before | Before | SQL | SQL |
| | | Type | DESCRIBE | OPEN or | FETCH | Sets at | Sets at |
| | | | | EXECUTE | | DESCRIBE | FETCH |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqldaid | reserved | Packed | | | | | |
| | | Array | | | | | |
| | | [1..8] | | | | | |
| | | of | | | | | |
| | | char | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqldabc | reserved | Integer| | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqln | number of format | Integer| X | | | | |
| | array elements | | | | | | |
| | (for output, one | | | | | | |
| | record per select | | | | | | |
| | list item to a | | | | | | |
| | maximum of 1024; | | | | | | |
| | for input, one | | | | | | |
| | record per | | | | | | |
| | dynamic parameter | | | | | | |
| | to a maximum of | | | | | | |
| | 255) | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqld | for output, | Integer| | | | X | |
| | number of columns | | | | | | |
| | in query result | | | | | | |
| | (0 if non-query); | | | | | | |
| | for input, number | | | | | | |
| | of "?"s in the | | | | | | |
| | prepared | | | | | | |
| | statement | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlfmtarr | address of format | Integer| X | | | X 4 | |
| | array | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlnrow | number of rows in | Integer| | X 1 | X 2 | | |
| | the data buffer 3 | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlrrow | number of rows | Integer| | | | | X 2 |
| | put into the data | | | | | | |
| | buffer (used for | | | | | | |
| | output only, not | | | | | | |
| | for input) | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlrowlen | number of bytes | Integer| | | | X | |
| | in each row | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlbuflen | number of bytes | Integer| | X 1 | X 2 | | |
| | in the data | | | | | | |
| | buffer | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
| | | | | | | | |
| sqlrowbuf | address of data | Integer| | X 1 | X 2 | | |
| | buffer | | | | | | |
| | | | | | | | |
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| |
| 1 Used for input only. |
| |
| 2 Used for output only. |
| |
| 3 When you describe for output, use sqlnrow to specify the number of rows to fetch |
| into the data buffer. When you describe for input, use sqlnrow to specify the |
| number of rows you have loaded into the data buffer (Always set to one for a |
| non-bulk statement.). |
| |
| 4 Data is loaded into the format array when the DESCRIBE statement executes. |
| |
------------------------------------------------------------------------------------------
Table 3-5. Fields in a Format Array Record in C
-----------------------------------------------------------------------------------------------
| | | |
| Field Name | Meaning of Field | C Data Type |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| sqlnty | reserved; always set to 111 | 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 | |
| | 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. | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| 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 | int |
| | buffer | |
| | | |
| | for output: | |
| | 0 bytes: column defined NOT NULL | |
| | 2 bytes: column allows null values | |
| | | |
| | for input: always 2 bytes | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| 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[20] |
| | EXPR | |
| | | |
-----------------------------------------------------------------------------------------------
Table 3-6. Fields in a Format Array Record in Pascal
-----------------------------------------------------------------------------------------------
| | | |
| 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. | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| 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 | Integer |
| | buffer: | |
| | | |
| | for output: | |
| | 0 bytes: column defined NOT NULL | |
| | 2 bytes: column allows null values | |
| | | |
| | for input: always 2 bytes | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| 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 |
| | | |
-----------------------------------------------------------------------------------------------
Example in C Using Output and Input Data Buffers.
Suppose you have designed an application that builds a SELECT statement.
A user can enter any valid DBEnvironment name, table name, column name,
and a column value to be used as a filter in the WHERE clause. The
application builds the appropriate query and displays the query result.
Your application prepares this SELECT statement and describes it for
output. It also describes the statement for input so that ALLBASE/SQL
can determine a default data type and format for the user entered column
value. Note that the "Using Default Data Types with Dynamic Parameters"
section later in this chapter contains detailed information about default
data types and default data formats for dynamic parameters.
The following C pseudocode outlines the above scenario with emphasis on
ALLBASE/SQL programming for dynamic parameter substitution. The
functionality is available for the C and Pascal languages.
:
#define NbrFmtRecords 255
#define MaxDataBuff 2500
#define MaxColSize 3996
#define MaxName 20
#define MaxStr 132
#define SQLINT 0
#define SQLCHAR 2
#define OK 0
#define TRUE 1
#define FALSE 0
The ConvertType union structure is used to convert the SearchValue before
it is assigned to DataBufferIn.
typedef union ct {
char CharData[MaxColSize];
char VarCharData[MaxColSize];
int IntegerData;
short SmallIntData;
float FloatData;
double DecimalData;
} ConvertType;
Host variables are declared as follows:
DynamicCommand contains the dynamic SELECT statement. SQLMessage holds messages
returned by the SQLEXPLAIN statement. SearchValue, entered by the user,
is the value searched for by the SELECT statement.
EXEC SQL BEGIN DECLARE SECTION;
char DynamicCommand[1023];
char SQLMessage[133];
char SearchValue[1024];
EXEC SQL END DECLARE SECTION;
Declare the SQL communications area.
EXEC SQL INCLUDE SQLCA;
The sqldain record contains information about the sqlfmtsin format array and the
DataBufferIn variable.
sqlda_type sqldain;
The sqldaout record contains information about the sqlfmtsout format array and the
DataBufferOut variable.
sqlda_type sqldaout;
The sqlfmtsin format array describes the dynamic parameters in the WHERE clause of
the SELECT statement. Each record in the array describes one dynamic parameter.
Since this program specifies a single dynamic parameter, only the first record
in the array, sqlfmtsin[0], will be checked.
sqlformat_type sqlfmtsin[NbrFmtRecords];
The sqlfmtsout format array describes the columns in the select list of the
SELECT statement. Each record in the array describes one column.
sqlformat_type sqlfmtsout[NbrFmtRecords];
DBEName contains the user specified database environment name.
char DBEName[MaxName];
TableName contains the user specified table name of the SELECT statment.
char TableName[MaxName];
ColName contains the user specified column name of the SELECT statment.
char ColName[MaxName];
DataBufferIn contains the value of the dynamic parameter, in this case the
value of the column in the WHERE clause of the SELECT statement.
char DataBufferIn[MaxDataBuff];
DataBufferOut contains the row values retrieved by the SELECT statement.
char DataBufferOut[MaxDataBuff];
:
/********************************************************************/
main()
/********************************************************************/
{
Prompt the user for the database environment used in the CONNECT statement.
sprintf (DBEName,"");
sprintf (DynamicCommand,"");
Prompt ("DBEnvironment name",DBEName);
After prompting the user for the table name and the column name, move the
SELECT statement into the DynamicCommand variable. The dynamic parameter,
represented by the question mark, is not specified until after the PREPARE
and DESCRIBE statements.
if (ConnectDBE()) {
Prompt ("Table Name",TableName);
while (strlen(TableName)!=0) {
Prompt ("Column Name",ColName);
sprintf (DynamicCommand,"SELECT * FROM %s WHERE %s = ?;",
TableName, ColName);
Prepare();
Prompt ("Table Name",TableName);
} /* end while */
ReleaseDBE();
} /* end if */
else
printf("\nError: Cannot Connect to %s",DBEName);
} /* End of Main Program */
/********************************************************************/
int Prepare()
/********************************************************************/
{
Before the PREPARE statement, the input and output descriptor fields must be
set up.
The sqldain.sqln variable is assigned the number of records in the sqlfmtsin
array and the sqldain.sqlfmtarr variable is assigned the address of the
sqlfmtsin array.
sqldain.sqln = NbrFmtRecords;
sqldain.sqlfmtarr = sqlfmtsin;
The sqldaout.sqln variable is assigned the number of records in the sqlfmtsout
array and the sqldaout.sqlfmtarr variable is assigned the address of the
sqlfmtsout array.
sqldaout.sqln = NbrFmtRecords;
sqldaout.sqlfmtarr = sqlfmtsout;
if (BeginTransaction()) {
Prepare the dynamic SELECT statement. At this point the value of the
dynamic parameter is still undefined.
EXEC SQL PREPARE CMD1 FROM :DynamicCommand;
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
EndTransaction();
}
else {
The DESCRIBE statement gets information about the statement that was
dynamically preprocessed by the PREPARE statement.
Here dynamic parameter information is obtained:
EXEC SQL DESCRIBE INPUT CMD1 INTO sqldain;
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
EndTransaction();
}
else
Here query result information is obtained:
EXEC SQL DESCRIBE CMD1 INTO sqldaout;
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
EndTransaction();
}
else
Fetch();
}
} /* End if BeginTransaction */
} /* End of Prepare function */
/********************************************************************/
int Fetch()
/********************************************************************/
{
short i;
ConvertType ConvertedSearch;
Declare the cursor for the SELECT statement.
EXEC SQL DECLARE CURSOR1 CURSOR FOR CMD1;
Prompt the user for the search value, which will be assigned to the dynamic
parameter in the WHERE clause of the SELECT statement.
The sqlfmtsin[0].sqlname variable contains the column name in the WHERE
clause of the SELECT statement.
Prompt (sqlfmtsin[0].sqlname,SearchValue);
Set up the input descriptor fields of the sqldain record before opening the cursor.
The sqldain.sqlnrow variable is assigned the number of rows in DataBufferIn, that is,
the number of dynamic parameters specified.
sqldain.sqlnrow = 1;
The sqldain.sqlbuflen varable is assigned the number of bytes in DataBufferIn.
sqldain.sqlbuflen = MaxDataBuff;
The sqldain.sqlrowbuf variable is assigned the address of DataBufferIn.
sqldain.sqlrowbuf = (int) DataBufferIn;
Since the search value entered by the user is a character string, it must be
converted to the format of the column in the WHERE clause of the dynamic
SELECT statement. The SearchValue is first assigned to the ConvertedSearch
record, and then assigned to DataBufferIn.
Check the value of sqlfmtsin[0].sqltype to determine the data type of the column
in the WHERE clause.
if (sqlfmtsin[0].sqltype == SQLINT) {
INT or SMALLINT columns generate the same data type value in sqltype, but
must be distinguished because they have different lengths. If sqlvallen
is equal to the size of an integer variable, then the data type is INT.
if (sqlfmtsin[0].sqlvallen == sizeof(ConvertedSearch.IntegerData))
SQL INT data type.
ConvertedSearch.IntegerData = atoi(SearchValue);
else
Otherwise, the column data type is SMALLINT.
ConvertedSearch.SmallIntData = atoi(SearchValue);
}
else if (sqlfmtsin[0].sqltype == SQLCHAR) {
Otherwise, the column data type is CHAR.
for (i = 0;i < strlen(SearchValue); i++)
ConvertedSearch.CharData[i] = SearchValue[i];
for (i = strlen(SearchValue); i < sqlfmtsin[0].sqlvallen; i++)
ConvertedSearch.CharData[i] = ' ';
}
else
printf ("Error: Conversion routine unavailable for that data type.\n");
Move the ConvertedSearch data to DataBufferIn.
StrMove (sqlfmtsin[0].sqlvallen,ConvertedSearch.CharData,0,DataBufferIn,0);
Assign zero to the two-byte, null field value that must follow the search value
data in DataBufferIn.
ConvertedSearch.SmallIntData = 0;
StrMove (2,ConvertedSearch.CharData,0,DataBufferIn,sqlfmtsin[0].sqlvallen);
Open the cursor, using the input description record sqldain.
EXEC SQL OPEN CURSOR1 USING SQL DESCRIPTOR sqldain;
if (sqlca.sqlcode != OK)
SQLStatusCheck();
Set up the output descriptor fields of the sqldaout record before performing the fetch.
The sqldaout.sqlbuflen varable is assigned the number of bytes in DataBufferOut.
sqldaout.sqlbuflen = MaxDataBuff;
The sqldaout.sqlnrow variable is assigned the number of rows in DataBufferOut,
that is, the number of rows to fetch.
sqldaout.sqlnrow = ((sqldaout.sqlbuflen) / (sqldaout.sqlrowlen));
The sqldaout.sqlrowbuf variable is assigned the address of DataBufferOut.
sqldaout.sqlrowbuf = (int) DataBufferOut;
Fetch rows into DataBufferOut until no more rows are found.
do {
EXEC SQL FETCH CURSOR1 USING SQL DESCRIPTOR sqldaout;
if (sqlca.sqlcode == 100)
printf ("Warning: No more rows qualify for this operation\n");
else if (sqlca.sqlcode != 0)
SQLStatusCheck();
else
The DisplaySelect function parses DataBufferOut and displays the data.
See program cex10a in the ALLBASE/SQL C Application Programming Guide
for a full listing of the DisplaySelect function.
DisplaySelect();
} while (sqlca.sqlcode == 0);
Close the cursor and end the transaction.
EXEC SQL CLOSE CURSOR1;
if (sqlca.sqlcode != OK)
SQLStatusCheck();
EndTransaction();
} /* End of Fetch function */
/********************************************************************/
int StrMove (n,source,subs,dest,subd)
/********************************************************************/
int n, subs, subd;
char source[], dest[];
{
int i = 1;
Move n number of bytes from source, starting at source[subs] to dest,
starting at dest[subd].
while (i++ <= n)
dest[subd++] = source[subs++];
} /* End of StrMove function */
/********************************************************************/
int Prompt (displaystr,inputstr)
/********************************************************************/
char *displaystr, *inputstr;
{
printf("Enter %s: ",displaystr);
gets(inputstr);
} /* End of Prompt function */
:
You could enhance the above pseudocode by coding an application for the
following scenario. After the display, offer the user these choices:
* Enter another value for the same column.
* Enter another table name, column name, and column value for the
same DBEnvironment.
* Exit the application.
Since the column value is passed by means of a dynamic parameter, if the
user chooses to enter another column value for the same column, you can
improve performance by reusing the already prepared stored section for
the given SELECT statement.
Each time the user enters another value for the same SELECT statement,
your application does the following:
* Loads the new value into the input data buffer.
* Opens the dynamic cursor.
* Fetches all qualifying rows.
* Closes the cursor.
Using a BULK INSERT Statement with Dynamic Parameters
Suppose you are writing an application that inserts multiple rows of
data. At coding time, you know the format of the BULK INSERT statement,
and you know which parameters in the statement will differ for each row
(the dynamic parameters).
The application must run in many DBEnvironments, and you want it to
achieve maximum performance. For portability, you decide on dynamic
statements. For maximum performance, you decide to use parameter
substitution and the BULK INSERT statement. To minimize your coding
time, you use host variables, rather than a data buffer.
The following pseudocode examples illustrate this scenario for C, COBOL,
and Pascal applications.
NOTE When host variables are used, EXECUTE statement syntax differs for
a BULK INSERT statement and an INSERT statement.
Example in C Using a BULK INSERT.
:
boolean OrdersOk;
boolean ConnectDBE();
:
sqlca_type sqlca; /* SQL communication area. */
Define a host variable array to hold dynamic parameter values. Be sure each
host variable data type matches (or is compatible with) its ALLBASE/SQL default
data type:
EXEC SQL BEGIN DECLARE SECTION;
struct {
int NewOrderNumber;
int NewVendorNumber;
sqlind NewVendorNumberInd;
char NewOrderDate[11]; /* Add a byte for end of char array. */
sqlind NewOrderDateInd;
} NewOrders[25];
If the dynamic parameter represents data for a column that can contain nulls, and
it is possible that input data will contain null values, be sure to define a null
indicator host variable immediately following the related host variable.
If you are using other than the default values for the starting index and number of
rows in the array, define host variables for these as well:
short StartIndex;
short NumberOfRows; /* Maximum possible rows to bulk insert. */
int OrderNumber; /* Host variables for input data. */
int VendorNumber;
sqlind VendorNumberInd;
char OrderDate[11];
sqlind OrderDateInd;
:
char SQLMessage[133]; /* Add a byte for end of char array. */
EXEC SQL END DECLARE SECTION;
main() { /*Specify main functions. */
if (ConnectDBE()) { /* If the application is successfully */
/* connected to a DBEnvironment, proceed. */
OrdersOk = TRUE;
BeginTransaction();
PrepareIt();
CreateOrders();
InsertNew();
if (OrdersOk) /* If there were no errors in processing, */
CommitWork(); /* data is committed to the database. */
TerminateProgram();
} /* End if. */
} /* End of main program. */
:
Use the PREPARE statement to preprocess the dynamic statement, in this case, from
a string:
int PrepareIt(){
EXEC SQL PREPARE CMD from 'BULK INSERT INTO PurchDB.Orders VALUES (?,?,?);';
switch (sqlca.sqlcode){ /* Check for processing errors. */
case OK: break;
default: SQLStatusCheck();
RollBackWork();
OrdersOk = FALSE;
} /* End switch. */
} /* End function PrepareIt. */
Load up to 25 rows of new orders for the BULK INSERT. This data could originate
from an interactive user or from a file:
int CreateOrders()
{
int i = 0; /* Define and initialize an index to move */
/* through array elements. */
NumberOfRows = 25;
StartIndex = 1;
Count rows as they are loaded into the NewOrders array up to a maximum of 25:
for (i = 0; i <= NumberOfRows; i++){
Read a file record or accept a row of data from the user into
the appropriate host variables.
Load host variable data into the bulk insert array.
NewOrders[i].NewOrderNumber = OrderNumber;
NewOrders[i].NewVendorNumber = VendorNumber;
NewOrders[i].NewVendorNumberInd = VendorNumberInd;
strcpy (NewOrders[i].NewOrderDate,OrderDate);
NewOrders[i].NewOrderDateInd = OrderDateInd;
} /* End for. */
} /* End of function CreateOrders. */
Execute the prepared CMD command specifying the array where data for the BULK
INSERT is located:
int InsertNew(){
EXEC SQL EXECUTE CMD USING :NewOrders, :StartIndex, :NumberOfRows;
switch (sqlca.sqlcode){ /* Check for processing errors. */
case OK: break;
default: SQLStatusCheck();
RollBackWork();
OrdersOk = FALSE;
} /* End switch. */
} /* End of function InsertNew. */
Example in COBOL Using a BULK INSERT.
:
WORKING-STORAGE SECTION.
* SQL communication area. *
EXEC SQL INCLUDE SQLCA END-EXEC.
* Host variables for input data. *
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ORDERNUMBER PIC S9(9) COMP.
01 VENDORNUMBER PIC S9(9) COMP.
01 VENDORNUMBERIND SQLIND.
01 ORDERDATE PIC X(8).
01 ORDERDATEIND SQLIND.
Define a host variable array to hold dynamic parameter values. Be sure each
host variable data type matches (or is compatible with) its ALLBASE/SQL default
data type:
01 NEWORDERS.
05 EACH-ROW OCCURS 25 TIMES.
10 NEWORDERNUMBER PIC S9(9) COMP.
10 NEWVENDORNUMBER PIC S9(9) COMP.
10 NEWVENDORNUMBERIND SQLIND.
10 NEWORDERDATE PIC X(8).
10 NEWORDERDATEIND SQLIND.
If the dynamic parameter represents data for a column that can contain nulls, and
it is possible that input data will contain null values, be sure to define a null
indicator host variable immediately following the related host variable.
If you are using other than the default values for the starting index and number of
rows in the array, define host variables for these as well:
01 STARTINDEX PIC S9(4) COMP.
* Maximum possible rows to bulk insert. *
01 NUMBEROFROWS PIC S9(4) COMP.
01 SQLMESSAGE PIC X(132).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
A100-MAIN.
PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT.
PERFORM B100-PREPARE-IT THRU B100-EXIT.
PERFORM C100-CREATE-ORDERS THRU C100-EXIT
UNTIL DONE.
PERFORM D100-BULK-INSERT THRU D100-EXIT.
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A100-EXIT.
EXIT.
:
Use the PREPARE statement to preprocess the dynamic statement, in this case, from
a string:
B100-PREPARE-IT.
MOVE 1 to I.
MOVE SPACES TO DONE-FLAG.
MOVE SPACES TO NEWORDERS.
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT.
EXEC SQL
PREPARE CMD from
'BULK INSERT INTO PurchDB.Orders VALUES (?, ?, ?);'
END-EXEC.
Check for processing errors. Display any messages, and either commit or roll back
the transaction:
IF SQLCODE = OK
PERFORM A400-COMMIT-WORK THRU A400-EXIT
ELSE
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A450-ROLLBACK-WORK THRU A450-EXIT.
B100-EXIT.
EXIT.
Load up to 25 rows of new orders for the BULK INSERT. This data could originate
from an interactive user or from a file (In this case, it is an interactive user.):
C100-CREATE-ORDERS.
DISPLAY ' '.
DISPLAY 'You can specify as many as 25 line items.'.
DISPLAY ' '.
MOVE ' Order Number> ' TO PROMPT-USER
DISPLAY " "
WRITE PROMPT-USER
ACCEPT NEWORDERNUMBER(I)
MOVE ' Vendor Number> ' TO PROMPT-USER
DISPLAY " "
WRITE PROMPT-USER
ACCEPT NEWVENDORNUMBER(I)
MOVE ' Order Date (YYYYMMDD)> ' TO PROMPT-USER
DISPLAY " "
WRITE PROMPT-USER
MOVE SPACES TO NEWORDERDATE(I)
ACCEPT NEWORDERDATE(I)
IF I = 25
MOVE "X" TO DONE-FLAG
GO TO C100-EXIT
ELSE
PERFORM C200-MORE-LINES THRU C200-EXIT.
C100-EXIT.
EXIT.
C200-MORE-LINES.
DISPLAY ' '
MOVE 'Do you want to specify another line item (Y/N)?> '
TO PROMPT-USER
MOVE SPACE TO RESPONSE-ALPHA
DISPLAY " "
WRITE PROMPT-USER
ACCEPT RESPONSE-ALPHA.
IF RESPONSE-ALPHA NOT = "Y"
AND RESPONSE-ALPHA NOT = "y"
MOVE "X" TO DONE-FLAG
GO TO C200-EXIT
ELSE
COMPUTE I = I + 1.
C200-EXIT.
EXIT.
Execute the prepared CMD command specifying the array where data for the BULK INSERT
is located:
D100-BULK-INSERT.
DISPLAY ' '.
MOVE I TO NUMBEROFROWS.
MOVE 1 TO STARTINDEX.
MOVE 1 to I.
DISPLAY 'BULK INSERT INTO PurchDB.OrderItems'.
EXEC SQL
EXECUTE CMD USING :NEWORDERS, :STARTINDEX, :NUMBEROFROWS
END-EXEC.
Check for processing errors. Display any messages, and either commit or roll back
the transaction:
IF SQLCODE = OK
PERFORM A400-COMMIT-WORK THRU A400-EXIT
ELSE
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A450-ROLLBACK-WORK THRU A450-EXIT.
D100-EXIT.
EXIT.
:
Example in Pascal Using a BULK INSERT.
:
Define a host variable array to hold dynamic parameter values. Be sure each
host variable data type matches (or is compatible with) its ALLBASE/SQL default
data type:
EXEC SQL BEGIN DECLARE SECTION;
NewOrders : array[1..25]
of record
NewOrderNumber : integer;
NewVendorNumber : integer;
NewVendorNumberInd : sqlind;
NewOrderDate : packed array[1..10] of char;
NewOrderDateInd : sqlind;
end;
If the dynamic parameter represents data for a column that can contain nulls, and
it is possible that input data will contain null values, be sure to define a null
indicator host variable immediately following the related host variable.
If you are using other than the default values for the starting index and number of
rows in the array, define host variables for these as well:
StartIndex : SmallInt;
NumberOfRows : SmallInt; (* Maximum possible rows to bulk *)
(* insert. *)
OrderNumber : integer; (* Host variables for user input.*)
VendorNumber : integer;
VendorNumberInd : sqlind;
OrderDate : packed array[1..10] of char;
OrderDateInd : sqlind;
:
SQLMessage : packed array[1..132] of char;
EXEC SQL END DECLARE SECTION;
sqlca : SQLCA_type; (* SQL Communication Area *)
OrdersOK : boolean;
:
Use the PREPARE statement to preprocess the dynamic statement, in this case, from
a string:
procedure PrepareIt;
begin
EXEC SQL PREPARE CMD from 'BULK INSERT INTO PurchDB.Orders VALUES (?,?,?);';
if SQLCA.SQLCODE
OK then (* Check for processing errors. *)
begin
SQLStatusCheck;
RollBackWork;
OrdersOK := FALSE;
end;
end; (* End PrepareIt Procedure. *)
Load up to 25 rows of new orders for the BULK INSERT. This data could originate
from an interactive user or from a file:
procedure CreateOrders;
var
i:integer;
begin
NumberOfRows := 25;
StartIndex := 1;
Count rows as they are loaded into the NewOrders array up to a maximum of 25:
for i := 1 to NumberOfRows do
begin
Read a file record or accept a row of data from the user into
the appropriate host variables.
Load host variable data into the bulk insert array.
NewOrders[i].NewOrderNumber := OrderNumber;
NewOrders[i].NewVendorNumber := VendorNumber;
NewOrders[i].NewVendorNumberInd := VendorNumberInd;
NewOrders[i].NewOrderDate := OrderDate;
NewOrders[i].NewOrderDateInd := OrderDateInd;
end; (* End for. *)
end; (* End procedure CreateOrders. *)
Execute the prepared CMD command specifying the array where data for the BULK
INSERT is located:
procedure InsertNew;
begin
EXEC SQL EXECUTE CMD USING :NewOrders, :StartIndex, :NumberOfRows;
if SQLCA.SQLCODE
OK then (* Check for processing errors. *)
begin
SQLStatusCheck;
RollBackWork;
OrdersOK := FALSE;
end;
end; (* End of procedure InsertNew. *)
:
begin (* Begin the program. *)
if ConnectDBE then (* If the application is successfully *)
(* connected to a DBEnvironment, proceed. *)
begin
OrdersOK := TRUE;
BeginTransaction;
PrepareIt;
CreateOrders;
InsertNew;
if OrdersOK then (* If there were no errors in processing, *)
CommitWork; (* data is committed to the database. *)
end;
TerminateProgram;
end. (* End the Program. *)