help on transactions

[Piradharsan Navaratnam]

Hello friedns I need to run a query and based on its results to run another query. How can I implement it. Im using mysql innodb.

import java.sql.*;
import javax.sql.*;
import javax.servlet.http.*;
import java.util.*;
 
public class QueryArticleBean{
 
  QueryArticleBean(){
  }
 
  private String adate, status, ddate, notes;
  private long spid;
  private Statement stmt = null;
  private Connection con = null;
  private ResultSet rs_01 = null, rs_02 = null, rs_03 = null;
 
  public boolean retrieveArticle(DataSource ds, HttpServletRequest req){
 
    try{
      con = ds.getConnection();
      stmt = con.createStatement();
 
      spid = Long.parseLong(req.getParameter("spid"));
 
      String query_01 = "SELECT adate,status FROM article WHERE spid=" + spid ;
      String query_02 = "SELECT ddate FROM delivered WHERE spid=" + spid;
      String query_03 = "SELECT notes FROM notdelivered WHERE spid=" + spid;
 
      rs_01 = stmt.executeQuery(query_01);
 
      while(rs_01.next()){
        adate = rs_01.getString("adate");
        status = rs_01.getString("status");
 
        req.setAttribute("spid",Long.toString(spid));
        req.setAttribute("adate",adate);
        req.setAttribute("status",status);
 
        if("DELIVERED".equals(status)){
          rs_02 = stmt.executeQuery(query_02);
          ddate = rs_02.getString("ddate");
          req.setAttribute("ddate",ddate);
        }
 
        if("NOT DELIVERED".equals(status)){
          rs_03 = stmt.executeQuery(query_03);
          notes = rs_03.getString("notes");
          req.setAttribute("notes",notes);
        }
 
        return true;
       }
 
      return false;
 
 
    }catch(Exception e){
      System.out.println(e.getMessage());
    }finally{
      try{
        if(con != null){
          con.close();
        }
      }catch(Exception e){
        System.out.println(e.getMessage());
      }
 
    }
    return false;
  }
}

[ UD: added CODE tags for better readability ]
[ August 19, 2007: Message edited by: Ulf Dittmer ]

[Ulf Dittmer]

You don't say what happens if you run the code, but I'm guessing that it doesn't work because there can only be a single ResultSet active per Statement. Try what happens if you use different Statement objects for rs_02 and rs_03.

In the future, please UseCodeTags when posting ocde of any length.