Running a script to create tables with HSQLDB

Question

I use hsqldb to run my unit tests that need a database access.

For the moment, when I want to create a table for a specific test, I have the following code:

private void createTable() {
    PreparedStatement ps;
    try {
        ps = getConnection().prepareStatement("CREATE TABLE T_DATE (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP)");
        ps.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The getConnection() method retrieve a DataSource defined in a Spring context:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
    <property name="url" value="jdbc:hsqldb:mem:memoryDB"/>
    <property name="username" value="SA"/>
    <property name="password" value=""/>
</bean>

Now, I want to create my table from a SQL script (of course, this script will contain more than one table creation):

CREATE TABLE T_DATE_FOO (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP);
CREATE TABLE T_DATE_BAR (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP);
...

I've seen in the HSQLDB documentation that I can ask him to run a script at the startup. However, it does not meet my requirements, as I want to run a script at the runtime.

Of course, I can read the file myself, and for every SQL statement, I run a ps.executeUpdate() command, but I don't want to use this kind of solution (except if there are no other solution).

Any idea?

score 4 · Answer 1 · edited Sep 13 '11 at 20:53

4

You might give org.hsqldb.util.SqlFile a try. This class seems to be a perfect match for your problem.

edited Sep 13 '11 at 20:53

Jérôme Verstrynge

57,710
92
283
453

answered Dec 20 '10 at 08:10

Ralf Huthmann

41
1

sqltool and SqlFile are not in Fedora hsqldb-2.4.0 package, and don't seem to be in the source. It seems to be a separate product, perhaps with licensing incompatible with Fedora. – Stuart Gathman Oct 23 '19 at 20:27
In the current version (hsqldb 2.7.2), the class SqlFile is in a different namespace org.hsqldb.cmdline. Also, full qualified class name is org.hsqldb.cmdline.SqlFile. [Here](https://hsqldb.org/doc/verbatim/src/org/hsqldb/sample/SqlFileEmbedder.java) is an example of a program that loads/used a SQL file. – XelaNimed Aug 16 '23 at 16:29

score 2 · Accepted Answer · edited Jul 06 '10 at 20:12

2

since you're already using spring, you might want to use the SimpleJdbcUtils.executeSQLScript method which executes an SQL script where the statements are separated with semicolon. this class is in the spring-test module (JAR).

edited Jul 06 '10 at 20:12

Tom H

46,766
14
87
128

answered Feb 19 '10 at 10:28

Stefan De Boey

2,344
16
14

This is working, thanks! However, I don't understand why Spring refuse to have a statement in several lines (even with a `;` at the end)... – Romain Linsolas Feb 19 '10 at 12:41
1

which executeSqlScript method do you use ? there's a difference in implementation – Stefan De Boey Feb 19 '10 at 14:39

score 2 · Answer 3 · answered Feb 19 '10 at 10:49

2

First of all, I do not know implications of this. I used it long time back it worked for me. The SQLExec class is from ant.jar, you can probably look into the ant source to create your own utility class,

SQLExec sqlExec=new SQLExec();
sqlExec.setUserid("user");
sqlExec.setPassword("passowrd");
sqlExec.setUrl("jdbc:mysql://localhost:3306/dbname");
sqlExec.setDriver("com.mysql.jdbc.Driver");
sqlExec.setProject(new Project());
sqlExec.setSrc(new File("c:/test.sql"));
sqlExec.execute();

answered Feb 19 '10 at 10:49

Adisesha

5,200
1
32
43

What would the ant file look like to simply initialize an hsqldb database from an sql script file? I am building openas2 which uses maven, but I could add ant as a build requirement and run an ant file as part of installation. – Stuart Gathman Oct 23 '19 at 20:30
I just used an actual ant build file, which worked perfectly to run arbitrary SQL to initialize the database: https://src.fedoraproject.org/rpms/openas2/blob/master/f/hsqldb.xml – Stuart Gathman Oct 23 '19 at 22:52

score 0 · Answer 4 · answered Feb 19 '10 at 10:22

0

I had the same problem. I ended up splitting the text file by ; and executing each statement separately. It was OK because we had no inserts therefore no semicolons inside statements. I haven't found an easy way to run an SQL script at that time

answered Feb 19 '10 at 10:22

artemb

9,251
9
48
68

In my case, splitting scripts will be easy, as the scripts are not really complicated and has no specific characters. However, I would really prefer a better solution if it exists... – Romain Linsolas Feb 19 '10 at 10:26

Running a script to create tables with HSQLDB

4 Answers4

Linked