Method 2: Non-query Statements with Parameters (Embedded SQL Guide)
Go Up to DSQL Programming Methods (Embedded SQL Guide)
Contents
There are two steps to process a SQL statement string containing a non-query statement with placeholder parameters:
- Creating an input
XSQLDA
to process the parameters of a statement string. - Preparing and executing the statement string with its parameters.
Creating the Input XSQLDA (Embedded SQL Guide)|Creating the Input XSQLDA
Placeholder parameters are replaced with actual data before a prepared SQL statement string is executed. Because those parameters are unknown when the statement string is created, an input XSQLDA
must be created to supply parameter values at execute time. To prepare the XSQLDA
, follow these steps:
- Declare a variable to hold the
XSQLDA
needed to process parameters. For example, the following declaration creates anXSQLDA
calledin_sqlda
:XSQLDA *in_sqlda;
- Optionally declare a variable for accessing the
XSQLVAR
structure of theXSQLDA
:XSQLVAR *var;
Declaring a pointer to theXSQLVAR
structure is not necessary, but can simplify referencing the structure in subsequent statements. - Allocate memory for the
XSQLDA
using theXSQLDA_LENGTH
macro. The following statement allocates storage forin_sqlda
:in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(10));
In this statement space for 10XSQLVAR
structures is allocated, allowing the XSQLDA to accommodate up to 10 parameters. - Set the
version
field of theXSQLDA
toSQLDA_CURRENT_VERSION
, and set thesqln
field to indicate the number ofXSQLVAR
structures allocated:in_sqlda_version = SQLDA_CURRENT_VERSION; in_sqlda->sqln = 10;
Preparing and Executing a Statement String with Parameters
After an XSQLDA is created for holding the parameters of a statement string, the statement string can be created and prepared. Local variables corresponding to the placeholder parameters in the string must be assigned to their corresponding sqldata
fields in the XSQLVAR
structures.
To prepare and execute a non-query statement string with parameters, follow these steps:
- Elicit a statement string from the user or create one that contains the SQL statement to be processed. For example, the following statement creates a SQL statement string with placeholder parameters:
char *str = "UPDATE DEPARTMENT SET BUDGET = ?, LOCATION = ?";
This statement string contains two parameters: a value to be assigned to theBUDGET
field and a value to be assigned to theLOCATION
field. - Parse and name the statement string with
PREPARE
. The name is used in subsequent calls toDESCRIBE
andEXECUTE
:EXEC SQL PREPARE SQL_STMT FROM :str;
SQL_STMT
is the name assigned to the prepared statement string. - Use
DESCRIBE INPUT
to fill the input XSQLDA with information about the parameters contained in the SQL statement:EXEC SQL DESCRIBE INPUT SQL_STMT USING SQL DESCRIPTOR in_sqlda;
- Compare the value of the
sqln
field of the XSQLDA to the value of thesqld
field to make sure enoughXSQLVARs
are allocated to hold information about each parameter.sqln
should be at least as large assqln
. If not, free the storage previously allocated to the input descriptor, reallocate storage to reflect the number of parameters specified bysqld
, resetsqln
andversion
, then executeDESCRIBE INPUT
again:if (in_sqlda->sqld > in_sqlda->sqln) { n = in_sqlda->sqld; free(in_sqlda); in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(n)); in_sqlda->sqln = n; in_sqlda->version = SQLDA_CURRENT_VERSION; EXEC SQL DESCRIBE INPUT SQL_STMT USING SQL DESCRIPTOR in_sqlda; }
- Process each
XSQLVAR
parameter structure in the XSQLDA. Processing a parameter structure involves up to four steps:- Coercing the data type of a parameter (optional).
- Allocating local storage for the data pointed to by the
sqldata
field of theXSQLVAR
. This step is only required if space for local variables is not allocated until run time. The following example illustrates dynamic allocation of local variable storage space. - Providing a value for the parameter consistent with its data type (required).
- Providing a
NULL
value indicator for the parameter.
XSQLVAR
structure in thein_sqlda
XSQLDA:for (i=0, var = in_sqlda->sqlvar; i < in_sqlda->sqld; i++, var++) { /* Process each XSQLVAR parameter structure here. The parameter structure is pointed to by var.*/ dtype = (var->sqltype & ~1) /* drop NULL flag for now */ switch(dtype) { case SQL_VARYING: /* coerce to SQL_TEXT */ var->sqltype = SQL_TEXT; /* Allocate local variable storage. */ var->sqldata = (char *)malloc(sizeof(char)*var->sqllen); . . . break; case SQL_TEXT: var->sqldata = (char *)malloc(sizeof(char)*var->sqllen); /* Provide a value for the parameter. */ . . . break; case SQL_LONG: var->sqldata = (char *)malloc(sizeof(long)); /* Provide a value for the parameter. */ *(long *)(var->sqldata) = 17; break; . . . } /* End of switch statement. */ if (var->sqltype & 1) { /* Allocate variable to hold NULL status. */ var->sqlind = (short *)malloc(sizeof(short)); } } /* End of for loop. */
For more information about data type coercion andNULL
indicators, see Coercing Data Types (Embedded SQL Guide). - Execute the named statement string with
EXECUTE
. Reference the parameters in the input XSQLDA with theUSING SQL DESCRIPTOR
clause. For example, the following statement executes a statement string namedSQL_STMT
:EXEC SQL EXECUTE SQL_STMT USING SQL DESCRIPTOR in_sqlda;
Re-executing the Statement String
Once a non-query statement string with parameters is prepared, it can be executed as often as required in an application. Before each subsequent execution, the input XSQLDA can be supplied with new parameter and NULL indicator data.
To supply new parameter and NULL indicator data for a prepared statement, repeat steps 3–5 of Preparing and Executing a Statement String with Parameters, in this chapter.