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.
On 28 nov, 14:31, Boy van der Werf <[email protected]> wrote:
> 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]
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
