Count the nuimber of results in a Result Set

Thread view:

Bond - 23 May 2005 21:34 GMT

Hi! I'm having a problem where I can't get the results from a
ResultSet AFTER counting the number of results in a ResultSet.

At the moment I have:

public static int countNumberOfRows(ResultSet rs)
{
int count = 0;
try
{
while (rs.next())
count++;
rs.first();
}
catch (SQLException e)
{
System.out.println(e);
}
return count;
}

The problem is when the program runs the line "rs.first()" an error
occurs and it says "Result Set type is TYPE_FORWARD_ONLY". I must use
that line rs.first() in order to continue on after counting the number
of results. In another part of my program I check to see if the number
of results is equal to 1... if it is, then I want to get information
from the database... so, my problem right now is that I can't go to
the first row in the ResultSet. How do I eliminate this
TYPE_FORWARD_ONLY problem???

Thanks!

Reply to this Message

kaeli - 23 May 2005 21:45 GMT

> The problem is when the program runs the line "rs.first()" an error
> occurs and it says "Result Set type is TYPE_FORWARD_ONLY". I must use
[quoted text clipped - 4 lines]
> the first row in the ResultSet. How do I eliminate this
> TYPE_FORWARD_ONLY problem???

That would depend on whether your DBMS / driver supports anything other than
forward only result sets.
Mine didn't.
I had to make my own resultset object from the orginal resultset that
implemented that as well as maintained a record count and whatnot. My
application then used my custom rs object instead of a real ResultSet.

If it IS supported, then you have to specify what you wanted as scroll
insensitive in the create statement, IIRC.
See the docs.
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html

Signature

--
~kaeli~
You feel stuck with your debt if you can't budge it.
http://www.ipwebdesign.net/wildAtHeart
http://www.ipwebdesign.net/kaelisSpace

Reply to this Message

Robert Klemme - 24 May 2005 10:16 GMT

>> The problem is when the program runs the line "rs.first()" an error
>> occurs and it says "Result Set type is TYPE_FORWARD_ONLY". I must
[quoted text clipped - 11 lines]
> implemented that as well as maintained a record count and whatnot. My
> application then used my custom rs object instead of a real ResultSet.

For large results it can be more efficient to execute two queries: one
that counts records and the second one that actually returns results.

> If it IS supported, then you have to specify what you wanted as scroll
> insensitive in the create statement, IIRC.
> See the docs.
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html

Yep, that would be the first thing to try.

Kind regards

robert

Reply to this Message

kaeli - 24 May 2005 14:58 GMT

> the first row in the ResultSet. How do I eliminate this
> TYPE_FORWARD_ONLY problem???

I found some of my old code that worked with a DB that supported this.
See if it helps you.

(edited slightly)

String qs = "select * from myTable";
PreparedStement ps = connection.prepareStatement
(qs,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = ps.executeQuery();

Signature

--
~kaeli~
The man who fell into an upholstery machine is fully
recovered.
http://www.ipwebdesign.net/wildAtHeart
http://www.ipwebdesign.net/kaelisSpace

Reply to this Message

Lee Fesperman - 24 May 2005 22:16 GMT

> > the first row in the ResultSet. How do I eliminate this
> > TYPE_FORWARD_ONLY problem???
[quoted text clipped - 8 lines]
> (qs,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
> ResultSet rs = ps.executeQuery();

There's no need to request more than is needed. Try:

Statement s = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = s.executeQuery(qs);

... using TYPE_SCROLL_INSENSITIVE will mean the count retrieved will match the resultset
retrieved.

Signature

Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

tzvika.barenholz@gmail.com - 25 May 2005 08:36 GMT

Just to add to Lee's point: specifying CONCUR_UPDATABLE has tremendous
overhead. We've seen cases with Oracle 8i for example where just going
over and changing from upd to RO doubled performance of the system as a
whole. Don't ever make a ResultSet updatable if you don't have to. Even
when you want to update, it is mostly better to run a separate update
statement.

Reply to this Message

Count the nuimber of results in a Result Set

Free Magazines