5

I have encountered an error, java.sql.SQLException: 

    Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
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:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)

It's super frustrating, because I have been making amendments to the code like changing in the arg to all string, but the error still appears.

public void readDataBase(int val, String d1, String d2, String d3, String d4 ) throws     Exception {
try {
// This will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection with the DB
connect = DriverManager
.getConnection("jdbc:mysql://localhost/MAXdb?"+ "user=root&password=");

// Statements allow to issue SQL queries to the database
statement = connect.createStatement();
// Result set get the result of the SQL query       
   resultSet = statement
.executeQuery("select * from MAXdb.emcsg");
writeResultSet(resultSet);

// PreparedStatements can use variables and are more efficient
preparedStatement = connect
.prepareStatement("insert into  MAXdb.emcsg values (default,?, ?, ? , ?, ?)");
// Parameters start with 1

preparedStatement.setInt(1, val);
    preparedStatement.setString(2, d1);
preparedStatement.setString(3, d2);
preparedStatement.setString(4, d3);
preparedStatement.setString(5, d4);


preparedStatement.executeUpdate();

preparedStatement = connect
.prepareStatement("SELECT  id, Date, Time, Demand, SUPPLY from MAXdb.emcsg");
resultSet = preparedStatement.executeQuery();
writeResultSet(resultSet);



    } catch (Exception e) {
throw e;
} finally {
close();
}

}

My second class:

public void Csvreader() throws IOException {
try {
// TODO code application logic here

CSVReader reader = new CSVReader(new FileReader("D:/TEST.csv"));

String  nextLine[];
int i = 1;
Mysql sen = new Mysql();
while ((nextLine = reader.readNext()) != null) {
try {
sen.readDataBase( i, nextLine[0], nextLine[1], nextLine[2], nextLine[3] );
i = i+1;
} catch (Exception ex) {
Logger.getLogger(Opencsv.class.getName()).log(Level.SEVERE, null, ex);
}
}


} catch (FileNotFoundException ex) {
Logger.getLogger(Opencsv.class.getName()).log(Level.SEVERE, null, ex);
}

}

Database: Field Type Collation Attributes Null Default Extra Action id int(11) No None
Date text utf8_general_ci No None
Time text utf8_general_ci No None
Demand text utf8_general_ci No None
SUPPLY text utf8_general_ci

Eugene
  • 263
  • 2
  • 5
  • 16

6 Answers6

10

Well, I suspect this is the problem:

insert into  MAXdb.emcsg values (default,?, ?, ? , ?, ?)

You haven't specified which column each of those parameters is meant to refer to - and I suspect you've not got 6 columns. Even if you do have 6 columns in the table, it would be a good idea to explicitly state in the SQL which column you mean to use for each parameter.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
5

It seems that the following statement: 

insert into  MAXdb.emcsg values (default,?, ?, ? , ?, ?)

causes an error. Check if emcsg has 6 columns.

Piotr Gwiazda
  • 12,080
  • 13
  • 60
  • 91
3

Had you tried changing this

preparedStatement = connect
.prepareStatement("insert into  MAXdb.emcsg values (default,?, ?, ? , ?, ?)");

to this

preparedStatement = connect
.prepareStatement("insert into  MAXdb.emcsg values (?, ?, ? , ?, ?)");

That might work for you.

Regards

nIcE cOw
  • 24,468
  • 7
  • 50
  • 143
2

Do you definitely have six columns in the database table?

Always google an error message or error code. Quite often the first link returned will clearly explain the problem and provide a solution.

Paul Medcraft
  • 1,386
  • 11
  • 23
2

It seems to me, that there is a problem with your database structure. I think you don't have all the fields you assume you have.

Dragos
  • 2,911
  • 12
  • 39
  • 55
0

Your method signature

public void readDataBase(int val, String d1, String d2, String d3, String d4 )

has 5 variables, but your method call has 6:

preparedStatement = connect
.prepareStatement("insert into  MAXdb.emcsg values (default,?, ?, ? , ?, ?)");
Clive van Hilten
  • 851
  • 5
  • 16
  • 32
  • 1
    That doesn't matter, the method could hard code some of the values or calculate them from one or more of the other arguments. – Paul Medcraft Jan 16 '12 at 16:00
  • My previous comment was downvoted. Perhaps I wasn't clear: it doesn't matter how many arguments the method sig has, the important part is that the INSERT statement has the same number of values as the table has columns. – Paul Medcraft Jan 16 '12 at 16:07
  • @PaulMedcraft : My bad, I did noticed, wanted to vote someone else, but seems like clicked at the wrong place. So sorry, my bad. I realized my mistake once i clicked it. Regards – nIcE cOw Jan 16 '12 at 16:15
  • My bad actually mate, it wasn't downvoted at all! – Paul Medcraft Jan 16 '12 at 16:21