• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5174
  • Last Modified:

JDBC - Need help getting a scrollable resultset!!!

Hello everyone,

I am having a problem with the following code, as I would like to have a scroll_insensitive resultset in order to use the resultset.getfirst() method:

.....

CallableStatement cs = myConnection.prepareCall("call MY PROC",ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                  
cs.registerOutParameter(1, OracleTypes.CURSOR);    
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
rs.beforeFirst(); --> This throws the following exception:

java.sql.SQLException: Invalid operation for forward only resultset : beforeFirst

Now, I am assuming that since I am declaring a new ResultSet object below where I declare my callablestatement, that this is the reason why my program thinks I have a forward-only resultset.
Can anyone suggest a way to make this resultset scroll_insensitive? I am drawing a blank here..
Thanks for any help!



0
smyers2003
Asked:
smyers2003
  • 7
  • 7
1 Solution
 
kiranhkCommented:
you need to have this

ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY

check this out

http://javaalmanac.com/egs/java.sql/MoveCursor.html
0
 
smyers2003Author Commented:
Sorry, Kiranhk, my mistake - I did have scroll_insensitive at first and posted the wrong code here. When I do use scroll_insensitive at the top, it still doesn't work. I guess I could ask this question, then - how could I just create a resultset object from an oraclecallablestatement? I think this line here is the culprit, and it is returning a forward only resultset:

ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
0
 
kiranhkCommented:
ok, try this

cs.getResultSet()
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
smyers2003Author Commented:
Hi Kiranhk,

If I do that, then, how would I make that resultset scroll_insensitive?

ResultSet rs = cs.getResultSet();
rs = ((OracleCallableStatement)cs).getCursor(1);

Thanks for your help,

Shawn
0
 
kiranhkCommented:
you need to use only one of them not both...

ResultSet rs = cs.getResultSet();
//rs = ((OracleCallableStatement)cs).getCursor(1);
0
 
smyers2003Author Commented:
Hi Kiranhk,

I think I need to use the line of code already in there:

ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);

The proc I am calling returns a refcursor. When I try to use the cs.getResultSet it keeps coming back with nullPointerExceptions.

Thanks,

Shawn

0
 
kiranhkCommented:
ok... do the previous one only but also post the stack trace and the full method code you are having..

//ResultSet rs = cs.getResultSet();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
0
 
smyers2003Author Commented:
Hi Kiranhk,

I am not getting much of a stack trace, even when I am catching everything with a throwable. I am connecting fine, etc. and the code is the same as originally listed:

CallableStatement cs = myConnection.prepareCall("call MY PROC",ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
               
cs.registerOutParameter(1, OracleTypes.CURSOR);    
cs.execute();

//ResultSet rs = cs.getResultSet();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);

//jump to first record
rs.beforeFirst();

The nullpointer exception is being thrown after the execute takes place if I use cs.getResultSet().

The error "java.sql.SQLException: Invalid operation for forward only resultset : beforeFirst" is being thrown if I use ResultSet rs = ((OracleCallableStatement)cs).getCursor(1); Either way, it looks as if I just need a way to set that damn rs as scrollable, eben though I am making the callablestatement scrollable.

The reason I want to get to the first record is that I am currently using rs.next (below the above code) to make my way through the recordset, and from my returned records it seems that I am always missing the very first record. Everything else works ok. I figured that by calling rs.beforeFirst() it would eliminate this problem, unless I am totally missing something obvious here.

Thanks




0
 
kiranhkCommented:
ok...
since beforeFirst will try to put put the cursor before the first row itself...

so instead of trying with rs.beforeFirst() try
rs.first()

then if there is any first row then you can check out your scrollable things...



0
 
smyers2003Author Commented:
;-) Yes, I already tried that a while ago before I posted here, but it had the same result:

java.sql.SQLException: Invalid operation for forward only resultset : first

This is frustrating! Should be a simple fix! Very strange and I have a feeling that this is something obvious that I am missing...



0
 
smyers2003Author Commented:
Increased the points grab....
0
 
kiranhkCommented:
ok, got the problem with this..
Oracle Ref Cursors are not scrollable... so you cannot do that it seems.

check out this documentation...


https://cwisdb.cc.kuleuven.ac.be/ora10doc/java.101/b10979/oraint.htm#i1063951
0
 
kiranhkCommented:
0
 
smyers2003Author Commented:
Kiranhk,

Thank you very much for all of your efforts - I figured out another solution. Maybe talk to you again on the board sometime!

Thanks

Shawn
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now