One thing it can't seem to do right now though is support Oracle result sets from functions. If there is a way to do so without modifying the code let me know. I went in and modified SQLExecutor to support the functionality I need. This is a really bad hack, and it would be great if we could specify our own SQLExecutor in the xml file to implement database specific code like this in a more elegant fashion. But this works, and all you have to do is modify the SQLExecutor like this:
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).");
if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
{
boolean b = cs.execute();
errorContext.setMoreInfo("In Oracle query mode.");
errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
retrieveOutputParameters(cs, mappings, parameters);
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);
}
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);
}
}
If you stick this above the base iBatis libraries, you can now get the results out.
No big deal in the xml file:
<typeAlias alias="Client" type="com.test.vo.ClientVO" />
<resultMap id="clientResult" class="Client">
<result property="clntSeq" columnIndex="1" />
<result property="clntName" columnIndex="2" />
<result property="clntSubType" columnIndex="3" />
<result property="clntCode" columnIndex="4"/>
<result property="clntMailName" columnIndex="5" />
<result property="clntLOBCode" columnIndex="6" />
<result property="clntCompanyID" columnIndex="7" />
<result property="clntCostCenter" columnIndex="8" />
</resultMap><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>
Hope nobody get's offended by the ugliness of this hack, but we really need the functionality as all queries are sp's in my project (no choice).
Thanks,
Ken
SqlExecutor.jar
Description: Binary data
