Re: iBATIS for Java 2.1.0 Released

[Graham Cruickshanks]

The update for IBATIS to use .execute instead of .executeQuery sadly does 
not resolve the Oracle Cursor issue (IBATIS-53)

Bellow is an example of the issue with SqlExecutor problem in straight JDBC.
This shows how the SqlExecutor.java will not work without being adjusted to 
loop over the out parameters to see if a out parameter is a resultset for 
cursor compatiablity. IThe resultset needs to be retieved via the 
getObject(index) method. Oracle Driver problem I know but I predict hell 
freezing over before Oracle fix their driver, Weblogic's Oracle Driver and 
DataDirects Oracle drivers all map cursors to getResultSet.

Cheers
Graham

import java.sql.*;
import oracle.jdbc.*;
/**
* @author Graham Cruickshanks
*
*/
public class OracleJDBCTest {

    /**  Store Procedure Definition
        *
        *    CREATE OR REPLACE
        *     PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,
        *                     p_recordset OUT Types.cursor_type) AS
        *          BEGIN
        *             OPEN p_recordset FOR
        *                 SELECT ename,
        *                 empno,
        *                 deptno
        *                 FROM   emp
        *                 WHERE  deptno = p_deptno
        *                 ORDER BY ename;
        *     END GetEmpRS;
        *    /
        */

	public static void TestCursorViaGetObject() {
	    try {
	      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
	      Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", 
"scott", "tiger");
	      CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); 
END;");
	      stmt.setInt(1, 30); // DEPTNO
	      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
	      stmt.execute();

	      ResultSet rs = (ResultSet)stmt.getObject(2);
	      while (rs.next()) {
	        System.out.println(rs.getString("ename") + ":" + 
rs.getString("empno") + ":" + rs.getString("deptno"));
	      }
	      rs.close();
	      rs = null;
	      stmt.close();
	      stmt = null;
	      conn.close();
	      conn = null;
	    }
	    catch (SQLException e) {
	      System.out.println(e.getLocalizedMessage());
	    }
	 }

	public static void TestCursorViaGetResultSet() {
	    try {
	      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
	      Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", 
"scott", "tiger");
	      CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); 
END;");
	      stmt.setInt(1, 30); // DEPTNO
	      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
	      stmt.execute();

              ResultSet rs = stmt.getResultSet();
	      //NULL POINTER HERE, AKA IBATIS SqlExecutor.java
	      while (rs.next()) {
	        System.out.println(rs.getString("ename") + ":" + 
rs.getString("empno") + ":" + rs.getString("deptno"));
	      }
	      rs.close();
	      rs = null;
	      stmt.close();
	      stmt = null;
	      conn.close();
	      conn = null;
	    }
	    catch (SQLException e) {
	      System.out.println(e.getLocalizedMessage());
	    }

         }


    public static void main(String[] args){
                OracleJDBCTest.TestCursorViaGetObject();
                OracleJDBCTest.TestCursorViaGetResultSet();
        }
}

From: Ken Katsma <[EMAIL PROTECTED]>
Reply-To: Ken Katsma <[EMAIL PROTECTED]>
To: ibatis-user-java@incubator.apache.org
CC: [EMAIL PROTECTED]
Subject: Re: iBATIS for Java 2.1.0 Released
Date: Mon, 16 May 2005 20:11:23 -0500
Graham,
Oracle doesn't support the getResultSet call in it's JDBC drivers. Though 
it
does appear to be a step in the right direction :)

Ken
On 5/16/05, Graham Cruickshanks <[EMAIL PROTECTED]> wrote:
>
> Hi Clinton,
>
> Great news on the 2.1.0 release. I see in the change log "Changed SQL
> executor to always call .execute instead of .executeQuery and
> .executeUpdate", does this address bug IBATIS-53?
>
> Cheers
>
> Graham
>
> >From: Clinton Begin <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: ibatis-dev@incubator.apache.org,
> >"ibatis-user-java@incubator.apache.org"
> ><ibatis-user-java@incubator.apache.org>
> >Subject: iBATIS for Java 2.1.0 Released
> >Date: Mon, 16 May 2005 01:58:39 -0600
> >
> >Hi all....
> >
> >The subject says it all. 2.1.0 is now available.
> >
> >Change Log:
> http://sourceforge.net/project/shownotes.php?release_id=327667
> >Download: http://www.ibatis.com/downloads.html
> >
> >Cheers,
> >Clinton
>
>