001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.util;
032:
033: import java.io.BufferedReader;
034: import java.io.FileReader;
035: import java.sql.Connection;
036: import java.sql.DriverManager;
037: import java.sql.ResultSet;
038: import java.sql.ResultSetMetaData;
039: import java.sql.SQLException;
040: import java.sql.Statement;
041: import java.util.Properties;
042:
043: import org.hsqldb.lib.java.JavaSystem;
044:
045: // fredt@users 20011220 - patch 481239 by yfl@users - new class
046: // jrmaher@users 20020710 - support for batch mode
047:
048: /**
049: * Script tool - command line tool to read in sql script and execute it.
050: *
051: *
052: * @version 1.7.0
053: */
054: public class ScriptTool {
055:
056: private static Properties pProperties = new Properties();
057: private Connection cConn;
058: private Statement sStatement;
059: private boolean BATCH = true;
060: private String EKW = new String("go");
061: private boolean EOF = false;
062: private int ln = 0;
063:
064: /**
065: * Main method
066: *
067: *
068: * @param arg
069: */
070: public static void main(String[] arg) {
071:
072: for (int i = 0; i < arg.length; i++) {
073: String p = arg[i];
074:
075: if (p.equals("-?")) {
076: printHelp();
077: System.exit(0);
078: }
079: }
080:
081: ScriptTool tool = new ScriptTool();
082:
083: tool.execute(arg);
084: System.exit(0);
085: } // end main
086:
087: public void execute(String[] arg) {
088:
089: for (int i = 0; i < arg.length; i++) {
090: String p = arg[i];
091:
092: if (p.charAt(0) == '-') {
093: pProperties.put(p.substring(1), arg[i + 1]);
094:
095: i++;
096: }
097: }
098:
099: ln = 0;
100: EOF = false;
101:
102: BufferedReader in = null;
103: Properties p = pProperties;
104: String driver = p
105: .getProperty("driver", "org.hsqldb.jdbcDriver");
106: String url = p.getProperty("url", "jdbc:hsqldb:");
107: String database = p.getProperty("database", "test");
108: String user = p.getProperty("user", "sa");
109: String password = p.getProperty("password", "");
110: String script = p.getProperty("script", "st.sql");
111: boolean log = p.getProperty("log", "false").equalsIgnoreCase(
112: "true");
113:
114: BATCH = p.getProperty("batch", "true").equalsIgnoreCase("true");
115:
116: try {
117: if (log) {
118: trace("driver = " + driver);
119: trace("url = " + url);
120: trace("database = " + database);
121: trace("user = " + user);
122: trace("password = " + password);
123: trace("script = " + script);
124: trace("log = " + log);
125: trace("batch = " + BATCH);
126: JavaSystem.setLogToSystem(true);
127: }
128:
129: // As described in the JDBC FAQ:
130: // http://java.sun.com/products/jdbc/jdbc-frequent.html;
131: // Why doesn't calling class.forName() load my JDBC driver?
132: // There is a bug in the JDK 1.1.x that can cause Class.forName() to fail.
133: // new org.hsqldb.jdbcDriver();
134: Class.forName(driver).newInstance();
135:
136: cConn = DriverManager.getConnection(url + database, user,
137: password);
138: in = new BufferedReader(new FileReader(script));
139: } catch (Exception e) {
140: System.out.println("ScriptTool.init error: "
141: + e.getMessage());
142: e.printStackTrace();
143: }
144:
145: try {
146: sStatement = cConn.createStatement();
147:
148: String sql;
149:
150: while ((sql = fileToString(in)) != null) {
151: if (sql.length() == 1) {
152: continue;
153: }
154:
155: if (log) {
156: trace("SQL (" + ln + ") : "
157: + sql.substring(0, sql.length() - 2));
158: }
159:
160: sStatement.execute(sql);
161:
162: ResultSet results = sStatement.getResultSet();
163: int updateCount = sStatement.getUpdateCount();
164:
165: if (updateCount == -1) {
166: trace(toString(results));
167: } else {
168: trace("update count " + updateCount);
169: }
170: }
171: } catch (SQLException e) {
172: System.out.println("SQL Error at line " + ln + ": " + e);
173: }
174:
175: try {
176: cConn.close();
177: in.close();
178: } catch (Exception ce) {
179: }
180: }
181:
182: /**
183: * Translate ResultSet to String representation
184: * @param r
185: */
186: private String toString(ResultSet r) {
187:
188: try {
189: if (r == null) {
190: return "No Result";
191: }
192:
193: ResultSetMetaData m = r.getMetaData();
194: int col = m.getColumnCount();
195: StringBuffer strbuf = new StringBuffer();
196:
197: for (int i = 1; i <= col; i++) {
198: strbuf = strbuf.append(m.getColumnLabel(i) + "\t");
199: }
200:
201: strbuf = strbuf.append("\n");
202:
203: while (r.next()) {
204: for (int i = 1; i <= col; i++) {
205: strbuf = strbuf.append(r.getString(i) + "\t");
206:
207: if (r.wasNull()) {
208: strbuf = strbuf.append("(null)\t");
209: }
210: }
211:
212: strbuf = strbuf.append("\n");
213: }
214:
215: return strbuf.toString();
216: } catch (SQLException e) {
217: return null;
218: }
219: }
220:
221: /**
222: * Read file and convert it to string.
223: */
224: private String fileToString(BufferedReader in) {
225:
226: if (EOF) {
227: return null;
228: }
229:
230: EOF = true;
231:
232: StringBuffer a = new StringBuffer();
233:
234: try {
235: String line;
236:
237: while ((line = in.readLine()) != null) {
238: ln = ln + 1;
239:
240: if (BATCH) {
241: if (line.startsWith("print ")) {
242: trace("\n" + line.substring(5));
243:
244: continue;
245: }
246:
247: if (line.equalsIgnoreCase(EKW)) {
248: EOF = false;
249:
250: break;
251: }
252: }
253:
254: a.append(line);
255: a.append('\n');
256: }
257:
258: a.append('\n');
259:
260: return a.toString();
261: } catch (Exception e) {
262: e.printStackTrace();
263:
264: throw new RuntimeException(e.getMessage());
265: }
266: }
267:
268: /**
269: * Method declaration
270: *
271: *
272: * @param s
273: */
274: private void trace(String s) {
275: System.out.println(s);
276: }
277:
278: /**
279: * Method declaration
280: *
281: */
282: private static void printHelp() {
283:
284: System.out
285: .println("Usage: java ScriptTool [-options]\n"
286: + "where options include:\n"
287: + " -driver <classname> name of the driver class\n"
288: + " -url <name> first part of the jdbc url\n"
289: + " -database <name> second part of the jdbc url\n"
290: + " -user <name> username used for connection\n"
291: + " -password <name> password for this user\n"
292: + " -log <true/false> write log to system out\n"
293: + " -batch <true/false> allow go/print pseudo statements\n"
294: + " -script <script file> reads from script file\n");
295: }
296: }
|