Support for oracle cursors as resultsets
----------------------------------------
Key: IBATIS-53
URL: http://issues.apache.org/jira/browse/IBATIS-53
Project: iBatis for Java
Type: New Feature
Components: SQL Maps
Reporter: Ken Katsma
Priority: Minor
iBatis doesn't currently support result sets from functions in Oracle. A
modification to SQLExecutor as detailed below can add the necessary support.
However, it requires a hard-coded check for an Oracle driver. A better option
would be to supply a factory for alternate SQLExecutor's for different
dialects. This would allow for any future database specific customization as
well.
The code change is in SQLExecutor.executeQueryProcedure (see comments):
public void executeQueryProcedure(RequestScope request, Connection conn,
String sql, Object[] parameters,
int skipResults, int maxResults,
RowHandlerCallback callback)
throws SQLException {
ErrorContext errorContext = request.getErrorContext();
errorContext.setActivity("executing query procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
ResultSet rs = null;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
cs = conn.prepareCall(sql);
ParameterMap parameterMap = request.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext.setMoreInfo("Check the output parameters (register output
parameters failed).");
registerOutputParameters(cs, mappings);
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
parameterMap.setParameters(request, cs, parameters);
errorContext.setMoreInfo("Check the statement (update procedure failed).");
// ****************************************
// Code changes below
// ****************************************
if
(conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
{
// If in oracle then execute instead of executeQuery
boolean b = cs.execute();
errorContext.setMoreInfo("In Oracle query mode.");
errorContext.setMoreInfo("Check the output parameters (retrieval of
output parameters failed).");
// Get the output parameters first, instead of last
retrieveOutputParameters(cs, mappings, parameters);
// Then find the resultset and handle it
for (int i=0;i<parameters.length;i++)
{
if (parameters[i] instanceof ResultSet)
{
rs = (ResultSet) parameters[i];
break;
}
}
errorContext.setMoreInfo("Check the results (failed to retrieve
results).");
handleResults(request, rs, skipResults, maxResults, callback);
}
//****************************************
// Non-oracle..original code
else
{
errorContext.setMoreInfo("In non-Oracle mode.");
rs = cs.executeQuery();
errorContext.setMoreInfo("Check the results (failed to retrieve
results).");
handleResults(request, rs, skipResults, maxResults, callback);
errorContext.setMoreInfo("Check the output parameters (retrieval of
output parameters failed).");
retrieveOutputParameters(cs, mappings, parameters);
}
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(cs);
}
}
An example mapping looks like:
<parameterMap id="clientParameters" class="map" >
<parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
<parameter property="maxRows" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
</parameterMap>
<procedure id="getClientListProc" resultMap="clientResult"
parameterMap="clientParameters">
{?= call abc.CLIENT_VIEW_PKG.client_result_list_f(?)}
</procedure>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
