Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Question about SQL ResultSet

Posted on 1998-11-04
4
Medium Priority
?
187 Views
Last Modified: 2010-03-30
I need count the total number of these people who want to run with somebody whose request is "late". The "Request" includes: "early", "late", or "with #somebodyBibNo"(such as with #100).
The following function doCount() can not get the next record from while(rs1.next()). But doCountTest() can. Could you tell me what the problem is? How can I do a query inside another query?Thanks!!

public ResultSet doQuery() throws SQLException  {    
 ResultSet rs;
 String  query = "SELECT * FROM Competitors WHERE Request LIKE 'with%'";
 rs = db.getStatement().executeQuery(query);
 return rs;
 }
 
 public ResultSet doQuery1(int BibNo) throws SQLException  {
  ResultSet rs;
 String  query = "SELECT * FROM Competitors WHERE BibNo = " + BibNo;
  rs = db.getStatement().executeQuery(query);
  return rs;
 }


public void doCount()  throws SQLException{
      String new_request;
      int somebodyBibNo;
      ResultSet rs1, rs2;

      rs1 = doQuery();
      while(rs1.next()){
        new_request = rs1.getString("Request");
       somebodyBibNo = Integer.parseInt(new_request.substring(6));
       rs2 = doQuery1(somebodyBibNo);         
       if(rs2.next()){
            new_request = rs2.getString("Request");
            if (new_request.equals("late"))
              countlate = countlate + 1;
        }
      }
  }


public void doCountTest()  throws SQLException{
      String new_request;
      int somebodyBibNo;
      ResultSet rs1, rs2;

      rs1 = doQuery();
      while(rs1.next()){
          new_id = Integer.parseInt(rs1.getString("BibNo"));
         System.out.println(new_id);
         new_request = rs1.getString("Request");
         somebodyBibNo = Integer.parseInt(new_request.substring(6));
         rs2 = doQuery1(somebodyBibNo);         
     /*    if(rs2.next()){
            new_request = rs2.getString("Request");
            if (new_request.equals("late"))
              countlate = countlate + 1;
             } */
      }
  }


When I stored data such as:   
BibNo         Request
100               late
200              with #100
300              with #100
400              with #200

When I added a code after  while(rs1.next()) inside the doCountTest() to print out the "BibNo" for checking the results from the ResultSet, there were 200 and 100. They should be 200, 300 and 400. It seems that the 100 come from the rs2, not the rs1 when second time to do the code:
  new_id = Integer.parseInt(rs1.getString("BibNo"));

 Do you have a good idea to solve this problem?
0
Comment
Question by:Jun080298
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
heyhey_ earned 140 total points
ID: 1226976
the first ResultSet returns this data

200              with #100
300              with #100
400              with #200

you start a second request for each of these records and you get only one hit (record) for each internal RecordSet (rs2)

200              with #100
   100               late   //the hit BibNo = 100

--
300              with #100
   100               late   //the hit BibNo = 100

--
400              with #200
   200              with #100    //the hit BibNo = 200

and of course you got 'late' only two times. (countlate = 2)

I am not sure what do you want to implement. If you want to traverse the tree style structure, you will need another approach.
<Code>
late - 100
        |
        |-like- 200
        |
        |-like- 300
                 |
                 |-like- 400
</CODE>

The basic (but not best :) approach is to for each element (BibNo) to find his father (like#100) the look at his father's father (grandfather) and so on ... until you reach early or late.

(for 400 you check if it is early or late, then check his father 300 if it is early or late, then check 100 and ok - it's '<B>late</B>'

of course
1. this may result in too many database calls.
2. you can have problems, if this isn't exactly tree, but cyclic graph

hope this helps
  heyhey
0
 

Author Comment

by:Jun080298
ID: 1226977
Thank you for your interesting my question. But sorry, the countlate is 1, not 2, and also the BibNo are 200 and 100 in order.

0
 
LVL 16

Expert Comment

by:heyhey_
ID: 1226978
???
>>But sorry, the countlate is 1, not 2, and also the BibNo are 200 and 100 in order.
???
it seems that i don't understand you ...

so the first resultSet should return all 'people' whose requst is 'late' and second resultSet shoul return the number of these people who want to run with somebody (whose request is "late").

so there is only one 'person' which is late (100)
and there are two persons that 'want to run with somebody whose request is "late") - that's 200 and 300 ...

if you need a tree like hierachy, you'll need another approach ...
0
 
LVL 16

Expert Comment

by:heyhey_
ID: 1226979
ok i looked closer and it seems that your SQLs are messed
(you extract the 'with#..." persons and then count the ones from the "..." that are late ...

why don't do it this way

  String sql 1 = "SELECT * FROM Competitors WHERE Request = late"

to find all that are late
for every one of them

  String sql 1 = "SELECT * FROM Competitors WHERE Request = with#" + curLate;

will find out all that "want to run with somebody whose request is "late""

you can count them and print them ...

(if you need to find all that "want to run with somebody that wants to run with somebody whose request is "late""
you have to make another (third) result set and so on ...


0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question