CODEWITHSUNDEEP
javajdbc
praveen2609
praveen2609

Reputation: 233

Not able to iterate through two while loops in single function

The First while loop iterates through only one record and then the other while loop get iterates through multiple records but first while loop is not getting reference to second record or further. Im using two different resultset object and single statement object am i doing anything wrong pls revert and thanks in advance.

   public void generateCsvFile()
   {
       String vSQL = "";
       String mSQL = "";
       String mDealer_id = "";
        try
        {
            //mSQL = "mSQL"
            mSQL = "select distinct em_controller_id as dealer_id from entity_master";
            System.out.println("mSQL :: \n"+mSQL);
            rs1 = stmt.executeQuery(mSQL);

            while(rs1.next())
            {           

                //System.out.println("@#@# Dealer id @##@#::"+rs1.getString("dealer_id"));
                mDealer_id = rs1.getString("dealer_id");
                vSQL = "select e.em_entity_id,e.em_name,e.em_address_line_1,e.em_address_line_2,e.em_address_line_3,e.em_phone_no_1,e.em_phone_no_2,e.em_dob,e.em_pan_no,e.em_exch_client_id from entity_master e where e.em_controller_id='"+mDealer_id+"'";   
                System.out.println("vSQL :: \n"+vSQL);
                rs = stmt.executeQuery(vSQL);

                FileWriter writer = new FileWriter(mDealer_id+".csv");

                while(rs.next())
                {

                    System.out.println("em_entity_id !@!@!('"+mDealer_id+"') :: "+rs.getString("em_entity_id"));
                    System.out.println("ENTITY :: "+rs.getString("em_entity_id"));
                    writer.append(rs.getString("em_entity_id"));
                    writer.append(',');
                    writer.append(rs.getString("em_name"));
                    writer.append(',');
                    writer.append(rs.getString("em_address_line_1"));
                    writer.append(',');
                    writer.append(rs.getString("em_address_line_2"));
                    writer.append(',');
                    writer.append(rs.getString("em_address_line_3"));
                    writer.append(',');
                    writer.append(rs.getString("em_phone_no_1"));
                    writer.append(',');
                    writer.append(rs.getString("em_phone_no_2"));
                    writer.append(',');
                    writer.append(rs.getString("em_dob"));
                    writer.append(',');
                    writer.append(rs.getString("em_pan_no"));
                    writer.append(',');
                    writer.append(rs.getString("em_exch_client_id"));
                    writer.append('\n');
                }
                rs.close();
//              //generate whatever data you want
                writer.flush();
                writer.close();

            }
            rs1.close();

        }
        catch(Exception e)
        {
             e.printStackTrace();
        }
    }

Upvotes: 0

Views: 246

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1500535

I suspect this is the problem:

rs1 = stmt.executeQuery(mSQL);
// later...
rs = stmt.executeQuery(vSQL);

It looks like you're using the same Statement for two concurrent queries. I'm not really surprised that that's failed. Use two separate Statement variables - ideally, ones declared within your method. Also:

  • You should also close everything within finally blocks, so that you still close them even if an exception is thrown.

  • You should use parameterized SQL rather than code like this:

    where e.em_controller_id='"+mDealer_id+"'"
  • You should use a join rather than executing N+1 queries

Upvotes: 4

Lefteris Laskaridis
Lefteris Laskaridis

Reputation: 2322

From the Java API:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/Statement.html

Upvotes: 2

Related Questions