Ken,
To get back to the original question, the problem you have lies in the
part:
public Object getResult(ResultGetter arg0) throws SQLException {
System.out.println("Object: " +
arg0.getObject().getClass().getName());
ResultSet result =3D (ResultSet) arg0.getObject();
System.out.println("Iterating");
while(result.next())
System.out.println("test");
result.close();
return result;
}
1) You already fetch the result in the method above (for debugging
purposes probably), so by the time you want to really extract the
data, the data has already been fetched. This is the explanation of
the Oracle error "ORA-01002 fetch out of sequence" (have a look at the
documentation for it).
Cursor are for single use... if you used a cursor, you have reexecute
the query to be able to fetch again
2) You close result before returning it???
Regards,
Sven
On Sun, 15 May 2005 20:00:28 -0600, you wrote:
>I'm not really sure what the problem could be. It should work fine.
>
>Is there no way to just join the data and return a set of repeating groups
>and use the iBATIS N+1 Selects solution?
>
>http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem%3F
>
>Cheers,
>Clinton
>
>
>On 5/15/05, Ken Katsma <[EMAIL PROTECTED]> wrote:
>>
>> 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.OracleDrive r());
>>
>> 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
>>
