Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Apr 2006
    Posts
    50
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Nested ResultSet Issue

    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:

    Code:
    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:

    Code:
    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.
    Last edited by xxkylexx; 12-04-2008 at 11:14 PM.

  • #2
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    why use nested resultsets when you can achieve the same result using a simple query like

    Code:
    select itemid, itemname, transid from items inner join trans on items.itemid=trans.itemid
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • #3
    New Coder
    Join Date
    Apr 2006
    Posts
    50
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.
    Last edited by xxkylexx; 12-05-2008 at 06:12 AM.

  • #4
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    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 ...)
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • Users who have thanked shyam for this post:

    xxkylexx (12-05-2008)

  • #5
    New Coder
    Join Date
    Apr 2006
    Posts
    50
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Awesome. Thanks shyam. Also, yes, I am aware of the issues with nested queries.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •