Using StoredProceduresBlox

StoredProceduresBlox is the starting point for using relational database stored procedures. It allows you to create a connection to a database and prepare a stored procedure statement. Once the correct IBM^® Alphablox data source and any other connection parameters are set, you can:

use the prepare(...) method to return a JDBC CallableStatement object, which can be used to set up any stored procedure parameters necessary to execute the stored procedure
use the getStoredProcedure() method to access the current StoredProcedure object; you can then execute the stored procedure, get to the ResultSet of the executed stored procedure, or access the JDBC ResultSet
use the getStoredProcedures() or getStoredProcedures(...) methods to return one or more MetaData objects that give you access to the individual parameters

The StoredProcedure object and the MetaData object are separate classes in the com.alphablox.blox.data.rdb.storedprocedure package. By having separate objects for StoredProcedure and MetaData from StoredProceduresBlox, you can prepare a stored procedure once and then execute it multiple times. Even though stored procedure parameters can be altered between executions, you can enhance the performance by not preparing the stored procedures at every execution.

The following diagram shows the object hierarchy of stored procedure related objects.

Because the StoredProcedure and MetaData objects are in a separate package, you must use the following JSP import statement at the beginning of any JSP file to use any of the APIs in these objects:

<%@ page import="com.alphablox.blox.data.rdb.storedprocedure.*" %>

Note: JDBC Stored procedures are supported for IBM DB2^®, Sybase, Oracle, and Microsoft^® SQL Server databases.

Note the following when using the StoredProcedure object to execute a prepared stored procedure:

If a DataBlox is used to display information from a stored procedure, the DataBlox must be separately connected to the same data source as StoredProceduresBlox.
If a DataBlox is used to display information from a stored procedure and the stored procedure also has output parameters, the result set must first be used before getting the output parameters. This is a JDBC restriction.
If the stored procedure has input and output parameters, you should use StoredProceduresBlox.prepare(...) to get the JDBC CallableStatement object. This object allows you to get and set input and output parameters on the stored procedure.
Once the stored procedure has been executed and any output parameters or result sets are used, you need to call the StoredProceduresBlox.disconnect() to disconnect and free up any resources. If you want to keep the connection to the data base open, call StoredProceduresBlox.close() to free up any resources used.
If a DataException is thrown, extra information might be available as a SQLException by looking at DataException.getNestedException().

Once the stored procedure is executed, it returns a StoredProcedure.ResultSet object, which gives you access to the JDBC ResultSet object. If you need to use the JDBC ResultSet object directly, use the ResultSet.getResultSet() method to get to this object.

It is recommended that you also import the java.sql package when working with stored procedures, so your JSP files should import two packages:

<%@ page import="com.alphablox.blox.data.rdb.storedprocedure.*" %>
<%@ page import="java.sql.*" %>