Unable to insert data from spring-jdbc with SQL script (error code 42122)

[Boy van der Werf]

I am trying to use H2 as an embedded database for unit testing with
Spring. For this I use spring-jdbc.
Everything seems to be in order untill I try to insert data from a SQL
file.

My Spring JDBC configuration is like this:
<jdbc:embedded-database id="dataSource" type="H2" >
<jdbc:script location="classpath:/sql/h2-schema.sql" />
</jdbc:embedded-database>

The SQL script contains the creation of tables etc. As soon as I add
an INSERT at the end of the SQL file (or in a seperate file within a
second jdbc:script line) I get an error telling me I have an invalid
column name in my statement (see partial log below).

Even more puzzling for me is that I am able to insert data using my
test classes.

Does anybody have any idea what I'm missing here?

Kind regards,
Boy van der Werf

For the logging below my SQL script contains:
--start SQL script
SET TRACE_LEVEL_SYSTEM_OUT 3;

CREATE TABLE "MYTABLE" (
"COL1" VARCHAR(10) NOT NULL,
"COL2" VARCHAR(10) NULL,
PRIMARY KEY(COL1)
);

INSERT INTO MYTABLE (COL1, COL2) VALUES ("A", "B");
--end SQL script

/*SQL t:1322484314236*/SET TRACE_LEVEL_SYSTEM_OUT 3;
11-28 13:45:14 command: slow query: 1,322,484,314,243 ms
11-28 13:45:14 jdbc[2]:
/**/stat31.executeUpdate(" CREATE TABLE \"MYTABLE\" ( \"COL1\"
VARCHAR(10) NOT NULL, \"COL2\" VARCHAR(10) NULL, PRIMARY
KEY(COL1) )");
11-28 13:45:14 lock: 2 exclusive write lock requesting for SYS
11-28 13:45:14 lock: 2 exclusive write lock added for SYS
11-28 13:45:14 lock: 2 exclusive write lock unlock SYS
11-28 13:45:14 lock: 2 exclusive write lock requesting for SYS
11-28 13:45:14 lock: 2 exclusive write lock added for SYS
11-28 13:45:14 lock: 2 exclusive write lock requesting for MYTABLE
11-28 13:45:14 lock: 2 exclusive write lock added for MYTABLE
11-28 13:45:14 jdbc[2]:
/*SQL t:8*/CREATE TABLE \"MYTABLE\" ( \"COL1\" VARCHAR(10) NOT
NULL, \"COL2\" VARCHAR(10) NULL, PRIMARY KEY(COL1) );
11-28 13:45:14 lock: 2 exclusive write lock unlock SYS
11-28 13:45:14 lock: 2 exclusive write lock unlock MYTABLE
11-28 13:45:14 jdbc[2]:
/**/stat31.executeUpdate(" INSERT INTO MYTABLE (COL1, COL2) VALUES (\"A
\", \"B\")");
11-28 13:45:14 jdbc[2]: exception
org.h2.jdbc.JdbcSQLException: Column "A" not found; SQL statement:
INSERT INTO MYTABLE (COL1, COL2) VALUES ("A", "B") [42122-161]

[Boy van der Werf]

I already solved this one myself.

I split up the table creation and the insert in seperate SQL files.
The table creation file is the one I use in the <jdbc:embedded-
database />. The SQL file with the insert is now handled by a
<jdbc:initialize-database />

<jdbc:initialize-database data-source="dataSource">
<jdbc:script location="classpath:sql/test-data.sql"/>
</jdbc:initialize-database>

Still I feel odd that this is required.

[Thomas Mueller]

Hi,

You need to use single quotes not double quotes.

INSERT INTO MYTABLE (COL1, COL2) VALUES ('A', 'B');

instead of:

INSERT INTO MYTABLE (COL1, COL2) VALUES ("A", "B");

Regards,
Thomas

[Boy van der Werf]

You're absolutely right. Can't beleive I missed that ....

Thanx