Question on looping thru 2 recordsets

[Melinda Savoy]

Sorry guys but I'm lost in the weeds here. Been at this one for a few hours and I keep getting deeper.

I have a recordset (rs) that has 41 records and another recordset (rs1) that has 3 records. I need to loop through the entire (rs) to see if rs1.itemNum in (rs1) matches any of the rs.itemNum in (rs) and if it does then I create a list and if it does not then I need to go on to the next (rs) record.

I just can't get out of the maze and any help or direction would be greatly appreciated. Thank you.

Here is my code:

for (int i=0; rs.next(); i++) {
String vendorItemNum = rs.getString(0);
for (int j=0; rs1.next(); j++) {
String acrpriceItemNum = rs1.getString(0);
if (vendorItemNum.equalsIgnoreCase(acrpriceItemNum)) {
results[j][0] = new LookupResult(rs.getString(0));
results[j][1] = new LookupResult(rs1.getString(1), rs.getString(1));
results[j][2] = new LookupResult(rs.getString(2));
results[j][3] = new LookupResult(rs.getString(3));
buildList.add(results);
break;
}
}
}

[Adam Nace]

Nothing's jumping out at me that looks obviously wrong. What behavior are you seeing?

- Adam

[Melinda Savoy]

Adam,

Thanks for the reply. I think I just got my problem fixed.

Again I appreciate your time in replying.

Regards.

[Jim Yingst]

Is a RecordSet like a ResultSet? Generally ResultSets are designed to be looped through exactly once, not multiple times. You can try calling rs.first() to reset the ResultSet to the first result; this will error if the ResultSet does not support this.Probably a better method would be to read the contents of rs1 just once, before doing anything with the other rs. Store all the data you need from rs1 in come sort of Collection, e.g. a HashSet. Then you can loop through the other rs and access what you need from rs1 using the Collection, not the original ResultSet.

[Krithika Srinath]

To achieve what you are trying to, you should first have two way scrollable ResultSet for the rs1 ResultSet. By default, forward only scrollable resultset is created. If you are using JDBC2.0 driver then you can create two way scrollable ResultSet by using the following createStatement API

Statement stmt = con.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE,
                           ResultSet.CONCUR_READ_ONLY);

Your code might be changed to use while instead of for(...) - this is just a suggestion since you dont need to introduce a new variable for just scrolling the resultset.

while(rs.next()) {
String vendorItemNum = rs.getString("vendorItenNum"); // use column name instead of index.
while(rs1.next()) {
String acrpriceItemNum = rs1.getString("acrpriceItemNum");
if (vendorItemNum.equalsIgnoreCase(acrpriceItemNum)) {
// do the stuff that you want to do..
break;
}
}
   [1]
}

now at this point if the data match happens for last record in rs1, then you are at the end of the resultset and you need to once again set the pointer of rs1 to before 1st record.

Your code at [1] should be:

   rs1.beforeFirst();

For more details on this please check beforeFirst() API

Sun's doc on ResultSet gives a good overview on how to create and manage different types of ResultSet.

Hope this helps.

[Krithika Srinath]

Melinda,

I was typing so slowly that before i could complete you could fix your problem.

Cheers

[Melinda Savoy]

Hey guys,

Thanks for all the replies. I did find that I still had an error but your suggestions are helping get through the recordset issue.

Unfortunately we're working with a squirrelly jdbc driver called J204 for the M204 database (legacy system). It is not a relational DB and consequently a lot of quirky stuff to workaround.

My lead is working on a fix on a sql tool we're using to be able to advance the recordset or reset it if the criteria is not found and then I can advance to the next record in the recordset without having to create a hashset.

Thanks again for all the help. Regards.

[Stan James]

If you can sort the lists, matching is easy.

while not a end and not b end
   if a key < b key
      handle a only
      next a
   else if a key > b key
      handle b only
      next b
   else // a key == b key
      handle match
      next a
      next b
end while