wood burning stoves 2.0*
The moose likes JDBC and the fly likes select count(*) does not work Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select count(*) does not work" Watch "select count(*) does not work" New topic
Author

select count(*) does not work

Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
I am trying to execute query select count(*) from users thru jdbc, but get this exception:

Here is the code:

Does it mean that my driver does not support this?
Also, will I get a faster result if I do select count(*) or
rs.last();
rs.getRow(); ?
thanks,
Alex
[ April 30, 2004: Message edited by: Alex Kravets ]

All right brain, you don't like me and I don't like you, but let's just do this one thing so I can get back to killing you with beer.<br /> <br />- Homer Simpson
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
Generally, you want to use 'select count(*)' rather than rs.last(). Think about what would happen if your query could result in 1,000,000 rows and you called rs.last(): the database would have to step through all the rows to get to the one you ask for.
Your first set of code should work, assuming the query returns a row. It normally should, but you can test it by using this code:

If the query returns no rows, then it is possible that the database or driver does not support count(*), or that there is something else wrong with some other code of yours that you did not post.
Your second example will never work. You are asking the result set for a column named 'id,' however your SQL command does not retrieve this column. It retrieves 'count(id).' Use rs.getInt(1) or rs.getString(1).
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30753
    
156

Alex,
Your second example won't work for two reasons. One is what Kevin pointed out. You can get around that by using an index or an explicit column name, like select count(id) as "id" from users
Also, you need to call rs.getNext() before you call rs.getString() or rs.getInt(). Otherwise the resultset is not pointing to a valid row and you get exceptions like you did.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
thanks guys!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select count(*) does not work