my dog learned polymorphism*
The moose likes JDBC and the fly likes Using nested Resultsets in Routines Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using nested Resultsets in Routines " Watch "Using nested Resultsets in Routines " New topic
Author

Using nested Resultsets in Routines

dipti khullar
Greenhorn

Joined: Oct 23, 2008
Posts: 6
package routines;
import java.io.*;
import java.sql.*;

public class GetCategoryName {

public static void getCategoryName() {
try {
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbc:oracle:thin:@10.210.236.32:1521:xe",
"proj_staging",
"proj");
Statement s=con.createStatement();
String category = "";
String [] list = {};
String list_cat = "";
int id_av=0;
int count=0;
ResultSet rs_category=null;

ResultSet rs_breadcrumb = s.executeQuery("select ID_AUDIOVIDEO, SOURCE_SYSTEM_BREADCRUMB from AUDIOVIDEO");

while(rs_breadcrumb.next()) {

id_av = rs_breadcrumb.getInt(1);
category = rs_breadcrumb.getString(2);
System.out.println("id_av " + rs_breadcrumb.getInt(1));
System.out.println("category_ID " + rs_breadcrumb.getString(2));

rs_category = s.executeQuery("select CATEGORY_NAME from RBAR_CATEGORY WHERE ID_CATEGORY IN (" + category + ")");

rs_category.next();
list[0] = rs_category.getString(1);
for(int i=1;rs_category.next();i++)
{ list[i] = list[0] + "," + rs_category.getString(1);
System.out.println("list " + list[i]);
}
//I will be able to update the record, but the value in list is incorrect.
}

s.close();
con.close();
} catch(Exception e){e.printStackTrace();}
}

Problem 1: First loop. rs_breacrumb.next() executes only once for the first row and it breaks
Problem 2: The list[i] doesn't store correct values, it throws exception.


Summarizing the issue:

SOURCE_SYSTEM_BREADCRUMB stores the ID_CATEGORY values as "1,2,3,4,5"
which I need to replace with "cat1,cat2,cat3.cat4", corresponding to ID_AUDIOVIDEO, which means using nested resultsets.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

For your second issue most likely its throwing an ArrayIndexOutOfBoundsException.

String [] list = {};

probably you are missing the array dimension

String list[]=new String[2];

and most likely the exception itself is leading the resultset iteration problem.


Swastik
dipti khullar
Greenhorn

Joined: Oct 23, 2008
Posts: 6
Thanks a lot for your reply.

You were absolutely right.
But that resultset iteration problem is still not resolved.

I found another way of implementing that some time back using function calls and passing relevant info into that.

Thanks. It really helped and saved my considerable time of debugging.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

Use two different Statement objects for two different ResultSets.
dipti khullar
Greenhorn

Joined: Oct 23, 2008
Posts: 6
Thanks again.
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
You're underestimating the powers of the DB. Checkout the SQL JOIN clause.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using nested Resultsets in Routines
 
Similar Threads
<rich:tree> and database
after space remaing string is not displayed
jdbc connection to ms sql (urgent)
setAutoCommit(false) not working
sorting in Chinese