Errors on using Oracle SQLJ in WebSphere 6

From: WHY <why910_at_gmail.com>
Date: 3 Aug 2006 20:52:43 -0700
Message-ID: <1154663563.632310.26250@h48g2000cwc.googlegroups.com>

[Environment]

IDE: IBM Rational Software Development Platform 6.0.1 AP Server: WebSphere 6.0.0.0
JDBC Driver: Oracle JDBC Driver version 10.2.0.1.0 SQLJ: Oracle SQLJ Release 10.2.0.1.0 Production Web Technology: Java Server Faces

[Problem Description]

Precondition 1: Retrieve connection from WebSphere JDBC Settings (of Oracle JDBC Driver XA DataSource) and set for Default Context

     e.g.
     Context ic = new InitialContext();
     DataSource ds = (DataSource) ic.lookup(dataSourceName);
     java.sql.Connection con = ds.getConnection();
     DefaultContext.setDefaultContext(new DefaultContext(con));

Precondition 2: My SQLJ iterator implements Scrollable with sensitive.

     e.g. #sql public static iterator PbmIter implements sqlj.runtime.Scrollable with
(sensitivity=sqlj.runtime.ResultSetIterator.SENSITIVE) ( String ,String ,String ,String ,String ) ;

Precondition 3: My function returns the ResultSet object obtaining from Iterator.getResultSet().

Case1: No translation option

     When my program runs to any sqlj statements, it throws an exception as below:

     java.lang.IllegalArgumentException: SQLJ programs translated with the option -codegen=oracle must be run under Oracle JDBC 9.0.0 or higher. If the Oracle JDBC driver is wrapped, then the wrapper must implement all of the oracle.jdbc.OracleXxxx interfaces. Alternatively, you can translate SQLJ programs either with -codegen=iso.

Case2: -codegen=iso

     I can get the ResultSet object without previous error.
     BUT
     if I use SELECT statement with slightly complex WHERE clause (many

column conditions), the ResultSet will throw ArrayIndexOutOfBoundsException when the ResultSet points to any position exceeding 10 records.

     BUT
     this condition won't happend on no WHERE clause situation or while

result set containing less then 11 records.

Example SELECT statement:
#sql iter = { SELECT CUSTID, ACCOUNT, TYPE, BUSINESS, ENTERPRISE FROM PBM
WHERE ( CUSTID >= :custidBefore AND CUSTID <= :custidAfter OR CUSTID is NULL )
AND ( ACCOUNT >= :accountBefore AND ACCOUNT <= :accountAfter OR ACCOUNT is NULL )
AND ( TYPE >= :typeBefore AND TYPE <= :typeAfter OR TYPE is NULL ) AND ( BUSINESS >= :businessBefore AND BUSINESS <= :businessAfter OR BUSINESS is NULL )
AND ( ENTERPRISE >= :enterpriseBefore AND ENTERPRISE <= :enterpriseAfter OR ENTERPRISE is NULL ) ORDER BY CUSTID, ACCOUNT, TYPE, BUSINESS, ENTERPRISE };

Any help or suggestions are deeply appreciated. Thanks. Received on Thu Aug 03 2006 - 22:52:43 CDT