Executing a Procedure in a DSQL Application

From InterBase
Jump to: navigation, search


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;
. . .