Source code: org/hsqldb/util/ScriptTool.java
1 /* Copyright (c) 2001-2002, The HSQL Development Group
2 * All rights reserved.
3 *
4 * Redistribution and use in source and binary forms, with or without
5 * modification, are permitted provided that the following conditions are met:
6 *
7 * Redistributions of source code must retain the above copyright notice, this
8 * list of conditions and the following disclaimer.
9 *
10 * Redistributions in binary form must reproduce the above copyright notice,
11 * this list of conditions and the following disclaimer in the documentation
12 * and/or other materials provided with the distribution.
13 *
14 * Neither the name of the HSQL Development Group nor the names of its
15 * contributors may be used to endorse or promote products derived from this
16 * software without specific prior written permission.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 */
30
31
32 package org.hsqldb.util;
33
34 import java.awt.*;
35 import java.awt.event.*;
36 import java.applet.*;
37 import java.sql.*;
38 import java.net.*;
39 import java.io.*;
40 import java.util.*;
41
42 // fredt@users 20011220 - patch 481239 by yfl@users - new class
43 // jrmaher@users 20020710 - support for batch mode
44
45 /**
46 * Script tool - command line tool to read in sql script and execute it.
47 *
48 *
49 * @version 1.7.0
50 */
51 public class ScriptTool {
52
53 private static Properties pProperties = new Properties();
54 private Connection cConn;
55 private Statement sStatement;
56 private boolean BATCH = true;
57 private String EKW = new String("go");
58 private boolean EOF = false;
59 private int ln = 0;
60
61 /**
62 * Main method
63 *
64 *
65 * @param arg
66 */
67 public static void main(String arg[]) {
68
69 for (int i = 0; i < arg.length; i++) {
70 String p = arg[i];
71
72 if (p.equals("-?")) {
73 printHelp();
74 System.exit(0);
75 }
76 }
77
78 ScriptTool tool = new ScriptTool();
79
80 tool.execute(arg);
81 System.exit(0);
82 } // end main
83
84 public void execute(String arg[]) {
85
86 for (int i = 0; i < arg.length; i++) {
87 String p = arg[i];
88
89 if (p.charAt(0) == '-') {
90 pProperties.put(p.substring(1), arg[i + 1]);
91
92 i++;
93 }
94 }
95
96 ln = 0;
97 EOF = false;
98
99 BufferedReader in = null;
100 Properties p = pProperties;
101 String driver = p.getProperty("driver", "org.hsqldb.jdbcDriver");
102 String url = p.getProperty("url", "jdbc:hsqldb:");
103 String database = p.getProperty("database", "test");
104 String user = p.getProperty("user", "sa");
105 String password = p.getProperty("password", "");
106 String script = p.getProperty("script", "st.sql");
107 boolean log = p.getProperty("log", "false").equalsIgnoreCase("true");
108
109 BATCH = p.getProperty("batch", "true").equalsIgnoreCase("true");
110
111 try {
112 if (log) {
113 trace("driver = " + driver);
114 trace("url = " + url);
115 trace("database = " + database);
116 trace("user = " + user);
117 trace("password = " + password);
118 trace("script = " + script);
119 trace("log = " + log);
120 trace("batch = " + BATCH);
121 jdbcSystem.setLogToSystem(true);
122 }
123
124 // As described in the JDBC FAQ:
125 // http://java.sun.com/products/jdbc/jdbc-frequent.html;
126 // Why doesn't calling class.forName() load my JDBC driver?
127 // There is a bug in the JDK 1.1.x that can cause Class.forName() to fail.
128 // new org.hsqldb.jdbcDriver();
129 Class.forName(driver).newInstance();
130
131 cConn = DriverManager.getConnection(url + database, user,
132 password);
133 in = new BufferedReader(new FileReader(script));
134 } catch (Exception e) {
135 System.out.println("ScriptTool.init error: " + e.getMessage());
136 e.printStackTrace();
137 }
138
139 try {
140 sStatement = cConn.createStatement();
141
142 String sql;
143
144 while ((sql = fileToString(in)) != null) {
145 if (sql.length() == 1) {
146 continue;
147 }
148
149 if (log) {
150 trace("SQL (" + ln + ") : "
151 + sql.substring(0, sql.length() - 2));
152 }
153
154 sStatement.execute(sql);
155
156 ResultSet results = sStatement.getResultSet();
157 int updateCount = sStatement.getUpdateCount();
158
159 if (updateCount == -1) {
160 trace(toString(results));
161 } else {
162 trace("update count " + updateCount);
163 }
164 }
165 } catch (SQLException e) {
166 System.out.println("SQL Error at line " + ln + ": " + e);
167 }
168
169 try {
170 cConn.close();
171 in.close();
172 } catch (Exception ce) {}
173 }
174
175 /**
176 * Translate ResultSet to String representation
177 * @param r
178 */
179 private String toString(ResultSet r) {
180
181 try {
182 if (r == null) {
183 return "No Result";
184 }
185
186 ResultSetMetaData m = r.getMetaData();
187 int col = m.getColumnCount();
188 StringBuffer strbuf = new StringBuffer();
189
190 for (int i = 1; i <= col; i++) {
191 strbuf = strbuf.append(m.getColumnLabel(i) + "\t");
192 }
193
194 strbuf = strbuf.append("\n");
195
196 while (r.next()) {
197 for (int i = 1; i <= col; i++) {
198 strbuf = strbuf.append(r.getString(i) + "\t");
199
200 if (r.wasNull()) {
201 strbuf = strbuf.append("(null)\t");
202 }
203 }
204
205 strbuf = strbuf.append("\n");
206 }
207
208 return strbuf.toString();
209 } catch (SQLException e) {
210 return null;
211 }
212 }
213
214 /**
215 * Read file and convert it to string.
216 */
217 private String fileToString(BufferedReader in) {
218
219 if (EOF) {
220 return null;
221 }
222
223 EOF = true;
224
225 StringBuffer a = new StringBuffer();
226
227 try {
228 String line;
229
230 while ((line = in.readLine()) != null) {
231 ln = ln + 1;
232
233 if (BATCH) {
234 if (line.startsWith("print ")) {
235 trace("\n" + line.substring(5));
236
237 continue;
238 }
239
240 if (line.equalsIgnoreCase(EKW)) {
241 EOF = false;
242
243 break;
244 }
245 }
246
247 a.append(line);
248 a.append('\n');
249 }
250
251 a.append('\n');
252
253 return a.toString();
254 } catch (Exception e) {
255 e.printStackTrace();
256
257 throw new RuntimeException(e.getMessage());
258 }
259 }
260
261 /**
262 * Method declaration
263 *
264 *
265 * @param s
266 */
267 private void trace(String s) {
268 System.out.println(s);
269 }
270
271 /**
272 * Method declaration
273 *
274 */
275 private static void printHelp() {
276
277 System.out.println(
278 "Usage: java ScriptTool [-options]\n"
279 + "where options include:\n"
280 + " -driver <classname> name of the driver class\n"
281 + " -url <name> first part of the jdbc url\n"
282 + " -database <name> second part of the jdbc url\n"
283 + " -user <name> username used for connection\n"
284 + " -password <name> password for this user\n"
285 + " -log <true/false> write log to system out\n"
286 + " -batch <true/false> allow go/print pseudo statements\n"
287 + " -script <script file> reads from script file\n");
288 }
289 }