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

["Ken Katsma (JIRA)" <ib...@incubator.apache.org>]

     [ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_60693 ]
     
Ken Katsma commented on IBATIS-53:
----------------------------------

Just wondering if this might make it into an upcoming release?

I'm having a really hard time selling this to my organization (JPMorgan) without support for Oracle stored procedure resultsets somewhere in the base code.

I'm still using it in my project (with the hack), but it could get a lot more use if it had this feature.  

Long term, if iBatis can't support Oracle ref cursors, it will really limit its adoption.

Thanks,

Ken


> 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