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?
Add a comment |
4 Answers
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")); 2 Comments
Snekse
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
School Boy
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
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
Comments
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)
1 Comment
not2savvy
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.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
