ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ken Katsma <kenkat...@gmail.com>
Subject Re: Custom Type Handler question
Date Tue, 17 May 2005 00:54:50 GMT
Actually, I found my problem. It was me. A couple lines above I already made 
a call to getObject(). Which, of course counts as a fetch. When I made my 
second call to getObject it failed. No problem there for iBatis.


But this brings me to another issue which I'll post a new message on....

On 5/16/05, Sven Boden <Sven.Boden@pandora.be> wrote:
> 
> 
> Read it too quickly... so you have the problem before the parts I
> comment on. One further question: where do you set the "return type"
> as a cursor in your Ibatis version (the
> "cstmt.registerOutParameter(1,OracleTypes.CURSOR)" in the original
> version)?
> 
> The problem with cursor types is that they are not in the JDBC spec.
> Most database vendors support them but using their own db specific
> classes.
> 
> In general the "fetch out of sequence" in Oracle you usually get when:
> - You fetch from a closed cursor
> - You fetch from an open cursor after a commit.
> 
> My hunch here would be that Oracle gets confused because you don't
> seem to register your return value as cursor type.
> 
> Regards,
> Sven
> 
> 
> On Mon, 16 May 2005 06:49:24 -0500, you wrote:
> 
> >Sven,
> >
> >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 <kenkatsma@gmail.com> <kenkatsma@gmail.com>

> 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
> >>>
> 
>

Mime
View raw message