Actually this is a case of a cursor returned within another cursor, so I haven't performed a fetch until I called result.next() below.
See the straight Java example I included to understand the nesting I'm trying to accomplish. Or perhaps there is something I'm not understanding about how getObject() works?
Thanks,
Ken
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
