|
1
26
27 package com.sqlmagic.tinysql;
28
29 import java.io.*;
30 import java.sql.*;
31 import java.net.*;
32 import java.util.*;
33 public class tinySQLCmd
34 {
35 static Vector tableList;
36 static String  dbVersion;
37 public static void main(String [] args) throws IOException,SQLException
38 {
39 DatabaseMetaData dbMeta;
40 ResultSetMetaData meta;
41 ResultSet display_rs,typesRS;
42 BufferedReader stdin,loadFileReader;
43 BufferedReader startReader=(BufferedReader)null;
44 Connection con;
45 Statement stmt;
46 FieldTokenizer ft;
47 PreparedStatement pstmt=(PreparedStatement)null;
48 int i,rsColCount,endAt,colWidth,colScale,colPrecision,typeCount,
49 colType,parameterIndex,b1,b2,parameterInt,startAt,columnIndex,valueIndex;
50 String fName,tableName=null,inputString,cmdString,colTypeName,dbType,
51 parameterString,loadString,fieldString,readString;
52 StringBuffer lineOut,prepareBuffer,valuesBuffer,inputBuffer;
53 boolean debug=false;
54 stdin = new BufferedReader(new InputStreamReader(System.in));
55 try
56 {
57
60 Class.forName("com.sqlmagic.tinysql.dbfFileDriver");
61 } catch (ClassNotFoundException e) {
62 System.err.println(
63 "JDBC Driver could not be registered!!\n");
64 if ( debug ) e.printStackTrace();
65 }
66 fName = ".";
67 if ( args.length > 0 ) fName = args[0];
68
71 con = dbConnect(fName);
72 if ( con == (Connection)null )
73 {
74 fName = ".";
75 con = dbConnect(fName);
76 }
77 dbMeta = con.getMetaData();
78 dbType = dbMeta.getDatabaseProductName();
79 dbVersion = dbMeta.getDatabaseProductVersion();
80 System.out.println("===========================================");
81 System.out.println(dbType + " Command line interface version "
82 + dbVersion);
83 System.out.println("Type HELP to get information on available commands.");
84 cmdString = "NULL";
85 stmt = con.createStatement();
86 inputString = (String )null;
87 if ( args.length > 1 ) inputString = args[1].trim();
88 while ( !cmdString.toUpperCase().equals("EXIT") )
89 {
90 try
91 {
92 if ( startReader != (BufferedReader)null )
93 {
94
99 inputBuffer = new StringBuffer ();
100 inputString = (String )null;
101 while ( ( readString = startReader.readLine() ) != null )
102 {
103 if ( readString.startsWith("--") |
104 readString.startsWith("#") ) continue;
105 inputBuffer.append(readString + " ");
106
110 ft = new FieldTokenizer(inputBuffer.toString(),';',true);
111 if ( ft.countFields() > 1 )
112 {
113 inputString = inputBuffer.toString();
114 break;
115 }
116 }
117 if ( inputString == (String )null )
118 {
119 startReader = (BufferedReader)null;
120 continue;
121 }
122 } else if ( args.length == 0 ) {
123 System.out.print("tinySQL>");
124 inputString = stdin.readLine().trim();
125 }
126 if ( inputString == (String )null ) break;
127 if (inputString.toUpperCase().startsWith("EXIT") |
128 inputString.toUpperCase().startsWith("QUIT") ) break;
129 startAt = 0;
130 while ( startAt < inputString.length() - 1 )
131 {
132 endAt = inputString.indexOf(";",startAt);
133 if ( endAt == -1 )
134 endAt = inputString.length();
135 cmdString = inputString.substring(startAt,endAt);
136 startAt = endAt + 1;
137 if ( cmdString.toUpperCase().startsWith("SELECT") )
138 {
139 display_rs = stmt.executeQuery(cmdString);
140 if ( display_rs == (ResultSet)null )
141 {
142 System.out.println("Null ResultSet returned from query");
143 continue;
144 }
145 meta = display_rs.getMetaData();
146
149 rsColCount = meta.getColumnCount();
150 lineOut = new StringBuffer (100);
151 int[] columnWidths = new int[rsColCount];
152 int[] columnScales = new int[rsColCount];
153 int[] columnPrecisions = new int[rsColCount];
154 int[] columnTypes = new int[rsColCount];
155 String [] columnNames = new String [rsColCount];
156 for ( i = 0; i < rsColCount; i++ )
157 {
158 columnNames[i] = meta.getColumnName(i + 1);
159 columnWidths[i] = meta.getColumnDisplaySize(i + 1);
160 columnTypes[i] = meta.getColumnType(i + 1);
161 columnScales[i] = meta.getScale(i + 1);
162 columnPrecisions[i] = meta.getPrecision(i + 1);
163 if ( columnNames[i].length() > columnWidths[i] )
164 columnWidths[i] = columnNames[i].length();
165 lineOut.append(padString(columnNames[i],columnWidths[i]) + " ");
166 }
167 if ( debug ) System.out.println(lineOut.toString());
168 displayResults(display_rs);
169 } else if ( cmdString.toUpperCase().startsWith("CONNECT") ) {
170 con = dbConnect(cmdString.substring(8,cmdString.length()));
171 } else if ( cmdString.toUpperCase().startsWith("HELP") ) {
172 helpMsg(cmdString);
173 } else if ( cmdString.toUpperCase().startsWith("DESCRIBE") ) {
174 tableName = cmdString.toUpperCase().substring(9);
175 display_rs = stmt.executeQuery("SELECT * FROM " + tableName);
176 meta = display_rs.getMetaData();
177 rsColCount = meta.getColumnCount();
178 for ( i = 0; i < rsColCount; i++ )
179 {
180 lineOut = new StringBuffer (100);
181 lineOut.append(padString(meta.getColumnName(i + 1),32));
182 colTypeName = meta.getColumnTypeName(i + 1);
183 colType = meta.getColumnType(i + 1);
184 colWidth = meta.getColumnDisplaySize(i + 1);
185 colScale = meta.getScale(i + 1);
186 colPrecision = meta.getPrecision(i + 1);
187 if ( colTypeName.equals("CHAR") )
188 {
189 colTypeName = colTypeName + "("
190 + Integer.toString(colWidth) + ")";
191 } else if ( colTypeName.equals("FLOAT") ) {
192 colTypeName += "("+ Integer.toString(colPrecision)
193 + "," + Integer.toString(colScale) + ")";
194 }
195 lineOut.append(padString(colTypeName,20) + padString(colType,12));
196 System.out.println(lineOut.toString());
197 }
198 } else if ( cmdString.toUpperCase().equals("SHOW TABLES") ) {
199 for ( i = 0; i < tableList.size(); i++ )
200 System.out.println((String )tableList.elementAt(i));
201 } else if ( cmdString.toUpperCase().equals("SHOW TYPES") ) {
202 typesRS = dbMeta.getTypeInfo();
203 typeCount = displayResults(typesRS);
204 } else if ( cmdString.toUpperCase().startsWith("START ") ) {
205 ft = new FieldTokenizer(cmdString,' ',false);
206 fName = ft.getField(1);
207 if ( !fName.endsWith(".SQL") ) fName += ".SQL";
208 try
209 {
210 startReader = new BufferedReader(new FileReader(fName));
211 } catch ( Exception ex ) {
212 startReader = (BufferedReader)null;
213 throw new tinySQLException("No such file: " + fName);
214 }
215 } else if ( cmdString.toUpperCase().startsWith("LOAD") ) {
216 ft = new FieldTokenizer(cmdString,' ',false);
217 fName = ft.getField(1);
218 tableName = ft.getField(3);
219 display_rs = stmt.executeQuery("SELECT * FROM " + tableName);
220 meta = display_rs.getMetaData();
221 rsColCount = meta.getColumnCount();
222
225 prepareBuffer = new StringBuffer ("INSERT INTO " + tableName);
226 valuesBuffer = new StringBuffer (" VALUES");
227 for ( i = 0; i < rsColCount; i++ )
228 {
229 if ( i == 0 )
230 {
231 prepareBuffer.append(" (");
232 valuesBuffer.append(" (");
233 } else {
234 prepareBuffer.append(",");
235 valuesBuffer.append(",");
236 }
237 prepareBuffer.append(meta.getColumnName(i + 1));
238 valuesBuffer.append("?");
239 }
240 prepareBuffer.append(")" + valuesBuffer.toString() + ")");
241 try
242 {
243 pstmt = con.prepareStatement(prepareBuffer.toString());
244 loadFileReader = new BufferedReader(new FileReader(fName));
245 while ( (loadString=loadFileReader.readLine()) != null )
246 {
247 if ( loadString.toUpperCase().equals("ENDOFDATA") )
248 break;
249 columnIndex = 0;
250 valueIndex = 0;
251 ft = new FieldTokenizer(loadString,'|',true);
252 while ( ft.hasMoreFields() )
253 {
254 fieldString = ft.nextField();
255 if ( fieldString.equals("|") )
256 {
257 columnIndex++;
258 if ( columnIndex > valueIndex )
259 {
260 pstmt.setString(valueIndex+1,(String )null);
261 valueIndex++;
262 }
263 } else if ( columnIndex < rsColCount ) {
264 pstmt.setString(valueIndex+1,fieldString);
265 valueIndex++;
266 }
267 }
268 pstmt.executeUpdate();
269 }
270 pstmt.close();
271 } catch (Exception loadEx) {
272 System.out.println(loadEx.getMessage());
273 }
274 } else if ( cmdString.toUpperCase().startsWith("SETSTRING") |
275 cmdString.toUpperCase().startsWith("SETINT") ) {
276 b1 = cmdString.indexOf(" ");
277 b2 = cmdString.lastIndexOf(" ");
278 if ( b2 > b1 & b1 > 0 )
279 {
280 parameterIndex = Integer.parseInt(cmdString.substring(b1+1,b2));
281 parameterString = cmdString.substring(b2+1);
282 if ( debug ) System.out.println("Set parameter["
283 + parameterIndex + "]=" + parameterString);
284 if ( cmdString.toUpperCase().startsWith("SETINT") )
285 {
286 parameterInt = Integer.parseInt(parameterString);
287 pstmt.setInt(parameterIndex,parameterInt);
288 } else {
289 pstmt.setString(parameterIndex,parameterString);
290 }
291 if ( parameterIndex == 2 )
292 pstmt.executeUpdate();
293 }
294 } else {
295 if ( cmdString.indexOf("?") > -1 )
296 {
297 pstmt = con.prepareStatement(cmdString);
298 } else {
299 try
300 {
301 stmt.executeUpdate(cmdString);
302 System.out.println("DONE\n");
303 } catch( Exception upex ) {
304 System.out.println(upex.getMessage());
305 if ( debug ) upex.printStackTrace();
306 }
307 }
308 }
309 }
310 if ( args.length > 1 ) cmdString = "EXIT";
311 } catch ( SQLException te ) {
312 System.out.println(te.getMessage());
313 if ( debug ) te.printStackTrace(System.out);
314 inputString = (String )null;
315 } catch( Exception e ) {
316 System.out.println(e.getMessage());
317 cmdString = "EXIT";
318 break;
319 }
320 }
321 }
322 private static void helpMsg(String inputCmd)
323 {
324 String upperCmd;
325 upperCmd = inputCmd.toUpperCase().trim();
326 if ( upperCmd.equals("HELP") )
327 {
328 System.out.println("The following help topics are available:\n"
329 + "=============================================================\n"
330 + "HELP NEW - list of new features in tinySQL " + dbVersion + "\n"
331 + "HELP COMMANDS - help for the non-SQL commands\n"
332 + "HELP LIMITATIONS - limitations of tinySQL " + dbVersion + "\n"
333 + "HELP ABOUT - short description of tinySQL.\n");
334 } else if ( upperCmd.equals("HELP COMMANDS") ) {
335 System.out.println("The following non-SQL commands are supported:\n"
336 + "=============================================================\n"
337 + "SHOW TABLES - lists the tinySQL tables (DBF files) in the current "
338 + "directory\n"
339 + "SHOW TYPES - lists column types supported by tinySQL.\n"
340 + "DESCRIBE table_name - describes the columns in table table_name.\n"
341 + "CONNECT directory - connects to a different directory;\n"
342 + " Examples: CONNECT C:\\TEMP in Windows\n"
343 + " CONNECT /home/mydir/temp in Linux/Unix\n"
344 + "EXIT - leave the tinySQL command line interface.\n");
345 } else if ( upperCmd.equals("HELP LIMITATIONS") ) {
346 System.out.println("tinySQL " + dbVersion
347 + " does NOT support the following:\n"
348 + "=============================================================\n"
349 + "Subqueries: eg SELECT COL1 from TABLE1 where COL2 in (SELECT ...\n"
350 + "IN specification within a WHERE clause.\n"
351 + "GROUP BY clause in SELECT statments.\n"
352 + "AS in CREATE statements; eg CREATE TABLE TAB2 AS SELECT ...\n"
353 + "UPDATE statements including JOINS.\n\n"
354 + "If you run into others let us know by visiting\n"
355 + "http://sourceforge.net/projects/tinysql\n");
356 } else if ( upperCmd.equals("HELP NEW") ) {
357 System.out.println("New features in tinySQL " + dbVersion
358 + " include the following:\n"
359 + "=============================================================\n"
360 + "The package name has been changed to com.sqlmagic.tinysql.\n"
361 + "Support for table aliases in JOINS: see example below\n"
362 + " SELECT A.COL1,B.COL2 FROM TABLE1 A,TABLE2 B WHERE A.COL3=B.COL3\n"
363 + "COUNT,MAX,MIN,SUM aggregate functions.\n"
364 + "CONCAT,UPPER,SUBSTR in-line functions for strings.\n"
365 + "SYSDATE - current date.\n"
366 + "START script_file.sql - executes SQL commands in file.\n"
367 + "Support for selection of constants: see example below:\n"
368 + " SELECT 'Full Name: ',first_name,last_name from person\n"
369 + "All comparisions work properly: < > = != LIKE \n");
370 } else if ( upperCmd.equals("HELP ABOUT") ) {
371 System.out.println(
372 "=============================================================\n"
373 + "tinySQL was originally written by Brian Jepson\n"
374 + "as part of the research he did while writing the book \n"
375 + "Java Database Programming (John Wiley, 1996). The database was\n"
376 + "enhanced by Andreas Kraft, Thomas Morgner, Edson Alves Pereira,\n"
377 + "and Marcel Ruff between 1997 and 2002.\n"
378 + "The current version " + dbVersion
379 + " was developed by Davis Swan in 2004.\n\n"
380 + "tinySQL is free software; you can redistribute it and/or\n"
381 + "modify it under the terms of the GNU Lesser General Public\n"
382 + "License as published by the Free Software Foundation; either\n"
383 + "version 2.1 of the License, or (at your option) any later version.\n"
384 + "This library is distributed in the hope that it will be useful,\n"
385 + "but WITHOUT ANY WARRANTY; without even the implied warranty of\n"
386 + "MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU\n"
387 + "Lesser General Public License for more details at\n"
388 + "http://www.gnu.org/licenses/lgpl.html");
389 } else {
390 System.out.println("Unknown help command.\n");
391 }
392 }
393 private static String padString(int inputint, int padLength)
394 {
395 return padString(Integer.toString(inputint),padLength);
396 }
397 private static String padString(String inputString, int padLength)
398 {
399 String outputString;
400 String blanks = " ";
401 if ( inputString == (String )null )
402 outputString = blanks + blanks + blanks;
403 else
404 outputString = inputString;
405 if ( outputString.length() > padLength )
406 return outputString.substring(0,padLength);
407 else
408 outputString = outputString + blanks + blanks + blanks;
409 return outputString.substring(0,padLength);
410 }
411 private static Connection dbConnect(String tinySQLDir) throws SQLException
412 {
413 Connection con=null;
414 DatabaseMetaData dbMeta;
415 File conPath;
416 File[] fileList;
417 String tableName;
418 ResultSet tables_rs;
419 conPath = new File(tinySQLDir);
420 fileList = conPath.listFiles();
421 if ( fileList == null )
422 {
423 System.out.println(tinySQLDir + " is not a valid directory.");
424 return (Connection)null;
425 } else {
426 System.out.println("Connecting to " + conPath.getAbsolutePath());
427 con = DriverManager.getConnection("jdbc:dbfFile:" + conPath, "", "");
428 }
429 dbMeta = con.getMetaData();
430 tables_rs = dbMeta.getTables(null,null,null,null);
431 tableList = new Vector();
432 while ( tables_rs.next() )
433 {
434 tableName = tables_rs.getString("TABLE_NAME");
435 tableList.addElement(tableName);
436 }
437 if ( tableList.size() == 0 )
438 System.out.println("There are no tinySQL tables in this directory.");
439 else
440 System.out.println("There are " + tableList.size() + " tinySQL tables"
441 + " in this directory.");
442 return con;
443 }
444
448 static int displayResults(ResultSet rs) throws java.sql.SQLException
449 {
450 if (rs == null) {
451 System.err.println("ERROR in displayResult(): No data in ResulSet");
452 return 0;
453 }
454
455 int numCols = 0;
456
457 ResultSetMetaData meta = rs.getMetaData();
458 int cols = meta.getColumnCount();
459 int[] width = new int[cols];
460 String dashes = "=============================================";
461
462 boolean first=true;
465 StringBuffer head = new StringBuffer ();
466 StringBuffer line = new StringBuffer ();
467
468 while (rs.next()) {
471
472 String text = new String ();
475 for (int ii=0; ii<cols; ii++) {
476 String value = rs.getString(ii+1);
477 if (first) {
478 width[ii] = meta.getColumnDisplaySize(ii+1);
479 if (meta.getColumnName(ii+1).length() > width[ii])
480 width[ii] = meta.getColumnName(ii+1).length();
481 head.append(padString(meta.getColumnName(ii+1), width[ii]));
482 head.append(" ");
483 line.append(padString(dashes+dashes,width[ii]));
484 line.append(" ");
485 }
486 text += padString(value, width[ii]);
487 text += " "; }
489 if (first) {
490 System.out.println(head.toString());
491 System.out.println(line.toString());
492 first = false;
493 }
494 System.out.println(text);
495 numCols++;
496 }
497 return numCols;
498 }
499 }
500 |
Popular Tags |
Java API By Example, From Geeks To Geeks.