2

I'm tying to use H2 as an in-memory database for my unit testing. I'm wiring everything together through Spring. Everything seems to work fine. The DB loads up, the ddl sql file runs, as does the dml sql (or so it seems).

I can run a test that creates a record in the database, then retrieves and validates it was appropriately persisted. So this tells me the ddl scripts ran fine.

The issue is when I try to receive a record that was inserted via the dml script, i get nothing.

I tried putting the same insert statement in the file twice and do indeed receive a unique constraint error, which tells me they are running...but for some reason not persisting.

DDL:

DROP TABLE IF EXISTS `schema`.`region` ;CREATE  TABLE IF NOT EXISTS `schema`.`region` (
  `region_id` INT(11) NOT NULL ,
  `name` VARCHAR(56) NOT NULL ,
  `description` VARCHAR(512) NULL DEFAULT NULL ,
  PRIMARY KEY (`region_id`) );

DML:

INSERT INTO `schema`.`region` (`region_id`, `name`, `description`) VALUES (1001, 'TEST', 'TESTING');

URL:

jdbc:h2:file:db_test;MODE=MYSQL;INIT=create schema if not exists test_db\\;runscript from 'classpath:test_ddl.sql'\\;runscript from 'classpath:test_dml.sql'

Any help would be helpful.

thanks

andrewsi
  • 10,807
  • 132
  • 35
  • 51
user1655798
  • 53
  • 2
  • 9

1 Answers1

1

See Where are the Database Files Stored? in the FAQ. With the database URL you used, jdbc:h2:file:db_test, the files are stored in the current working directory. Depending on where you start your application, this is a different place, so a different database is used.

I suggest to use jdbc:h2:~/db/test_db... instead.

I'm tying to use H2 as an in-memory database

You are actually using a persistent database, see the database URL overview.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • Yes, you are correct about my url indicating a file not in-memory db. My intent is to ultimately use in-memory (jdbc:h2:mem:...) I was playing with other options to see if it would work and inaccurately posted on here. my apologies. with that said, I did use the ~/db/... format and this is giving more information, which is what i need. The bulk of my trace file contains "org.h2.jdbc.JdbcSQLException: Column "FOREIGN" not found; SQL statement:" errors I have a basic statements as follows: – user1655798 Sep 10 '12 at 12:56
  • The bulk of my trace file contains "org.h2.jdbc.JdbcSQLException: Column "FOREIGN" not found; SQL statement:" errors ex statement: CREATE TABLE IF NOT EXISTS `s1`.`tableA` ( `id` INT(11) NOT NULL , `name` VARCHAR(56) NOT NULL , `description` VARCHAR(512) NULL DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE IF NOT EXISTS `s1`.`tableB` ( `tableB_id` INT(11) NOT NULL , `name` VARCHAR(12) NOT NULL , `tableA_id` INT(11) NOT NULL , PRIMARY KEY (`tableB_id`) , CONSTRAINT `fk_tableA` FOREIGN KEY (`tableA_id` ) REFERENCES `s1`.`tableA` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION); – user1655798 Sep 10 '12 at 13:05
  • This sounds like a different problem now. Could you post the complete error message (including error code) and all stack traces? – Thomas Mueller Sep 10 '12 at 14:23
  • org.h2.jdbc.JdbcSQLException: Column "FOREIGN" not found; SQL statement: alter table s1.customer_network drop foreign key FKF3FBC2CDB2644654 [42122-168] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.table.Table.getColumn(Table.java:605) at org.h2.command.Parser.parseAlterTable(Parser.java:4874) at org.h2.command.Parser.parseAlter(Parser.java:4315) at org.h2.command.Parser.parsePrepared(Parser.java:306) – user1655798 Sep 10 '12 at 14:58
  • at org.h2.command.Parser.parse(Parser.java:279) at org.h2.command.Parser.parse(Parser.java:251) at org.h2.command.Parser.prepareCommand(Parser.java:217) at org.h2.engine.Session.prepareLocal(Session.java:415) at org.h2.engine.Session.prepareCommand(Session.java:364) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1109) – user1655798 Sep 10 '12 at 14:59
  • at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:121) at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:110) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:421) at org.hibernate.tool.hbm2ddl.SchemaExport.drop(SchemaExport.java:396) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:269) at org.hibernate.tool.hbm2ddl.SchemaExport.drop(SchemaExport.java:229) at org.hibernate.impl.SessionFactoryImpl.close(SessionFactoryImpl.java:959) – user1655798 Sep 10 '12 at 15:00
  • it's happening as it's loading the hbm files. – user1655798 Sep 10 '12 at 15:01
  • This is now about Hibernate so I guess it's a different question. – Thomas Mueller Sep 10 '12 at 18:42
  • When I run this against the mysql database, all runs well. Also, the exception is thrown on a create statement, so this would lead me to believe H2 is not understanding some configuration or association somewhere, no? – user1655798 Sep 10 '12 at 19:27
  • Please create a new question. In your original one you didn't say anything about Hibernate, and the exception is different. – Thomas Mueller Sep 10 '12 at 19:30