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 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.
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 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 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:

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
^[1]Data is loaded into the format array when a DESCRIBE or ADVANCE statement executes. ^[2]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.).

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
^[1]Data is loaded into the format array when a DESCRIBE or ADVANCE statement executes. ^[2]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.).

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]

^[1]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.

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 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.



       .

       .

       .

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.                       *)