CODEWITHSUNDEEP
javamysqljdbc
Aaron Decker
Aaron Decker

Reputation: 558

MySQL Insert error?

I am writing a program that tracks sales for a local business. I wrote a method to export a table to a csv file, then right after load that csv file into a new table for archiving purposes; however, when it runs through the method archive() it will only run through the while loop once, works but then on the second loop through it gives me this error: 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'N' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at serverarchiver.ServerGui.archive(ServerGui.java:118)
at serverarchiver.ServerGui.run(ServerGui.java:144)
at java.lang.Thread.run(Thread.java:722)

Here is the relevant code:

 public void archive(){
      try {

             //Archive and new table

             stmt.executeQuery("SELECT * INTO OUTFILE '"+ getMonth()+"" +  getYear() +  ".csv' FIELDS TERMINATED BY ',' FROM last");
             stmt.executeUpdate("CREATE  TABLE `lastdb`.`"+ getMonth()+"" +  getYear() +  "` (`Name` VARCHAR(50) NOT NULL ,`Goal` INT  NOT NULL ,`New` INT NOT NULL , `Used` INT NOT NULL  , `Total` INT NOT NULL , `Pace` INT NOT NULL  ,PRIMARY KEY (`Name`, `Goal`, `New`, `Used`, `Total`, `Pace`) );");

             CSVReader reader = new CSVReader(new FileReader("C:/ProgramData/MySQL/MySQL Server 5.5/data/lastdb/"+getMonth()+"" +  cal.getInstance().get(Calendar.YEAR) +  ".csv"));
             String[] nextLine;

             while((nextLine = reader.readNext()) != null){
                 stmt.executeUpdate("INSERT INTO `lastdb`.`"+ getMonth()+"" +  cal.getInstance().get(Calendar.YEAR) +  "` (`Name`, `Goal`, `New`, `Used`, `Total`, `Pace`) VALUES ('"+ nextLine[0]+ "', " +nextLine[1]+ " , " +nextLine[2]+ "  , " +nextLine[3]+ "  , " +nextLine[4]+ "  , " +nextLine[5]+ ")");
             }
         }catch(FileNotFoundException e){
            e.printStackTrace();
         }catch(IOException ed){
            ed.printStackTrace();
         }catch(SQLException eds){
            eds.printStackTrace();
         }
 }

Upvotes: 0

Views: 605

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40061

So you want to move the entire table into a new table without changing anything.

Have you considered simply renaming the table and creating a new?

RENAME TABLE last TO lastdb.<month><year>;
CREATE TABLE last LIKE lastdb.<month><year>;

That's going to be lightning fast and much safer.

Upvotes: 0

BalusC
BalusC

Reputation: 1109695

That can only be caused by a SQL injection attack of the CSV file which you're reading.

In other words, the SQL string which you're concatenating there with unvalidated CSV values could have resulted in a malformed SQL string. I suggest to do a System.out.println() of the populated SQL string before it's been executed, so that you can check what exactly made the SQL query invalid.

You can fix it by escaping the CSV values accordingly, but best solution would be to use PreparedStatement instead of Statement. E.g.

preparedStatement = connection.prepareStatement("INSERT INTO `lastdb`.`" + getMonth() + "" +  cal.getInstance().get(Calendar.YEAR) 
    + "` (`Name`, `Goal`, `New`, `Used`, `Total`, `Pace`) VALUES (?, ?, ?, ?, ?, ?)");

while ((nextLine = reader.readNext()) != null) {
    // ...
    preparedStatement.setString(1, nextLine[0]);
    preparedStatement.setString(2, nextLine[1]);
    preparedStatement.setString(3, nextLine[2]);
    preparedStatement.setString(4, nextLine[3]);
    preparedStatement.setString(5, nextLine[4]);
    preparedStatement.setString(6, nextLine[5]);
    preparedStatement.executeUpdate();
}

It not only escapes column values, but it also improves performance. You can even improve it further by using batches by addBatch() and executeBatch().

while ((nextLine = reader.readNext()) != null) {
    // ...
    preparedStatement.setString(1, nextLine[0]);
    preparedStatement.setString(2, nextLine[1]);
    preparedStatement.setString(3, nextLine[2]);
    preparedStatement.setString(4, nextLine[3]);
    preparedStatement.setString(5, nextLine[4]);
    preparedStatement.setString(6, nextLine[5]);
    preparedStatement.addBatch();
}

preparedStatement.executeBatch();

See also:

Upvotes: 1

Related Questions