how to get recordcount from table in Access mdb?

[Rpng123]

I need to get a count of records from an Access mdb. My program connects to
Access OK via JDBC. I do the Class.ForName(driver) thing, Connection, etc.
then Statement stmt = con.createStatement(); ResultSet rs =
stmt.executeQuery(sql);

Then I try rs.last(); so that I can say int recCount = rs.getRow();

But I keep getting a SqlException error - Result set type is TYPE_FORWARD_ONLY.
I have tried rs.setFetchDirection(FETCH_FORWARD) but I guess FETCH_FORWARD is
not a known Java constant. Is there way to get the recordcount without having
to loop through the entire table?

While(rs.next())
recCount = rs.getRow();

I would like to avoid this because some of my tables are quite large.

Thanks,
Rich

 

[steve souza]

If you just care about the record count you are better off executing
"select count(*) from table". Unfortunately ResultSets don't give you
a row count. You could iterate through it twice if it was a
scrollable ResultSet. For large ResultSets you shouldn't pull them
all in memory anyway. A sure way to choke your app is to pull a
200,000 row table into it.

My api has an object that converts a ResultSet into a 2 dim array. If
you use it I have a method called getRowCount() that does what you
want.

ResultSetConverter rsc=new ResultSetConverter(resultSet);
int rows=rsc.getRowCount();
Object[][] data=rsc.getResultSet();

steve - http://www.fdsapi.com - The easiest way to generate dynamic
xml and html

 

[Thomas Kellerer]

I need to get a count of records from an Access mdb. My program connects to
Access OK via JDBC. I do the Class.ForName(driver) thing, Connection, etc.
then Statement stmt = con.createStatement(); ResultSet rs =
stmt.executeQuery(sql);

Then I try rs.last(); so that I can say int recCount = rs.getRow();

But I keep getting a SqlException error - Result set type is TYPE_FORWARD_ONLY.
I have tried rs.setFetchDirection(FETCH_FORWARD) but I guess FETCH_FORWARD is
not a known Java constant. Is there way to get the recordcount without having
to loop through the entire table?

While(rs.next())
recCount = rs.getRow();

I would like to avoid this because some of my tables are quite large.

Thanks,
Rich

You need to specify the result set type when creating the *statement*

<http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#createStatement(int, int)>

Btw: rs.last() is probably doing nothing else but a while(next());

Thomas

 

[Rpng123]

Thank you all for your replies to my post. One other solution that I finally
stumbled onto was this:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

I also tried
rs.setFetchDirection(rs.TYPE_SCROLL_INSENSITIVE);

leaving the stmt def as
Statement stmt = con.createStatement( );

but this did not work for me. Only the first one above worked propertly. Any
other suggestions are welcome.

Thanks,
Rich