ResultSet and fetchSize()

[Cruella DeVille]

Hi
I'm trying to determin the cardinality of a resultset from a mysql
database, but the method fetchSize() returns 0 (zero). I know there's
data in the database, so this result is not correct. Is there other
ways to retreive the cardinality other than fetchSize()?

My code (if needed)

public String[] getKindergartenNames(){
db.connect();
String[] kindergartenNames = null;
try{
ResultSet s = db.query("select name from kindergarten");
int size = s.getFetchSize();
kindergartenNames = new String[size];
System.out.println("size: " + size); // for debugging purposes
int antall = 0;
while(s.next()){
String name =s.getString("name");
//kindergartenNames[antall] = name;
System.out.println(antall); // debugging
antall++;

}
}catch(SQLException sqlE){

}
return kindergartenNames;

}

 

[=?ISO-8859-1?Q?Tobias_Schr=F6er?=]

Hi,

why don't you simply return a List? Then you don't need to know the
fetch size to create the array.

I inserted the alternative code below (don't forget the java.utilList
and java.util.ArrayList imports). You can use other List implementations
than ArrayList if needed.

Hi
I'm trying to determin the cardinality of a resultset from a mysql
database, but the method fetchSize() returns 0 (zero). I know there's
data in the database, so this result is not correct. Is there other
ways to retreive the cardinality other than fetchSize()?

My code (if needed)

public String[] getKindergartenNames(){

public List getKindergartenNames() {

db.connect();
String[] kindergartenNames = null;

List kinderGartenNames = new ArrayList();

try{
ResultSet s = db.query("select name from kindergarten");
int size = s.getFetchSize();
kindergartenNames = new String[size];
System.out.println("size: " + size); // for debugging purposes
int antall = 0;
while(s.next()){
String name =s.getString("name");
//kindergartenNames[antall] = name; kindergartenNames.add(name);
System.out.println(antall); // debugging
antall++;

}
}catch(SQLException sqlE){

}
return kindergartenNames;

}

If you're using Jave 1.5 you can use a generic list to make your result
typesave (List<String>).

Otherwise, the ResultSet implementation of the MySQL JDBC driver must
implement the getFetchSize() member properly. Maybe here's the fault?
I'm not sure, because I've never used MySQL with Java yet.

hth
Tobi

 

[dayjah]

Hi Cruella

I'd suggest something like

public int countRows(rs) {
rs.afterLast(); // note 1
int size = rs.getRow() -1; // note 2
rs.beforeFirst(); // note 3
}

note 1: this sets you to the end of your result set.
note 2: gets the number of the row you are at, I've not tested this so
I think you need to minus one from the number baring in mind the
previous line put you *after* the last row.
note 3: you need to rewind to the beginning of the result set otherwise
when you iterate through it the process will break.

Hope this helps,

dayjah

 

[dayjah]

Otherwise, the ResultSet implementation of the MySQL JDBC driver must

implement the getFetchSize() member properly. Maybe here's the fault?
I'm not sure, because I've never used MySQL with Java yet.

The MySQL JDBC driver does not implement getFetchSize(), I think it
also misses out other nice features such as meta data - although, if my
memory serves me right, I read that it is the db that actually does not
pass meta data back but this changes with mysql 5 so we should start to
see cooler mysql jdbc driver functionality in due time.

dayjah

 

[ahjiang]

if im not wrong..fetchsize means that if you set the number of rows
you want to fetch from the database and not the size of the resultset..

and using moving the result to last and first..is an expensive
operation..

 

[Rhino]

The MySQL JDBC driver does not implement getFetchSize(), I think it
also misses out other nice features such as meta data - although, if my
memory serves me right, I read that it is the db that actually does not
pass meta data back but this changes with mysql 5 so we should start to
see cooler mysql jdbc driver functionality in due time.

You're mistaken about the metadata. I'm using MySQL 4.0.15 and the JDBC
driver does pass back metadata. In fact, this is the one way I can get
information about tables, etc. in MySQL 4.0.x, aside from the meager
information provided by the SHOW commands.

 

[dayjah]

if im not wrong..fetchsize means that if you set the number of rows

you want to fetch from the database and not the size of the resultset..

I just cheked the javadoc, you're right. There doesn't seem to be a
rs.countRows() at all. A shame because it would be a very handy
addition!

and using moving the result to last and first..is an expensive
operation..

Correct! However it seems the requirement is to count the number of
rows in the result set is there a less expensive way?

 

[dayjah]

You're mistaken about the metadata. I'm using MySQL 4.0.15 and the JDBC

driver does pass back metadata.

Neat! What driver are you using?

 

[Nikola Vidovic]

Yes, you pass select count(*) from KinderGarten query and get its result.
Few lines of code more but inexpensive.


Zvonko

 

[=?ISO-8859-1?Q?Tobias_Schr=F6er?=]

Yes, you pass select count(*) from KinderGarten query and get its result.
Few lines of code more but inexpensive.

You're sure? I'd say that a database request is quite expensive,
especially if you don't use things like PreparedStatements. At least,
this depends on the data amount and should be tested. Roughly, I'd
suggest that a small data count should preferrably be processed on Java
side, whereas I'd hand over large data counts to the Database.

Or read the ResultSet to a List, as I suggested before. Lists can easily
be converted to arrays, if needed. The ResultSet size was only used
for array initialization.

Tobi

 

[dayjah]

You're sure? I'd say that a database request is quite expensive,

especially if you don't use things like PreparedStatements.

you are right - submitting the count would be way way expensive.

Or read the ResultSet to a List, as I suggested before. Lists can easily
be converted to arrays, if needed. The ResultSet size was only used
for array initialization.

I think the issue here is memory use - we often have result sets
comprising of 100's of very wide tupples. I know what we do is
relatively small but assigning this to a list would essentially, at a
guess, double the amount of memory needed which in Cruella's case is
*just* debug. Rather expensive - sure fast forwarding to the end of the
result set and then back to the start is not a great solution but it is
the least expensive so far mentioned.

I wonder if there is some meta data that handles this? When you submit
a query to mysql you get a lot of information back, most notably there
is the number of rows returned and the time it took the query to run.
Perhaps these can be extracted some how?