HSQL embedded database giving "user lacks privilege or object not found"

[Lonnie Lewis]

Hello,
I'm attempting to configure an embedded HSQL database in a maven/hibernate project for unit testing. For "production mode", I have configured an Oracle database, with works fine (I can add, delete, etc.).

For unit testing, I first tried specifying in the spring config file:

	<jdbc:embedded-database id="dataSource" type="HSQL" />
	
	<jdbc:initialize-database data-source="dataSource">
		<jdbc:script location="classpath:schema.sql" />
		<jdbc:script location="classpath:test-data.sql" />
	</jdbc:initialize-database>

schema.sql contains SQL for creating all of the tables and sequences. When running, I get errors saying the tables already exist. OK, I remove that line and have just:

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

When I run that, I get "user lacks privilege or object not found" when attempting to insert the data found in the "test-data.sql" file. I assume that the tables do exist (since I get the first error message) and I just don't have access. The information I've seen don't seem to imply security credentials are needed for the embedded databases, but I'm not sure about that. I attempted to change the configuration passing credentials like:

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="org.hsqldb.jdbc.JDBCDataSource" />
		<property name="url" value="jdbc:hsqldb:mem:testdb" />
		<property name="username" value="sa" />
		<property name="password" value="" />
		<property name="initialSize" value="5" />
		<property name="maxActive" value="10" />
	</bean> 

But I get the same "user lacks privilege or object not found".

If I remove the jdbc:initialize-database element completely, I get the same "user lacks privilege or object not found" error as the test case is attempting to create a JPA object.


Any idea what I'm doing wrong?

[Lonnie Lewis]

Never mind, I got it to work. I needed to add "if not exists" in the creation of the elements. Not sure why, maybe the script is being executed more than once? I don't know...