18.2.3 Using dynamic parameters

Executing an SQL statement with dynamic parameters involves allocation of a statement handle, preprocessing of the SQL statement, specifying the dynamic parameters, and execution of the SQL statement. The following figure shows the procedure for executing an SQL statement with dynamic parameters specified.

Figure 18‒6: Procedure for executing an SQL statement with dynamic parameters specified

The methods for allocating the statement handle and preprocessing and executing the SQL statement are the same as explained in 18.2.2 Referencing data. This subsection explains how to acquire the number of dynamic parameters and dynamic parameter information, convert input data to SQL data types, and specify dynamic parameters.

Organization of this subsection

(1) Acquiring the number of dynamic parameters
(2) Acquiring dynamic parameter information
(3) Converting input data to SQL data types
(4) Specifying the dynamic parameters

(1) Acquiring the number of dynamic parameters

If the number of dynamic parameters is not known at the time of application program creation, such as when SQL statements will be executed dynamically, you use a_rdb_SQLNumParams() to acquire the number of dynamic parameters. The following shows an example of acquiring the number of dynamic parameters.

Example of acquiring the number of dynamic parameters

/* Acquire the number of dynamic parameters */
rtnc = a_rdb_SQLNumParams(hCnct,
                          hStmt,
                          &paramCount,    /* Number of dynamic parameters */
                          NULL) ;

For details about a_rdb_SQLNumParams(), see 19.4.12 a_rdb_SQLNumParams() (acquire the number of dynamic parameters).

To Page Top

(2) Acquiring dynamic parameter information

If the dynamic parameter information, such as the data types and data lengths, is not known at the time of application program creation, such as when SQL statements will be executed dynamically, you use a_rdb_SQLDescribeParams() to acquire the dynamic parameter information. You can acquire the following information by using a_rdb_SQLDescribeParams():

Data types of the dynamic parameters
Maximum numbers of elements of the dynamic parameters
Data lengths of the dynamic parameters

The following shows an example of acquiring dynamic parameter information.

Example of acquiring dynamic parameter information

/* Acquire dynamic parameter information */
rtnc = a_rdb_SQLDescribeParams(hCnct,
                               hStmt,
                               paramCount,       /* Number of dynamic parameters */
                               &(paramInfo[0]),  /* Area for returning all dynamic parameter information */
                               NULL) ;

For details about a_rdb_SQLDescribeParams(), see 19.4.7 a_rdb_SQLDescribeParams() (acquire dynamic parameter information).

To Page Top

(3) Converting input data to SQL data types

If the SQL data type of the input data for dynamic parameters is DECIMAL, BINARY, VARBINARY, DATE, TIME, or TIMESTAMP, you can use a CLI function to convert the character string data supported by C or C++ to the corresponding data type. The following shows an example of converting character string data in C or C++ to DECIMAL-type data.

Example of data conversion

#define PRECISION 6
#define SCALE 3
 
char data_char[]="-123.567";
unsigned char data_decimal[4];
 
/* Convert to DECIMAL-type data */
rtnc = a_rdb_CNV_charDECIMAL(data_char,          /* Start address of data to be converted */
                            (unsigned short)strlen(data_char), /* Length of data to be converted */
                            PRECISION,           /* Precision of input data */
                            SCALE,               /* Scaling of input data */
                            data_decimal,        /* Address of input data storage area */
                            4,                   /* Length of input data storage area */
                            NULL);

To convert input data to DECIMAL-type data, you use a_rdb_CNV_charDECIMAL(). For details about a_rdb_CNV_charDECIMAL(), see 19.5.3 a_rdb_CNV_charDECIMAL() (convert to DECIMAL-type data).
To convert input data to BINARY-type data, you use a_rdb_CNV_charBINARY(). For details about a_rdb_CNV_charBINARY(), see 19.5.1 a_rdb_CNV_charBINARY() (convert to BINARY-type data).
To convert input data to VARBINARY-type data, you use a_rdb_CNV_charVARBINARY(). For details about a_rdb_CNV_charVARBINARY(), see 19.5.6 a_rdb_CNV_charVARBINARY() (convert to VARBINARY-type data).
To convert input data to DATE-type data, you use a_rdb_CNV_charDATE(). For details about a_rdb_CNV_charDATE(), see 19.5.2 a_rdb_CNV_charDATE() (convert to DATE-type data).
To convert input data to TIME-type data, you use a_rdb_CNV_charTIME(). For details about a_rdb_CNV_charTIME(), see 19.5.4 a_rdb_CNV_charTIME() (convert to TIME-type data).
To convert input data to TIMESTAMP-type data, you use a_rdb_CNV_charTIMESTAMP(). For details about a_rdb_CNV_charTIMESTAMP(), see 19.5.5 a_rdb_CNV_charTIMESTAMP() (convert to TIMESTAMP-type data).

To Page Top

(4) Specifying the dynamic parameters

You use a_rdb_SQLBindParams() to specify the dynamic parameters. When the dynamic parameters are specified successfully, the return value a_rdb_RC_SQL_SUCCESS is returned. The following shows an example of specifying dynamic parameters.

Example of dynamic parameter specification

/* Specify dynamic parameters */
rtnc = a_rdb_SQLBindParams(hCnct,
                           hStmt,
                           paramCount,       /* Number of dynamic parameters */
                           &(paramInfo[0]),  /* Area for specifying all dynamic parameters */
                           NULL) ;

For details about a_rdb_SQLBindParams(), see 19.4.4 a_rdb_SQLBindParams() (associate dynamic parameters).

To Page Top