CODEWITHSUNDEEP
javasqljdbch2sql2o
Amir
Amir

Reputation: 2416

Error while inserting in sql2o

I want to create table and insert some values into it. I'm trying to do it using H2 database and sql2o framework at the code below:

public class Main {

    private static final String DB_DRIVER = "org.h2.Driver";
    private static final String DB_CONNECTION = "jdbc:h2:~/test";
    private static final String DB_USER = "";
    private static final String DB_PASSWORD = "";
    static String TABLE = "PERSONS";
    static Sql2o sql2o;

    public static void main(String[] args) throws Exception, SQLException {
        sql2o = new Sql2o(DB_CONNECTION, DB_USER, DB_PASSWORD);
        createTable();
        insertIntoTable();
    }

    public static void createTable() {
        final String tableSql = "CREATE TABLE IF NOT EXISTS " + TABLE + " (id int, name varchar(255))";
        try (org.sql2o.Connection con = sql2o.beginTransaction()) {
            con.createQuery(tableSql).executeUpdate();
            con.commit();
            con.close();
        }
    }

    public static void insertIntoTable() {
        String insertSql = "insert into " + TABLE + " values (:id, :name)";
        try (org.sql2o.Connection con = sql2o.open()) {
            con.createQuery(insertSql).addParameter("id", 1).addParameter("name", "test").executeUpdate();
            con.close();
        }
    }
}

And after all I get an error:

Error preparing statement - Column count does not match; SQL statement: insert into PERSONS values (?, ?) [21002-191]

The SQL statement is correct, the names and types of columns match each other, and I really can't understand what the problem.

Upvotes: 3

Views: 1264

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

There was an existing PERSONS table that did not match the structure in the CREATE TABLE IF NOT EXISTS statement. Dropping and recreating the table solved the problem.

Upvotes: 3

Related Questions