ScriptTool

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.

 

Command Line Usage:

  java [jvm options] org.hsqldb.util.ScriptTool [-options] 

options are:

-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

 

Invocation from a class:

  import org.hsqldb.ScriptTool;
  
  dbparms = new String [] { "-database",
                             "//localhost",
                             "-url", "jdbc:hsqldb:hsql:",
                             "-log", "true" };
  
  st = new ScriptTool()
  st.execute( dbparms );
    
   ..
    


Option Defaults

   driver   = "org.hsqldb.jdbcDriver"
   url      = "jdbc:hsqldb: "
   database = "test"
   user     = "sa"
   password = ""
   script   = "st.sql"
   log      = "false"
   batch    = "true"

 

Samples

 

BeanShell script

//
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

 

Command Line execution:

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.

http://hsqldb.sourceforge.net