Re: [jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets

[Ken Katsma]

Would that still allow us to map an output parameter into a result map?

Brandon Goodin wrote:

The unofficial line on this is that we will make it possible to run
execute instead of execute Update.

Brandon

On Tue, 22 Mar 2005 19:46:04 -0600, Ken Katsma <[EMAIL PROTECTED]> wrote:

Brandon,

I haven't heard back from you on this.  Do you think this functionality
might be integrated in a near future release?  I've got a meeting with
23 development managers and enterprise architects tomorrow and they'll
probably be asking me about this feature in iBatis.  I was hoping to be
able to tell them it'll be in the main dev stream soon.  Do you see any
roadblocks to its eventual integration?

Thanks,

Ken

Brandon Goodin (JIRA) wrote:

   [ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_60696 ]

Brandon Goodin commented on IBATIS-53:
--------------------------------------

Let me see if i understand this correctly...

A cursor must be retrieved using a CallableStatement/Stored Procedure. The 
Callable Statement returns a ResultSet as an OUT parameter that you want to map 
to objects as normal with ibatis. Your current issue is that you need to 
cs.execute instead of cs.executeQuery because you do no expect a resultset 
directly back.

Is that correct?

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>