Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: ibatis-user-java@incubator.apache.org
Received-SPF: pass (hermes.apache.org: domain of
 grahamcruickshanks@hotmail.com designates 64.4.56.36 as permitted sender)
Message-ID: <BAY101-F264F78530DE32C5CA4EA9DD6160@phx.gbl>
In-Reply-To: <68ba8fae050516181160beec14@mail.gmail.com>
From: "Graham Cruickshanks" <grahamcruickshanks@hotmail.com>
To: kenkatsma@gmail.com, ibatis-user-java@incubator.apache.org
Cc: clinton.begin@gmail.com
Bcc: 
Subject: Re: iBATIS for Java 2.1.0 Released
Date: Tue, 17 May 2005 20:04:46 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

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 <kenkatsma@gmail.com>
>Reply-To: Ken Katsma <kenkatsma@gmail.com>
>To: ibatis-user-java@incubator.apache.org
>CC: clinton.begin@gmail.com
>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 <grahamcruickshanks@hotmail.com> 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 <clinton.begin@gmail.com>
> > >Reply-To: cbegin@ibatis.com
> > >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
> >
> >