Executing a Procedure in a DSQL Application

To execute a stored procedure in a dynamic SQL (DSQL) application, follow these steps:

1. Use a PREPARE statement to parse and prepare the procedure call for execution, using the following syntax:

   EXEC SQL
   PREPARE sql_statement_name FROM :var | '<statement>';
   

2. Set up an input XSQLDA using the following syntax:

   EXEC SQL
   DESCRIBE INPUT sql_statement_name INTO SQL DESCRIPTOR input_xsqlda;
   

3. Use DESCRIBE OUTPUT to set up an output XSQLDA using the following syntax:

   EXEC SQL
   DESCRIBE OUTPUT sql_statement_name INTO SQL DESCRIPTOR
   output_xsqlda;
   

   Setting up an output XSQLDA is only necessary for procedures that return values.
4. Execute the statement using the following syntax:

   EXEC SQL
   EXECUTE statement USING SQL DESCRIPTOR input_xsqlda
   INTO DESCRIPTOR output_xsqlda;
   

Input parameters to stored procedures can be passed as run-time values by substituting a question mark (?) for each value. For example, the following DSQL statements prepare and execute the ADD_EMP_PROJ procedure:

. . .
strcpy(uquery, "EXECUTE PROCEDURE ADD_EMP_PROJ ?, ?");
. . .
EXEC SQL
PREPARE QUERY FROM :uquery;
EXEC SQL
DESCRIBE INPUT QUERY INTO SQL DESCRIPTOR input_xsqlda;
EXEC SQL
DESCRIBE OUTPUT QUERY INTO SQL DESCRIPTOR output_xsqlda;
EXEC SQL
EXECUTE QUERY USING SQL DESCRIPTOR input_xsqlda INTO SQL DESCRIPTOR
output_xsqlda;
. . .