Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using nested Resultsets in Routines

 
dipti khullar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
dipti khullar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use two different Statement objects for two different ResultSets.
 
dipti khullar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks again.
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're underestimating the powers of the DB. Checkout the SQL JOIN clause.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic