The new Script Tool class allows executing an SQL script. A script file is read into a string buffer and executed against the database. Results and update counts are written to system out.
In the default "batch" mode, lines containing "go" (and no other characters or white space) are used to delimit batches of sql statements to execute. Additionally, a line beginning with "print " can be supplied to write comments to system out.
Script processing is terminated if any errors are encountered.
Note: there is no checking that the statements executed against the database are valid sql. Nor is there any checking that a "batch" is correct. So if your sql/data contains new lines in combination with the words "go" or "print" be careful. The class uses readline and appends a new line at the end of the file.
java [jvm options] org.hsqldb.util.ScriptTool [-options]
-driver <classname> name of the JDBC driver class
-url <name> first part of the jdbc url
-database <name> second part of the jdbc url
-user <name> username used for connection
-password <name> password for this user
-log <true/false> write log to system out
-batch <true/false> allow go/print pseudo statements
-script <script file> reads from script file
import org.hsqldb.ScriptTool;
…
dbparms = new String [] { "-database",
"//localhost",
"-url", "jdbc:hsqldb:hsql:",
"-log", "true" };
…
st = new ScriptTool()
st.execute( dbparms );
..
driver = "org.hsqldb.jdbcDriver" url = "jdbc:hsqldb: " database = "test" user = "sa" password = "" script = "st.sql" log = "false" batch = "true"
//
print( "\nStarting ..." );
import org.hsqldb.util.ScriptTool;
//
dbparms = new String [] { "-database", "//localhost", "-url", "jdbc:hsqldb:hsql:", "-log", "true" };
st = new ScriptTool();
st.execute( dbparms );
//
print( "Console avaliable for commands ...\n" );
where st.sql is:
select * from hold3
produces something like:
CA CB CC CD CE CF CG CH CI CJ CK
0523563 R 2 A 7 7 001 42728 WILKINSON, DANNY R
0504588 R 2 A 4 4 002 42133 PEDIGO, ALBERT
0024617 R 2 A 9 0 006 40360 COYLE, MARSHALL
0347521 R 2 A 6 8 011 40422 GILBERT, TERRY R
0082243 R 2 A 4 3 016 42261 MOORE, SAM
0092239 R 2 A 1 2 017 42445 WILLIAMS, SAMMY K
0390731 R 2 A 1 1 018 42025 BURCHETT, MICHAEL
0203927 R 2 A 9 0 022 41143 MCGLONE, DAVID
0428239 R 2 A 2 2 024 42266 BOLINGER, STEVE L
1047318 R 2 A 6 9 025 40391 HENDRICKS, JOHN
0132766 R 2 A 6 1 026 40962 HENSLEY, BIGE
1005094 R 2 A 7 7 027 42602 RYAN, WAYNE
0136007 R 2 A 4 7 029 42717 MORGAN, RANDALL
0034998 R 2 A 5 5 034 40583 MAHAN, JAMES R
0035623 R 2 A 5 5 034 40511 ELAM, ROBERT B
1001299 A 2 A 5 5 034 40546 SMITH, M SCOTT
0046274 R 2 A 0 9 035 41041 MCCORMACK, GLEN R
0302050 R 2 A 1 1 042 42051 HEATH, RANDALL W
0481707 A 2 A 3 3 047 42701 PATTERSON, TERRY N
0628678 R 2 A 3 3 047 42701 THOMAS, JOSEPH L
0342885 R 2 A 4 3 050 42749 GIESECKE, JOHN F
0111906 R 2 A 5 6 052 40011 BUSH, ROBERT R
0037503 R 2 A 7 1 063 40741 BENGE, CHARLIE
0033466 R 2 A 6 8 069 40484 CAMPBELL, DAVID
0139598 R 2 A 6 8 069 40484 GRIGSON, DANNY J
0087984 R 2 A 2 2 075 42352 THURMAN, COLLEEN J
0060462 R 2 A 8 0 077 41465 ARNETT, OLLIE J
0670710 R 2 A 4 4 086 42167 LYONS, KERRY
0086899 R 2 A 2 3 092 42347 MILLIGAN, DANE
0110918 R 2 A 5 6 094 40359 CHAPPELL, DAVID
0583985 R 2 A 7 7 100 42544 HANEY, MARK E
0725630 A 2 A 5 6 105 40601 KELLY, RODNEY D
0369541 R 2 A 5 5 106 40065 HORNBACK, PAUL
0092999 R 2 A 2 2 113 42459 SPRAGUE, WILLIAM R
0432752 R 2 A 2 2 117 42455 MELTON, EDDIE A
The same script may be executed from the command line(NT) with something like:
@java -classpath .;hsqldb.jar;%CLASSPATH% org.hsqldb.util.ScriptTool
A typical script might look something like:
drop table ddl01 if exists;
create temp table ddl01( tab varchar, seq int, l int, cmd varchar );
go
insert into ddl01
select "SET", -2, 0, 'drop table z' + "SET" + ' if exists;'
from sets
where seq = 1
and settype = 'T'
and "SET" <> 'Audits'
and ucase("SET") in (select table_name from system_tables)
union
select "SET", -1, 0, 'alter table ' + "SET" + ' rename to z' + "SET" + ';'
from sets
where seq = 1
and settype = 'T'
and "SET" <> 'Audits'
and ucase("SET") in (select table_name from system_tables)
union
select "SET", 0, 0, 'create table ' + "SET" + ' ( '
from sets
where seq = 1
and settype = 'T'
and "SET" <> 'Audits'
union
select "SET", seq, 0, ' ' + DBName + ' ' + DBType + casewhen(DBATTRIB = 'identity', ' identity', '') + casewhen(DBATTRIB like 'not%', ' not null', '') + ','
from sets
where "SET" <> 'Audits'
and settype = 'T'
and Attribute <> 'PK'
union
select "SET", 8000 + seq, 0, ' primary key( ' + DBName + ' )'
from sets
where "SET" <> 'Audits'
and settype = 'T'
and Attribute = 'PK'
and "SET" not in (select distinct x."SET" from sets x where x.DBAttrib = 'identity')
union
select "SET", 9999, 0, ');'
from sets
where seq = 1
and settype = 'T'
and "SET" <> 'Audits'
order by 1, 2;
go
drop table ddl00 if exists;
create temp table ddl00 ( tab varchar, seq int );
insert into ddl00
select tab, max(seq)
from ddl01
where seq < 8000
and tab in (select distinct x."SET" from sets x where x.DBAttrib = 'identity')
group by tab;
go
update ddl01
set cmd = replace(cmd, ',', ' ')
where ddl01.tab + ddl01.seq in ( select x.tab + x.seq from ddl00 x );
go
select cmd
from ddl01
order by tab, seq;
go
Author: Joe Maher - 14 July 2002
Copyright 2002 Joe Maher. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQLDB Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.