3 Replies Latest reply: Nov 16, 2007 10:37 AM by 796254 RSS

    Sub - ResultSet from ResultSet

    807603
      I have the following problem to solve. I have extracted a ResultSet from my DB. Within the ResultSet there are two columns such as this:

      =====================
      COLUMN 1 ### COLUMN 2
      =====================
      APPLE ### ORANGE
      CHERRY ### ORANGE
      APPLE ### PEAR
      CHERRY ### PEAR
      APPLE ### LEMON
      CHERRY ### LEMON
      ...

      I need to be abble to ask a question to the ResultSet like this: "Give me the row where Column1 = Apple and Column2 = Pear"
      I can't see a method in ResultSet which will do this so perhaps the only way is to take a subset of the ResultSet such that Column1 only contains Appples and then perform a normal search. Again, is this possible.

      I know that the obvious answer to this question is to be more detailed in the original SQL statement. However for reasons of DB overhead I want to return the ResultSet and then iterate over it to separate out all relevant data.

      Any help much appreciated, thanks, H
        • 1. Re: Sub - ResultSet from ResultSet
          807603
          To begin with, what do you do now in your code when you generate a ResultSet? You should be turning each row into an appropriate business object, and putting all these rows into an appropriate row collection class. Since you are doing that, why not add the functionality you need to your row collection class?
          • 2. Re: Sub - ResultSet from ResultSet
            JoachimSauer
            Hacland wrote:
            I know that the obvious answer to this question is to be more detailed in the original SQL statement. However for reasons of DB overhead I want to return the ResultSet and then iterate over it to separate out all relevant data.
            Sorry to nag about this, but I'm not sure I understood this. What exactly do you mean by "for reasons of DB overhead"? Do you want to reduce the load on your Database server? Or what else?

            Are you aware that actually passing your requirements to the DB might reduce the load it takes for the DB to process your query? It could use a Index instead of a full table scan and having to send less data definitely reduces the load on the DB service. Basically the more information you give the DB the more possibilities it has to optimize your query. If you simply tell it "give me the content of table A" then it really can't do anything smart. If you tell it "give me every column in A where c1 = foo and c2 = bar" then it can use indices and other smart techniques.

            What you are trying to do is treat the DB as a dumb datastore and do the data filtering in your program and that's exactly the wrong way.
            • 3. Re: Sub - ResultSet from ResultSet
              796254
              I know that the obvious answer to this question is to be more detailed in the original SQL statement. However for reasons of DB overhead I want to return the ResultSet and then iterate over it to separate out all relevant data.
              neophyte thinking.

              the database is optimized to do that much faster than you ever will. write the query so the database does the work and bring it back.

              %