Aspire not only allows you to access and update data using stored procedures but also allows additional features to use stored procedures as the business layer. Here are a few things where and how stored procedures could be used in Aspire

1. You can use stored procedures to retrieve/update data in plain java programs
2. You can use the relational rows and columns returned by stored procedures to construct hierarchical data sets which will eventually be used to paint html screens or xml
3. You can use stored procedures in update pipelines to update a certain tables
4. You can use a stored procedure as a target of an action in a web page

But there are some patterns and caveats you have to follow to effectively use stored procedures in aspire. For example calling a stored procedure is somewhat dependent on a database. Calling a stored procedure in Oracle is different from calling a stored procedure in SQLServer. This article goes into some of these details.

Basic Idea of calling a stored procedure in Aspire

Let's first consider reading from a table using plain sql

request.getEmployeeInfo.classname=com.ai.db.DBRequestExecturo2
request.getEmployeeInfo.db=some-db
request.getEmployeeInfo.stmt=\
select * from employees_table \
where employee_id = {employeeId}

Let's assume we have a stored procedure in SQLServer that does this. Assume further the name of the procedure is

sp_getEmployeeInfo(employeeId)

You can alter the above aspire properties as follows

request.getEmployeeInfo.classname=com.ai.db.DBRequestExecturo2
request.getEmployeeInfo.db=some-db
request.getEmployeeInfo.stmt=sp_getEmployeeInfo({employeeId})

Calling an Oracle Stored Procedure

request.getEmployeeInfo.classname=com.ai.db.StoredProcedureExecutor2
request.getEmployeeInfo.db=some-db
request.getEmployeeInfo.stmt=\
call sp_getEmployeeInfo(?,{employeeId})

The component StoredProcedureExecutor2 is specific to Oracle. Also notice the syntactical difference of calling the oracle stored procedure. Oracle has special requirements for calling stored procedures. Inside of the Oracle stored procedure you will have to use REFCURSOR as your first argument to the procedure. This component supports only result sets that come back, and not individual variables. Read Oracle's JDBC documentation for writing stored procedures with REFCURSORS.

Returning individual values from stored procedures

Even if you want to return a single value from the stored procedure, to fit into the Aspire pattern, you have to return a cursor (same as a result set). This means depending on the database the mechanism of doing this may be different.

You can throw exceptions from Stored Procedures

Using stored procedures you can also raise exceptions. These exceptions can be used to redirect the user to a different web page. Read this document to see how exceptions effect redirections.

raise_application_error(-20001,"INVALID_EMPLOYEE:this is an invalid emloyee")

request.SomeUpdateRequest.classname=com.ai.db.StoredProcedureExecutor2
.... other definitions to call the proc

request.SomeUpdateRequest.redirectURL=\
/{aspireContext}/display?url=welcomePageURL&username={username}

request.SomeUpdateRequest.failureRedirectURL.INVALID_EMPLOYEE=\
/{aspireContext}/display?url=LoginPageURL\
&pageStatus=invalid-employee&vs={viewState}