Bookmark and Share Share

Invoking Stored Procedures

The EMML statements that you use to directly invoke stored procedures depends on what the stored procedure does and the expected response:

  • Use the <sql> statement for stored procedures that execute queries or return a result set. You specify the SQL to invoke the stored procedure in the query attribute.

  • Use the <sqlUpdate> statement for stored procedures that update database records or do not return a result. You specify the SQL to invoke the stored procedure in the statement attribute.

The following example executes a store procedure that has one input parameter. The SQL to invoke the stored parameter is defined in the query attribute for <sql>:

... 
<!-- input parameter for stored procedure --> 
<input name="custId" type="string" /> 
<!-- data source for stored procedure -- > 
<datasource name="customerdb" url="jdbc:mysql://localhost:3306/mysql" 
    username="root" password="adminadmin" 
    driverclassname="com.mysql.jdbc.Driver" /> 
<!-- variable for result set --> 
<variable name="invoices" type="document" /> 
<!-- invoke stored procedure --> 
<sql name="customerdb" query="call sp_customer_invoices( :custId )" 
    outputvariable="$invoices" /> 
...

The next example shows a stored procedure that inserts a new record. The SQL to invoke the stored parameter is defined in the query attribute for <sqlUpdate>: 

... 
<!-- input parameters for stored procedure --> 
<input name="custId" type="string" /> 
<input name="customerName" type="string" /> 
<input name="invoices" type="string" /> 
<!-- data source for stored procedure -- > 
<datasource name="customerdb" url="jdbc:mysql://localhost:3306/mysql" 
    username="root" password="adminadmin" 
    driverclassname="com.mysql.jdbc.Driver" /> 
<!-- invoke stored procedure --> 
<sqlUpdate name="customerdb" 
    statement="call sp_customer_insert(:custId, :customerName, :invoices)" 
    outputvariable="$result" /> 
...

For more information on the attributes and requirements of these commands, see <sql> or <sqlUpdate>.