HP 3000 Manuals HP 3000 Manuals
Programming with Dynamic Parameters (contd)
Using Data Structures and a Data Buffer to Process Dynamic Parameters
(contd)
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 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.
* 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.
: