 |
» |
|
|
|
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 4-2 Dynamic Parameter Functionality by Programming Language | Language | Dynamic Parameter Data Assignment via Host Variables | Dynamic Parameter Data Assignment via a Data Buffer | Dynamic Parameters in a BULK INSERT 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.
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 InputTo 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 4-6 “Fields in a Format Array Record in C” and Table 4-7 “Fields in a Format Array Record in Pascal” 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. | | | | |
Note that for both input data and output data you must byte align the
data in the data buffer. Data alignment for the series 300 and 400 differs from that of other systems as shown in table Table 4-3 “ALLBASE/SQL Data Type Byte Alignment”. Refer to the "Using Dynamic Operations" chapters in the ALLBASE/SQL C Application Programming Guide and the ALLBASE/SQL Pascal Application Programming Guide for further information on byte alignment in the data buffer. Using the Data Buffer for InputWhen 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: Define any host variables to be used to pass a command string to ALLBASE/SQL via the PREPARE and DESCRIBE statements. 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). 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. 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. Use the PREPARE statement to preprocess the dynamic statement. Set the appropriate sqlda_type fields. See Table 4-4 “Setting SQLDA Fields for Output and for Input in C” and Table 4-5 “Setting SQLDA Fields for Output and for Input in Pascal”. 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. 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. 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.) Load the input data buffer with dynamic parameter values based on information provided by the DESCRIBE INPUT statement. 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. Close the cursor and commit work. If the prepared statement is not a query, use the EXECUTE statement with
the USING clause to pass in dynamic parameter values.
Table 4-3 ALLBASE/SQL Data Type Byte Alignment | Format Array sqltype Field | Data Type | Series 700 and 800 Byte Alignment | Series 300 and 400 Byte Alignment |
|---|
| 0 | INTEGER | 4 | 2 | | 0 | SMALLINT | 2 | 2 | | 1 | BINARY | 1 | 1 | | 2 | CHAR | 1 | 1 | | 3 | VARCHAR | 4 | 2 | | 4 | DOUBLE PRECISION | 8 | 2 | | 4 | FLOAT (4 bytes) | 4 | 2 | | 4 | FLOAT (8 bytes) | 8 | 2 | | 4 | REAL | 4 | 2 | | 5 | DECIMAL | 4 | 2 | | 5 | NUMERIC | 4 | 2 | | 6 | TID | 4 | 2 | | 10 | DATE | 1 | 1 | | 11 | TIME | 1 | 1 | | 12 | DATETIME | 1 | 1 | | 13 | INTERVAL | 1 | 1 | | 14 | VARBINARY | 4 | 2 | | 15 | LONG BINARY | 1 | 1 | | 16 | LONG VARBINARY | 1 | 1 |
Table 4-4 Setting SQLDA Fields for Output and for Input in C | Field Name | Field Description | C Data Type | You Set Before DESCRIBE or ADVANCE | You Set Before OPEN or EXECUTE USING INPUT | You Set Before FETCH or EXECUTE USING OUTPUT | ALLBASE/ SQL Sets at DESCRIBE or ADVANCE | ALLBASE/ SQL Sets at FETCH or EXECUTE USING OUTPUT |
|---|
| sqldaid | reserved | char[8] | | | | | | | sqlmproc | number of multiple row result sets inside a procedure | short | | | | IOR | | | sqloparm | number of output dynamic parameters in a dynamically prepared
EXECUTE PROCEDURE statement | short | | | | O | | | sqln | number of format 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) | int | IOR | | | | | | sqld | for output, number of columns in query result (0 if non-query or EXECUTE PROCEDURE); for input, number of input dynamic parameters in the prepared statement | int | | | | IOR | | | sqlfmtarr | address of format array | int | IOR | | | IOR [1] | | | sqlnrow | number of rows in the data buffer [2] | int | | I[2] | O[2] | | | | sqlrrow | number of rows put into the data buffer | int | | | | | O | | sqlrowlen | number of bytes in each row | int | | | | IOR | | | sqlbuflen | number of bytes in the data buffer | int | | I | O | | | | sqlrowbuf | address of data buffer | int | | I | O | | | | I | Used for input. | | O | Used for output. | | R | Used for DESCRIBE RESULT and ADVANCE |
Table 4-5 Setting SQLDA Fields for Output and for Input in Pascal | Field Name | Field Description | C Data Type | You Set Before DESCRIBE or ADVANCE | You Set Before OPEN or EXECUTE USING INPUT | You Set Before FETCH or EXECUTE USING OUTPUT | ALLBASE/ SQL Sets at DESCRIBE or ADVANCE | ALLBASE/ SQL Sets at FETCH or EXECUTE USING OUTPUT |
|---|
| sqldaid | reserved | char[8] | | | | | | | sqlmproc | number of multiple row result sets inside a procedure | short | | | | IOR | | | sqloparm | number of output dynamic parameters in a dynamically prepared
EXECUTE PROCEDURE statement | smallint | | | | O | | | sqln | number of format 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) | integer | IOR | | | | | | sqld | for output, number of columns in query result (0 if non-query or EXECUTE PROCEDURE); for input, number of input dynamic parameters in the prepared statement | integer | | | | IOR | | | sqlfmtarr | address of format array | integer | IOR | | | IOR [1] | | | sqlnrow | number of rows in the data buffer [2] | integer | | I[2] | O[2] | | | | sqlrrow | number of rows put into the data buffer | integer | | | | | O | | sqlrowlen | number of bytes in each row | integer | | | | IOR | | | sqlbuflen | number of bytes in the data buffer | integer | | I | O | | | | sqlrowbuf | address of data buffer | integer | | I | O | | | | I | Used for input. | | O | Used for output. | | R | Used for DESCRIBE RESULT and ADVANCE |
Table 4-6 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:
0 = SMALLINT or INTEGER
1 = BINARY
2 = CHAR*
3 = VARCHAR*
4 = FLOAT
5 = DECIMAL
8 = NATIVE CHAR [1]
9 = NATIVE VARCHAR [1]
10 = DATE*
11 = TIME*
12 = DATETIME*
13 = INTERVAL*
14 = VARBINARY
15 = LONG BINARY
16 = LONG VARBINARY
19 = case insensitive CHAR*
20 = case insensitive VARCHAR*
21 = case insensitive NATIVE CHAR*
22 = case insensitive NATIVE VARCHAR*
|
| short | | sqlprec | precision of DECIMAL data | short | | sqlscale | scale of DECIMAL data | short | | sqltotallen | byte sum of sqlvallen, sqlindlen, indicator alignment bytes, and next data value alignment bytes | int | | sqlvallen | number of bytes in data value, including a 4-byte prefix containing actual length of VARCHAR data | int | | sqlindlen | number of bytes null indicator occupies in the data buffer
for output:
0 bytes: column defined NOT NULL
2 bytes: column allows null values
for input: always 2 bytes
|
| int | | sqlvof | byte offset of value from the beginning of a row | int | | sqlnof | byte offset of null indicator from the beginning of a row, dependent on the value of sqlindlen | int | | sqlname | defined name of column or, for computed expression, EXPR | char[20] |
Table 4-7 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:
0 = SMALLINT or INTEGER
1 = BINARY
2 = CHAR*
3 = VARCHAR*
4 = FLOAT
5 = DECIMAL
8 = NATIVE CHAR [1]
9 = NATIVE VARCHAR [1]
10 = DATE*
11 = TIME*
12 = DATETIME*
13 = INTERVAL*
14 = VARBINARY
15 = LONG BINARY
16 = LONG VARBINARY
19 = case insensitive CHAR*
20 = case insensitive VARCHAR*
21 = case insensitive NATIVE CHAR*
22 = case insensitive NATIVE VARCHAR*
|
| SmallInt | | sqlprec | precision of DECIMAL data | SmallInt | | sqlscale | scale of DECIMAL data | SmallInt | | sqltotallen | byte sum of sqlvallen, sqlindlen, indicator alignment bytes, and next data value alignment bytes | Integer | | sqlvallen | number of bytes in data value, including a 4-byte prefix containing actual length of VARCHAR data | Integer | | sqlindlen | number of bytes null indicator occupies in the data buffer:
for output:
0 bytes: column defined NOT NULL
2 bytes: column allows null values
for input: always 2 bytes
|
| Integer | | sqlvof | byte offset of value from the beginning of a row | Integer | | sqlnof | byte offset of null indicator from the beginning of a row, dependent on the value of sqlindlen | Integer | | sqlname | defined name of column or, for computed expression, EXPR | Packed Array [1..20] of char |
Example in C Using Output and Input Data BuffersSuppose 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 variable 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 variable 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.
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.
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. *)
|
|
