select count(*) does not work

[Alex Kravets]

I am trying to execute query select count(*) from users thru jdbc, but get this exception:

java.lang.ArrayIndexOutOfBoundsException
at simba.jdbc.DataSet.getValue(DataSet.java:170)
at simba.jdbc.SimbaResultSet.getString(SimbaResultSet.java:646)
at com.kwi.ges.DBCommander.retrieveIndexes(DBCommander.java:146)

Here is the code:

stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
sql = rs.getString(1);
Even if I do:
select count(id) from users
int num = rs.getInt(id);
I get error: "Column not found"

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 ]

[Kevin Mukhar]

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:

stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
String result = "";
if (rs.next()) {
  result = "Number of rows is " + rs.getString(1);
} else {
  result = "No rows returned by query";
}
System.out.println(result);

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]

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.

[Alex Kravets]

thanks guys!