OUT and INOUT parameters in StoredProcedureResult objects

When a StoredProcedureResult is returned, the value of any OUT or INOUT parameter returned by the stored procedure is in the Object[] that is returned by the StoredProcedureResult's getOutputParms() method.
StoredProcedureResult objects are returned by this version of the call() method of the Data interface:
StoredProcedureResult call (java.lang.String sql, Object... parameters)

Passing positional parameters

For example, consider a stored procedure defined to have two IN Integer parameters, one OUT Float, and no returned query results. You could invoke it with logic like this:

Integer one = new Integer(1);                                                      1 
Integer three = new Integer(3);
Connection con = DriverManager.getConnection(...);                                 2 
Data db = DataFactory.getData(con);                                                3 

StoredProcedureResult spr = db.call("CALL getRatio(?, ?, ?)", one, three, null);   4 
Object[] outputs = spr.getOutputParms();                                           5 
Float oneThird = outputs[2]; // outputs is Object[3]                               6 
spr.close();   // best practice                                                    7 

The code performs the following steps:

  1. Declare and initialize two variables to use for the IN parameters.
  2. Create a connection to the database.
  3. Create an instance of the implementation of the Data interface.
  4. Call the Data.call() method, running the CALL statement that passes in the values of the IN parameters and storing the result in an implementation of StoredProcedureResult.
  5. Store the values of the INOUT and OUT parameters in an Object[] array. IN parameters are not placed in the array and have a NULL placeholder
  6. Store the value of the OUT parameter in a Float object. Note that the value of the OUT parameter is the third object in the array because the first two objects are the values of the IN parameters.
  7. Close and free any resources that are associated with StoredProcedureResult spr.

The question marks are positional parameter markers. The first corresponds to the parameter Integer one, the second to the parameter Integer three, and the last to the parameter null. The null value is a placeholder for the value of the OUT parameter of the stored procedure.

For another example, suppose that you had only partial information regarding the SQL method getRatio(). If you did not know that the getRatio() method returns no query results, and did not know whether any of its IN parameters were also OUT parameters, the logic might look more like this:

Integer one = new Integer(1);                                                     1 
Integer three = new Integer(3);
Connection con = DriverManager.getConnection(...);                                2 
Data db = DataFactory.getData(con);                                               3 

StoredProcedureResult spr = db.call("CALL getRatio(?, ?, ?)", one, three, null);  4 
Object[] outputs = spr.getOutputParms();                                          5 
for (int j = 0; j < outputs.length; j++)                                          6 
 System.out.println(j + ": " + outputs[j]); 

 while (spr.moveToNext()) {                                                       7 
      ResultSet rsPtr = spr.getResults();
      ...                                                                         8 
   }
}

spr.close();                                                                      9 

The code performs the following steps:

  1. Declare and initialize two variables to use for the IN parameters.
  2. Create a connection to the database.
  3. Create an instance of the implementation of the Data interface.
  4. Call the Data.call() method, running the CALL statement that passes in the values of the IN parameters and storing the result in an implementation of StoredProcedureResult.
  5. Store the values of the INOUT and OUT parameters in an Object[] array. IN parameters are not placed in the array and have a NULL placeholder
  6. Print the values in the Object[] array.
  7. If the stored procedure returned query results, close the current returned ResultSet, if any, and move to the next ResultSet.
  8. Process the current ResultSet that is referred to by rsPtr..
  9. Close and free any resources that are associated with StoredProcedureResult spr.

Passing parameters in beans

When the parameters are passed to Data.call() method in either a bean or Map object, the values of the stored procedure's returned OUT and INOUT parameters appears in the Object[] array and are used to update the given bean or Map object.

For example, imagine a bean, Ratio, with properties numerator, denominator, and ratio.

Integer one = new Integer(1);                                   1 
Integer three = new Integer(3);
Ratio rValue = new Ratio(one, three);                           2 
Connection con = DriverManager.getConnection(...);              3 
Data db = DataFactory.getData(con);                             4 

StoredProcedureResult spr = db.call(
  "CALL getRatio(:numerator, :denominator, :ratio)", rValue);   5 
Float oneThird = rValue.getRatio();                             6 
spr.close();                                                    7 

In this example, the use of the :name parameter markers indicates that there is only one parameter, which is the bean although it could as easily have been a Map object.

The code performs the following steps:

  1. Declare and initialize two variables, one and three.
  2. Declare and initialize a reference to a new Ratio bean rValue, passing one and three to the constructor to provide the values of numerator and denominator.
  3. Create a connection to the database.
  4. Create an instance of the implementation of the Data interface.
  5. Call the Data.call() method, running the CALL statement that passes in rValue. The properties numerator and denominator are the IN parameters, and ratio is the OUT parameter. The CALL statement updates the value of ratio.
  6. Store the value of ratio in the new Float oneThird.
  7. Close and free any resources that are associated with StoredProcedureResult spr.

Passing parameters in map objects

Imagine that a stored procedure is invoked by passing in values that correspond to a DeptProject bean's properties for a project ID and a new end date for the project. The OUT parameter is an updated staff level that is needed for that department project.

One might not develop a stored procedure for just that purpose, but minor projects are contained by major projects, so an update to the end date for a single project could ripple across multiple containing projects, and that sort of cascaded operation is something stored procedures are suited for. The parameter passed to the call() method could be a DeptProject object, but it is not a good practice to use objects that one never intends to fully populate or use as one would use a fully populated instance. A Map object is a better choice for this sort of use.

In this example, a call to this stored procedure might look like this:

Map<String, Object> slipMap;                                             1 

//...                                                                    2 

Connection con = DriverManager.getConnection(...);                       3 
Data db = DataFactory.getData(con);                                      4 

StoredProcedureResult spr = db.call(
  "CALL newEndDate(":projectId, :endDate, :staffLevel)", slipMap );      5 
spr.close();                                                             6 

The code performs the following steps:

  1. Declare the new Map slipMap.
  2. Set projectId and endDate in slipMap.
  3. Create a connection to the database.
  4. Create an instance of the implementation of the Data interface.
  5. Call the Data.call() method, running the CALL statement that passes in slipMap. The properties projectId and endDate are the IN parameters, and staffLevel is the OUT parameter. The CALL statement updates the value of staffLevel.
  6. Close and free any resources that are associated with StoredProcedureResult spr.

Feedback