Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How do we know if there no records in resultset

 
Ravi Tiruvaipati
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I wrote a sql on a table and I know there are no records for the condition I mentioned, but the resultset seems to behave as if there are valid records. But when I try next() it comes up with exeception Invalid cursor state. I want to find if there are no records in the resultset...
Here is the code, I am trying..

I appreciate your help.
regards
ravi
[ September 26, 2003: Message edited by: Ravi Tiruvaipati ]
[ September 26, 2003: Message edited by: Ravi Tiruvaipati ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to know if there are records or not, you still need to use the next() method of the ResultSet. Here is what happens...
When you say something like:

rs is given the result of that query. You already know that. That query doesn not return a boolean, as you have somewhat coded in your example. So what you have to do next is:

Here, next() does 2 things.
1. Moves the cursor forward in the result set
2. Returns a boolean as to whether or not this was succesful.
So if you have no records, next() will immediatly return false, because it can't move the cursor if no records exist. I hope this makes sense.
 
James Swan
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just to help tidy up your code a bit, here's the general sort of flow you would want to do:

edit: Gregg bet me to it, that's what you get when you start a post, and get distracted before you finish the reply
[ September 26, 2003: Message edited by: James Swan ]
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Just for a discussion

Will this work? Is this a good approach? Please correct me.
----------------
Sainudheen
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sainudheen Mydeen:
Hi
Just for a discussion

Will this work? Is this a good approach? Please correct me.
----------------
Sainudheen


It's kind of more than what you really need to do. You are performing 2 queries when only 1 is necessary. Take a look at this.

I always use a while loop if I think I might be dealing with more than one item in the ResultSet. If I am only concerned with whether or not the ResultSet contains data or not OR if I know only 1 item might be returned, I will use an if statement instead.
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Gregg Bolinger for your valuable comment.
-------------
Sainudheen
 
Ravi Tiruvaipati
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys,
Thanks for your help. Here are the things I tried and the results...
Gregg,
When I execute a query and even if there are no records for that query, rs.next() returns true. I dont know why. The database I am trying is oracle 9i. The method statment.execute(sql query),(refer java doc for statment) returns boolean value. That is why I used execute to see if there are resultset exist or not. Anyway for some reason I couldn't get it work that way.
James,
You are right about the flow in general but I need to know when I execute a query using statment.executeQuery(sql string) whether it has some rows or not before I use next(). If I use next() the pointer is already moved forward and I dont want to do that.
Sainudheen,
You are right! That is what I did to find the count.
Once again thanks guys for you help
regards
ravi
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I execute a query and even if there are no records for that query, rs.next() returns true. I dont know why. The database I am trying is oracle 9i. The method statment.execute(sql query),(refer java doc for statment) returns boolean value. That is why I used execute to see if there are resultset exist or not. Anyway for some reason I couldn't get it work that way.
It doesn't return a boolean to say if there are rows in a ResultSet. It only returns a boolean to tell you if the execute was seccessful or not. Even if it does not return any rows, it can still be successful.
You are right about the flow in general but I need to know when I execute a query using statment.executeQuery(sql string) whether it has some rows or not before I use next(). If I use next() the pointer is already moved forward and I dont want to do that.
Try getFetchSize(). It returns an int. I would assume that an Empty ResultSet would return 0 but have never used this method. Let me know if it works or not.
 
Ravi Tiruvaipati
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gregg,
The getFetchSize() did return 1 even when there are no records for the query. Anyway I used count(*) to get the count and it works fine.
thanks
ravi
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ravi Tiruvaipati:
Gregg,
The getFetchSize() did return 1 even when there are no records for the query. Anyway I used count(*) to get the count and it works fine.
thanks
ravi

Ravi, I think I will continue to look into this. It bothers me that the ResultSet object doesn't have something like a hasValues() or size() method of some sort. It seems to me the ResultSet object should have more of a collections type of interface.
I will let you know if I dig anything up.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ravi,
Here is a work around to save you the extra database hit. Instead of calling count(*) on the databse, and then make another call to get the Results, you can do something like this:

Hope that helps a little bit.
 
Ravi Tiruvaipati
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gregg,
I already tried rs.next() as mentioned in the code, but suprisingly rs.next() return true even when there are no records for the sql. I couldn't get around it and I had left with only choice of count(*).
thanks for suggestions.
regards
ravi
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ravi Tiruvaipati:
Gregg,
I already tried rs.next() as mentioned in the code, but suprisingly rs.next() return true even when there are no records for the sql. I couldn't get around it and I had left with only choice of count(*).
thanks for suggestions.
regards
ravi

Then somehow, your database is always returning some sort of value. I wonder if it is because of the way you are creating your Statement as was mentioned in another post? Because this is not default behavior of a ResultSet's next() method.
In fact, I just tested it, and mine returned false. Maybe it's Oracle, or maybe it is your Statement object somehow. But if you can live with your solution then so can I.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic