com.ibm.pdq.runtime
Interface StoredProcedureResult
public interface StoredProcedureResultProvides methods that can be used to retrieve the OUT and INOUT parameters of an SQL stored procedure call and also can be used to access its series of query results sequentially.
If a data source supports returning update counts from stored procedure calls and if accessing the update counts is
wanted, a user can write a custom implementation of CallHandlerWithParameters
that retrieves the update counts. A probable approach would be to call Statement.getUpdateCount()
for the instance of CallableStatement.
The following three methods in the interface control the StoredProcedureResult as a whole, navigate
through the multiple query results, and retrieve the OUT and INOUT parameters:
-
getOutputParms() - retrieves the OUT and INOUT parameters of the SQL stored procedure. The OUT and INOUT parameters are not
available after the
StoredProcedureResultinstance has been closed. It is a good idea to callgetOutputParms()before callinggetXXX()becausegetXXX()automatically closes theStoredProcedureResultinstance if it is called when there are no more query results. - Various
getXXX()methods - advances to the next (or first) query result, if one exists, and attempts to return its contents in the format
indicated by the
getXXX()method signature. For example,getArray()returns the content of the query result in an array in which each row is represented by aMap. andgetList(Class returnClass)returns the content of the query result in aListin which each row is represented by an instance ofreturnClass. If the query result does not exist, these methods returnnulland close theStoredProcedureResultinstance. -
moveToNext() - (deprecated) skips over the next (or first) query result and returns
trueif there is another query result after the skipped query result. Returnsfalseand closes theStoredProcedureResultinstance if there is not another query result after the skipped query result.moveToNext()can be used to skip a query result without materializing it. -
close() - closes the
StoredProcedureResultinstance.
Attention: Some JDBC drivers cannot have multiple query results from an SQL stored procedure call open at the
same time. When one of these JDBC drivers is used, calling a getXXX() method closes the query result
of the previous getXXX() method, if one is open. This means that if the previous getXXX()
method returned an Iterator, the ResultSet from which the Iterator retrieves rows is closed, and if
the Iterator.hasNext() method is called again for the Iterator, a
RuntimeException is thrown. If such a JDBC driver is being used, this problem can be avoided by
calling getXXX() methods that return query results in formats in which they they are
fully-materialized (such as arrays or Lists). This approach allows the next result to be retrieved
without affecting the already-returned query result, because it already has been retrieved in its entirety from the
database.
Given an instance of StoredProcedureResult, OUT and INOUT parameters first can be retrieved by using
getOutputParms(). Each result then can be retrieved by using a getXXX() method. Finally, the
StoredProcedureResult instance can be closed by calling close().
The following code provides an example of using StoredProcedureResult correctly:
storedProcedureResult =
dataObject.call("{call MyStoredProcedure(?,?,?)}",
parameter1,parameter2,parameter3);
// get the OUT and INOUT parameters
outAndInoutParameters =
storedProcedureResult.getOutputParms();
// do something with outAndInoutParameters
// get Iterators of EmployeeBean until no more results available (null returned)
while ((empBeanIterator =
storedProcedureResult.getIterator(EmployeeBean.class)) != null) {
// do something with empBeanIterator
}
// close the StoredProcedureResult
storedProcedureResult.close();
Method Summary
| Modifier and Type | Method and Description |
|---|---|
|
close()
Closes the
StoredProcedureResult instance.
|
getArray()
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a
Map.
|
|
|
getArray(Class<ROW> returnClass)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a pureQuery bean that is an instance of the class
<ROW>.
|
|
getArray(Class<ROW> returnClass,RowHandler<ROW> singleRowHandler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a an instance of the class
<ROW>.
|
|
getArray(RowHandler<ROW> singleRowHandler)
Deprecated.
|
getIterator()
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Iterator instance such that each call to the
Iterator.next() method of the instance provides the contents of a row of the query result in a
Map.
|
|
|
getIterator(Class<ROW> returnClass)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Iterator instance such that each call to the
Iterator.next()method of the Iterator instance provides the contents of a row of
the query result in a pureQuery bean that is an instance of the class <ROW>.
|
|
getIterator(RowHandler<ROW> singleRowHandler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Iterator instance such that each call to the
Iterator.next()method of the Iterator instance provides the contents of a row of
the query result in an instance of the class <ROW>.
|
getList()
|
|
|
getList(Class<ROW> returnClass)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in a
Listinstance such that each element in the List instance
contains the contents of a row of the query result in a pureQuery bean that is an instance of the class
<ROW>.
|
|
getList(RowHandler<ROW> singleRowHandler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in a
Listinstance such that each element in the List instance
contains the contents of a row of the query result in an instance of the class <ROW>.
|
|
getOutputParms()
Returns an
Object[] of OUT and INOUT parameters.
|
|
getQuery(ResultHandler<RES> handler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Object of type <RES>.
|
getResults()
Advances to the next (or first) query result for the stored procedure call, if one eists, and returns the contents
of the query result in an instance of
ResultSet.
|
|
|
moveToNext()
Deprecated.
|
Method Detail
close
void close()
getArray
Map<String,Object>[] getArray()
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a
Map. The keys in the Map are the labels of the columns from the row,
and the values in the Map are the values that are stored in the particular columns of that row. When
the value in a column is SQL NULL, the corresponding value in the Map has the value of
null. If a next (or first) query result does not exist, null is returned, and the
StoredProcedureResult instance is closed.
Returns:
an array if a next (or first) query result exists, where each element in the array contains the contents of
a row of the query result in a
Map. Returns null if a next (or first)
query result does not exist. getArray
<ROW> ROW[] getArray(Class<ROW> returnClass)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a pureQuery bean that is an instance of the class
<ROW>. When the value in a column
is SQL NULL, the corresponding property in the bean has the value of null. See the
pureQuery Runtime documentation for details about how pureQuery maps query results to pureQuery beans.
If a next (or first) query result does not exist, null is returned, and the
StoredProcedureResult instance is closed.
Type Parameters:
ROW - the generic type of the array to return. <ROW> is indicated by the generic type
of returnClass. Parameters:
returnClass - the class to use to represent each row of the query result in the returned array. This parameter
indicates the type that is represented by <ROW>. Returns:
an array if a next (or first) query result exists, where each element in the array contains the contents of
a row of the query result in a pureQuery bean that is an instance of the class
<ROW>.
Returns null if a next (or first) query result does not exist. getArray
<ROW> ROW[] getArray(Class<ROW> returnClass, RowHandler<ROW> singleRowHandler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a an instance of the class
<ROW>. pureQuery uses singleRowHandler
(specifically singleRowHandler.handle(ResultSet, Object)) to create each object of type <ROW>. If a next
(or first) query result does not exist, null is returned, and the StoredProcedureResult
instance is closed.
Type Parameters:
ROW - the generic type of the array to return. <ROW> is indicated by the generic type
of returnClass and singleRowHandler. Parameters:
returnClass - the class to use to represent each row of the query result in the returned array. This parameter
indicates the type that is represented by <ROW>. singleRowHandler - the RowHandler whose
singleRowHandler.handle(ResultSet, Object) method maps the contents of each row to an Object
of type <ROW> Returns:
an array if a next (or first) query result exists, where each element in the array contains the contents of
a row of the query result in an instance of the class
<ROW>. Returns
null if a next (or first) query result does not exist. getArray
@Deprecated <ROW> ROW[] getArray( RowHandler<ROW> singleRowHandler)
Deprecated.
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an array such that each element in the array contains the contents of a row of the query
result in a an instance of the class
<ROW>. pureQuery uses singleRowHandler
(specifically singleRowHandler.handle(ResultSet, Object)) to create each object of type <ROW>. If a next
(or first) query result does not exist, null is returned, and the StoredProcedureResult
instance is closed.
Type Parameters:
ROW - the generic type of the array to return. <ROW> is indicated by the generic type
of singleRowHandler. Parameters:
singleRowHandler - the RowHandler whose
singleRowHandler.handle(ResultSet, Object) method maps the contents of each row to an Object
of type <ROW>. Returns:
an array if a next (or first) query result exists, where each element in the array contains the contents of
a row of the query result in an instance of the class
<ROW>. Returns
null if a next (or first) query result does snot exist. getIterator
Iterator<Map<String,Object>> getIterator( )
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Iterator instance such that each call to the
Iterator.next() method of the instance provides the contents of a row of the query result in a
Map. The keys in the Map are the labels of the columns from the row, and the
values in the Map are the values that are stored in the particular columns of that row. When the
value in a column is SQL NULL, the corresponding value in the Map has the value of null.
If a next (or first) query result does not exist, null is returned, and the
StoredProcedureResult instance is closed.
Returns:
an
Iterator instance if a next (or first) query result exists, where each call to the
Iterator.next() method of the instance provides the contents of a row of the query result
in a Map. Returns null if a next (or first) query does not exist. getIterator
<ROW> Iterator<ROW> getIterator( Class<ROW> returnClass)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Iterator instance such that each call to the
Iterator.next() method of the Iterator instance provides the contents of a row of
the query result in a pureQuery bean that is an instance of the class <ROW>. When the value
in a column is SQL NULL, the corresponding property in the bean has the value of null. See the
pureQuery Runtime documentation for details about how pureQuery maps query results to pureQuery beans.
If a next (or first) query result does not exist, null is returned, and the
StoredProcedureResult instance is closed.
Type Parameters:
ROW - the generic type of the Iterator to return. <ROW> is indicated
by the generic type of returnClass. Parameters:
returnClass - the class to use to represent each row of the query result in the returned instance of
Iterator. This parameter indicates the type that is represented by
<ROW>. Returns:
an
Iterator instance if a next (or first) query result exists, where each call to the
Iterator.next() method of the Iterator instance provides the contents of a
row of the query result in a pureQuery bean that is an instance of the class <ROW>.
Returns null if a next (or first) query result does not exist. getIterator
<ROW> Iterator<ROW> getIterator( RowHandler<ROW> singleRowHandler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Iterator instance such that each call to the
Iterator.next() method of the Iterator instance provides the contents of a row of
the query result in an instance of the class <ROW>. pureQuery uses
singleRowHandler (specifically
singleRowHandler.handle(ResultSet,
Object)) to create each object of type <ROW>. If a next (or first) query result does not
exist, null is returned, and the StoredProcedureResult instance is closed.
Type Parameters:
ROW - the generic type of the Iterator to return. <ROW> is indicated
by the generic type of singleRowHandler. Parameters:
singleRowHandler - the RowHandler whose
singleRowHandler.handle(ResultSet, Object) method maps the contents of each row to an Object
of type <ROW> Returns:
an
Iterator instance if a next (or first) query result exists, where each call to the
Iterator.next() method of the Iterator instance provides the contents of a
row of the query result in an instance of the class <ROW>. Returns null
if a next (or first) query result does not exist. getList
List<Map<String,Object>> getList( )
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in a
List instance such that each element in the instance contains the
contents of a row of the query result in a Map. The keys in the Map are the
labels of the columns from the row, and the values in the Map are the values that are stored in the
particular columns of that row. When the value in a column is SQL NULL, the corresponding value in the
Map has the value of null. If a next (or first) query result does not exist,
null is returned, and the StoredProcedureResult instance is closed.
Returns:
getList
<ROW> List<ROW> getList(Class<ROW> returnClass)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in a
List instance such that each element in the List instance
contains the contents of a row of the query result in a pureQuery bean that is an instance of the class
<ROW>. When the value in a column is SQL NULL, the corresponding property in the bean has the
value of null. See the pureQuery Runtime documentation for details about how pureQuery
maps query results to pureQuery beans. If a next (or first) query result does not exist, null is
returned, and the StoredProcedureResult instance is closed.
Type Parameters:
ROW - the generic type of the List to return. <ROW> is indicated by
the generic type of returnClass. Parameters:
returnClass - the class to use to represent each row of the query result in the returned instance of
List. This parameter indicates the type that is represented by <ROW>. Returns:
a
List instance if a next (or first) query result exists, where each element in the
List instance contains the contents of a row of the query result in a pureQuery bean that is
an instance of the class <ROW>. Returns null if a next (or first)
query result does not exist. getList
<ROW> List<ROW> getList(RowHandler<ROW> singleRowHandler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in a
List instance such that each element in the List instance
contains the contents of a row of the query result in an instance of the class <ROW>.
pureQuery uses singleRowHandler (specifically
singleRowHandler.handle(ResultSet,
Object)) to create each object of type <ROW>. If a next (or first) query result does not
exist, null is returned, and the StoredProcedureResult instance is closed.
Type Parameters:
ROW - the generic type of the List to return. <ROW> is indicated by
the generic type of singleRowHandler. Parameters:
singleRowHandler - the RowHandler whose
singleRowHandler.handle(ResultSet, Object) method maps the contents of each row to an Object
of type <ROW> Returns:
a
List instance if a next (or first) query result exists, where each element in the
List instance contains the contents of a row of the query result in an instance of the class
<ROW>. Returns null if a next (or first) query result does not exist. getOutputParms
Object[] getOutputParms()
Returns an
Object[] of OUT and INOUT parameters. The size of the array is the number of parameter
markers in the executed SQL. The parameters are returned in the order that they are defined by the called stored
procedure. IN parameters are not returned, and they are represented by placeholders of Java null.
For example, suppose the following stored procedure is created and called. DB2 syntax is used in this example,
although the parameters are handled the same way for any database.
CREATE PROCEDURE SAMPLE.EXAMPLE_PROCEDURE
(IN parameter1 CHAR(8),
OUT parameter2 SMALLINT,
OUT parameter3 BIGINT)
LANGUAGE SQL
BEGIN
⁄* do something here *⁄
END
In this case, getOutputParms() returns an Object[] of size 3. The value of
Object[0] is null. Object[1] contains the updated value of
parameter2 and Object[2] contains the updated value of parameter3.
If getOutputParms() is called before the stored procedure call is executed or after the
StoredProcedureResult instance is closed, then a RuntimeException is thrown.
Returns:
an
Object[] of OUT and INOUT parameters. The parameters are returned in the order that they
are defined by the called stored procedure. Input parameters are not returned, and they are represented by
placeholders of Java null. getQuery
<RES> RES getQuery(ResultHandler<RES> handler)
Advances to the next (or first) query result for the stored procedure call, if one exists, and returns the contents
of the query result in an
Object of type <RES>. The actual processing of the
query result is done by the handler, and the Object that is returned is the
Object that is returned by
handler.handle(ResultSet). If a next (or
first) query result does not exist, null is returned, and the StoredProcedureResult
instance is closed.
Type Parameters:
RES - the generic type of the Object to return. <RES> is indicated by
the generic type of handler. Parameters:
handler - the ResultHandler<RES> whose
handler.handle(ResultSet) method
processes the current query result and returns the contents of the query result in an Object
of type <RES>. The Object that is returned by this method (
getQuery(ResultHandler)) is actually the Object that is returned by
handle(ResultSet). Returns:
an instance of
ResultSet if a next (or first) query result exists, that contains the
contents of the query result. Returns null if a next (or first) query result does not exist. getResults
ResultSet getResults()
Advances to the next (or first) query result for the stored procedure call, if one eists, and returns the contents
of the query result in an instance of
ResultSet. The returned ResultSet is
read-only. If a next (or first) query result does not exist, null is returned, null
is returned, and the StoredProcedureResult instance is closed.
Returns:
an instance of
ResultSet if a next (or first) query result exists, that contains the
contents of the query result. The returned ResultSet is read-only. Returns null
if a next (or first) query result does not exist. moveToNext
@Deprecated boolean moveToNext()
Deprecated.
Skips over the next (or first) query result of the stored procedure call and returns
true if there
is another query result after the skipped query result. Returns false and closes the
StoredProcedureResult instance if there is not another query result after the skipped query result.
moveToNext() can be used to skip a query result without materializing it.
Returns:
true if another query result exists after the skipped query result, and false
if another one does not exist
StoredProcedureResultinstance. This includes closing any associatedStatement.If
close()is called for aStoredProcedureResultinstance that is closed already, there are no changes or consequences. However, calling any other method of a closedStoredProcedureResultinstance results in aRuntimeException.