Custom Type Handler question

[Ken Katsma <ke...@gmail.com>]

Hi,

My group is currently using stored procedures in Oracle for all our 
database access.  To get around the n+m problem we are considering 
several alternatives.  One we know will work is to do a user function 
for each column that maps to a "m" table that gives us the data in 
string format (name,value pairs).

Another approach, (which we would prefer) is to return the results for 
each "m" column as a cursor.  I can do this in java with code that looks 
something like this:

class RefCursor
{

public static void main (String args [])
throws SQLException, ClassNotFoundException
{
   String query  = "begin "+
                        "open ? for "+
                        "'select dname, CURSOR(select ename "+
                                                "from emp "+
                                               "where 
emp.deptno=dept.deptno) "+
                           "from dept'; "+
                    "end;";

   DriverManager.registerDriver
        (new oracle.jdbc.driver.OracleDriver());

   Connection conn=
       DriverManager.getConnection
       ("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
         "scott", "tiger");

   Statement trace = conn.createStatement();

   CallableStatement  cstmt = conn.prepareCall(query);

   cstmt.registerOutParameter(1,OracleTypes.CURSOR);
   cstmt.execute();

   ResultSet rset= (ResultSet)cstmt.getObject(1);

   for(int i = 0;  rset.next(); i++ )
   {
    System.out.println( rset.getString(1) );
    ResultSet rset2 = (ResultSet)rset.getObject(2);
    for( int j = 0; rset2.next(); j++ )
        System.out.println( "   " + rset2.getString(1) );
    rset2.close();

   }

   rset.close();
   cstmt.close();
}
}


To get the same functionality in iBatis we tried writing a custom type 
handler for the cursor column that looks like this:

import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.ResultGetterImpl;

/**
 * @author S001027
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class RefTypeHandler implements TypeHandlerCallback {

    /* (non-Javadoc)
     * @see 
com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#setParameter(com.ibatis.sqlmap.client.extensions.ParameterSetter, 
java.lang.Object)
     */
    public void setParameter(ParameterSetter arg0, Object arg1)
            throws SQLException {
        if (arg1 != null)
        {
            arg0.setObject(arg1);
        }
    }

    /* (non-Javadoc)
     * @see 
com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#getResult(com.ibatis.sqlmap.client.extensions.ResultGetter)
     */
    public Object getResult(ResultGetter arg0) throws SQLException {
        System.out.println("Object: " + 
arg0.getObject().getClass().getName());
        ResultSet result = (ResultSet) arg0.getObject();
        System.out.println("Iterating");
        while(result.next())
            System.out.println("test");
        result.close();
       
        return result;
    }

    /* (non-Javadoc)
     * @see 
com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#valueOf(java.lang.String)
     */
    public Object valueOf(String arg0) {
        // TODO Auto-generated method stub
        return null;
    }

Unfortunately, when the statement:

ResultSet result = (ResultSet) arg0.getObject();

executes, we get an error from oracle that says "FETCH OUT OF 
SEQUENCE".  Is there something with the cursor going on in iBatis that 
prevents us from touching the nested cursor?  I guess my next step is to 
start stepping through the iBatis code in debug to see whats happening, 
but I was hoping somebody may already have a solution or know the problem.

Thanks,

Ken