Java EE (Java Enterprise Edition) General Discussion

Hello people, I am new here, I was trying to write a servlet that transfers Excel data to MySQL database. The core version was working fine (without a servlet), but when I add the same code to servlet its not working.

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class ExcelUpload  extends HttpServlet{

			static final String DRIVER="com.mysql.jdbc.Driver";
	          	static final String DATABASE_URL="jdbc:mysql://localhost/Examcell";

   public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
{



		response.setContentType("text/html");

	PrintWriter pw=response.getWriter();
	pw.print("<font face=Tahoma><b>Excel File Uploader v1.0:<br //><//font>");


          try{



	String sheetname=request.getParameter("sheetname");
	String tablename=request.getParameter("tablename");


pw.print("<br>Uploading Excel Sheet "+sheetname+" to database table "+tablename);






			
			Class.forName(DRIVER);
			pw.println("<br>JDBC ODBC Drivers loaded");

			Connection con1=DriverManager.getConnection(DATABASE_URL,"jhtp7","jhtp7");
  			pw.println("<br>JDBC ODBC Connections set"); 


                  	Connection con2=DriverManager.getConnection("jdbc:odbc:xdd1");
                   	pw.println("<br>Excel Drivers loaded");



                  PreparedStatement ps1,ps2;
                  ps1=ps2=null;
                  Statement st2=con2.createStatement();


                  ResultSet rs2=st2.executeQuery("select * from [emp$]");
                  ps1=con1.prepareStatement("create table "+tablename+"(name char(20),sex char(1))");
                  ps1.executeUpdate();
                  ps2=con1.prepareStatement("insert into "+tablename+" values(?,?)");
                  while(rs2.next())
{

                      String name=rs2.getString(1);
                       //System.out.println(name);
                      String sex=rs2.getString(2);
                       System.out.println("   "+sex); 
                      ps2.setString(1,name); 
                      ps2.setString(2,sex);
                      ps2.executeUpdate();

                con1.close();st2.close();
                con2.close();ps1.close();ps2.close();


                 }


                        
                     pw.println("<br>Successfully transferred !"); 
                 
             }

catch(Exception e)
{ 
pw.print("<br>"+e);

             } 

finally{

pw.close();
}
     }
 
}

The same version with same drivers and code, was working fine without a servlet.

But the core version is wrking as i set up a UserDSN in Administrative Tools> Data Source(ODBC)>UserDSN>
--->xdd - Driver do Excel (.xls)
after that i added one more
--->xdd1-Microsoft Excel Driver(.xls),
then i added one more
--->xdd2-Microsoft Excel Treiber (.xls)

This is the output I get only UserDSN and no SystemDSN :

Excel File Uploader v1.0:

Uploading Excel Sheet emp to database table jmx1
JDBC ODBC Drivers loaded
JDBC ODBC Connections set
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Then i added the same ones to System DSN

For all of them i chose the workbook C:\EXCELTEMP\workbook1.xls which contains a sheet named emp which has certain data in it and i was able to retrieve data from it with xdd driver in the core version

I use Windows XP SP2 and Tomcat 5.5.27 and MySQL 5

When run with different excel drivers, i get the error :

for xdd1 and xdd2 i get the output:

Excel File Uploader v1.0:

Uploading Excel Sheet emp to database table jmxx
JDBC ODBC Drivers loaded
JDBC ODBC Connections set
java.sql.SQLException: General error

for xdd2 i get the output:

Excel File Uploader v1.0:

Uploading Excel Sheet emp to database table jmx34
JDBC ODBC Drivers loaded
JDBC ODBC Connections set
Excel Drivers loaded
java.sql.SQLException: ResultSet is closed

Please help me guys its really important . I tried my best explaining it to u people elaborately...

Also can u tell me how to print the

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

in the browser itself as there is no command line in servlets

Edited by: Jagzz on Feb 27, 2010 8:04 PM

Edited by: Jagzz on Feb 27, 2010 8:07 PM

Edited by: Jagzz on Feb 27, 2010 8:09 PM