GeeCON Prague 2014*
The moose likes JDBC and the fly likes ResultSet check... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet check..." Watch "ResultSet check..." New topic
Author

ResultSet check...

Mandy Smith
Ranch Hand

Joined: Jun 26, 2001
Posts: 62
I have a sql query, based on whether it returned "0" rows or many rows i wan't to do different operations. How to check for whether it returned 0 rows or many rows. If i do check rows.next() then it throws me SQLException saying "ResultSet has no more data". How to check the Resultset under such situations?
Could anyone please help me clarify this.
Appreciate your help!
Thanks.
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Originally posted by Mandy Smith:
I have a sql query, based on whether it returned "0" rows or many rows i wan't to do different operations. How to check for whether it returned 0 rows or many rows. If i do check rows.next() then it throws me SQLException saying "ResultSet has no more data". How to check the Resultset under such situations?
Could anyone please help me clarify this.
Appreciate your help!
Thanks.


It shouldn't throw an exception when you call .next() if it has no rows. It should return false.
You can try resultSet.isBeforeFirst() which will return false if there are no records and true if there are and you haven't accessed it yet.
Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
You can check if the resultset is equal to null. If it's equal to null, and you call the next() method, you will get an exception.


------------------
Bosun
SCJP for the Java� 2 Platform


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
hmm, I can't say I'm sure now that I was right. In fact, the more I think about it, the more I think I'm wrong. Mandy, do you really get an exception just because there are no rows in the resultset when you call .next()?
Mandy Smith
Ranch Hand

Joined: Jun 26, 2001
Posts: 62
This is what i am doing
ResultSet row = statement.executeQuery();
if (row == null)
{
throw new RuntimeException("Error");
}
else
{
row.next();
}
Its always going into else part, row is never null even if the query returns 0 rows. When the query returns 0 rows, this is what i am getting as exception "java.sql.SQLException: ResultSet has no more data". could any one please help me to solve this. I wan't to handle both 0 rows situation as well as many rows.
What's this resultSet.isBeforeFirst() method do? I was not able to find this in sql package. Is this in some extension package?
Apprecitate your help.
Thanks.
Mandy Smith
Ranch Hand

Joined: Jun 26, 2001
Posts: 62
I understand that the returned resultSet row from statement.excuteQuery() is never going to be null. I still dont understand why is it throwing a Sql exception if i am calling a .next() method?
Thanks.
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
Try this...
if (!row.next())
{
System.out.println("resultset is empty"}
else
{
row.next();
}

------------------
Bosun
SCJP for the Java� 2 Platform
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879


follow the same principles and you will avoid logic troubles. The catch block also gives you informative error messages to help diagnose the sql problems.
Jamie
[This message has been edited by Jamie Robertson (edited December 11, 2001).]
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Copied from http://java.sun.com/j2se/1.3/docs/api/index.html
java.sql.ResultSet.isBeforeFirst()
public boolean isBeforeFirst()
throws SQLException
Indicates whether the cursor is before the first row in this ResultSet object.
Returns:
true if the cursor is before the first row; false if the cursor is at any other position or the result set contains no rowsThrows:
SQLException - if a database access error occursSince:
1.2See Also:
What Is in the JDBC 2.0 API
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Mandy,
I tested my database and when I get a resultSet with no rows, then .next() just returns false, it doesn't throw an exception.
Annoyingly though I can only vaguely remember that I once had a situation where .next() was throwing an exception.
Actually I just checked just now and I've found the code where I had the problem, so I'm going to recreate the problem & I'll get back here later. (beats working)
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Mandy,
I checked out my code and sure enough, resultSet.next() is throwing an exception when the resultSet is empty (no rows). That is why I suggested using resultSet.isBeforeFirst() - it's the only way around the issue for me.
I can't see why .next() is throwing the exception. It shouldn't, and elsewhere it doesn't. Very strange.
James,
to make your code even more perfect : )


[This message has been edited by Adam Hardy (edited December 13, 2001).]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Adam, just curious what SQLException was being thrown in your code???
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
won't this work?
if(myRs.next())
{ do
{do something
}
while (myRs.next());
}
else
{
No rows
}
Regards
Beksy
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Originally posted by Jamie Robertson:
Adam, just curious what SQLException was being thrown in your code???


Ah. I dug into the code a bit more and realised that the resultSet I was using was not precisely normal - without going into it deeply, in a JSP static function I'm getting a normal resultSet and copying it to a sun.jdbc.rowset.CachedRowSet
objResultSet = staDB.executeQuery(strSQL);
//error checking...
crsCSG = new CachedRowSet();
crsCSG.populate(objResultSet);
And it is the crsCSG in my code (the sun.jdbc.rowset.CachedRowSet) that is having problems with empty resultsets.
Presumably internally it's not doing something it should. It's still in beta. (I think).
Anyway, the exception was Invalid Cursor Position.
regards
Adam
 
GeeCON Prague 2014
 
subject: ResultSet check...