We help IT Professionals succeed at work.

SQL and resultset to count records with MS Access Database

etstiles
etstiles asked
on
I am connecting to a Microsoft Database using the JDBC-ODBC Driver.  I would like to get the record count using the following SQL command:

'SELECT COUNT (*) from Table'

How can I set the return value to a variable.  Please be explicit.  The better the answer and I will give more points.

The only other way that I can get the record count is by getting the resultSet and using next() in a while loop to scroll through the resultset and using a counter to count the numbers of loops.  This seems like a very inefficient method, because then I also need to keep track of the record before I got the record count and then I have to re-sql the table to scroll forward to the record where I was before I got the recordcount.

If there is a better way, that works, then what I am asking for please also tell me.

Using last() returns an error telling me that the record is FORWARD_ONLY.   Using next() by itself and then trying to use getRow() will return 0 because next() always scrolls past the end of the resultset.  Using isLast() returns the same error as last().

thanks,
Comment
Watch Question

Ovi

Commented:
In the case of your select statement, the result set will not loop more than one time. So you can replace the loop with :

resultSet.next(); // move the pointer to the first row
int count = resultSet.next();//retrieve the first row and move the pointer to the next row
Commented:
1. Use an AS clause:
SELECT COUNT(*) AS 'cnt' FROM table
and get value:
iRecordCount = rs.getInt("cnt");

2. For last() and etc. use a suitable driver (http://industry.java.sun.com/products/jdbc/drivers)

Commented:
ResultSet rs = stmt.executeQuery("Select count(*) from table");

int count = 0;

if (rs.next()) {
   count = rs.getInt(1); // get the count
}
       

Author

Commented:
Thanks.  I thought it was something like this, but I couldn't find an example to help get through the problems.

Commented:
Thank you :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.