Nested ResultSet Issue

[xxkylexx]

Hey guys,
I am having an issue with nested ResultSet's in a project I am working on. For some reason, the outer loop breaks out/stops looping after the first inner loop executes (i.e. I am only getting one iteration of the outer loop).

For example, here is my codes output with just the one outer loop executing:


import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class test {
public static void main(String[] ags) throws IOException {
Connection sqlca = null;
Statement sqlStatement = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
try {
// connect to db
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
sqlca = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE", "system", "123456");
sqlStatement = sqlca.createStatement();

rs1 = sqlStatement
.executeQuery("SELECT itemid, itemname FROM items");

while(rs1.next()){
System.out.println(rs1.getObject("itemid") + ", " + rs1.getObject("itemname"));
}


// end db connection
sqlStatement.close();

} catch (SQLException ex) {
System.out.println("\n\nSQLException:" + ex.getMessage());
}
}
}


Results:


0, Acorn Squash
1, Parsley
2, T-Bone Steak
3, Hamburger Patties
4, Chives
5, Vanilla Ice Cream
6, Arm Roast
7, organic sweet cream butter
8, Juustoleipa Cheese
9, Snakes Tongue
10, Ribeye Steak
11, Espresso Roast
12, Hamburger
13, Sirloin Steak
14, Ground Turkey
15, Peppercorn Cheddar
16, Smoked Turkey Leg
17, Sausage Links
18, Dragon Flame
19, Ground Beef
20, Turkey Apple Brats
21, Skim Milk
22, Oregano
23, Graveyard Soil
24, Organic Green Tea
25, Onion Parsley Cheddar
26, Merlot Cheddar
27, Butternut Squash
28, Tombstone Moss
29, Basilisk Tail
30, Medium Roast Decaf
31, Short Ribs
32, Asiago
33, Kale
34, Organic Earl Grey Tea
35, Red Potatoes
36, Salamander Skin
37, Celery
38, Cheddar
39, Fresh Trout
40, Chuck Roast
41, Toad Essence
42, Romano
43, Strawberry Ice Cream
44, Goat Jack Cheese
45, Cabbage
46, Eggs
47, Eau Galle Italian
48, Medium Roast Coffee
49, Chuckeye Steak
50, Bacon
51, Pork Chops boneless
52, French Roast Coffee
53, Beets
54, salted sweet cream butter
55, Chocolate Ice Cream
56, Reduced Fat Milk
57, Butter Cheese
58, Ground Chicken
59, Chevre Cheese
60, Feta Cheese
61, Smoked Trout Spread
62, Organic Magic Green Tea
63, Ewezerella Cheese
64, Sharp Cheddar
65, Whole Milk
66, Rosemary
67, Earthworm Segments
68, Turkey
69, Half and Half
70, Boneless Chicken Breast
71, Rump Roast
72, Parmesan
73, Whole Chicken
74, Pork loin sliced
75, Chocolate Milk
76, Salad Mix
77, Porterhouse Steak
78, Smoked Trout
79, Yukon Gold Potatoes
80, Frog Toes
81, Sirloin Tip Roast
82, Carrots
83, Butter Jack Cheese
84, Eye of Bat
85, Summer Sausage
86, Soup Bone
87, Smoked Gouda Cheese
88, Thyme
89, Apple Brats




And now I introduce an inner, nested loop:


import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class test {
public static void main(String[] ags) throws IOException {
Connection sqlca = null;
Statement sqlStatement = null;
ResultSet rs1 = null;
ResultSet rs2 = null;

try {
// connect to db
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
sqlca = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE", "system", "123456");
sqlStatement = sqlca.createStatement();

rs1 = sqlStatement
.executeQuery("SELECT itemid, itemname FROM items");

while(rs1.next()){
System.out.println(rs1.getObject("itemid") + ", " + rs1.getObject("itemname"));


rs2 = sqlStatement
.executeQuery("SELECT transid FROM trans WHERE itemid = '"+rs1.getObject("itemid")+"'");

while(rs2.next()){
System.out.println("-- " + rs2.getObject("transid"));
}

}


// end db connection
sqlStatement.close();

} catch (SQLException ex) {
System.out.println("\n\nSQLException:" + ex.getMessage());
}
}
}



Result:


0, Acorn Squash
-- 1
-- 9
-- 12
-- 25
-- 28
-- 35
-- 46
-- 48
-- 53
-- 56
-- 57
-- 58
-- 71
-- 78
-- 114
-- 130
-- 132
-- 145
-- 154
-- 164
-- 169
-- 183
-- 190
-- 192
-- 215
-- 236
-- 248
-- 267
-- 274
-- 275
-- 290
-- 315
-- 319
-- 356
-- 367
-- 380
-- 381
-- 395
-- 399
-- 400
-- 437
-- 438
-- 440
-- 447
-- 458
-- 459
-- 460
-- 461
-- 462
-- 469
-- 485
-- 490





Any help is appreciated.


Thanks.

[shyam]

why use nested resultsets when you can achieve the same result using a simple query like

select itemid, itemname, transid from items inner join trans on items.itemid=trans.itemid

[xxkylexx]

This is just an example, not the actual thing I am trying to achieve. It always seems to break out of the outer loop after the first iteration.

[shyam]

as long as you use the same java.sql.Statement you cannot nest resultsets...(but, IMHO running nested queries is a sure sign of a deeper design problem :eek: ...)

[xxkylexx]

Awesome. Thanks shyam. Also, yes, I am aware of the issues with nested queries.