• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question on looping thru 2 recordsets

 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Adam Nace
Ranch Hand
Posts: 117
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nothing's jumping out at me that looks obviously wrong. What behavior are you seeing?

- Adam
 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Adam,

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

Again I appreciate your time in replying.

Regards.
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Your code at [1] should be:



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
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Melinda,

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

Cheers
 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you can sort the lists, matching is easy.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic