sql statement "into outfile" not working with jdbc

Posted by Celeste Berus on Stack Overflow See other posts from Stack Overflow or by Celeste Berus
Published on 2010-01-04T14:28:39Z Indexed on 2010/05/02 6:07 UTC
Read the original article Hit count: 272

Filed under:
|
|
|

I am attempting to add an "export to CSV" feature to a webapp that displays data from a MySQL database. I have a written a "queryExecuter" class to execute queries from my servlet. I have used this to successfully execute insert queries so I know the connection works etc however queries with the "into outfile" statement are simply not executing. Here is my code,

the java class...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

public class queryExecuter {
    public void exportToCSV(String query) {
        DBase db = new DBase();
        Connection conn = db.connect(
               mydatabaseurl ,"myusername","mypassword");

        db.exportData(conn,query);
    }

}

class DBase {
    public DBase() {
    }

    public Connection connect(String db_connect_str,
            String db_userid, String db_password) {
        Connection conn;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(db_connect_str,
                    db_userid, db_password);

        } catch(Exception e) {
            e.printStackTrace();
            conn = null;
        }
        return conn;
    }

    public void exportData(Connection conn,String query) {
        Statement stmt;

        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            stmt.execute(query);

        } catch(Exception e) {
            e.printStackTrace();
            stmt = null;
        }
    }
};

The servlet...

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public class MyExportServlet extends HttpServlet {

  public void doPost(HttpServletRequest request, HttpServletResponse response)

     throws IOException, ServletException {

            String query = "select into outfile 'theoutfile.txt' * from mytable;";

            request.setAttribute("query", query);

            queryExecuter mydata = new queryExecuter();

            mydata.exportToCSV(query);

            RequestDispatcher view = request.getRequestDispatcher("ConfirmationPage.jsp");

            view.forward(request, response);
    }
}

Any help would be greatly appreciated.

Thank you

© Stack Overflow or respective owner

Related posts about java

Related posts about mysql