• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How do we know if there no records in resultset

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Gregg Bolinger for your valuable comment.
-------------
Sainudheen
 
Ravi Tiruvaipati
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic