Source code: com/RuntimeCollective/webapps/dbtools/JDBCupload.java
1 /* $Header: /home/CVS/rjp/src/com/RuntimeCollective/webapps/dbtools/JDBCupload.java,v 1.8 2003/09/30 15:13:13 joe Exp $
2 * $Revision: 1.8 $
3 * $Date: 2003/09/30 15:13:13 $
4 *
5 * ====================================================================
6 *
7 * Josephine : http://www.runtime-collective.com/josephine/index.html
8 *
9 * Copyright (C) 2003 Runtime Collective
10 *
11 * This product includes software developed by the
12 * Apache Software Foundation (http://www.apache.org/).
13 *
14 * This library is free software; you can redistribute it and/or
15 * modify it under the terms of the GNU Lesser General Public
16 * License as published by the Free Software Foundation; either
17 * version 2.1 of the License, or (at your option) any later version.
18 *
19 * This library is distributed in the hope that it will be useful,
20 * but WITHOUT ANY WARRANTY; without even the implied warranty of
21 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
22 * Lesser General Public License for more details.
23 *
24 * You should have received a copy of the GNU Lesser General Public
25 * License along with this library; if not, write to the Free Software
26 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
27 *
28 */
29
30 package com.RuntimeCollective.webapps.dbtools;
31
32 import java.util.Vector;
33 import java.util.Date;
34 import java.sql.Connection;
35 import java.sql.ResultSet;
36 import java.sql.ResultSetMetaData;
37 import java.sql.Statement;
38 import java.sql.SQLException;
39 import org.apache.struts.util.GenericDataSource;
40
41 import java.io.OutputStream;
42 import java.io.InputStream;
43 import java.io.IOException;
44 import java.io.Writer;
45 import java.io.StringReader;
46 import java.io.BufferedReader;
47 import java.io.FileReader;
48 import java.util.Enumeration;
49
50 /** Reads an sql file and uploads it to an sqlserver database.
51 *
52 * @version $Id: JDBCupload.java,v 1.8 2003/09/30 15:13:13 joe Exp $
53 */
54
55 public class JDBCupload extends GenericDataSource {
56
57 /** Source an sql file to a database.
58 * <p>
59 * The following command line parameters can be specified, in order:
60 * <ul>
61 * <li><b>filename</b>: filename containing SQL statements to execute
62 * <li><b>url</b>: URL for database connection
63 * <li><b>driver</b>: name of the JDBC driver class
64 * <li><b>user</b>: user name of the tablespace
65 * <li><b>password</b>: password for the tablespace (if blank, omit)
66 * </ul>
67 * @param argv parameters to initialise the data source.
68 * @exception RuntimeException is thrown if an instance has already been created.
69 */
70 public static void main(String argv[]) {
71
72 if (argv.length < 4) {
73 System.out.println("Usage: java JDBCupload filename url driver user password");
74 return;
75 }
76
77 String filename;
78 String url;
79 String driver;
80 String user;
81 String password = "";
82 int min = 1;
83 int max = 1;
84
85 // Get the command line parameters
86 filename = argv[0];
87 url = argv[1];
88 driver = argv[2];
89 user = argv[3];
90 if (argv.length > 4) {
91 password = argv[4];
92 }
93
94 try {
95 // read the SQL file
96 Enumeration commands = parseFile(filename);
97
98 // init the connection
99 JDBCupload instance;
100 Connection conn;
101
102 instance = new JDBCupload();
103 instance.setAutoCommit( true );
104 //instance.setDescription( "RC to SQLServer connection" );
105 instance.setDriverClass( driver );
106 instance.setMaxCount( max );
107 instance.setMinCount( min );
108 instance.setPassword( password );
109 instance.setUrl( url );
110 instance.setUser( user );
111
112 conn = instance.getConnection();
113
114 // perform the commands
115 Object[][] result;
116 String query;
117
118 while (commands.hasMoreElements()) {
119 query = commands.nextElement().toString();
120 // System.out.println("QUERY: "+query+"\n");
121 try {
122 result = instance.queryRows(query, instance, conn);
123 // if ( result != null )
124 // System.out.println("RESULT: "+result.toString()+"\n\n");
125 // else
126 // System.out.println("RESULT: <none>\n\n");
127 } catch ( Exception e ) {
128 System.out.println("********************************\nEXCEPTION: "+e+"\n********************************\n\n");
129 }
130 }
131
132 conn.close();
133 conn = null;
134
135 instance.close();
136 instance = null;
137
138 } catch ( Exception e ) {
139 System.out.println("********************************\nGLOBAL EXCEPTION: "+e+"\n********************************\n\n");
140 }
141 }
142
143 public static Enumeration parseFile(String filename) throws Exception{
144 Vector commands = new Vector();
145
146 BufferedReader br = new BufferedReader(new FileReader(filename));
147 String command = "";
148 String nextLine;
149
150 while ((nextLine = br.readLine()) != null) {
151 nextLine.trim();
152 // System.out.println(" nextLine : "+nextLine);
153 if ( nextLine.length() == 0 || nextLine.startsWith("/*") ) {
154 command.trim();
155 if ( command.length() != 0 ) {
156 commands.addElement(command);
157 // System.out.println(" adding command : "+command);
158 }
159 command = "";
160 } else {
161 command = command.concat(nextLine+" ");
162 // System.out.println(" current command : "+command);
163 }
164 }
165 command.trim();
166 if ( command.length() != 0 ) {
167 commands.addElement(command);
168 // System.out.println(" adding command : "+command);
169 }
170 br.close();
171
172 return commands.elements();
173 }
174
175 /** Execute a SQL query and return all the results.
176 * @param sql The SQL query to execute.
177 * @return An array of arrays of Java objects (i.e. Object[row][column]) formed from the columns of the rows of the result set, or null if the result set was empty. The type of the Java objects in the inner array will be the default Java object type corresponding to the column's SQL type, following the mapping for built-in types specified in the JDBC specification.<p><strong>Note</strong> the first column of the result set is returned in result[row][0] etc.
178 * <br> The zero-length array is returned if there were no results.
179 * @exception SQLException is thrown if there was a problem connecting to the database. In this case the connection will be closed cleanly.
180 */
181 public static Object[][] queryRows(String sql, JDBCupload instance, Connection conn) throws SQLException {
182 if (instance==null) throw new SQLException("RuntimeDataSource not initialised.");
183
184 Vector result = new Vector();
185 Object[] row = null;
186 Statement stmt = null;
187 ResultSet rs = null;
188 boolean result_p;
189
190 try {
191 stmt = conn.createStatement();
192 //rs = stmt.executeQuery( sql );
193 result_p = stmt.execute( sql );
194 if (result_p) {
195 rs = stmt.getResultSet();
196 if (rs.next() ) {
197 // If there are some results
198 int cols = rs.getMetaData().getColumnCount();
199 // For each row ...
200 do {
201 // ... create a row array
202 row = new Object[ cols ];
203 for (int col=1; col<=cols; col++) {
204 row[ col-1 ] = rs.getObject( col );
205 }
206 result.add( row );
207 } while ( rs.next() );
208 }
209 rs.close();
210 rs = null;
211 }
212
213 // Set each level of connection to null once it has been successfuly closed.
214 stmt.close();
215 stmt = null;
216
217 } catch (Exception e) {
218 // If there was a problem, then close as much as possible and throw the original error.
219 if (rs != null) {
220 rs.close();
221 rs = null;
222 }
223 if (stmt != null) {
224 stmt.close();
225 stmt = null;
226 }
227 if (e instanceof SQLException) throw (SQLException) e;
228 else throw new SQLException(e.toString());
229 }
230
231 // And return the result.
232 return (Object[][]) result.toArray( new Object[0][0] );
233 }
234 }