aspose file tools*
The moose likes JDBC and the fly likes CallableStatements and Scrolling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CallableStatements and Scrolling" Watch "CallableStatements and Scrolling" New topic
Author

CallableStatements and Scrolling

Kevin Conaway
Ranch Hand

Joined: Jan 27, 2006
Posts: 57
Hi,

I am using stored procedures with an Oracle database to return cursors. However, the cursors that it returns are always TYPE_FORWARD_ONLY. I am trying to set the type to be TYPE_SCROLL_INSENSTIVE, but it does not seem to be working.

Here is the sample code I am using



In the above example, rs is always TYPE_FORWARD_ONLY. Anyone know how to fix this?

Thanks,

Kevin
[ March 13, 2006: Message edited by: Kevin Conaway ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
I don't know how to fix this, mostly because using Oracle scrollable ResultSets are implemented by caching the entire ResultSet in Java memory; the ResultSets my application has are often too big for that. Also, last I checked, ResultSet.last() transfered every row of a scrollable result to get to the end.

See:
http://download-east.oracle.com/docs/cd/B19306_01/java.102/b14355/resltset.htm#CIHCHBJB
Kevin Conaway
Ranch Hand

Joined: Jan 27, 2006
Posts: 57
Hi Stu,

Thanks for getting back to me.

How do you handle large result sets with oracle? I was hoping to be able to index or page into the result set to retrieve the items I wanted. I definitely do not want to read every row sequentially to get to where I want.

Thanks,

Kevin
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333


How do you handle large result sets with oracle?


First, a ResultSet should only contain rows you want; if you only want a page of data, only select a page of data - it's much much faster.
http://asktom.oracle.com/pls/ask/f?p=4950:8:2727786998497022568::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:76812348057
http://asktom.oracle.com/pls/ask/f?p=4950:8:2727786998497022568::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064
http://asktom.oracle.com/pls/ask/f?p=4950:8:2727786998497022568::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:20663081751269

Second, a well-designed web application should be able to handle display of an infinitely large ResultSet; the limit should be the memory of the client's browser and the client's patience, not some arbitrary limit in your code or the memory limit of your JVM. This is accomplished by "streaming" the data through the application; data is read, processed, and output to the response, row by row, with only buffered chunks of the ResultSet and the response in memory at once, not all of either of them. (A few J2EE servers do not normally stream the response, requiring a periodic flush(), but most do).
Kevin Conaway
Ranch Hand

Joined: Jan 27, 2006
Posts: 57
I was hoping to for something less invasive that does not require the modifications of stored procedures.

Also, if I need to know the total number of rows available, is SELECT COUNT(ID) FROM DATA the best way to handle this?

Thanks
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
It is the typical way to go, and I think the only way to go in situations where you have any kind of where clause to specify a subset. If you literally want to know the entire table size and don't care about supporting multiple database vendors in your code, there is Oracle metadata that will tell you how many rows there are in the table.

I haven't compared the timings of the two. Conceptually the metadata approach would be constant time and the count approach linear time, but I wouldn't be surprised if you found Oracle query processing spotted such an obvious attempt to measure table size and internally used the metadata anyways.


Reid - SCJP2 (April 2002)
 
Don't get me started about those stupid light bulbs.
 
subject: CallableStatements and Scrolling