Nested ResultSets in JSP

[carmelo]

Hi everybody,
I'm developing a simple JSP web app, which needs to execute 2 nested
sql queries:
1) executing a SELECT sql query, for extracting "base" data from
myTable_base
2) for each record found with the (1) query, execute a query for
extracting "special" data from myTable_special

I used this code:

Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
try
{
String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
Class.forName(DRIVER);

conn=DriverManager.getConnection("jdbcdbc:Sirx2;ServerName=192.168.1.50.1543;ServerDSN=sirx41","sirx","sirx");

query="SELECT * from myTable_base";
stmt=conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next())
{
field = rs.getString("field_base");

query="SELECT * from myTable_special";

Statement stmt2=null;
ResultSet rs2=null;
try
{

stmt2=conn.createStatement();
rs2 = stmt2.executeQuery(query);

//ottiene il dato
if(rs2.next())
{
field = rs.getString("field_special");
}
}
catch(Exception e)
{
out.println(e);
}
finally
{
try
{
if (rs2 != null)
rs2.close();
}
catch (Exception e)
{
out.println(e);
}

try
{
if (stmt2 != null)
stmt2.close();
}
catch (Exception e)
{
out.println(e);
}
}
out.println("Data: "+field);
}
}
catch(Exception e)
{
out.println(e);
}
finally
{
try
{
if (rs != null)
rs.close();
}
catch (Exception e)
{
out.println(e);
}


try
{
if (stmt != null)
stmt.close();
}
catch (Exception e)
{
out.println(e);
}


try
{
if (conn != null)
conn.close();
}
catch (SQLException e)
{
out.println(e);
}
}


It seems that the external while loop ends when the inner IF condition
is true...


I hope in your help
Thank you very much in advance
Carmelo

 

[shakah]

Hi everybody,
I'm developing a simple JSP web app, which needs to execute 2 nested
sql queries:
1) executing a SELECT sql query, for extracting "base" data from
myTable_base
2) for each record found with the (1) query, execute a query for
extracting "special" data from myTable_special

I used this code:
[...example code snipped...]

It seems that the external while loop ends when the inner IF condition
is true...

I hope in your help
Thank you very much in advance
Carmelo

Check if your JDBC driver supports multiple open Statments and/or
ResultSets
on the same Connection.

In any case, you might be able to sidestep this particular
issue by doing your fetch in a single query with a join
to the "special" (child) table, e.g.

// ...assume the connection is setup, and a
// one-to-many relationship between base and special
// tables on an "id" column, and an class (provided by
// you) to describe the base/special data
try {
String q = "SELECT base.id, base.name, special.data"
+ " FROM myTable_base base"
+ " LEFT JOIN myTable_special special"
+ " ON special.bid=base.id"
+ " ORDER BY 1,2,3"
;
java.sql.Statement stmt = conn.createStatement() ;
java.sql.ResultSet rs = stmt.executeQuery(q) ;
String cur_base_obj = null ;
java.util.ArrayList al = new java.util.ArrayList() ;
while(rs.next()) {
String base_id = rs.getString(1) ;
String base_name = rs.getString(2) ;
String special_data = rs.getString(3) ;

// ...new myTable_base row?
if(null==cur_base_obj || !cur_base_obj.id().equals(base_id)) {
// ...we have a new base row
if(null!=cur_base_obj) {
al.add(cur_base_obj) ;
}
cur_base_obj = new my_base_obj(base_id, base_name) ;
}

// ...record the special data (if there is any, that is)
if(null!=special_data) {
cur_base_obj.add_special_data(special_data) ;
}
}

// ...the array list holds the all the myTable_base info, along with
// the "special data"
}
....etc...

 

[carmelo]

I'm developing a simple JSP web app, which needs to execute 2 nested
sql queries:
1) executing a SELECT sql query, for extracting "base" data from
myTable_base
2) for each record found with the (1) query, execute a query for
extracting "special" data from myTable_special

I used this code:

[...example code snipped...]

It seems that the external while loop ends when the inner IF condition
is true...

I hope in your help
Thank you very much in advance
Carmelo

Check if your JDBC driver supports multiple open Statments and/or
ResultSets
on the same Connection.

In any case, you might be able to sidestep this particular
issue by doing your fetch in a single query with a join
to the "special" (child) table, e.g.

// ...assume the connection is setup, and a
//    one-to-many relationship between base and special
//    tables on an "id" column, and an class (provided by
//    you) to describe the base/special data
try {
  String q =   "SELECT base.id, base.name, special.data"
             + "  FROM myTable_base base"
             + "       LEFT JOIN myTable_special special"
             + "              ON special.bid=base.id"
             + " ORDER BY 1,2,3"
             ;
  java.sql.Statement stmt = conn.createStatement() ;
  java.sql.ResultSet rs = stmt.executeQuery(q) ;
  String cur_base_obj = null ;
  java.util.ArrayList al = new java.util.ArrayList() ;
  while(rs.next()) {
    String base_id = rs.getString(1) ;
    String base_name = rs.getString(2) ;
    String special_data = rs.getString(3) ;

    // ...new myTable_base row?
    if(null==cur_base_obj || !cur_base_obj.id().equals(base_id)) {
      // ...we have a new base row
      if(null!=cur_base_obj) {
        al.add(cur_base_obj) ;
      }
      cur_base_obj = new my_base_obj(base_id, base_name) ;
    }

    // ...record the special data (if there is any, that is)
    if(null!=special_data) {
      cur_base_obj.add_special_data(special_data) ;
    }
  }

  // ...the array list holds the all the myTable_base info, along with
  //    the "special data"}

...etc...

Thank you shakah, now it works
I used two different Connections for each Statement, without any JOIN
(which is not supported by this really old dbms)


Cheers
Carmelo

 

[Andrea Francia]

Thank you shakah, now it works
I used two different Connections for each Statement, without any JOIN
(which is not supported by this really old dbms)

What is the name of this DBMS?

 

[carmelo]

What is the name of this DBMS?

Unify