How to append data in CSV file using CSVWriter?

[Happy S Singh]

I want to append data of two tables in a resultset. I have tried the below code but not getting the desired output only the first resultset data i.e. the first table data

 ResultSet rs=null;
            String sql_query="select * from exception_main;select * from m_roles"
            String query1=sql_query.toUpperCase();
                        String[] results=query1.split(";");
                for(int i=0;i<results.length;i++)
                    {
                        if(results[i].startsWith("SELECT"))
                        {
                            System.out.println("Inside select"+ results[i]);
                        ps = conn1.prepareStatement(results[i].toString());
                        rs = ps.executeQuery();

    ...
                   //writing to csv file
                    CSVWriter writer = new CSVWriter(new FileWriter(csv_file_path + csv_file_name), ',',CSVWriter.NO_QUOTE_CHARACTER);
                    System.out.println("Count..." + rs.getRow());
                    writer.writeAll(rs, true);
                    System.out.println("Count...2::::" + rs.getRow());
                    writer.close();
                     while(rs.next()){

                            rs.deleteRow();

                          }
                     System.out.println("Count...3:::::::" + rs1.getRow());
}
}

I am getting Count as 0 in all three places Guide me please.

[Steve Luke]

HappyS Singh wrote:...I am getting Count as 0 in all three places Guide me please.

 System.out.println("Count..." + rs.getRow());

The count is zero because you are getting a result set with no results from your SQL query.

[Happy S Singh]

Despite of count 0, I get all records of second query in CSV file.
But I want both output..means 1st queries output appended with 2nd queries output.

[Paul Clapham]

But... the getRow() method returns the current row number. Which will always be zero when you haven't read any rows from the ResultSet, as in the code posted.

[Happy S Singh]

Okay.I got about getRow().
Now how to append the result in CSV file.

[Steve Luke]

Paul Clapham wrote:But... the getRow() method returns the current row number. Which will always be zero when you haven't read any rows from the ResultSet, as in the code posted.

Yeah, I mis-read the method name (I thought is was getRows()) and was gonna edit my post. Stopped when I saw responses so it wouldn't be ninja-editing.

[Paul Clapham]

If you want to append to a file, then you should use the FileWriter constructor with the parameter which specifies that. The constructor you chose always overwrites the data in the file, if it already exists.

[Stuart A. Burkett]

Or just open the file before the for loop and close it afterwards, rather than opening and closing it every time round the loop.

[Happy S Singh]

thanks alot Paul Clapham and Adrian Burkett.

[Paul Clapham]

Adrian Burkett wrote:Or just open the file before the for loop and close it afterwards, rather than opening and closing it every time round the loop.

Yes, I think that's a better fix than my suggestion.

[Happy S Singh]

Now I am able to write it in file..but when the ouput is large ,I get

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.9.78] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

exception

[Happy S Singh]

Resolved..Just moved ps = conn1.prepareStatement(results[i].toString()); outside the for loop