Thanks for the notice Clinton. As long as I can say that the
functionality will be added in a future release, I think people will
accept my work-around for the interim. Again, thanks for communicating.
Ken
Clinton Begin wrote:
Once we change to using execute() vs. executeQuery() (and
executeUpdate() for consistency), it should become easy to use a
custom type handler to deal with ref cursors.
Unfortuntately, there's no ETA on this. Larry said he'd start on it
soon, but he's currently in Brazil (maybe speaking to the Brazillian
JUG as I type this!).
I'm sorry to anyone who has to deal with Oracle ref cursors.
IIRC there are other ways to handle this. I seem to recall others who
have wrapped their procs in an outer procedure that returns a result
set. Maybe even a single proc that can wrap and execute any other
proc.
Cheers,
Clinton
On Thu, 24 Mar 2005 13:28:27 +0100 (CET), Fabio Grassi (JIRA)
<ibatis-dev@incubator.apache.org> wrote:
[ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_61482
]
Fabio Grassi commented on IBATIS-53:
------------------------------------
This issue is critical for the adoption of SqlMaps in my organization
(Assicurazioni Generali). We access the RDBMS (Oracle) exclusively through
stored procedures. Support for Oracle ref cursors both as function result and
as out parameter is fundamental. Thanks in advance, Fabio.
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
