Executing Table Procedures

The Host Integrator JDBC connector directly accesses the table layer of a given model. When processing an SQL statement, the Host Integrator Server analyzes the statement and decides the most appropriate table procedure to perform. It is possible to perform a procedure directly using the CallableStatement JDBC class. In the Host Integrator driver, the only purpose for the CallableStatement class is to provide access to the predefined procedure that exists in the connected model.

The format for preparing a callable statement is always the same for Host Integrator:

CallableStatement cstmt = myConnection.prepareCall("{call <procedureName>(?,?,?,?,?,?)}");

Where <procedureName> is the name of the procedure in the Host Integrator model. The parameters always have a fixed meaning, as follows:

1 table name a java.lang.String that contains the Host Integrator table which defines the procedure (required and may not be null).
2 data input values a java.util.Map object that contains any data input name-value pairs for the procedure (not required and may be null).
3 filter values a java.util.Map object that contains any filter name-value pairs for the procedure (not required and may be null).
4 filter is case sensitive flag a Boolean object that sets whether or not the filter should be case sensitive (not required and may be null).
5 output columns a java.util.List object that contains a list of java.lang.String objects, each of which is a column to return in the output result. The default is to return all output columns defined for the procedure (not required and may be null).
6 maximum rows an integer that set the maximum number of rows to return in the result The default is to return all available rows, which is the same as setting this parameter to 0 (not required).

For String parameters, use setString(); for Map, List and Boolean parameters, use setObject; and for the integer parameter (maximum rows), use setInt().

An example

To call the CompoundNameSearch procedure of the Accounts table in the CICSAccts model, the Java implementation would look like this:

CallableStatement cstmt = myConnection.prepareCall("{call CompoundNameSearch(?,?,?,?,?,?)}");

cstmt.setString(1, "Accounts");		// table name

HashMap filter = new HashMap();
filter.put("LastName", "W");
cstmt.setObject(3, filter);		// filter

cstmt.setObject(4, new Boolean(true));	// filter is case sensitive flag

List outputCols = new Vector();
outputCols.add("AcctNum");
outputCols.add("LastName");
outputCols.add("FirstName");
outputCols.add("MiddleInitial");
cstmt.setObject(5, outputCols);          // output columns

cstmt.setInt(6, 5);                  	 // maximum rows

ResultSet rs = cstmt.executeQuery();

 

Related Topics
Bullet SQL Overview
Bullet Tables Overview
Bullet Procedures Overview