option to download query in CSV format from JSP page
Hi all, i developing one web application in that i have to display query result and give option to user to download that query's result in CSV format. My code is able to create CSV file in jboss/bin directory but when i trying to download it, its coming in some different format, If anyone having any idea then please share. Below i have pasted my code, for reference you can check
<%@ page import="com.cw.ReadEnvVar,com.cw.connBean,java.io.*,java.util.*,java.sql.*,org.apache.log4j.Logger,org.apache.log4j.PropertyConfigurator" %>
<jsp:useBean id="com" class="com.cw.connBean"/>
<%response.setHeader("Content-Disposition","attachment; filename=\"report2.csv\";");
response.setContentType("application/octet-stream");
String chg_who=null;
String usg_type=null;
String err_code=null;
String err_desc=null;
String chg_date=null;
String acc_no=null;
String ext_id=null;
FileOutputStream fos=new FileOutputStream("report2.csv");
String dt=(String)session.getAttribute("date");
System.out.println("passing value="+ dt);
String PrepDate = dt;
String comma = ",";
Statement st = null;
Connection conn = null;
ResultSet rs = null;
connBean cb = new connBean();
ReadEnvVar rev = new ReadEnvVar();
//--Reading user name, password and database name from properties file--
String dbUser = rev.displayValue("EXTRACT_DB_USER");
String dbPsswd = rev.displayValue("EXTRACT_DB_PWFILE");
String dbName = rev.displayValue("ARBOR_CATALOG_DATABASE");
if(dbUser == null || dbPsswd == null || dbName == null)
{
System.out.println("not proper info to connect with database");
}
else
{
try
{
conn = cb.con(dbUser, dbPsswd, dbName);
System.out.println(conn);
st = conn.createStatement();
System.out.println(st);
String Pre_PrepDate = "to_date('";
String Suf_PrepDate = "','YYYY/MM/DD')";
String CompDate = null;
int i=0;
Pre_PrepDate = Pre_PrepDate.concat(PrepDate);
Pre_PrepDate = Pre_PrepDate.concat(Suf_PrepDate);
CompDate = Pre_PrepDate;
//-Query to generate Audit Control Report-
String query1 ="select account_no,child_count,hierarchy_id,bill_sequence_num,currency_code,language_code ,account_type from cmf where account_no between 13002000 and 13004500";
System.out.println(query1);
int count=0;
if(CompDate!=null){
rs=st.executeQuery(query1);
//-Writing heading in CSV file
new PrintStream(fos).print("Change Who,");
new PrintStream(fos).print("Usage Type,");
new PrintStream(fos).print("Error Code,");
new PrintStream(fos).print("Error Description,");
new PrintStream(fos).print("Change Date,");
new PrintStream(fos).print("Account No,");
new PrintStream(fos).println("External ID");
while(rs.next())
{
chg_who=rs.getString(1).trim();
chg_who=chg_who.concat(comma);
usg_type=rs.getString(2).trim();
usg_type=usg_type.concat(comma);
err_code=rs.getString(3).trim();
err_code=err_code.concat(comma);
err_desc=rs.getString(4).trim();
err_desc=err_desc.concat(comma);
chg_date=rs.getString(5).trim();
chg_date=chg_date.concat(comma);
acc_no=rs.getString(6).trim();
acc_no=acc_no.concat(comma);
ext_id=rs.getString(7).trim();
//Writing info. into file-
new PrintStream(fos).print(chg_who);
new PrintStream(fos).print(usg_type);
new PrintStream(fos).print(err_code);
new PrintStream(fos).print(err_desc);
new PrintStream(fos).print(chg_date);
new PrintStream(fos).println(acc_no);
new PrintStream(fos).print(ext_id);
}
}
else
{
System.out.println("no record");
}
fos.close();
rs.close();
st.close();
conn.close();
}catch(Exception e){
// System.out.println("problem in data fetching");
e.printStackTrace();
}
} %>
