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.
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.
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
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.
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.
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.