Executing a Procedure in a DSQL Application
From InterBase
To execute a stored procedure in a dynamic SQL (DSQL) application, follow these steps:
- 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>';
- Set up an input
XSQLDA
using the following syntax:EXEC SQL DESCRIBE INPUT sql_statement_name INTO SQL DESCRIPTOR input_xsqlda;
- Use
DESCRIBE OUTPUT
to set up an outputXSQLDA
using the following syntax:EXEC SQL DESCRIBE OUTPUT sql_statement_name INTO SQL DESCRIPTOR output_xsqlda;
Setting up an outputXSQLDA
is only necessary for procedures that return values. - 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; . . .