73

First of all I would like to say am new to h2 database. I need to execute a sql script file in h2 database. I have a script file test.sql and I want to execute this in h2 database. Is it possible?

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
Kamalam
  • 1,254
  • 6
  • 18
  • 32

4 Answers4

111

You can use the RUNSCRIPT SQL statement:

RUNSCRIPT FROM 'test.sql'

or you can use the RunScript standalone / command line tool:

java -cp h2*.jar org.h2.tools.RunScript -url jdbc:h2:~/test -script test.sql

You can also use the RunScript tool within an application:

RunScript.execute(conn, new FileReader("test.sql"));
Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • 2
    Is there a trick to using your first example? I have a `bootstrap.sql` that contains nothing but `RUNSCRIPT FROM 'test.sql'`and my test.sql is a sibling to the bootstrap file. If I run `jdbc:h2:mem:etsnom;INIT=RUNSCRIPT FROM 'C:/workspace/bootstrap.sql'`, I get a `FileNotFoundException` – Snekse Jul 17 '15 at 16:50
  • 2
    If we use, 2nd method of Thomas to create database, then default username and password will be set i.e. "" [empty string]. which is what implemented in "RunScript' class. However, It is always very important to set username and password to access database, and doing so in case H2 database, we can run below command, java -cp h2*.jar org.h2.tools.RunScript -url jdbc:h2:~/test -user sa -password sa -script test.sql – School Boy Jun 20 '16 at 08:06
19

If you are using spring-boot and spring-test with H2 it will automatically look for schema.sql and data.sql in your class path and attempt to run these. So if you put them in src/test/resources they should be picked up and run automatically

In addition you can specify the data files you want to run with properties. For example adding a property to yourapplication.properties like

spring.datasource.data=classpath:users.sql, classpath:books.sql, classpath:reviews.sql

will configure spring to run those three sql files instead of running data.sql

robjwilkins
  • 5,462
  • 5
  • 43
  • 59
11

On OSX (this shouldn't really matter) with v. 1.4.192 with the following commands, no matter what I did I could not see any results:

java -cp h2*.jar org.h2.tools.RunScript -url "jdbc:h2:file:~/testdb" -user someusername -password somepass -script select.sql

where select.sql had simply:

select * from PUBLIC.MYTABLE;

I had to add the -showResults before output began appearing. Here is the complete command:

java -cp /Users/az/.m2/repository/com/h2database/h2/1.4.192/h2-1.4.192.jar org.h2.tools.RunScript -url "jdbc:h2:file:~/testdb" -user someusername -password somepass -script select.sql -showResults

If you do not have the needed jar, download from here (Click on the jar links next to each version). Some direct links to the jars are as follows:

1.4.193 (Updated 31-Oct-2016)

1.4.192 (Updated 26-May-2016)

1.4.191 (Updated 21-Jan-2016)

1.4.190 (Updated 11-Oct-2016)

Ashutosh Jindal
  • 18,501
  • 4
  • 62
  • 91
  • 1
    Thanks for the hint! Use `java -cp h2*.jar org.h2.tools.RunScript -?` to display available options. However, I haven't been able to find a documentation that explains these in more detail. For example, option `[-checkResults] Check if the query results match the expected results` sounds interesting, but I couldn't find an explanation how to provide expected results. – not2savvy Jul 15 '21 at 12:54
2

For me the system responded with:

didn't find org.h2.tools.RunScript driver

The solution was:

java -classpath <path_to_your_h2-*.jar> org.h2.tools.RunScript \
-url jdbc:h2:tcp://localhost/~/test -script test.sql

See: http://www.h2database.com/html/tutorial.html#using_server

Stefan van den Akker
  • 6,661
  • 7
  • 48
  • 63
aspadacio
  • 323
  • 2
  • 12