Preparing and Executing a Statement String with Parameters (Embedded SQL Guide)

Go Up to Method 2: Non-query Statements with Parameters (Embedded SQL Guide)

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:

1. 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 the BUDGET field and a value to be assigned to the LOCATION field.
2. Parse and name the statement string with PREPARE. The name is used in subsequent calls to DESCRIBE and EXECUTE:

   EXEC SQL
   PREPARE SQL_STMT FROM :str;
   

   SQL_STMT is the name assigned to the prepared statement string.
3. 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;
   

4. Compare the value of the sqln field of the XSQLDA to the value of the sqld field to make sure enough XSQLVARs are allocated to hold information about each parameter. sqln should be at least as large as sqln. If not, free the storage previously allocated to the input descriptor, reallocate storage to reflect the number of parameters specified by sqld, reset sqln and version, then execute DESCRIBE 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;
   }
   

5. 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 the XSQLVAR. 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.
   The following code example illustrates these steps, looping through each XSQLVAR structure in the in_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 and NULL indicators, see Coercing Data Types (Embedded SQL Guide).
6. Execute the named statement string with EXECUTE. Reference the parameters in the input XSQLDA with the USING SQL DESCRIPTOR clause. For example, the following statement executes a statement string named SQL_STMT:

   EXEC SQL
   EXECUTE SQL_STMT USING SQL DESCRIPTOR in_sqlda;