Executing script file in h2 database
H2Sql ScriptsH2 Problem Overview
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?
H2 Solutions
Solution 1 - H2
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"));
Solution 2 - H2
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
Solution 3 - H2
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)
Solution 4 - H2
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