File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Beginning Java and the fly likes Question on looping thru 2 recordsets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Question on looping thru 2 recordsets" Watch "Question on looping thru 2 recordsets" New topic
Author

Question on looping thru 2 recordsets

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 386

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

Joined: Jul 17, 2006
Posts: 117
Nothing's jumping out at me that looks obviously wrong. What behavior are you seeing?

- Adam
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 386

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

Joined: Jan 30, 2000
Posts: 18671
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.


"I'm not back." - Bill Harding, Twister
Krithika Srinath
Ranch Hand

Joined: Apr 11, 2006
Posts: 52
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

Joined: Apr 11, 2006
Posts: 52
Melinda,

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

Cheers
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 386

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

Joined: Jan 29, 2003
Posts: 8791
If you can sort the lists, matching is easy.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Question on looping thru 2 recordsets
 
Similar Threads
How to set value in jtable fields??
Problem in viewing records ?
Trying to grab the last record.
Inserting single column values of database table into multiple columns of jtable and do comparison
Trying to grab the last record.