Error while inserting in sql2o

Question

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.

I am unable to reproduce your issue. I copied and pasted your code into a Maven project using the latest production releases of H2 and sql2o and it worked fine. Try deleting your H2 database file or issuing a `DROP TABLE PERSONS` at the beginning of your code. — Gord Thompson, May 16 '16 at 02:26
Oh, I've really forgot to drop the table! Thank you, now it works! — Amir, May 16 '16 at 02:52

score 3 · Accepted Answer · answered May 16 '16 at 03:05

3

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.

answered May 16 '16 at 03:05

Gord Thompson

116,920
32
215
418

Error while inserting in sql2o

1 Answers1