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!



smyers2003Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.