getFetchSize()

[parvez kmp]

hai,
we r using jdk1.3.We wrote a program (JSP file)to connect to the database.
Regarding number of records in the table we have used ResultSet object.getFetchSize().
We r getting error that method getFetchSize() not found in ResultSet interface.
We r using Tomcat server for JSP

[David O'Meara]

get/setFetchSize are on the java.sql.Statement and not the ResultSet.
Dave.

[Jamie Robertson]

Originally posted by David O'Meara:
get/setFetchSize are on the java.sql.Statement and not the ResultSet.
Dave.

there are getFetchSize()/setFetchSize() methods in the ResultSet interface. The problem may be that your driver does not implement these methods. Which driver/database are you using? You may have to check the documentation to see if your driver does implement the method.
You also might try what Dave suggested because the same method exists in the statement interface. Maybe they got lazy and didn't feel like cutting and pasting the code for the ResultSet!
Jamie
[This message has been edited by Jamie Robertson (edited September 24, 2001).]

[Gulab Singh]

The scrollable resultset is availble only in JDBC version 2.0, getFetchSize/setFetchSize methords are not supported by JDBC 1.1.x.
Check your version of JDBC.

[Jamie Robertson]

Originally posted by Gulab Singh:
The scrollable resultset is availble only in JDBC version 2.0, getFetchSize/setFetchSize methords are not supported by JDBC 1.1.x.
Check your version of JDBC.

jdbc 2.0 is shipped with the jdk 1.3 which he has indicated is the version he is running. Doesn't mean that his driver implements it though. And the getFetchSize() does not need to have a scrollable resultset for the method to be implemented.
Now that I took a closer look at the original post, the getFetchSize() method does not do what you want it to do. It does not retrieve the number of rows in a table/resultset. It can hint to the JDBC driver the number of rows that should be fetched from the database when more rows are required for processing by the ResultSet object, but does not give you the desired row count. If you have 1,000,000 rows returned in a query, the jdbc driver does not load all the rows into the memory of the client. It only loads the number set by ,for example, setFetchSize(50). When it needs more rows(finished processing the first 50) it fetches 50 more...and so on. There is no easy jdbc method for obtaining row count. There are 2 common workarounds to find rowcount:
use the count function in your sql first, then execute the real query
eg. "select count(*) from emp where job = 'salesman'" then
"select id, name from emp where job = 'salesman'"
or
create a scrollable resultset. use the last() method to move to the last row then getRow() to find out the record number. The only caution with this is that when you use the last() method it still interates through all the rows until it reaches the last record and the performance of a scrollable resultset is less than that of a type forward only.
let us know how you dealt with your problem,
Jamie