ibatis-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "vishakha sawant (JIRA)" <ibatis-...@incubator.apache.org>
Subject [jira] Commented: (IBATIS-53) Support for oracle cursors as resultsets
Date Wed, 12 Jul 2006 08:46:33 GMT
    [ http://issues.apache.org/jira/browse/IBATIS-53?page=comments#action_12420559 ] 

vishakha sawant commented on IBATIS-53:
---------------------------------------

Hi
We are using a patch version iBatis for REF CURSOR. We are using legacy database
we are getting exception "Cursor is closed" while executing a procedure.

stack trace of that exception is
java.sql.SQLException : Cursor is closed.
    at oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:271)
    at oracle.jdbc.driver.ResultSetAccessor.getObject(ResultSetAccessor.java:94)
    at oracle.jdbc.driver.OracleCallableStatement.getObject (OracleCallableStatement.java:1368)
    at test.TestSPA.main(TestSPA.java:95)

After doing some R&D I found that the if cursor is 'OUT' parameter and that is not opened
in procedure, it will result in this exception. According to stack trace I found that in retrieveOutputParameters()
method of  com.ibatis.sqlmap.engine.execution.SqlExecutor.java  cs.getObject(i+1)  is causing
this exception. For a time being we are catching that exception so that we can retrieve other
OUT values from procedure. Is there any other way to resolve this issue?
It's urgent.

Regards,

Vishakha Sawant

> 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
>      Fix For: 2.1.0
>  Attachments: SqlExecutor.java, SqlExecutor.java, SqlExecutor.java, SqlExecutor.java,
showcase.txt, showcase_storedprocedure.txt, showcase_storedprocedure1.txt
>
> 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


Mime
View raw message