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

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

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

two things

1. in ibatis 2.2.0 my ref cursor handle is not called anymore. is there a reason for that?. 

2. in SqlExecutor, i see in 2.2.0 there's still an old bug in
v  private void retrieveOutputParameters(RequestScope request, CallableStatement cs, ParameterMapping[] mappings, Object[] parameters, RowHandlerCallback callback) throws SQLException {

the problem is at this line:
   ResultSet rs = (ResultSet) cs.getObject(i + 1);

what if the stored procedure did not put anything in the put ref cursor?. this line will cause a really nasty SQLException,

> Support for oracle cursors as resultsets
> ----------------------------------------
>
>                 Key: IBATIS-53
>                 URL: http://issues.apache.org/jira/browse/IBATIS-53
>             Project: iBatis for Java
>          Issue Type: New Feature
>          Components: SQL Maps
>            Reporter: Ken Katsma
>            Priority: Minor
>             Fix For: 2.1.0
>
>         Attachments: showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java
>
>
> 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
-
For more information on JIRA, see: http://www.atlassian.com/software/jira