export mysql data to csv file

Question

Hi,

I am trying to do 2 output data from mysql to a csv file. My code is as follows: public void exportData(Connection conn,String filename) { Statement stmt; String query; try { stmt = conn.createStatement();

        //For comma separated file
        query = "SELECT * into OUTFILE  '/tmp/input.csv' FIELDS TERMINATED BY ',' FROM router ";
        stmt.executeQuery(query);

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

};

I get the following error at the line where stmt.executequery is called.

java.sql.SQLException: Access denied for user 'nxy'@'%' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1476) at DBase.exportData(automateExport.java:50) at automateExport.main(automateExport.java:18)

I am able to connect to the database properly and even execute basic queries but unable to export data to a .csv file. Please help.

Thank you

Answer 1

You need to have the FILE privilege for your user account to allow for the file to be created at the server host, via a SELECT .. INTO OUTFILE command.