Preparing and Executing a Statement String with Parameters
From InterBase
Go Up to Method 2: Non-query Statements With Parameters
After an XSQLDA
is created for holding 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:
This statement string contains two parameters: a value to be assigned to the
char *str = "UPDATE DEPARTMENT SET BUDGET = ?, LOCATION = ?";
BUDGET
column and a value to be assigned to theLOCATION
column. - Declare and initialize a SQL statement handle, then allocate it with
isc_dsql_allocate()
:isc_stmt_handle stmt; /* Declare a statement handle. */ stmt = NULL; /* Set handle to NULL before allocation. */ . . . isc_dsql_allocate_statement(status_vector, &db1, &stmt);
- Parse the statement string with
isc_dsql_prepare()
. This sets the statement handle (stmt
) to refer to the parsed format. The statement handle is used in subsequent calls toisc_dsql_describe_bind()
andisc_dsql_execute
():isc_dsql_prepare(status_vector, &trans, &stmt, 0, str, 1, in_sqlda);
- Use
isc_dsql_describe_bind()
to fill the inputXSQLDA
with information about the parameters contained in the SQL statement:isc_dsql_describe_bind(status_vector, &stmt, 1, in_sqlda);
- Compare the value of the
sqln
field of theXSQLDA
to the value of thesqld
field to make sure enough XSQLVAR are allocated to hold information about each parameter.sqln
should be at least as large assqld
. If not, free the storage previously allocated to the input descriptor, reallocate storage to reflect the number of parameters specified bysqld
, resetsqln
andversion
, then executeisc_dsql_describe_bind()
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; isc_dsql_describe_bind(status_vector, &stmt, 1, in_sqlda); }
- Process each XSQLVAR parameter structure in the
XSQLDA
. Processing a parameter structure involves up to four steps:- a. Coerce a data type of a parameter (optional).
- b. Allocate local storage for the data pointed to by the
sqldata
field of the XSQLVAR. This step is only required if space for local variables is not allocated until runtime. The following example illustrates dynamic allocation of local variable storage space. - c. Provide a value for the parameter consistent with its data type (required).
- d. Provide a
NULL
value indicator for the parameter.
in_sqlda
XSQLDA
:For more information about data type coercion andfor (i=0, var = in_sqlda->sqlvar; i < in_sqlda->sqld; i++, var++) { /* Process each XSQLVAR parameter structure here. * Var points to the parameter structure. */ 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 */ /* allocate variable to hold NULL status */ if (sqltype & 1) { var->sqlind = (short *)malloc(sizeof(short)); } } /* end of for loop */
NULL
indicators, see Coercing Data Types. - Execute the named statement string with
isc_dsql_execute()
. For example, the following statement executes a statement string namedstmt
:isc_dsql_execute(status_vector, &trans, &stmt, 1, in_sqlda);