how to count rows

[Anonymous]

hi all,

i donot know hot to get count of rows which match a specific criteria
froim a database.i donot want to use a counter inside a while(next())
loop. is any efficient way there???

regards

srijan

[Anonymous]

select count(*) from tablename;
(or)
select count(*) from tablename
where col1 = something;
(or)
checkout the sqlca....
do your homework. one of the rows will be the number of rows
returned.
(or)
there maybe a way to find this out within the meta-data but i can not remeber off the top of my head right now.
hope this helps...

[Milind Kulkarni]

Hi,
Monty's solution would definitely work but would it be efficient?? I wonder why you don't want to use the next() of ResultSet object. If you have any specific reason for not using next() of ResultSet object please let us know so that we can do further analysis.
Regards,
Milind

[Anonymous]

Would select count(*) from tbname; be efficient?
Yes. because processing is done within the db engine where the data is. To answer a question with a question.
1. Would it be more efficient to transport 1 or 10,000,000+ rows over you network? I would choose the one row. If all I had to do is count the number of rows.
2. Would it be more efficient to process 1 or 10,000,000+ rows within you application/applet. I would choose the one row. If all I had to do is count the number of rows.
think about it, Monty6

[Milind Kulkarni]

Hi Monty,
Your point is valid but I am under the impression that he is just interested in using COUNT(*) instead of next() of ResultSet object in the for loop and he is anyway going to process thousands of records.
Regards,
Milind

[This message has been edited by Milind Kulkarni (edited August 14, 2000).]

[Anonymous]

Milind,
In that case, try using ResultSet last() and getRow() methods.
Fyi, Watch out for the Statement setmaxRow() method. This method
can limit your ResultSet without warning!
Reguards,
monty6

------------------

[Anonymous]

no my point was to count records from java not using oracle commands from rseultset.
if i use a counter and records are 10000 it is not efficient.
i mean when we have getcolumn count !!! do not we have getrow count()
or something .....
srijan

[Anonymous]

I hate to say RTFM but... I think it is time you should read
the JDK documentation provided by sun... It's FREE.
I think if you spend about an hours review the java.lang.sql package ... most if not all of your question will be answered.
I hate it when someone tell me to RTFM...
But, when they tell me where and what to read I appreciate there efforts.
We are all here to help on and other
Stay in touch, Monty6

[Anonymous]

I have RTFM'd myself...
In JDBC API Tutorial... JDBC 2.0 core page 581
Code Fragment:
Resultset rs = stmt.execQuery("some sql goes here);
rs.last(); //make current row last row in rs
int numberofrows = rs.getRow(); // gets current ros number
That's it....

[sravan kumar]

Here you have to take the help of the ResultSetMetaData interface
you have to first get the reference of the ResultSetMetaData
which will return the ResultSet from which you can invoke the
method of the column count
Regards
Shravan
[This message has been edited by sravan kumar (edited September 13, 2000).]

[Mordred]

I had the same problem and only the "count(*) from .." works.
I use the JDBC 2.0 driver but if I execute the following method on an EMPTY table :
public int getResultSetSize(ResultSet rs) throws SQLException {
rs.last();
return rs.getRow();
}
I get the value 1 instead of the value 0 !
I guess this is because a row filled with NULL-values is still accounted for so it seems.. :s
Weird if you ask me..
Wich obligates me to do it via the rowcount-technique...