SqlFile


1   /* Copyright (c) 2001-2005, 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.io.BufferedReader  ;
35  import java.io.ByteArrayOutputStream  ;
36  import java.io.File  ;
37  import java.io.FileInputStream  ;
38  import java.io.FileOutputStream  ;
39  import java.io.IOException  ;
40  import java.io.InputStream  ;
41  import java.io.InputStreamReader  ;
42  import java.io.OutputStreamWriter  ;
43  import java.io.PrintStream  ;
44  import java.io.PrintWriter  ;
45  import java.io.StringWriter  ;
46  import java.sql.Connection  ;
47  import java.sql.DatabaseMetaData  ;
48  import java.sql.PreparedStatement  ;
49  import java.sql.ResultSet  ;
50  import java.sql.ResultSetMetaData  ;
51  import java.sql.SQLException  ;
52  import java.sql.Statement  ;
53  import java.util.ArrayList  ;
54  import java.util.HashMap  ;
55  import java.util.Iterator  ;
56  import java.util.Map  ;
57  import java.util.StringTokenizer  ;
58  import java.util.TreeMap  ;
59  
60  /* $Id: SqlFile.java,v 1.135 2006/07/27 20:04:31 fredt Exp $ */
61  
62  /**
63   * Encapsulation of a sql text file like 'myscript.sql'.
64   * The ultimate goal is to run the execute() method to feed the SQL
65   * commands within the file to a jdbc connection.
66   *
67   * Some implementation comments and variable names use keywords based
68   * on the following definitions.  <UL>
69   * <LI> COMMAND = Statement || SpecialCommand || BufferCommand
70   * Statement = SQL statement like "SQL Statement;"
71   * SpecialCommand =  Special Command like "\x arg..."
72   * BufferCommand =  Editing/buffer command like ":s/this/that/"
73   *
74   * When entering SQL statements, you are always "appending" to the
75   * "current" command (not the "buffer", which is a different thing).
76   * All you can do to the current command is append new lines to it,
77   * execute it, or save it to buffer.
78   *
79   * In general, the special commands mirror those of Postgresql's psql,
80   * but SqlFile handles command editing much different from Postgresql
81   * because of Java's lack of support for raw tty I/O.
82   * The \p special command, in particular, is very different from psql's.
83   * Also, to keep the code simpler, we're sticking to only single-char
84   * special commands until we really need more.
85   *
86   * Buffer commands are unique to SQLFile.  The ":" commands allow
87   * you to edit the buffer and to execute the buffer.
88   *
89   * The command history consists only of SQL Statements (i.e., special
90   * commands and editing commands are not stored for later viewing or
91   * editing).
92   *
93   * Most of the Special Commands and Editing Commands are for
94   * interactive use only.
95   *
96   * \d commands are very poorly supported for Mysql because
97   * (a) Mysql lacks most of the most basic JDBC support elements, and
98   * the most basic role and schema features, and
99   * (b) to access the Mysql data dictionay, one must change the database
100  * instance (to do that would require work to restore the original state
101  * and could have disastrous effects upon transactions).
102  *
103  * To make changes to this class less destructive to external callers,
104  * the input parameters should be moved to setters (probably JavaBean
105  * setters would be best) instead of constructor args and System
106  * Properties.
107  *
108  * @version $Revision: 1.135 $
109  * @author Blaine Simpson unsaved@users
110  */
111 public class SqlFile {
112 
113     private static final int DEFAULT_HISTORY_SIZE = 20;
114     private File               file;
115     private boolean          interactive;
116     private String             primaryPrompt    = "sql> ";
117     private String             chunkPrompt      = "raw> ";
118     private String             contPrompt       = "  +> ";
119     private Connection         curConn          = null;
120     private boolean          htmlMode         = false;
121     private HashMap            userVars         = null;
122     private String  []         statementHistory = null;
123     private boolean          chunking         = false;
124     private String             csvNullRep       = null;
125 
126     /**
127      * Private class to "share" a variable among a family of SqlFile
128      * instances.
129      */
130     private static class BooleanBucket {
131 
132         private boolean bPriv = false;
133 
134         public void set(boolean bIn) {
135             bPriv = bIn;
136         }
137 
138         public boolean get() {
139             return bPriv;
140         }
141     }
142 
143     // This is an imperfect solution since when user runs SQL they could
144     // be running DDL or a commit or rollback statement.  All we know is,
145     // they MAY run some DML that needs to be committed.
146     BooleanBucket possiblyUncommitteds = new BooleanBucket();
147 
148     // Ascii field separator blanks
149     private static final int SEP_LEN = 2;
150     private static final String   DIVIDER =
151         "-----------------------------------------------------------------"
152         + "-----------------------------------------------------------------";
153     private static final String   SPACES =
154         "                                                                 "
155         + "                                                                 ";
156     private static String   revnum = null;
157 
158     static {
159         revnum = "$Revision: 1.135 $".substring("$Revision: ".length(),
160                 "$Revision: 1.135 $".length() - 2);
161     }
162 
163     private static String   BANNER =
164         "(SqlFile processor v. " + revnum + ")\n"
165         + "Distribution is permitted under the terms of the HSQLDB license.\n"
166         + "(c) 2004-2005 Blaine Simpson and the HSQLDB Development Group.\n\n"
167         + "    \\q    to Quit.\n" + "    \\?    lists Special Commands.\n"
168         + "    :?    lists Buffer/Editing commands.\n"
169         + "    *?    lists PL commands (including alias commands).\n\n"
170         + "SPECIAL Commands begin with '\\' and execute when you hit ENTER.\n"
171         + "BUFFER Commands begin with ':' and execute when you hit ENTER.\n"
172         + "COMMENTS begin with '/*' and end with the very next '*/'.\n"
173         + "PROCEDURAL LANGUAGE commands begin with '*' and end when you hit ENTER.\n"
174         + "All other lines comprise SQL Statements.\n"
175         + "  SQL Statements are terminated by either a blank line (which moves the\n"
176         + "  statement into the buffer without executing) or a line ending with ';'\n"
177         + "  (which executes the statement).\n"
178         + "  SQL Statements may begin with '/PLVARNAME' and/or contain *{PLVARNAME}s.\n";
179     private static final String   BUFFER_HELP_TEXT =
180         "BUFFER Commands (only \":;\" is available for non-interactive use).\n"
181         + "    :?                Help\n"
182         + "    :;                Execute current buffer as an SQL Statement\n"
183         + "    :a[text]          Enter append mode with a copy of the buffer\n"
184         + "    :l                List current contents of buffer\n"
185         + "    :s/from/to        Substitute \"to\" for first occurrence of \"from\"\n"
186         + "    :s/from/to/[i;g2] Substitute \"to\" for occurrence(s) of \"from\"\n"
187         + "                from:  '$'s represent line breaks\n"
188         + "                to:    If empty, from's will be deleted (e.g. \":s/x//\").\n"
189         + "                       '$'s represent line breaks\n"
190         + "                       You can't use ';' in order to execute the SQL (use\n"
191         + "                       the ';' switch for this purpose, as explained below).\n"
192         + "                /:     Can actually be any character which occurs in\n"
193         + "                       neither \"to\" string nor \"from\" string.\n"
194         + "                SUBSTITUTION MODE SWITCHES:\n"
195         + "                       i:  case Insensitive\n"
196         + "                       ;:  execute immediately after substitution\n"
197         + "                       g:  Global (substitute ALL occurrences of \"from\" string)\n"
198         + "                       2:  Narrows substitution to specified buffer line number\n"
199         + "                           (Use any line number in place of '2').\n"
200     ;
201     private static final String   HELP_TEXT = "SPECIAL Commands.\n"
202         + "* commands only available for interactive use.\n"
203         + "In place of \"3\" below, you can use nothing for the previous command, or\n"
204         + "an integer \"X\" to indicate the Xth previous command.\n"
205         + "Filter substrings are cases-sensitive!  Use \"SCHEMANAME.\" to narrow schema.\n"
206         + "    \\?                   Help\n"
207         + "    \\p [line to print]   Print string to stdout\n"
208         + "    \\w file/path.sql     Append current buffer to file\n"
209         + "    \\i file/path.sql     Include/execute commands from external file\n"
210         + "    \\d{tvsiSanur*} [substr]  List objects of specified type:\n"
211         + "  (Tbls/Views/Seqs/Indexes/SysTbls/Aliases/schemaNames/Users/Roles/table-like)\n"
212         + "    \\d OBJECTNAME [subs] Describe table or view columns\n"
213         + "    \\o [file/path.html]  Tee (or stop teeing) query output to specified file\n"
214         + "    \\H                   Toggle HTML output mode\n"
215         + "    \\! COMMAND ARGS      Execute external program (no support for stdin)\n"
216         + "    \\c [true|false]      Continue upon errors (a.o.t. abort upon error)\n"
217         + "    \\a [true|false]      Auto-commit JDBC DML commands\n"
218         + "    \\b                   save next result to Binary buffer (no display)\n"
219         + "    \\bd file/path.bin    Dump Binary buffer to file\n"
220         + "    \\bl file/path.bin    Load file into Binary buffer\n"
221         + "    \\bp                  Use ? in next SQL statement to upload Bin. buffer\n"
222         + "    \\.                   Enter raw SQL.  End with line containing only \".\"\n"
223         + "    \\s                   * Show previous commands (i.e. SQL command history)\n"
224         + "    \\-[3][;]             * reload a command to buffer (opt. exec. w/ \":;\"))\n"
225         + "    \\x {TABLE|SELECT...} eXport table or query to CSV text file\n"
226         + "    \\m file/path.csv     iMport CSV text file records into a table\n"
227         + "    \\q [abort message]   Quit (or end input like Ctrl-Z or Ctrl-D)\n"
228     ;
229     private static final String   PL_HELP_TEXT = "PROCEDURAL LANGUAGE Commands.\n"
230         + "    *?                            Help\n"
231         + "    *                             Expand PL variables from now on.\n"
232         + "                                  (this is also implied by all the following).\n"
233         + "    * VARNAME = Variable value    Set variable value\n"
234         + "    * VARNAME =                   Unset variable\n"
235         + "    * VARNAME ~                   Set variable value to the value of the very\n"
236         + "                                  next SQL statement executed (see details\n"
237         + "                                  at the bottom of this listing).\n"
238         + "    * VARNAME _                   Same as * VARNAME _, except the query is\n"
239         + "                                  done silently (i.e, no rows to screen)\n"
240         + "    * list[value] [VARNAME1...]   List variable(s) (defaults to all)\n"
241         + "    * load VARNAME path.txt       Load variable value from text file\n"
242         + "    * dump VARNAME path.txt       Dump variable value to text file\n"
243         + "    * prepare VARNAME             Use ? in next SQL statement to upload val.\n"
244         + "    * foreach VARNAME ([val1...]) Repeat the following PL block with the\n"
245         + "                                  variable set to each value in turn.\n"
246         + "    * if (logical expr)           Execute following PL block only if expr true\n"
247         + "    * while (logical expr)        Repeat following PL block while expr true\n"
248         + "    * end foreach|if|while        Ends a PL block\n"
249         + "    * break [foreach|if|while|file] Exits a PL block or file early\n"
250         + "    * continue [foreach|while]    Exits a PL block iteration early\n\n"
251         + "Use PL variables (which you have set) like: *{VARNAME}.\n"
252         + "You may use /VARNAME instead iff /VARNAME is the first word of a SQL command.\n"
253         + "Use PL variables in logical expressions like: *VARNAME.\n\n"
254         + "'* VARNAME ~' or '* VARNAME _' sets the variable value according to the very\n"
255         + "next SQL statement (~ will echo the value, _ will do it silently):\n"
256         + "    Query:  The value of the first field of the first row returned.\n"
257         + "    other:  Return status of the command (for updates this will be\n"
258         + "            the number of rows updated).\n"
259     ;
260 
261     /**
262      * Interpret lines of input file as SQL Statements, Comments,
263      * Special Commands, and Buffer Commands.
264      * Most Special Commands and many Buffer commands are only for
265      * interactive use.
266      *
267      * @param inFile  inFile of null means to read stdin.
268      * @param inInteractive  If true, prompts are printed, the interactive
269      *                       Special commands are enabled, and
270      *                       continueOnError defaults to true.
271      */
272     public SqlFile(File   inFile, boolean inInteractive,
273                    HashMap   inVars) throws IOException   {
274 
275         file        = inFile;
276         interactive = inInteractive;
277         userVars    = inVars;
278 
279         try {
280             statementHistory =
281                 new String  [interactive ? Integer.parseInt(System.getProperty("sqltool.historyLength"))
282                                        : 1];
283         } catch (Throwable   t) {
284             statementHistory = null;
285         }
286 
287         if (statementHistory == null) {
288             statementHistory = new String  [DEFAULT_HISTORY_SIZE];
289         }
290 
291         if (file != null &&!file.canRead()) {
292             throw new IOException  ("Can't read SQL file '" + file + "'");
293         }
294     }
295 
296     /**
297      * Constructor for reading stdin instead of a file for commands.
298      *
299      * @see #SqlFile(File,boolean)
300      */
301     public SqlFile(boolean inInteractive, HashMap   inVars) throws IOException   {
302         this(null, inInteractive, inVars);
303     }
304 
305     /**
306      * Process all the commands on stdin.
307      *
308      * @param conn The JDBC connection to use for SQL Commands.
309      * @see #execute(Connection,PrintStream,PrintStream,boolean)
310      */
311     public void execute(Connection   conn,
312                         Boolean   coeOverride)
313                         throws IOException  , SqlToolError, SQLException   {
314         execute(conn, System.out, System.err, coeOverride);
315     }
316 
317     /**
318      * Process all the commands on stdin.
319      *
320      * @param conn The JDBC connection to use for SQL Commands.
321      * @see #execute(Connection,PrintStream,PrintStream,boolean)
322      */
323     public void execute(Connection   conn,
324                         boolean coeOverride)
325                         throws IOException  , SqlToolError, SQLException   {
326         execute(conn, System.out, System.err, new Boolean  (coeOverride));
327     }
328 
329     // So we can tell how to handle quit and break commands.
330     public boolean      recursed     = false;
331     private String        curCommand   = null;
332     private int         curLinenum   = -1;
333     private int         curHist      = -1;
334     private PrintStream   psStd        = null;
335     private PrintStream   psErr        = null;
336     private PrintWriter   pwQuery      = null;
337     private PrintWriter   pwCsv        = null;
338     StringBuffer          stringBuffer = new StringBuffer  ();
339     /*
340      * This is reset upon each execute() invocation (to true if interactive,
341      * false otherwise).
342      */
343     private boolean             continueOnError = false;
344     private static final String   DEFAULT_CHARSET = "US-ASCII";
345     private BufferedReader        br              = null;
346     private String                charset         = null;
347 
348     /**
349      * Process all the commands in the file (or stdin) associated with
350      * "this" object.
351      * Run SQL in the file through the given database connection.
352      *
353      * This is synchronized so that I can use object variables to keep
354      * track of current line number, command, connection, i/o streams, etc.
355      *
356      * Sets encoding character set to that specified with System Property
357      * 'sqlfile.charset'.  Defaults to "US-ASCII".
358      *
359      * @param conn The JDBC connection to use for SQL Commands.
360      */
361     public synchronized void execute(Connection   conn, PrintStream   stdIn,
362                                      PrintStream   errIn,
363                                      Boolean   coeOverride)
364                                      throws IOException  , SqlToolError,
365                                          SQLException   {
366 
367         psStd      = stdIn;
368         psErr      = errIn;
369         curConn    = conn;
370         curLinenum = -1;
371 
372         String    inputLine;
373         String    trimmedCommand;
374         String    trimmedInput;
375         String    deTerminated;
376         boolean inComment = false;    // Globbling up a comment
377         int     postCommentIndex;
378         boolean gracefulExit = false;
379 
380         continueOnError = (coeOverride == null) ? interactive
381                                                 : coeOverride.booleanValue();
382 
383         if (userVars != null && userVars.size() > 0) {
384             plMode = true;
385         }
386 
387         String   specifiedCharSet = System.getProperty("sqlfile.charset");
388 
389         charset = ((specifiedCharSet == null) ? DEFAULT_CHARSET
390                                               : specifiedCharSet);
391 
392         try {
393             br = new BufferedReader  (new InputStreamReader  ((file == null)
394                     ? System.in
395                     : new FileInputStream  (file), charset));
396             curLinenum = 0;
397 
398             if (interactive) {
399                 stdprintln(BANNER);
400             }
401 
402             while (true) {
403                 if (interactive) {
404                     psStd.print((stringBuffer.length() == 0)
405                                 ? (chunking ? chunkPrompt
406                                             : primaryPrompt)
407                                 : contPrompt);
408                 }
409 
410                 inputLine = br.readLine();
411 
412                 if (inputLine == null) {
413                     /*
414                      * This is because interactive EOD on some OSes doesn't
415                      * send a line-break, resulting in no linebreak at all
416                      * after the SqlFile prompt or whatever happens to be
417                      * on their screen.
418                      */
419                     if (interactive) {
420                         psStd.println();
421                     }
422 
423                     break;
424                 }
425 
426                 curLinenum++;
427 
428                 if (chunking) {
429                     if (inputLine.equals(".")) {
430                         chunking = false;
431 
432                         setBuf(stringBuffer.toString());
433                         stringBuffer.setLength(0);
434 
435                         if (interactive) {
436                             stdprintln("Raw SQL chunk moved into buffer.  "
437                                        + "Run \":;\" to execute the chunk.");
438                         }
439                     } else {
440                         if (stringBuffer.length() > 0) {
441                             stringBuffer.append('\n');
442                         }
443 
444                         stringBuffer.append(inputLine);
445                     }
446 
447                     continue;
448                 }
449 
450                 if (inComment) {
451                     postCommentIndex = inputLine.indexOf("*/") + 2;
452 
453                     if (postCommentIndex > 1) {
454 
455                         // I see no reason to leave comments in history.
456                         inputLine = inputLine.substring(postCommentIndex);
457 
458                         // Empty the buffer.  The non-comment remainder of
459                         // this line is either the beginning of a new SQL
460                         // or Special command, or an empty line.
461                         stringBuffer.setLength(0);
462 
463                         inComment = false;
464                     } else {
465 
466                         // Just completely ignore the input line.
467                         continue;
468                     }
469                 }
470 
471                 trimmedInput = inputLine.trim();
472 
473                 try {
474 
475                     // This is the try for SQLException.  SQLExceptions are
476                     // normally thrown below in Statement processing, but
477                     // could be called up above if a Special processing
478                     // executes a SQL command from history.
479                     if (stringBuffer.length() == 0) {
480                         if (trimmedInput.startsWith("/*")) {
481                             postCommentIndex = trimmedInput.indexOf("*/", 2)
482                                                + 2;
483 
484                             if (postCommentIndex > 1) {
485 
486                                 // I see no reason to leave comments in
487                                 // history.
488                                 inputLine = inputLine.substring(
489                                     postCommentIndex + inputLine.length()
490                                     - trimmedInput.length());
491                                 trimmedInput = inputLine.trim();
492                             } else {
493 
494                                 // Just so we get continuation lines:
495                                 stringBuffer.append("COMMENT");
496 
497                                 inComment = true;
498 
499                                 continue;
500                             }
501                         }
502 
503                         // This is just to filter out useless newlines at
504                         // beginning of commands.
505                         if (trimmedInput.length() == 0) {
506                             continue;
507                         }
508 
509                         if (trimmedInput.charAt(0) == '*'
510                                 && (trimmedInput.length() < 2
511                                     || trimmedInput.charAt(1) != '{')) {
512                             try {
513                                 processPL((trimmedInput.length() == 1) ? ""
514                                                                        : trimmedInput
515                                                                        .substring(1)
516                                                                        .trim());
517                             } catch (BadSpecial bs) {
518                                 errprintln("Error at '"
519                                            + ((file == null) ? "stdin"
520                                                              : file.toString()) + "' line "
521                                                              + curLinenum
522                                                              + ":\n\""
523                                                              + inputLine
524                                                              + "\"\n"
525                                                              + bs.getMessage());
526 
527                                 if (!continueOnError) {
528                                     throw new SqlToolError(bs);
529                                 }
530                             }
531 
532                             continue;
533                         }
534 
535                         if (trimmedInput.charAt(0) == '\\') {
536                             try {
537                                 processSpecial(trimmedInput.substring(1));
538                             } catch (BadSpecial bs) {
539                                 errprintln("Error at '"
540                                            + ((file == null) ? "stdin"
541                                                              : file.toString()) + "' line "
542                                                              + curLinenum
543                                                              + ":\n\""
544                                                              + inputLine
545                                                              + "\"\n"
546                                                              + bs.getMessage());
547 
548                                 if (!continueOnError) {
549                                     throw new SqlToolError(bs);
550                                 }
551                             }
552 
553                             continue;
554                         }
555 
556                         if (trimmedInput.charAt(0) == ':'
557                                 && (interactive
558                                     || (trimmedInput.charAt(1) == ';'))) {
559                             try {
560                                 processBuffer(trimmedInput.substring(1));
561                             } catch (BadSpecial bs) {
562                                 errprintln("Error at '"
563                                            + ((file == null) ? "stdin"
564                                                              : file.toString()) + "' line "
565                                                              + curLinenum
566                                                              + ":\n\""
567                                                              + inputLine
568                                                              + "\"\n"
569                                                              + bs.getMessage());
570 
571                                 if (!continueOnError) {
572                                     throw new SqlToolError(bs);
573                                 }
574                             }
575 
576                             continue;
577                         }
578 
579                         String   ucased = trimmedInput.toUpperCase();
580 
581                         if (ucased.startsWith("DECLARE")
582                                 || ucased.startsWith("BEGIN")) {
583                             chunking = true;
584 
585                             stringBuffer.append(inputLine);
586 
587                             if (interactive) {
588                                 stdprintln(
589                                     "Enter RAW SQL.  No \\, :, * commands.  "
590                                     + "End with a line containing only \".\":");
591                             }
592 
593                             continue;
594                         }
595                     }
596 
597                     if (trimmedInput.length() == 0) {
598 
599                         // Blank lines delimit commands ONLY IN INTERACTIVE
600                         // MODE!
601                         if (interactive &&!inComment) {
602                             setBuf(stringBuffer.toString());
603                             stringBuffer.setLength(0);
604                             stdprintln("Current input moved into buffer.");
605                         }
606 
607                         continue;
608                     }
609 
610                     deTerminated = deTerminated(inputLine);
611 
612                     // A null terminal line (i.e., /\s*;\s*$/) is never useful.
613                     if (!trimmedInput.equals(";")) {
614                         if (stringBuffer.length() > 0) {
615                             stringBuffer.append('\n');
616                         }
617 
618                         stringBuffer.append((deTerminated == null) ? inputLine
619                                                                    : deTerminated);
620                     }
621 
622                     if (deTerminated == null) {
623                         continue;
624                     }
625 
626                     // If we reach here, then stringBuffer contains a complete
627                     // SQL command.
628                     curCommand     = stringBuffer.toString();
629                     trimmedCommand = curCommand.trim();
630 
631                     if (trimmedCommand.length() == 0) {
632                         throw new SQLException  ("Empty SQL Statement");
633                     }
634 
635                     setBuf(curCommand);
636                     processSQL();
637                 } catch (SQLException   se) {
638                     errprintln("SQL Error at '" + ((file == null) ? "stdin"
639                                                                   : file.toString()) + "' line "
640                                                                   + curLinenum
641                                                                       + ":\n\""
642                                                                           + curCommand
643                                                                               + "\"\n"
644                                                                                   + se
645                                                                                   .getMessage());
646 
647                     if (!continueOnError) {
648                         throw se;
649                     }
650                 } catch (BreakException be) {
651                     String   msg = be.getMessage();
652 
653                     if ((!recursed) && (msg != null &&!msg.equals("file"))) {
654                         errprintln("Unsatisfied break statement"
655                                    + ((msg == null) ? ""
656                                                     : (" (type " + msg
657                                                        + ')')) + '.');
658                     } else {
659                         gracefulExit = true;
660                     }
661 
662                     if (recursed ||!continueOnError) {
663                         throw be;
664                     }
665                 } catch (ContinueException ce) {
666                     String   msg = ce.getMessage();
667 
668                     if (!recursed) {
669                         errprintln("Unsatisfied continue statement"
670                                    + ((msg == null) ? ""
671                                                     : (" (type " + msg
672                                                        + ')')) + '.');
673                     } else {
674                         gracefulExit = true;
675                     }
676 
677                     if (recursed ||!continueOnError) {
678                         throw ce;
679                     }
680                 } catch (QuitNow qn) {
681                     throw qn;
682                 } catch (SqlToolError ste) {
683                     if (!continueOnError) {
684                         throw ste;
685                     }
686                 }
687 
688                 stringBuffer.setLength(0);
689             }
690 
691             if (inComment || stringBuffer.length() != 0) {
692                 errprintln("Unterminated input:  [" + stringBuffer + ']');
693 
694                 throw new SqlToolError("Unterminated input:  ["
695                                        + stringBuffer + ']');
696             }
697 
698             gracefulExit = true;
699         } catch (QuitNow qn) {
700             gracefulExit = qn.getMessage() == null;
701 
702             if ((!recursed) &&!gracefulExit) {
703                 errprintln("Aborting: " + qn.getMessage());
704             }
705 
706             if (recursed ||!gracefulExit) {
707                 throw qn;
708             }
709 
710             return;
711         } finally {
712             closeQueryOutputStream();
713 
714             if (fetchingVar != null) {
715                 errprintln("PL variable setting incomplete:  " + fetchingVar);
716 
717                 gracefulExit = false;
718             }
719 
720             if (br != null) {
721                 br.close();
722             }
723 
724             if ((!gracefulExit) && possiblyUncommitteds.get()) {
725                 errprintln("Rolling back SQL transaction.");
726                 curConn.rollback();
727                 possiblyUncommitteds.set(false);
728             }
729         }
730     }
731 
732     /**
733      * Returns a copy of given string without a terminating semicolon.
734      * If there is no terminating semicolon, null is returned.
735      *
736      * @param inString Base String, which will not be modified (because
737      *                 a "copy" will be returned).
738      */
739     private static String   deTerminated(String   inString) {
740 
741         int index = inString.lastIndexOf(';');
742 
743         if (index < 0) {
744             return null;
745         }
746 
747         for (int i = index + 1; i < inString.length(); i++) {
748             if (!Character.isWhitespace(inString.charAt(i))) {
749                 return null;
750             }
751         }
752 
753         return inString.substring(0, index);
754     }
755 
756     /**
757      * Utility nested Exception class for internal use.
758      */
759     private class BadSpecial extends Exception   {
760 
761         // Special-purpose constructor
762         private BadSpecial() {}
763 
764         // Normal use constructor
765         private BadSpecial(String   s) {
766             super(s);
767         }
768     }
769 
770     /**
771      * Utility nested Exception class for internal use.
772      * This must extend SqlToolError because it has to percolate up from
773      * recursions of SqlTool.execute(), yet SqlTool.execute() is public
774      * and external users should not declare (or expect!) QuitNows to be
775      * thrown.
776      * SqlTool.execute() on throws a QuitNow if it is in a recursive call.
777      */
778     private class QuitNow extends SqlToolError {
779 
780         public QuitNow(String   s) {
781             super(s);
782         }
783 
784         public QuitNow() {
785             super();
786         }
787     }
788 
789     /**
790      * Utility nested Exception class for internal use.
791      * Very similar to QuitNow.
792      */
793     private class BreakException extends SqlToolError {
794 
795         public BreakException() {
796             super();
797         }
798 
799         public BreakException(String   s) {
800             super(s);
801         }
802     }
803 
804     /**
805      * Utility nested Exception class for internal use.
806      * Very similar to QuitNow.
807      */
808     private class ContinueException extends SqlToolError {
809 
810         public ContinueException() {
811             super();
812         }
813 
814         public ContinueException(String   s) {
815             super(s);
816         }
817     }
818 
819     /**
820      * Utility nested Exception class for internal use.
821      */
822     private class BadSwitch extends Exception   {
823 
824         private BadSwitch(int i) {
825             super(Integer.toString(i));
826         }
827     }
828 
829     /**
830      * Process a Buffer/Edit Command.
831      *
832      * Due to the nature of the goal here, we don't trim() "other" like
833      * we do for other kinds of commands.
834      *
835      * @param inString Complete command, less the leading ':' character.
836      * @throws SQLException Passed through from processSQL()
837      * @throws BadSpecial Runtime error()
838      */
839     private void processBuffer(String   inString)
840     throws BadSpecial, SQLException   {
841 
842         int    index = 0;
843         int    special;
844         char   commandChar = 'i';
845         String   other       = null;
846 
847         if (inString.length() > 0) {
848             commandChar = inString.charAt(0);
849             other       = inString.substring(1);
850 
851             if (other.trim().length() == 0) {
852                 other = null;
853             }
854         }
855 
856         switch (commandChar) {
857 
858             case ';' :
859                 curCommand = commandFromHistory(0);
860 
861                 stdprintln("Executing command from buffer:\n" + curCommand
862                            + '\n');
863                 processSQL();
864 
865                 return;
866 
867             case 'a' :
868             case 'A' :
869                 stringBuffer.append(commandFromHistory(0));
870 
871                 if (other != null) {
872                     String   deTerminated = deTerminated(other);
873 
874                     if (!other.equals(";")) {
875                         stringBuffer.append(((deTerminated == null) ? other
876                                                                     : deTerminated));
877                     }
878 
879                     if (deTerminated != null) {
880 
881                         // If we reach here, then stringBuffer contains a
882                         // complete SQL command.
883                         curCommand = stringBuffer.toString();
884 
885                         setBuf(curCommand);
886                         stdprintln("Executing:\n" + curCommand + '\n');
887                         processSQL();
888                         stringBuffer.setLength(0);
889 
890                         return;
891                     }
892                 }
893 
894                 stdprintln("Appending to:\n" + stringBuffer);
895 
896                 return;
897 
898             case 'l' :
899             case 'L' :
900                 stdprintln("Current Buffer:\n" + commandFromHistory(0));
901 
902                 return;
903 
904             case 's' :
905             case 'S' :
906 
907                 // Sat Apr 23 14:14:57 EDT 2005.  Changing history behavior.
908                 // It's very inconvenient to lose all modified SQL
909                 // commands from history just because _some_ may be modified
910                 // because they are bad or obsolete.
911                 boolean modeIC      = false;
912                 boolean modeGlobal  = false;
913                 boolean modeExecute = false;
914                 int     modeLine    = 0;
915 
916                 try {
917                     String         fromHist = commandFromHistory(0);
918                     StringBuffer   sb       = new StringBuffer  (fromHist);
919 
920                     if (other == null) {
921                         throw new BadSwitch(0);
922                     }
923 
924                     String   delim = other.substring(0, 1);
925                     StringTokenizer   toker = new StringTokenizer  (other, delim,
926                         true);
927 
928                     if (toker.countTokens() < 4
929                             ||!toker.nextToken().equals(delim)) {
930                         throw new BadSwitch(1);
931                     }
932 
933                     String   from = toker.nextToken().replace('$', '\n');
934 
935                     if (!toker.nextToken().equals(delim)) {
936                         throw new BadSwitch(2);
937                     }
938 
939                     String   to = toker.nextToken().replace('$', '\n');
940 
941                     if (to.equals(delim)) {
942                         to = "";
943                     } else {
944                         if (toker.countTokens() > 0
945                                 &&!toker.nextToken().equals(delim)) {
946                             throw new BadSwitch(3);
947                         }
948                     }
949 
950                     if (toker.countTokens() > 0) {
951                         String   opts = toker.nextToken("");
952 
953                         for (int j = 0; j < opts.length(); j++) {
954                             switch (opts.charAt(j)) {
955 
956                                 case 'i' :
957                                     modeIC = true;
958                                     break;
959 
960                                 case ';' :
961                                     modeExecute = true;
962                                     break;
963 
964                                 case 'g' :
965                                     modeGlobal = true;
966                                     break;
967 
968                                 case '1' :
969                                 case '2' :
970                                 case '3' :
971                                 case '4' :
972                                 case '5' :
973                                 case '6' :
974                                 case '7' :
975                                 case '8' :
976                                 case '9' :
977                                     modeLine = Character.digit(opts.charAt(j),
978                                                                10);
979                                     break;
980 
981                                 default :
982                                     throw new BadSpecial(
983                                         "Unknown Substitution option: "
984                                         + opts.charAt(j));
985                             }
986                         }
987                     }
988 
989                     if (modeIC) {
990                         fromHist = fromHist.toUpperCase();
991                         from     = from.toUpperCase();
992                     }
993 
994                     // lineStart will be either 0 or char FOLLOWING a \n.
995                     int lineStart = 0;
996 
997                     // lineStop is the \n AFTER what we consider.
998                     int lineStop = -1;
999 
1000                    if (modeLine > 0) {
1001                        for (int j = 1; j < modeLine; j++) {
1002                            lineStart = fromHist.indexOf('\n', lineStart) + 1;
1003
1004                            if (lineStart < 1) {
1005                                throw new BadSpecial(
1006                                    "There are not " + modeLine
1007                                    + " lines in the buffer.");
1008                            }
1009                        }
1010
1011                        lineStop = fromHist.indexOf('\n', lineStart);
1012                    }
1013
1014                    if (lineStop < 0) {
1015                        lineStop = fromHist.length();
1016                    }
1017
1018                    // System.err.println("["
1019                    // + fromHist.substring(lineStart, lineStop) + ']');
1020                    int i;
1021
1022                    if (modeGlobal) {
1023                        i = lineStop;
1024
1025                        while ((i = fromHist.lastIndexOf(from, i - 1))
1026                                >= lineStart) {
1027                            sb.replace(i, i + from.length(), to);
1028                        }
1029                    } else if ((i = fromHist.indexOf(from, lineStart)) > -1
1030                               && i < lineStop) {
1031                        sb.replace(i, i + from.length(), to);
1032                    }
1033
1034                    //statementHistory[curHist] = sb.toString();
1035                    curCommand = sb.toString();
1036
1037                    setBuf(curCommand);
1038                    stdprintln((modeExecute ? "Executing"
1039                                            : "Current Buffer") + ":\n"
1040                                            + curCommand);
1041
1042                    if (modeExecute) {
1043                        stdprintln();
1044                    }
1045                } catch (BadSwitch badswitch) {
1046                    throw new BadSpecial(
1047                        "Substitution syntax:  \":s/from this/to that/i;g2\".  "
1048                        + "Use '$' for line separations.  ["
1049                        + badswitch.getMessage() + ']');
1050                }
1051
1052                if (modeExecute) {
1053                    processSQL();
1054                    stringBuffer.setLength(0);
1055                }
1056
1057                return;
1058
1059            case '?' :
1060                stdprintln(BUFFER_HELP_TEXT);
1061
1062                return;
1063        }
1064
1065        throw new BadSpecial("Unknown Buffer Command");
1066    }
1067
1068    private boolean doPrepare   = false;
1069    private String    prepareVar  = null;
1070    private String    csvColDelim = null;
1071    private String    csvRowDelim = null;
1072    private static final String   CSV_SYNTAX_MSG =
1073        "Export syntax:  x table_or_view_anme "
1074        + "[column_delimiter [record_delimiter]]";
1075
1076    /**
1077     * Process a Special Command.
1078     *
1079     * @param inString Complete command, less the leading '\' character.
1080     * @throws SQLException Passed through from processSQL()
1081     * @throws BadSpecial Runtime error()
1082     * @throws QuitNot Command execution (but not the JVM!) should stop
1083     */
1084    private void processSpecial(String   inString)
1085    throws BadSpecial, QuitNow, SQLException  , SqlToolError {
1086
1087        int    index = 0;
1088        int    special;
1089        String   arg1,
1090               other = null;
1091
1092        if (inString.length() < 1) {
1093            throw new BadSpecial("Null special command");
1094        }
1095
1096        if (plMode) {
1097            inString = dereference(inString, false);
1098        }
1099
1100        StringTokenizer   toker = new StringTokenizer  (inString);
1101
1102        arg1 = toker.nextToken();
1103
1104        if (toker.hasMoreTokens()) {
1105            other = toker.nextToken("").trim();
1106        }
1107
1108        switch (arg1.charAt(0)) {
1109
1110            case 'q' :
1111                if (other != null) {
1112                    throw new QuitNow(other);
1113                }
1114
1115                throw new QuitNow();
1116            case 'H' :
1117                htmlMode = !htmlMode;
1118
1119                stdprintln("HTML Mode is now set to: " + htmlMode);
1120
1121                return;
1122
1123            case 'm' :
1124                if (arg1.length() != 1 || other == null) {
1125                    throw new BadSpecial();
1126                }
1127
1128                csvColDelim =
1129                    convertEscapes((String  ) userVars.get("*CSV_COL_DELIM"));
1130                csvRowDelim =
1131                    convertEscapes((String  ) userVars.get("*CSV_ROW_DELIM"));
1132                csvNullRep = (String  ) userVars.get("*CSV_NULL_REP");
1133
1134                if (csvColDelim == null) {
1135                    csvColDelim = DEFAULT_COL_DELIM;
1136                }
1137
1138                if (csvRowDelim == null) {
1139                    csvRowDelim = DEFAULT_ROW_DELIM;
1140                }
1141
1142                if (csvNullRep == null) {
1143                    csvNullRep = DEFAULT_NULL_REP;
1144                }
1145
1146                try {
1147                    importCsv(other);
1148                } catch (IOException   ioe) {
1149                    System.err.println("Failed to read in CSV file:  " + ioe);
1150                }
1151
1152                return;
1153
1154            case 'x' :
1155                try {
1156                    if (arg1.length() != 1 || other == null) {
1157                        throw new BadSpecial();
1158                    }
1159
1160                    String   tableName = ((other.indexOf(' ') > 0) ? null
1161                                                                 : other);
1162
1163                    csvColDelim = convertEscapes(
1164                        (String  ) userVars.get("*CSV_COL_DELIM"));
1165                    csvRowDelim = convertEscapes(
1166                        (String  ) userVars.get("*CSV_ROW_DELIM"));
1167                    csvNullRep = (String  ) userVars.get("*CSV_NULL_REP");
1168
1169                    String   csvFilepath =
1170                        (String  ) userVars.get("*CSV_FILEPATH");
1171
1172                    if (csvFilepath == null && tableName == null) {
1173                        throw new BadSpecial(
1174                            "You must set PL variable '*CSV_FILEPATH' in "
1175                            + "order to use the query variant of \\x");
1176                    }
1177
1178                    File   csvFile = new File  ((csvFilepath == null)
1179                                            ? (tableName + ".csv")
1180                                            : csvFilepath);
1181
1182                    if (csvColDelim == null) {
1183                        csvColDelim = DEFAULT_COL_DELIM;
1184                    }
1185
1186                    if (csvRowDelim == null) {
1187                        csvRowDelim = DEFAULT_ROW_DELIM;
1188                    }
1189
1190                    if (csvNullRep == null) {
1191                        csvNullRep = DEFAULT_NULL_REP;
1192                    }
1193
1194                    pwCsv = new PrintWriter  (
1195                        new OutputStreamWriter  (
1196                            new FileOutputStream  (csvFile), charset));
1197
1198                    displayResultSet(
1199                        null,
1200                        curConn.createStatement().executeQuery(
1201                            (tableName == null) ? other
1202                                                : ("SELECT * FROM "
1203                                                   + tableName)), null, null);
1204                    pwCsv.flush();
1205                    stdprintln("Wrote " + csvFile.length()
1206                               + " characters to file '" + csvFile + "'");
1207                } catch (Exception   e) {
1208                    if (e instanceof BadSpecial) {
1209
1210                        // Not sure this test is right.  Maybe .length() == 0?
1211                        if (e.getMessage() == null) {
1212                            throw new BadSpecial(CSV_SYNTAX_MSG);
1213                        } else {
1214                            throw (BadSpecial) e;
1215                        }
1216                    }
1217
1218                    throw new BadSpecial("Failed to write to file '" + other
1219                                         + "':  " + e);
1220                } finally {
1221
1222                    // Reset all state changes
1223                    if (pwCsv != null) {
1224                        pwCsv.close();
1225                    }
1226
1227                    pwCsv       = null;
1228                    csvColDelim = null;
1229                    csvRowDelim = null;
1230                }
1231
1232                return;
1233
1234            case 'd' :
1235                if (arg1.length() == 2) {
1236                    listTables(arg1.charAt(1), other);
1237
1238                    return;
1239                }
1240
1241                if (arg1.length() == 1 && other != null) {
1242                    int space = other.indexOf(' ');
1243
1244                    if (space < 0) {
1245                        describe(other, null);
1246                    } else {
1247                        describe(other.substring(0, space),
1248                                 other.substring(space + 1).trim());
1249                    }
1250
1251                    return;
1252                }
1253
1254                throw new BadSpecial("Describe commands must be like "
1255                                     + "'\\dX' or like '\\d OBJECTNAME'.");
1256            case 'o' :
1257                if (other == null) {
1258                    if (pwQuery == null) {
1259                        throw new BadSpecial(
1260                            "There is no query output file to close");
1261                    }
1262
1263                    closeQueryOutputStream();
1264
1265                    return;
1266                }
1267
1268                if (pwQuery != null) {
1269                    stdprintln(
1270                        "Closing current query output file and opening "
1271                        + "new one");
1272                    closeQueryOutputStream();
1273                }
1274
1275                try {
1276                    pwQuery = new PrintWriter  (
1277                        new OutputStreamWriter  (
1278                            new FileOutputStream  (other, true), charset));
1279
1280                    /* Opening in append mode, so it's possible that we will
1281                     * be adding superfluous <HTML> and <BODY> tages.
1282                     * I think that browsers can handle that */
1283                    pwQuery.println((htmlMode ? "<HTML>\n<!--"
1284                                              : "#") + " "
1285                                                     + (new java.util.Date  ())
1286                                                     + ".  Query output from "
1287                                                     + getClass().getName()
1288                                                     + (htmlMode
1289                                                        ? ". -->\n\n<BODY>"
1290                                                        : ".\n"));
1291                    pwQuery.flush();
1292                } catch (Exception   e) {
1293                    throw new BadSpecial("Failed to write to file '" + other
1294                                         + "':  " + e);
1295                }
1296
1297                return;
1298
1299            case 'w' :
1300                if (other == null) {
1301                    throw new BadSpecial(
1302                        "You must supply a destination file name");
1303                }
1304
1305                if (commandFromHistory(0).length() == 0) {
1306                    throw new BadSpecial("Empty command in buffer");
1307                }
1308
1309                try {
1310                    PrintWriter   pw = new PrintWriter  (
1311                        new OutputStreamWriter  (
1312                            new FileOutputStream  (other, true), charset));
1313
1314                    pw.println(commandFromHistory(0) + ';');
1315                    pw.flush();
1316                    pw.close();
1317                } catch (Exception   e) {
1318                    throw new BadSpecial("Failed to append to file '" + other
1319                                         + "':  " + e);
1320                }
1321
1322                return;
1323
1324            case 'i' :
1325                if (other == null) {
1326                    throw new BadSpecial("You must supply an SQL file name");
1327                }
1328
1329                try {
1330                    SqlFile sf = new SqlFile(new File  (other), false,
1331                                             userVars);
1332
1333                    sf.recursed = true;
1334
1335                    // Share the possiblyUncommitted state
1336                    sf.possiblyUncommitteds = possiblyUncommitteds;
1337                    sf.plMode               = plMode;
1338
1339                    sf.execute(curConn, continueOnError);
1340                } catch (ContinueException ce) {
1341                    throw ce;
1342                } catch (BreakException be) {
1343                    String   beMessage = be.getMessage();
1344
1345                    if (beMessage != null &&!beMessage.equals("file")) {
1346                        throw be;
1347                    }
1348                } catch (QuitNow qe) {
1349                    throw qe;
1350                } catch (Exception   e) {
1351                    throw new BadSpecial("Failed to execute SQL from file '"
1352                                         + other + "':  " + e.getMessage());
1353                }
1354
1355                return;
1356
1357            case 'p' :
1358                if (other == null) {
1359                    stdprintln(true);
1360                } else {
1361                    stdprintln(other, true);
1362                }
1363
1364                return;
1365
1366            case 'a' :
1367                if (other != null) {
1368                    curConn.setAutoCommit(
1369                        Boolean.valueOf(other).booleanValue());
1370                }
1371
1372                stdprintln("Auto-commit is set to: "
1373                           + curConn.getAutoCommit());
1374
1375                return;
1376
1377            case 'b' :
1378                if (arg1.length() == 1) {
1379                    fetchBinary = true;
1380
1381                    return;
1382                }
1383
1384                if (arg1.charAt(1) == 'p') {
1385                    doPrepare = true;
1386
1387                    return;
1388                }
1389
1390                if ((arg1.charAt(1) != 'd' && arg1.charAt(1) != 'l')
1391                        || other == null) {
1392                    throw new BadSpecial("Malformatted binary command");
1393                }
1394
1395                File   file = new File  (other);
1396
1397                try {
1398                    if (arg1.charAt(1) == 'd') {
1399                        dump(file);
1400                    } else {
1401                        load(file);
1402                    }
1403                } catch (Exception   e) {
1404                    throw new BadSpecial(
1405                        "Failed to load/dump binary  data to file '" + other
1406                        + "'");
1407                }
1408
1409                return;
1410
1411            case '*' :
1412            case 'c' :
1413                if (other != null) {
1414
1415                    // But remember that we have to abort on some I/O errors.
1416                    continueOnError = Boolean.valueOf(other).booleanValue();
1417                }
1418
1419                stdprintln("Continue-on-error is set to: " + continueOnError);
1420
1421                return;
1422
1423            case 's' :
1424                showHistory();
1425
1426                return;
1427
1428            case '-' :
1429                int     commandsAgo = 0;
1430                String    numStr;
1431                boolean executeMode = arg1.charAt(arg1.length() - 1) == ';';
1432
1433                if (executeMode) {
1434
1435                    // Trim off terminating ';'
1436                    arg1 = arg1.substring(0, arg1.length() - 1);
1437                }
1438
1439                numStr = (arg1.length() == 1) ? null
1440                                              : arg1.substring(1,
1441                                              arg1.length());
1442
1443                if (numStr == null) {
1444                    commandsAgo = 0;
1445                } else {
1446                    try {
1447                        commandsAgo = Integer.parseInt(numStr);
1448                    } catch (NumberFormatException   nfe) {
1449                        throw new BadSpecial("Malformatted command number");
1450                    }
1451                }
1452
1453                setBuf(commandFromHistory(commandsAgo));
1454
1455                if (executeMode) {
1456                    processBuffer(";");
1457                } else {
1458                    stdprintln(
1459                        "RESTORED following command to buffer.  Enter \":?\" "
1460                        + "to see buffer commands:\n"
1461                        + commandFromHistory(0));
1462                }
1463
1464                return;
1465
1466            case '?' :
1467                stdprintln(HELP_TEXT);
1468
1469                return;
1470
1471            case '!' :
1472                InputStream   stream;
1473                byte[]      ba         = new byte[1024];
1474                String        extCommand = ((arg1.length() == 1) ? ""
1475                                                               : arg1.substring(1)) + ((arg1.length() > 1 && other != null)
1476                                                                   ? " "
1477                                                                   : "") + ((other == null)
1478                                                                       ? ""
1479                                                                       : other);
1480
1481                try {
1482                    Process   proc = Runtime.getRuntime().exec(extCommand);
1483
1484                    proc.getOutputStream().close();
1485
1486                    int i;
1487
1488                    stream = proc.getInputStream();
1489
1490                    while ((i = stream.read(ba)) > 0) {
1491                        stdprint(new String  (ba, 0, i));
1492                    }
1493
1494                    stream.close();
1495
1496                    stream = proc.getErrorStream();
1497
1498                    while ((i = stream.read(ba)) > 0) {
1499                        errprint(new String  (ba, 0, i));
1500                    }
1501
1502                    stream.close();
1503
1504                    if (proc.waitFor() != 0) {
1505                        throw new BadSpecial("External command failed: '"
1506                                             + extCommand + "'");
1507                    }
1508                } catch (Exception   e) {
1509                    throw new BadSpecial("Failed to execute command '"
1510                                         + extCommand + "':  " + e);
1511                }
1512
1513                return;
1514
1515            case '.' :
1516                chunking = true;
1517
1518                if (interactive) {
1519                    stdprintln("Enter RAW SQL.  No \\, :, * commands.  "
1520                               + "End with a line containing only \".\":");
1521                }
1522
1523                return;
1524        }
1525
1526        throw new BadSpecial("Unknown Special Command");
1527    }
1528
1529    private static final char[] nonVarChars = {
1530        ' ', '\t', '=', '}', '\n', '\r'
1531    };
1532
1533    /**
1534     * Returns index specifying 1 past end of a variable name.
1535     *
1536     * @param inString String containing a variable name
1537     * @param startIndex Index within inString where the variable name begins
1538     * @returns Index within inString, 1 past end of the variable name
1539     */
1540    static int pastName(String   inString, int startIndex) {
1541
1542        String   workString = inString.substring(startIndex);
1543        int    e          = inString.length();    // Index 1 past end of var name.
1544        int    nonVarIndex;
1545
1546        for (int i = 0; i < nonVarChars.length; i++) {
1547            nonVarIndex = workString.indexOf(nonVarChars[i]);
1548
1549            if (nonVarIndex > -1 && nonVarIndex < e) {
1550                e = nonVarIndex;
1551            }
1552        }
1553
1554        return startIndex + e;
1555    }
1556
1557    /**
1558     * Deference PL variables.
1559     *
1560     * @throws SQLException  This is really an inappropriate exception
1561     * type.  Only using it because I don't have time to do things properly.
1562     */
1563    private String   dereference(String   inString,
1564                               boolean permitAlias) throws SQLException   {
1565
1566        String         varName, varValue;
1567        StringBuffer   expandBuffer = new StringBuffer  (inString);
1568        int          b, e;    // begin and end of name.  end really 1 PAST name
1569        int          nonVarIndex;
1570
1571        if (permitAlias && inString.trim().charAt(0) == '/') {
1572            int slashIndex = inString.indexOf('/');
1573
1574            e = pastName(inString.substring(slashIndex + 1), 0);
1575
1576            // In this case, e is the exact length of the var name.
1577            if (e < 1) {
1578                throw new SQLException  ("Malformed PL alias use");
1579            }
1580
1581            varName  = inString.substring(slashIndex + 1, slashIndex + 1 + e);
1582            varValue = (String  ) userVars.get(varName);
1583
1584            if (varValue == null) {
1585                throw new SQLException  ("Undefined PL variable:  " + varName);
1586            }
1587
1588            expandBuffer.replace(slashIndex, slashIndex + 1 + e,
1589                                 (String  ) userVars.get(varName));
1590        }
1591
1592        String   s;
1593
1594        while (true) {
1595            s = expandBuffer.toString();
1596            b = s.indexOf("*{");
1597
1598            if (b < 0) {
1599
1600                // No more unexpanded variable uses
1601                break;
1602            }
1603
1604            e = s.indexOf('}', b + 2);
1605
1606            if (e == b + 2) {
1607                throw new SQLException  ("Empty PL variable name");
1608            }
1609
1610            if (e < 0) {
1611                throw new SQLException  ("Unterminated PL variable name");
1612            }
1613
1614            varName = s.substring(b + 2, e);
1615
1616            if (!userVars.containsKey(varName)) {
1617                throw new SQLException  ("Use of undefined PL variable: "
1618                                       + varName);
1619            }
1620
1621            expandBuffer.replace(b, e + 1, (String  ) userVars.get(varName));
1622        }
1623
1624        return expandBuffer.toString();
1625    }
1626
1627    public boolean plMode = false;
1628
1629    //  PL variable name currently awaiting query output.
1630    private String    fetchingVar = null;
1631    private boolean silentFetch = false;
1632    private boolean fetchBinary = false;
1633
1634    /**
1635     * Process a Process Language Command.
1636     * Nesting not supported yet.
1637     *
1638     * @param inString Complete command, less the leading '\' character.
1639     * @throws BadSpecial Runtime error()
1640     */
1641    private void processPL(String   inString)
1642    throws BadSpecial, SqlToolError, SQLException   {
1643
1644        if (inString.length() < 1) {
1645            plMode = true;
1646
1647            stdprintln("PL variable expansion mode is now on");
1648
1649            return;
1650        }
1651
1652        if (inString.charAt(0) == '?') {
1653            stdprintln(PL_HELP_TEXT);
1654
1655            return;
1656        }
1657
1658        if (plMode) {
1659            inString = dereference(inString, false);
1660        }
1661
1662        StringTokenizer   toker      = new StringTokenizer  (inString);
1663        String            arg1       = toker.nextToken();
1664        String  []        tokenArray = null;
1665
1666        // If user runs any PL command, we turn PL mode on.
1667        plMode = true;
1668
1669        if (userVars == null) {
1670            userVars = new HashMap  ();
1671        }
1672
1673        if (arg1.equals("end")) {
1674            throw new BadSpecial("PL end statements may only occur inside of "
1675                                 + "a PL block");
1676        }
1677
1678        if (arg1.equals("continue")) {
1679            if (toker.hasMoreTokens()) {
1680                String   s = toker.nextToken("").trim();
1681
1682                if (s.equals("foreach") || s.equals("while")) {
1683                    throw new ContinueException(s);
1684                } else {
1685                    throw new BadSpecial(
1686                        "Bad continue statement."
1687                        + "You may use no argument or one of 'foreach', "
1688                        + "'while'");
1689                }
1690            }
1691
1692            throw new ContinueException();
1693        }
1694
1695        if (arg1.equals("break")) {
1696            if (toker.hasMoreTokens()) {
1697                String   s = toker.nextToken("").trim();
1698
1699                if (s.equals("foreach") || s.equals("if")
1700                        || s.equals("while") || s.equals("file")) {
1701                    throw new BreakException(s);
1702                } else {
1703                    throw new BadSpecial(
1704                        "Bad break statement."
1705                        + "You may use no argument or one of 'foreach', "
1706                        + "'if', 'while', 'file'");
1707                }
1708            }
1709
1710            throw new BreakException();
1711        }
1712
1713        if (arg1.equals("list") || arg1.equals("listvalue")) {
1714            String    s;
1715            boolean doValues = (arg1.equals("listvalue"));
1716
1717            if (toker.countTokens() == 0) {
1718                stdprint(formatNicely(userVars, doValues));
1719            } else {
1720                tokenArray = getTokenArray(toker.nextToken(""));
1721
1722                if (doValues) {
1723                    stdprintln("The outermost parentheses are not part of "
1724                               + "the values.");
1725                } else {
1726                    stdprintln("Showing variable names and length of values "
1727                               + "(use 'listvalue' to see values).");
1728                }
1729
1730                for (int i = 0; i < tokenArray.length; i++) {
1731                    s = (String  ) userVars.get(tokenArray[i]);
1732
1733                    stdprintln("    " + tokenArray[i] + ": "
1734                               + (doValues ? ("(" + s + ')')
1735                                           : Integer.toString(s.length())));
1736                }
1737            }
1738
1739            return;
1740        }
1741
1742        if (arg1.equals("dump") || arg1.equals("load")) {
1743            if (toker.countTokens() != 2) {
1744                throw new BadSpecial("Malformatted PL dump/load command");
1745            }
1746
1747            String   varName = toker.nextToken();
1748            File     file    = new File  (toker.nextToken());
1749
1750            try {
1751                if (arg1.equals("dump")) {
1752                    dump(varName, file);
1753                } else {
1754                    load(varName, file);
1755                }
1756            } catch (Exception   e) {
1757                throw new BadSpecial("Failed to dump/load variable '"
1758                                     + varName + "' to file '" + file + "'");
1759            }
1760
1761            return;
1762        }
1763
1764        if (arg1.equals("prepare")) {
1765            if (toker.countTokens() != 1) {
1766                throw new BadSpecial("Malformatted prepare command");
1767            }
1768
1769            String   s = toker.nextToken();
1770
1771            if (userVars.get(s) == null) {
1772                throw new SQLException  ("Use of unset PL variable: " + s);
1773            }
1774
1775            prepareVar = s;
1776            doPrepare  = true;
1777
1778            return;
1779        }
1780
1781        if (arg1.equals("foreach")) {
1782            if (toker.countTokens() < 2) {
1783                throw new BadSpecial("Malformatted PL foreach command (1)");
1784            }
1785
1786            String   varName   = toker.nextToken();
1787            String   parenExpr = toker.nextToken("").trim();
1788
1789            if (parenExpr.length() < 2 || parenExpr.charAt(0) != '('
1790                    || parenExpr.charAt(parenExpr.length() - 1) != ')') {
1791                throw new BadSpecial("Malformatted PL foreach command (2)");
1792            }
1793
1794            String  [] values = getTokenArray(parenExpr.substring(1,
1795                parenExpr.length() - 1));
1796            File     tmpFile = null;
1797            String   varVal;
1798
1799            try {
1800                tmpFile = plBlockFile("foreach");
1801            } catch (IOException   ioe) {
1802                throw new BadSpecial(
1803                    "Failed to write given PL block temp file: " + ioe);
1804            }
1805
1806            String   origval = (String  ) userVars.get(varName);
1807
1808            try {
1809                SqlFile sf;
1810
1811                for (int i = 0; i < values.length; i++) {
1812                    try {
1813                        varVal = values[i];
1814
1815                        userVars.put(varName, varVal);
1816
1817                        sf          = new SqlFile(tmpFile, false, userVars);
1818                        sf.plMode   = true;
1819                        sf.recursed = true;
1820
1821                        // Share the possiblyUncommitted state
1822                        sf.possiblyUncommitteds = possiblyUncommitteds;
1823
1824                        sf.execute(curConn, continueOnError);
1825                    } catch (ContinueException ce) {
1826                        String   ceMessage = ce.getMessage();
1827
1828                        if (ceMessage != null
1829                                &&!ceMessage.equals("foreach")) {
1830                            throw ce;
1831                        }
1832                    }
1833                }
1834            } catch (BreakException be) {
1835                String   beMessage = be.getMessage();
1836
1837                if (beMessage != null &&!beMessage.equals("foreach")) {
1838                    throw be;
1839                }
1840            } catch (QuitNow qe) {
1841                throw qe;
1842            } catch (Exception   e) {
1843                throw new BadSpecial("Failed to execute SQL from PL block.  "
1844                                     + e.getMessage());
1845            }
1846
1847            if (origval == null) {
1848                userVars.remove(varName);
1849            } else {
1850                userVars.put(varName, origval);
1851            }
1852
1853            if (tmpFile != null &&!tmpFile.delete()) {
1854                throw new BadSpecial(
1855                    "Error occurred while trying to remove temp file '"
1856                    + tmpFile + "'");
1857            }
1858
1859            return;
1860        }
1861
1862        if (arg1.equals("if")) {
1863            if (toker.countTokens() < 1) {
1864                throw new BadSpecial("Malformatted PL if command (1)");
1865            }
1866
1867            String   parenExpr = toker.nextToken("").trim();
1868
1869            if (parenExpr.length() < 2 || parenExpr.charAt(0) != '('
1870                    || parenExpr.charAt(parenExpr.length() - 1) != ')') {
1871                throw new BadSpecial("Malformatted PL if command (2)");
1872            }
1873
1874            String  [] values = getTokenArray(parenExpr.substring(1,
1875                parenExpr.length() - 1));
1876            File   tmpFile = null;
1877
1878            try {
1879                tmpFile = plBlockFile("if");
1880            } catch (IOException   ioe) {
1881                throw new BadSpecial(
1882                    "Failed to write given PL block temp file: " + ioe);
1883            }
1884
1885            try {
1886                if (eval(values)) {
1887                    SqlFile sf = new SqlFile(tmpFile, false, userVars);
1888
1889                    sf.plMode   = true;
1890                    sf.recursed = true;
1891
1892                    // Share the possiblyUncommitted state
1893                    sf.possiblyUncommitteds = possiblyUncommitteds;
1894
1895                    sf.execute(curConn, continueOnError);
1896                }
1897            } catch (BreakException be) {
1898                String   beMessage = be.getMessage();
1899
1900                if (beMessage == null ||!beMessage.equals("if")) {
1901                    throw be;
1902                }
1903            } catch (ContinueException ce) {
1904                throw ce;
1905            } catch (QuitNow qe) {
1906                throw qe;
1907            } catch (BadSpecial bs) {
1908                throw new BadSpecial("Malformatted PL if command (3): " + bs);
1909            } catch (Exception   e) {
1910                throw new BadSpecial("Failed to execute SQL from PL block.  "
1911                                     + e.getMessage());
1912            }
1913
1914            if (tmpFile != null &&!tmpFile.delete()) {
1915                throw new BadSpecial(
1916                    "Error occurred while trying to remove temp file '"
1917                    + tmpFile + "'");
1918            }
1919
1920            return;
1921        }
1922
1923        if (arg1.equals("while")) {
1924            if (toker.countTokens() < 1) {
1925                throw new BadSpecial("Malformatted PL while command (1)");
1926            }
1927
1928            String   parenExpr = toker.nextToken("").trim();
1929
1930            if (parenExpr.length() < 2 || parenExpr.charAt(0) != '('
1931                    || parenExpr.charAt(parenExpr.length() - 1) != ')') {
1932                throw new BadSpecial("Malformatted PL while command (2)");
1933            }
1934
1935            String  [] values = getTokenArray(parenExpr.substring(1,
1936                parenExpr.length() - 1));
1937            File   tmpFile = null;
1938
1939            try {
1940                tmpFile = plBlockFile("while");
1941            } catch (IOException   ioe) {
1942                throw new BadSpecial(
1943                    "Failed to write given PL block temp file: " + ioe);
1944            }
1945
1946            try {
1947                SqlFile sf;
1948
1949                while (eval(values)) {
1950                    try {
1951                        sf          = new SqlFile(tmpFile, false, userVars);
1952                        sf.recursed = true;
1953
1954                        // Share the possiblyUncommitted state
1955                        sf.possiblyUncommitteds = possiblyUncommitteds;
1956                        sf.plMode               = true;
1957
1958                        sf.execute(curConn, continueOnError);
1959                    } catch (ContinueException ce) {
1960                        String   ceMessage = ce.getMessage();
1961
1962                        if (ceMessage != null &&!ceMessage.equals("while")) {
1963                            throw ce;
1964                        }
1965                    }
1966                }
1967            } catch (BreakException be) {
1968                String   beMessage = be.getMessage();
1969
1970                if (beMessage != null &&!beMessage.equals("while")) {
1971                    throw be;
1972                }
1973            } catch (QuitNow qe) {
1974                throw qe;
1975            } catch (BadSpecial bs) {
1976                throw new BadSpecial("Malformatted PL while command (3): "
1977                                     + bs);
1978            } catch (Exception   e) {
1979                throw new BadSpecial("Failed to execute SQL from PL block.  "
1980                                     + e.getMessage());
1981            }
1982
1983            if (tmpFile != null &&!tmpFile.delete()) {
1984                throw new BadSpecial(
1985                    "Error occurred while trying to remove temp file '"
1986                    + tmpFile + "'");
1987            }
1988
1989            return;
1990        }
1991
1992        /* Since we don't want to permit both "* VARNAME = X" and
1993         * "* VARNAME=X" (i.e., whitespace is OPTIONAL in both positions),
1994         * we can't use the Tokenzier.  Therefore, start over again with
1995         * the inString. */
1996        toker = null;
1997
1998        int    index    = pastName(inString, 0);
1999        int    inLength = inString.length();
2000        String   varName  = inString.substring(0, index);
2001
2002        while (index + 1 < inLength
2003                && (inString.charAt(index) == ' '
2004                    || inString.charAt(index) == '\t')) {
2005            index++;
2006        }
2007
2008        // index now set to the next non-whitespace AFTER the var name.
2009        if (index + 1 > inLength) {
2010            throw new BadSpecial("Unterminated PL variable definition");
2011        }
2012
2013        char   operator  = inString.charAt(index);
2014        String   remainder = inString.substring(index + 1);
2015
2016        switch (inString.charAt(index)) {
2017
2018            case '_' :
2019                silentFetch = true;
2020            case '~' :
2021                if (remainder.length() > 0) {
2022                    throw new BadSpecial(
2023                        "PL ~/_ set commands take no other args");
2024                }
2025
2026                userVars.remove(varName);
2027
2028                fetchingVar = varName;
2029
2030                return;
2031
2032            case '=' :
2033                if (fetchingVar != null && fetchingVar.equals(varName)) {
2034                    fetchingVar = null;
2035                }
2036
2037                if (remainder.length() > 0) {
2038                    userVars.put(varName,
2039                                 inString.substring(index + 1).trim());
2040                } else {
2041                    userVars.remove(varName);
2042                }
2043
2044                return;
2045        }
2046
2047        throw new BadSpecial("Unknown PL command (3)");
2048    }
2049
2050    /*
2051     * Read a PL block into a new temp file.
2052     *
2053     * WARNING!!! foreach blocks are not yet smart about comments
2054     * and strings.  We just look for a line beginning with a PL "end"
2055     * command without worrying about comments or quotes (for now).
2056     *
2057     * WARNING!!! This is very rudimentary.
2058     * Users give up all editing and feedback capabilities while
2059     * in the foreach loop.
2060     * A better solution would be to pass current input stream to a
2061     * new SqlFile.execute() with a mode whereby commands are written
2062     * to a separate history but not executed.
2063     */
2064    private File   plBlockFile(String   type) throws IOException  , SqlToolError {
2065
2066        String            s;
2067        StringTokenizer   toker;
2068
2069        // Have already read the if/while/foreach statement, so we are already
2070        // at nest level 1.  When we reach nestlevel 1 (read 1 net "end"
2071        // statement), we're at level 0 and return.
2072        int    nestlevel = 1;
2073        String   curPlCommand;
2074
2075        if (type == null
2076                || ((!type.equals("foreach")) && (!type.equals("if"))
2077                    && (!type.equals("while")))) {
2078            throw new RuntimeException  (
2079                "Assertion failed.  Unsupported PL block type:  " + type);
2080        }
2081
2082        File   tmpFile = File.createTempFile("sqltool-", ".sql");
2083        PrintWriter   pw = new PrintWriter  (
2084            new OutputStreamWriter  (new FileOutputStream  (tmpFile), charset));
2085
2086        pw.println("/* " + (new java.util.Date  ()) + ". "
2087                   + getClass().getName() + " PL block. */\n");
2088
2089        while (true) {
2090            s = br.readLine();
2091
2092            if (s == null) {
2093                errprintln("Unterminated '" + type + "' PL block");
2094
2095                throw new SqlToolError("Unterminated '" + type
2096                                       + "' PL block");
2097            }
2098
2099            curLinenum++;
2100
2101            if (s.trim().length() > 1 && s.trim().charAt(0) == '*') {
2102                toker        = new StringTokenizer  (s.trim().substring(1));
2103                curPlCommand = toker.nextToken();
2104
2105                // PL COMMAND of some sort.
2106                if (curPlCommand.equals(type)) {
2107                    nestlevel++;
2108                } else if (curPlCommand.equals("end")) {
2109                    if (toker.countTokens() < 1) {
2110                        errprintln("PL end statement requires arg of "
2111                                   + "'foreach' or 'if' or 'while' (1)");
2112
2113                        throw new SqlToolError(
2114                            "PL end statement requires arg "
2115                            + " of 'foreach' or 'if' or 'while' (1)");
2116                    }
2117
2118                    String   inType = toker.nextToken();
2119
2120                    if (inType.equals(type)) {
2121                        nestlevel--;
2122
2123                        if (nestlevel < 1) {
2124                            break;
2125                        }
2126                    }
2127
2128                    if ((!inType.equals("foreach")) && (!inType.equals("if"))
2129                            && (!inType.equals("while"))) {
2130                        errprintln("PL end statement requires arg of "
2131                                   + "'foreach' or 'if' or 'while' (2)");
2132
2133                        throw new SqlToolError(
2134                            "PL end statement requires arg of "
2135                            + "'foreach' or 'if' or 'while' (2)");
2136                    }
2137                }
2138            }
2139
2140            pw.println(s);
2141        }
2142
2143        pw.flush();
2144        pw.close();
2145
2146        return tmpFile;
2147    }
2148
2149    /**
2150     * Wrapper methods so don't need to call x(..., false) in most cases.
2151     */
2152    private void stdprintln() {
2153        stdprintln(false);
2154    }
2155
2156    private void stdprint(String   s) {
2157        stdprint(s, false);
2158    }
2159
2160    private void stdprintln(String   s) {
2161        stdprintln(s, false);
2162    }
2163
2164    /**
2165     * Encapsulates normal output.
2166     *
2167     * Conditionally HTML-ifies output.
2168     */
2169    private void stdprintln(boolean queryOutput) {
2170
2171        if (htmlMode) {
2172            psStd.println("<BR>");
2173        } else {
2174            psStd.println();
2175        }
2176
2177        if (queryOutput && pwQuery != null) {
2178            if (htmlMode) {
2179                pwQuery.println("<BR>");
2180            } else {
2181                pwQuery.println();
2182            }
2183
2184            pwQuery.flush();
2185        }
2186    }
2187
2188    /**
2189     * Encapsulates error output.
2190     *
2191     * Conditionally HTML-ifies error output.
2192     */
2193    private void errprint(String   s) {
2194
2195        psErr.print(htmlMode
2196                    ? ("<DIV style='color:white; background: red; "
2197                       + "font-weight: bold'>" + s + "</DIV>")
2198                    : s);
2199    }
2200
2201    /**
2202     * Encapsulates error output.
2203     *
2204     * Conditionally HTML-ifies error output.
2205     */
2206    private void errprintln(String   s) {
2207
2208        psErr.println(htmlMode
2209                      ? ("<DIV style='color:white; background: red; "
2210                         + "font-weight: bold'>" + s + "</DIV>")
2211                      : s);
2212    }
2213
2214    /**
2215     * Encapsulates normal output.
2216     *
2217     * Conditionally HTML-ifies output.
2218     */
2219    private void stdprint(String   s, boolean queryOutput) {
2220
2221        psStd.print(htmlMode ? ("<P>" + s + "</P>")
2222                             : s);
2223
2224        if (queryOutput && pwQuery != null) {
2225            pwQuery.print(htmlMode ? ("<P>" + s + "</P>")
2226                                   : s);
2227            pwQuery.flush();
2228        }
2229    }
2230
2231    /**
2232     * Encapsulates normal output.
2233     *
2234     * Conditionally HTML-ifies output.
2235     */
2236    private void stdprintln(String   s, boolean queryOutput) {
2237
2238        psStd.println(htmlMode ? ("<P>" + s + "</P>")
2239                               : s);
2240
2241        if (queryOutput && pwQuery != null) {
2242            pwQuery.println(htmlMode ? ("<P>" + s + "</P>")
2243                                     : s);
2244            pwQuery.flush();
2245        }
2246    }
2247
2248    // Just because users may be used to seeing "[null]" in normal
2249    // SqlFile output, we use the same default value for null in CSV
2250    // files, but this CSV null representation can be changed to anything.
2251    private static final String   DEFAULT_NULL_REP = "[null]";
2252    private static final String   DEFAULT_ROW_DELIM =
2253        System.getProperty("line.separator");
2254    private static final String   DEFAULT_COL_DELIM = "|";
2255    private static final int    DEFAULT_ELEMENT   = 0,
2256                                HSQLDB_ELEMENT    = 1,
2257                                ORACLE_ELEMENT    = 2
2258    ;
2259
2260    // These do not specify order listed, just inclusion.
2261    private static final int[] listMDSchemaCols = { 1 };
2262    private static final int[] listMDIndexCols  = {
2263        2, 6, 3, 9, 4, 10, 11
2264    };
2265
2266    /** Column numbering starting at 1. */
2267    private static final int[][] listMDTableCols = {
2268        {
2269            2, 3
2270        },    // Default
2271        {
2272            2, 3
2273        },    // HSQLDB
2274        {
2275            2, 3
2276        },    // Oracle
2277    };
2278
2279    /**
2280     * SYS and SYSTEM are the only base system accounts in Oracle, however,
2281     * from an empirical perspective, all of these other accounts are
2282     * system accounts because <UL>
2283     * <LI> they are hidden from the casual user
2284     * <LI> they are created by the installer at installation-time
2285     * <LI> they are used automatically by the Oracle engine when the
2286     *      specific Oracle sub-product is used
2287     * <LI> the accounts should not be <I>messed with</I> by database users
2288     * <LI> the accounts should certainly not be used if the specific
2289     *      Oracle sub-product is going to be used.
2290     * </UL>
2291     *
2292     * General advice:  If you aren't going to use an Oracle sub-product,
2293     * then <B>don't install it!</B>
2294     * Don't blindly accept default when running OUI.
2295     *
2296     * If users also see accounts that they didn't create with names like
2297     * SCOTT, ADAMS, JONES, CLARK, BLAKE, OE, PM, SH, QS, QS_*, these
2298     * contain sample data and the schemas can safely be removed.
2299     */
2300    private static final String  [] oracleSysSchemas = {
2301        "SYS", "SYSTEM", "OUTLN", "DBSNMP", "OUTLN", "MDSYS", "ORDSYS",
2302        "ORDPLUGINS", "CTXSYS", "DSSYS", "PERFSTAT", "WKPROXY", "WKSYS",
2303        "WMSYS", "XDB", "ANONYMOUS", "ODM", "ODM_MTR", "OLAPSYS", "TRACESVR",
2304        "REPADMIN"
2305    };
2306
2307    /**
2308     * Lists available database tables.
2309     *
2310     * When a filter is given, we assume that there are no lower-case
2311     * characters in the object names (which would require "quotes" when
2312     * creating them).
2313     *
2314     * @throws BadSpecial
2315     */
2316    private void listTables(char c, String   inFilter) throws BadSpecial {
2317
2318        String     schema  = null;
2319        int[]    listSet = null;
2320        String  [] types   = null;
2321
2322        /** For workaround for \T for Oracle */
2323        String  [] additionalSchemas = null;
2324
2325        /** This is for specific non-getTable() queries */
2326        Statement   statement = null;
2327        ResultSet   rs        = null;
2328        String      narrower  = "";
2329        /*
2330         * Doing case-sensitive filters now, for greater portability.
2331        String                    filter = ((inFilter == null)
2332                                          ? null : inFilter.toUpperCase());
2333         */
2334        String   filter = inFilter;
2335
2336        try {
2337            DatabaseMetaData   md            = curConn.getMetaData();
2338            String             dbProductName = md.getDatabaseProductName();
2339
2340            //System.err.println("DB NAME = (" + dbProductName + ')');
2341            // Database-specific table filtering.
2342            String   excludePrefix = null;
2343
2344            /* 3 Types of actions:
2345             *    1) Special handling.  Return from the "case" block directly.
2346             *    2) Execute a specific query.  Set statement in the "case".
2347             *    3) Otherwise, set filter info for dbmd.getTable() in the
2348             *       "case".
2349             */
2350            types = new String  [1];
2351
2352            switch (c) {
2353
2354                case '*' :
2355                    types = null;
2356                    break;
2357
2358                case 'S' :
2359                    if (dbProductName.indexOf("Oracle") > -1) {
2360                        System.err.println(
2361                            "*** WARNING:\n*** Listing tables in "
2362                            + "system-supplied schemas since\n*** Oracle"
2363                            + "(TM) doesn't return a JDBC system table list.");
2364
2365                        types[0]          = "TABLE";
2366                        schema            = "SYS";
2367                        additionalSchemas = oracleSysSchemas;
2368                    } else {
2369                        types[0] = "SYSTEM TABLE";
2370                    }
2371                    break;
2372
2373                case 's' :
2374                    if (dbProductName.indexOf("HSQL") > -1) {
2375
2376                        //  HSQLDB does not consider Sequences as "tables",
2377                        //  hence we do not list them in
2378                        //  DatabaseMetaData.getTables().
2379                        if (filter != null
2380                                && filter.charAt(filter.length() - 1)
2381                                   == '.') {
2382                            narrower =
2383                                "\nWHERE sequence_schema = '"
2384                                + filter.substring(0, filter.length() - 1)
2385                                + "'";
2386                            filter = null;
2387                        }
2388
2389                        statement = curConn.createStatement();
2390
2391                        statement.execute(
2392                            "SELECT sequence_schema, sequence_name FROM "
2393                            + "information_schema.system_sequences"
2394                            + narrower);
2395                    } else {
2396                        types[0] = "SEQUENCE";
2397                    }
2398                    break;
2399
2400                case 'r' :
2401                    if (dbProductName.indexOf("HSQL") > -1) {
2402                        statement = curConn.createStatement();
2403
2404                        statement.execute(
2405                            "SELECT authorization_name FROM "
2406                            + "information_schema.system_authorizations\n"
2407                            + "WHERE authorization_type = 'ROLE'\n"
2408                            + "ORDER BY authorization_name");
2409                    } else if (dbProductName.indexOf(
2410                            "Adaptive Server Enterprise") > -1) {
2411
2412                        // This is the basic Sybase server.  Sybase also has
2413                        // their "Anywhere", ASA (for embedded), and replication
2414                        // databases, but I don't know the Metadata strings for
2415                        // those.
2416                        statement = curConn.createStatement();
2417
2418                        statement.execute(
2419                            "SELECT name FROM syssrvroles ORDER BY name");
2420                    } else {
2421                        throw new BadSpecial(
2422                            "SqlFile does not yet support "
2423                            + "\\dr for your database vendor");
2424                    }
2425                    break;
2426
2427                case 'u' :
2428                    if (dbProductName.indexOf("HSQL") > -1) {
2429                        statement = curConn.createStatement();
2430
2431                        statement.execute(
2432                            "SELECT user, admin FROM "
2433                            + "information_schema.system_users\n"
2434                            + "ORDER BY user");
2435                    } else if (dbProductName.indexOf("Oracle") > -1) {
2436                        statement = curConn.createStatement();
2437
2438                        statement.execute(
2439                            "SELECT username, created FROM all_users "
2440                            + "ORDER BY username");
2441                    } else if (dbProductName.indexOf("PostgreSQL") > -1) {
2442                        statement = curConn.createStatement();
2443
2444                        statement.execute(
2445                            "SELECT usename, usesuper FROM pg_catalog.pg_user "
2446                            + "ORDER BY usename");
2447                    } else if (dbProductName.indexOf(
2448                            "Adaptive Server Enterprise") > -1) {
2449
2450                        // This is the basic Sybase server.  Sybase also has
2451                        // their "Anywhere", ASA (for embedded), and replication
2452                        // databases, but I don't know the Metadata strings for
2453                        // those.
2454                        statement = curConn.createStatement();
2455
2456                        statement.execute(
2457                            "SELECT name, accdate, fullname FROM syslogins "
2458                            + "ORDER BY name");
2459                    } else {
2460                        throw new BadSpecial(
2461                            "SqlFile does not yet support "
2462                            + "\\du for your database vendor");
2463                    }
2464                    break;
2465
2466                case 'a' :
2467                    if (dbProductName.indexOf("HSQL") > -1) {
2468
2469                        //  HSQLDB Aliases are not the same things as the
2470                        //  aliases listed in DatabaseMetaData.getTables().
2471                        if (filter != null
2472                                && filter.charAt(filter.length() - 1)
2473                                   == '.') {
2474                            narrower =
2475                                "\nWHERE alias_schem = '"
2476                                + filter.substring(0, filter.length() - 1)
2477                                + "'";
2478                            filter = null;
2479                        }
2480
2481                        statement = curConn.createStatement();
2482
2483                        statement.execute(
2484                            "SELECT alias_schem, alias FROM "
2485                            + "information_schema.system_aliases" + narrower);
2486                    } else {
2487                        types[0] = "ALIAS";
2488                    }
2489                    break;
2490
2491                case 't' :
2492                    excludeSysSchemas = (dbProductName.indexOf("Oracle")
2493                                         > -1);
2494                    types[0] = "TABLE";
2495                    break;
2496
2497                case 'v' :
2498                    types[0] = "VIEW";
2499                    break;
2500
2501                case 'n' :
2502                    rs = md.getSchemas();
2503
2504                    if (rs == null) {
2505                        throw new BadSpecial(
2506                            "Failed to get metadata from database");
2507                    }
2508
2509                    displayResultSet(null, rs, listMDSchemaCols, filter);
2510
2511                    return;
2512
2513                case 'i' :
2514
2515                    // Some databases require to specify table, some don't.
2516                    /*
2517                    if (filter == null) {
2518                        throw new BadSpecial("You must specify the index's "
2519                                + "table as argument to \\di");
2520                    }
2521                     */
2522                    schema = null;
2523
2524                    String   table = null;
2525
2526                    if (filter != null) {
2527                        int dotat = filter.indexOf('.');
2528
2529                        schema = ((dotat > 0) ? filter.substring(0, dotat)
2530                                              : null);
2531
2532                        if (dotat < filter.length() - 1) {
2533
2534                            // Not a schema-only specifier
2535                            table = ((dotat > 0) ? filter.substring(dotat + 1)
2536                                                 : filter);
2537                        }
2538
2539                        filter = null;
2540                    }
2541
2542                    // N.b. Oracle incorrectly reports the INDEX SCHEMA as
2543                    // the TABLE SCHEMA.  The Metadata structure seems to
2544                    // be designed with the assumption that the INDEX schema
2545                    // will be the same as the TABLE schema.
2546                    rs = md.getIndexInfo(null, schema, table, false, true);
2547
2548                    if (rs == null) {
2549                        throw new BadSpecial(
2550                            "Failed to get metadata from database");
2551                    }
2552
2553                    displayResultSet(null, rs, listMDIndexCols, null);
2554
2555                    return;
2556
2557                default :
2558                    throw new BadSpecial("Unknown describe option: '" + c
2559                                         + "'");
2560            }
2561
2562            if (statement == null) {
2563                if (dbProductName.indexOf("HSQL") > -1) {
2564                    listSet = listMDTableCols[HSQLDB_ELEMENT];
2565                } else if (dbProductName.indexOf("Oracle") > -1) {
2566                    listSet = listMDTableCols[ORACLE_ELEMENT];
2567                } else {
2568                    listSet = listMDTableCols[DEFAULT_ELEMENT];
2569                }
2570
2571                if (schema == null && filter != null
2572                        && filter.charAt(filter.length() - 1) == '.') {
2573                    schema = filter.substring(0, filter.length() - 1);
2574                    filter = null;
2575                }
2576            }
2577
2578            rs = ((statement == null)
2579                  ? md.getTables(null, schema, null, types)
2580                  : statement.getResultSet());
2581
2582            if (rs == null) {
2583                throw new BadSpecial("Failed to get metadata from database");
2584            }
2585
2586            displayResultSet(null, rs, listSet, filter);
2587
2588            if (additionalSchemas != null) {
2589                for (int i = 1; i < additionalSchemas.length; i++) {
2590                    /*
2591                     * Inefficient, but we have to do each successful query
2592                     * twice in order to prevent calling displayResultSet
2593                     * for empty/non-existent schemas
2594                     */
2595                    rs = md.getTables(null, additionalSchemas[i], null,
2596                                      types);
2597
2598                    if (rs == null) {
2599                        throw new BadSpecial(
2600                            "Failed to get metadata from database for '"
2601                            + additionalSchemas[i] + "'");
2602                    }
2603
2604                    if (!rs.next()) {
2605                        continue;
2606                    }
2607
2608                    displayResultSet(
2609                        null,
2610                        md.getTables(
2611                            null, additionalSchemas[i], null, types), listSet, filter);
2612                }
2613            }
2614        } catch (SQLException   se) {
2615            throw new BadSpecial("Failure getting MetaData: " + se);
2616        } catch (NullPointerException   npe) {
2617            throw new BadSpecial("Failure getting MetaData (NPE)");
2618        } finally {
2619            excludeSysSchemas = false;
2620
2621            if (rs != null) {
2622                rs = null;
2623            }
2624
2625            if (statement != null) {
2626                try {
2627                    statement.close();
2628                } catch (Exception   e) {}
2629
2630                statement = null;
2631            }
2632        }
2633    }
2634
2635    private boolean excludeSysSchemas = false;
2636
2637    /**
2638     * Process the current command as an SQL Statement
2639     */
2640    private void processSQL() throws SQLException   {
2641
2642        // Really don't know whether to take the network latency hit here
2643        // in order to check autoCommit in order to set
2644        // possiblyUncommitteds more accurately.
2645        // I'm going with "NO" for now, since autoCommit will usually be off.
2646        // If we do ever check autocommit, we have to keep track of the
2647        // autocommit state when every SQL statement is run, since I may
2648        // be able to have uncommitted DML, turn autocommit on, then run
2649        // other DDL with autocommit on.  As a result, I could be running
2650        // SQL commands with autotommit on but still have uncommitted mods.
2651        String      sql       = (plMode ? dereference(curCommand, true)
2652                                      : curCommand);
2653        Statement   statement = null;
2654
2655        if (doPrepare) {
2656            if (sql.indexOf('?') < 1) {
2657                throw new SQLException  (
2658                    "Prepared statements must contain one '?'");
2659            }
2660
2661            doPrepare = false;
2662
2663            PreparedStatement   ps = curConn.prepareStatement(sql);
2664
2665            if (prepareVar == null) {
2666                if (binBuffer == null) {
2667                    throw new SQLException  ("Binary SqlFile buffer is empty");
2668                }
2669
2670                ps.setBytes(1, binBuffer);
2671            } else {
2672                String   val = (String  ) userVars.get(prepareVar);
2673
2674                if (val == null) {
2675                    throw new SQLException  ("PL Variable '" + prepareVar
2676                                           + "' is empty");
2677                }
2678
2679                prepareVar = null;
2680
2681                ps.setString(1, val);
2682            }
2683
2684            ps.executeUpdate();
2685
2686            statement = ps;
2687        } else {
2688            statement = curConn.createStatement();
2689
2690            statement.execute(sql);
2691        }
2692
2693        possiblyUncommitteds.set(true);
2694
2695        try {
2696            displayResultSet(statement, statement.getResultSet(), null, null);
2697        } finally {
2698            try {
2699                statement.close();
2700            } catch (Exception   e) {}
2701        }
2702    }
2703
2704    /**
2705     * Display the given result set for user.
2706     * The last 3 params are to narrow down records and columns where
2707     * that can not be done with a where clause (like in metadata queries).
2708     *
2709     * @param statement The SQL Statement that the result set is for.
2710     *                  (This is so we can get the statement's update count.
2711     *                  Can be null for non-update queries.)
2712     * @param r         The ResultSet to display.
2713     * @param incCols   Optional list of which columns to include (i.e., if
2714     *                  given, then other columns will be skipped).
2715     * @param incFilter Optional case-insensitive substring.
2716     *                  Rows are skipped which to not contain this substring.
2717     */
2718    private void displayResultSet(Statement   statement, ResultSet   r,
2719                                  int[] incCols,
2720                                  String   filter) throws SQLException   {
2721
2722        java.sql.Timestamp   ts;
2723        int                updateCount = (statement == null) ? -1
2724                                                             : statement
2725                                                                 .getUpdateCount();
2726        boolean            silent      = silentFetch;
2727        boolean            binary      = fetchBinary;
2728
2729        silentFetch = false;
2730        fetchBinary = false;
2731
2732        if (excludeSysSchemas) {
2733            stdprintln(
2734                "*** WARNING:\n*** Omitting tables from system-supplied "
2735                + "schemas\n*** (because Oracle(TM) "
2736                + "doesn't differentiate them to JDBC).");
2737        }
2738
2739        switch (updateCount) {
2740
2741            case -1 :
2742                if (r == null) {
2743                    stdprintln("No result", true);
2744
2745                    break;
2746                }
2747
2748                ResultSetMetaData   m        = r.getMetaData();
2749                int               cols     = m.getColumnCount();
2750                int               incCount = (incCols == null) ? cols
2751                                                               : incCols
2752                                                                   .length;
2753                String              val;
2754                ArrayList           rows        = new ArrayList  ();
2755                String  []          headerArray = null;
2756                String  []          fieldArray;
2757                int[]             maxWidth = new int[incCount];
2758                int               insi;
2759                boolean           skip;
2760                boolean           ok;
2761
2762                // STEP 1: GATHER DATA
2763                if (!htmlMode) {
2764                    for (int i = 0; i < maxWidth.length; i++) {
2765                        maxWidth[i] = 0;
2766                    }
2767                }
2768
2769                boolean[] rightJust = new boolean[incCount];
2770                int[]     dataType  = new int[incCount];
2771                boolean[] autonulls = new boolean[incCount];
2772
2773                insi        = -1;
2774                headerArray = new String  [incCount];
2775
2776                for (int i = 1; i <= cols; i++) {
2777                    if (incCols != null) {
2778                        skip = true;
2779
2780                        for (int j = 0; j < incCols.length; j++) {
2781                            if (i == incCols[j]) {
2782                                skip = false;
2783                            }
2784                        }
2785
2786                        if (skip) {
2787                            continue;
2788                        }
2789                    }
2790
2791                    headerArray[++insi] = m.getColumnLabel(i);
2792                    dataType[insi]      = m.getColumnType(i);
2793                    rightJust[insi]     = false;
2794                    autonulls[insi]     = true;
2795
2796                    switch (dataType[insi]) {
2797
2798                        case java.sql.Types.BIGINT :
2799                        case java.sql.Types.BIT :
2800                        case java.sql.Types.DECIMAL :
2801                        case java.sql.Types.DOUBLE :
2802                        case java.sql.Types.FLOAT :
2803                        case java.sql.Types.INTEGER :
2804                        case java.sql.Types.NUMERIC :
2805                        case java.sql.Types.REAL :
2806                        case java.sql.Types.SMALLINT :
2807                        case java.sql.Types.TINYINT :
2808                            rightJust[insi] = true;
2809                            break;
2810
2811                        case java.sql.Types.VARBINARY :
2812                        case java.sql.Types.VARCHAR :
2813                            autonulls[insi] = false;
2814                            break;
2815                    }
2816
2817                    if (htmlMode) {
2818                        continue;
2819                    }
2820
2821                    if (headerArray[insi].length() > maxWidth[insi]) {
2822                        maxWidth[insi] = headerArray[insi].length();
2823                    }
2824                }
2825
2826                boolean filteredOut;
2827
2828                EACH_ROW:
2829                while (r.next()) {
2830                    fieldArray  = new String  [incCount];
2831                    insi        = -1;
2832                    filteredOut = filter != null;
2833
2834                    for (int i = 1; i <= cols; i++) {
2835
2836                        // This is the only case where we can save a data
2837                        // read by recognizing we don't need this datum early.
2838                        if (incCols != null) {
2839                            skip = true;
2840
2841                            for (int j = 0; j < incCols.length; j++) {
2842                                if (i == incCols[j]) {
2843                                    skip = false;
2844                                }
2845                            }
2846
2847                            if (skip) {
2848                                continue;
2849                            }
2850                        }
2851
2852                        // This row may still be ditched, but it is now
2853                        // certain that we need to increment the fieldArray
2854                        // index.
2855                        ++insi;
2856
2857                        if (!canDisplayType(dataType[insi])) {
2858                            binary = true;
2859                        }
2860
2861                        val = null;
2862
2863                        if (!binary) {
2864
2865                            // The special formatting for Timestamps is
2866                            // because the most popular current databases
2867                            // are VERY inconsistent about the format
2868                            // returned by getString() for a Timestamp field.
2869                            // In many cases, the output is very user-
2870                            // unfriendly.  However, getTimestamp().toString()
2871                            // is consistent and convenient.
2872                            if (dataType[insi] == java.sql.Types.TIMESTAMP) {
2873                                ts  = r.getTimestamp(i);
2874                                val = ((ts == null) ? null
2875                                                    : ts.toString());
2876                            } else {
2877                                val = r.getString(i);
2878
2879                                // If we tried to get a String but it failed,
2880                                // try getting it with a String Stream
2881                                if (val == null) {
2882                                    try {
2883                                        val = streamToString(
2884                                            r.getAsciiStream(i));
2885                                    } catch (Exception   e) {}
2886                                }
2887                            }
2888                        }
2889
2890                        if (binary || (val == null &&!r.wasNull())) {
2891                            if (pwCsv != null) {
2892
2893                                // TODO:  Should throw something other than
2894                                // a SQLException
2895                                throw new SQLException  (
2896                                    "Table has a binary column.  CSV files "
2897                                    + "are text, not binary, files");
2898                            }
2899
2900                            // DB has a value but we either explicitly want
2901                            // it as binary, or we failed to get it as String.
2902                            try {
2903                                binBuffer =
2904                                    streamToBytes(r.getBinaryStream(i));
2905                            } catch (IOException   ioe) {
2906                                throw new SQLException  (
2907                                    "Failed to read value using stream");
2908                            }
2909
2910                            stdprintln("Read " + binBuffer.length
2911                                       + " bytes from field '"
2912                                       + headerArray[insi] + "' (type "
2913                                       + sqlTypeToString(dataType[insi])
2914                                       + ") into binary buffer");
2915
2916                            return;
2917                        }
2918
2919                        if (excludeSysSchemas && i == 2) {
2920                            for (int z = 0; z < oracleSysSchemas.length;
2921                                    z++) {
2922                                if (val.equals(oracleSysSchemas[z])) {
2923                                    filteredOut = true;
2924
2925                                    break;
2926                                }
2927                            }
2928                        }
2929
2930                        if (fetchingVar != null) {
2931                            userVars.put(fetchingVar, val);
2932
2933                            fetchingVar = null;
2934                        }
2935
2936                        if (silent) {
2937                            return;
2938                        }
2939
2940                        // We do not omit rows here.  We collect information
2941                        // so we can make the decision after all rows are
2942                        // read in.
2943                        if (filter != null
2944                                && (val == null
2945                                    || val.indexOf(filter) > -1)) {
2946                            filteredOut = false;
2947                        }
2948
2949                        ///////////////////////////////
2950                        // A little tricky here.  fieldArray[] MUST get set.
2951                        if (val == null && pwCsv == null) {
2952                            if (dataType[insi] == java.sql.Types.VARCHAR) {
2953                                fieldArray[insi] = (htmlMode ? "<I>null</I>"
2954                                                             : "[null]");
2955                            } else {
2956                                fieldArray[insi] = "";
2957                            }
2958                        } else {
2959                            fieldArray[insi] = val;
2960                        }
2961
2962                        ///////////////////////////////
2963                        if (htmlMode || pwCsv != null) {
2964                            continue;
2965                        }
2966
2967                        if (fieldArray[insi].length() > maxWidth[insi]) {
2968                            maxWidth[insi] = fieldArray[insi].length();
2969                        }
2970                    }
2971
2972                    if (!filteredOut) {
2973                        rows.add(fieldArray);
2974                    }
2975                }
2976
2977                // STEP 2: DISPLAY DATA  (= 2a OR 2b)
2978                // STEP 2a (Non-CSV)
2979                if (pwCsv == null) {
2980                    condlPrintln("<TABLE border='1'>", true);
2981
2982                    if (incCount > 1) {
2983                        condlPrint(htmlRow(COL_HEAD) + '\n' + PRE_TD, true);
2984
2985                        for (int i = 0; i < headerArray.length; i++) {
2986                            condlPrint("<TD>" + headerArray[i] + "</TD>",
2987                                       true);
2988                            condlPrint(((i > 0) ? spaces(2)
2989                                                : "") + pad(
2990                                                    headerArray[i],
2991                                                    maxWidth[i],
2992                                                    rightJust[i],
2993                                                    (i < headerArray.length
2994                                                     - 1 || rightJust[i])), false);
2995                        }
2996
2997                        condlPrintln("\n" + PRE_TR + "</TR>", true);
2998                        condlPrintln("", false);
2999
3000                        if (!htmlMode) {
3001                            for (int i = 0; i < headerArray.length; i++) {
3002                                condlPrint(((i > 0) ? spaces(2)
3003                                                    : "") + divider(
3004                                                        maxWidth[i]), false);
3005                            }
3006
3007                            condlPrintln("", false);
3008                        }
3009                    }
3010
3011                    for (int i = 0; i < rows.size(); i++) {
3012                        condlPrint(htmlRow(((i % 2) == 0) ? COL_EVEN
3013                                                          : COL_ODD) + '\n'
3014                                                          + PRE_TD, true);
3015
3016                        fieldArray = (String  []) rows.get(i);
3017
3018                        for (int j = 0; j < fieldArray.length; j++) {
3019                            condlPrint("<TD>" + fieldArray[j] + "</TD>",
3020                                       true);
3021                            condlPrint(((j > 0) ? spaces(2)
3022                                                : "") + pad(
3023                                                    fieldArray[j],
3024                                                    maxWidth[j],
3025                                                    rightJust[j],
3026                                                    (j < fieldArray.length
3027                                                     - 1 || rightJust[j])), false);
3028                        }
3029
3030                        condlPrintln("\n" + PRE_TR + "</TR>", true);
3031                        condlPrintln("", false);
3032                    }
3033
3034                    condlPrintln("</TABLE>", true);
3035
3036                    if (rows.size() != 1) {
3037                        stdprintln("\n" + rows.size() + " rows", true);
3038                    }
3039
3040                    condlPrintln("<HR>", true);
3041
3042                    break;
3043                }
3044
3045                // STEP 2b (CSV)
3046                if (incCount > 0) {
3047                    for (int i = 0; i < headerArray.length; i++) {
3048                        csvSafe(headerArray[i]);
3049                        pwCsv.print(headerArray[i]);
3050
3051                        if (i < headerArray.length - 1) {
3052                            pwCsv.print(csvColDelim);
3053                        }
3054                    }
3055
3056                    pwCsv.print(csvRowDelim);
3057                }
3058
3059                for (int i = 0; i < rows.size(); i++) {
3060                    fieldArray = (String  []) rows.get(i);
3061
3062                    for (int j = 0; j < fieldArray.length; j++) {
3063                        csvSafe(fieldArray[j]);
3064                        pwCsv.print((fieldArray[j] == null)
3065                                    ? (autonulls[j] ? ""
3066                                                    : csvNullRep)
3067                                    : fieldArray[j]);
3068
3069                        if (j < fieldArray.length - 1) {
3070                            pwCsv.print(csvColDelim);
3071                        }
3072                    }
3073
3074                    pwCsv.print(csvRowDelim);
3075                }
3076
3077                stdprintln(Integer.toString(rows.size())
3078                           + " rows read from DB");
3079                break;
3080
3081            default :
3082                if (fetchingVar != null) {
3083                    userVars.put(fetchingVar, Integer.toString(updateCount));
3084
3085                    fetchingVar = null;
3086                }
3087
3088                if (updateCount != 0) {
3089                    stdprintln(Integer.toString(updateCount) + " row"
3090                               + ((updateCount == 1) ? ""
3091                                                     : "s") + " updated");
3092                }
3093                break;
3094        }
3095    }
3096
3097    private static final int    COL_HEAD = 0,
3098                                COL_ODD  = 1,
3099                                COL_EVEN = 2
3100    ;
3101    private static final String   PRE_TR   = spaces(4);
3102    private static final String   PRE_TD   = spaces(8);
3103
3104    /**
3105     * Print a properly formatted HTML &lt;TR&gt; command for the given
3106     * situation.
3107     *
3108     * @param colType Column type:  COL_HEAD, COL_ODD or COL_EVEN.
3109     */
3110    private static String   htmlRow(int colType) {
3111
3112        switch (colType) {
3113
3114            case COL_HEAD :
3115                return PRE_TR + "<TR style='font-weight: bold;'>";
3116
3117            case COL_ODD :
3118                return PRE_TR
3119                       + "<TR style='background: #94d6ef; font: normal "
3120                       + "normal 10px/10px Arial, Helvitica, sans-serif;'>";
3121
3122            case COL_EVEN :
3123                return PRE_TR
3124                       + "<TR style='background: silver; font: normal "
3125                       + "normal 10px/10px Arial, Helvitica, sans-serif;'>";
3126        }
3127
3128        return null;
3129    }
3130
3131    /**
3132     * Returns a divider of hypens of requested length.
3133     *
3134     * @param len Length of output String.
3135     */
3136    private static String   divider(int len) {
3137        return (len > DIVIDER.length()) ? DIVIDER
3138                                        : DIVIDER.substring(0, len);
3139    }
3140
3141    /**
3142     * Returns a String of spaces of requested length.
3143     *
3144     * @param len Length of output String.
3145     */
3146    private static String   spaces(int len) {
3147        return (len > SPACES.length()) ? SPACES
3148                                       : SPACES.substring(0, len);
3149    }
3150
3151    /**
3152     * Pads given input string out to requested length with space
3153     * characters.
3154     *
3155     * @param inString Base string.
3156     * @param fulllen  Output String length.
3157     * @param rightJustify  True to right justify, false to left justify.
3158     */
3159    private static String   pad(String   inString, int fulllen,
3160                              boolean rightJustify, boolean doPad) {
3161
3162        if (!doPad) {
3163            return inString;
3164        }
3165
3166        int len = fulllen - inString.length();
3167
3168        if (len < 1) {
3169            return inString;
3170        }
3171
3172        String   pad = spaces(len);
3173
3174        return ((rightJustify ? pad
3175                              : "") + inString + (rightJustify ? ""
3176                                                               : pad));
3177    }
3178
3179    /**
3180     * Display command history, which consists of complete or incomplete SQL
3181     * commands.
3182     */
3183    private void showHistory() {
3184
3185        int      ctr = -1;
3186        String     s;
3187        String  [] reversedList = new String  [statementHistory.length];
3188
3189        try {
3190            for (int i = curHist; i >= 0; i--) {
3191                s = statementHistory[i];
3192
3193                if (s == null) {
3194                    return;
3195                }
3196
3197                reversedList[++ctr] = s;
3198            }
3199
3200            for (int i = statementHistory.length - 1; i > curHist; i--) {
3201                s = statementHistory[i];
3202
3203                if (s == null) {
3204                    return;
3205                }
3206
3207                reversedList[++ctr] = s;
3208            }
3209        } finally {
3210            if (ctr < 0) {
3211                stdprintln("<<<    No history yet    >>>");
3212
3213                return;
3214            }
3215
3216            for (int i = ctr; i >= 0; i--) {
3217                psStd.println(((i == 0) ? "BUFR"
3218                                        : ("-" + i + "  ")) + " **********************************************\n"
3219                                        + reversedList[i]);
3220            }
3221
3222            psStd.println(
3223                "\n<<<  Copy a command to buffer like \"\\-3\"       "
3224                + "Re-execute buffer like \":;\"  >>>");
3225        }
3226    }
3227
3228    /**
3229     * Return a SQL Command from command history.
3230     */
3231    private String   commandFromHistory(int commandsAgo) throws BadSpecial {
3232
3233        if (commandsAgo >= statementHistory.length) {
3234            throw new BadSpecial("History can only hold up to "
3235                                 + statementHistory.length + " commands");
3236        }
3237
3238        String   s =
3239            statementHistory[(statementHistory.length + curHist - commandsAgo) % statementHistory.length];
3240
3241        if (s == null) {
3242            throw new BadSpecial("History doesn't go back that far");
3243        }
3244
3245        return s;
3246    }
3247
3248    /**
3249     * Push a command onto the history array (the first element of which
3250     * is the "Buffer").
3251     */
3252    private void setBuf(String   inString) {
3253
3254        curHist++;
3255
3256        if (curHist == statementHistory.length) {
3257            curHist = 0;
3258        }
3259
3260        statementHistory[curHist] = inString;
3261    }
3262
3263    /**
3264     * Describe the columns of specified table.
3265     *
3266     * @param tableName  Table that will be described.
3267     * @param filter  Substring to filter by
3268     */
3269    private void describe(String   tableName,
3270                          String   inFilter) throws SQLException   {
3271
3272        /*
3273         * Doing case-sensitive filters now, for greater portability.
3274        String filter = ((inFilter == null) ? null : inFilter.toUpperCase());
3275         */
3276        String      filter = inFilter;
3277        String      val;
3278        ArrayList   rows        = new ArrayList  ();
3279        String  []  headerArray = {
3280            "name", "datatype", "width", "no-nulls"
3281        };
3282        String  []  fieldArray;
3283        int[]     maxWidth  = {
3284            0, 0, 0, 0
3285        };
3286        boolean[] rightJust = {
3287            false, false, true, false
3288        };
3289
3290        // STEP 1: GATHER DATA
3291        for (int i = 0; i < headerArray.length; i++) {
3292            if (htmlMode) {
3293                continue;
3294            }
3295
3296            if (headerArray[i].length() > maxWidth[i]) {
3297                maxWidth[i] = headerArray[i].length();
3298            }
3299        }
3300
3301        Statement   statement = curConn.createStatement();
3302        ResultSet   r         = null;
3303
3304        try {
3305            statement.execute("SELECT * FROM " + tableName + " WHERE 1 = 2");
3306
3307            r = statement.getResultSet();
3308
3309            ResultSetMetaData   m    = r.getMetaData();
3310            int               cols = m.getColumnCount();
3311
3312            for (int i = 0; i < cols; i++) {
3313                fieldArray    = new String  [4];
3314                fieldArray[0] = m.getColumnName(i + 1);
3315
3316                if (filter != null && fieldArray[0].indexOf(filter) < 0) {
3317                    continue;
3318                }
3319
3320                fieldArray[1] = m.getColumnTypeName(i + 1);
3321                fieldArray[2] = Integer.toString(m.getColumnDisplaySize(i
3322                        + 1));
3323                fieldArray[3] =
3324                    ((m.isNullable(i + 1) == java.sql.ResultSetMetaData.columnNullable)
3325                     ? (htmlMode ? "&nbsp;"
3326                                 : "")
3327                     : "*");
3328
3329                rows.add(fieldArray);
3330
3331                for (int j = 0; j < fieldArray.length; j++) {
3332                    if (fieldArray[j].length() > maxWidth[j]) {
3333                        maxWidth[j] = fieldArray[j].length();
3334                    }
3335                }
3336            }
3337
3338            // STEP 2: DISPLAY DATA
3339            condlPrint("<TABLE border='1'>\n" + htmlRow(COL_HEAD) + '\n'
3340                       + PRE_TD, true);
3341
3342            for (int i = 0; i < headerArray.length; i++) {
3343                condlPrint("<TD>" + headerArray[i] + "</TD>", true);
3344                condlPrint(((i > 0) ? spaces(2)
3345                                    : "") + pad(headerArray[i], maxWidth[i],
3346                                                rightJust[i],
3347                                                (i < headerArray.length - 1
3348                                                 || rightJust[i])), false);
3349            }
3350
3351            condlPrintln("\n" + PRE_TR + "</TR>", true);
3352            condlPrintln("", false);
3353
3354            if (!htmlMode) {
3355                for (int i = 0; i < headerArray.length; i++) {
3356                    condlPrint(((i > 0) ? spaces(2)
3357                                        : "") + divider(maxWidth[i]), false);
3358                }
3359
3360                condlPrintln("", false);
3361            }
3362
3363            for (int i = 0; i < rows.size(); i++) {
3364                condlPrint(htmlRow(((i % 2) == 0) ? COL_EVEN
3365                                                  : COL_ODD) + '\n'
3366                                                  + PRE_TD, true);
3367
3368                fieldArray = (String  []) rows.get(i);
3369
3370                for (int j = 0; j < fieldArray.length; j++) {
3371                    condlPrint("<TD>" + fieldArray[j] + "</TD>", true);
3372                    condlPrint(((j > 0) ? spaces(2)
3373                                        : "") + pad(
3374                                            fieldArray[j], maxWidth[j],
3375                                            rightJust[j],
3376                                            (j < fieldArray.length - 1
3377                                             || rightJust[j])), false);
3378                }
3379
3380                condlPrintln("\n" + PRE_TR + "</TR>", true);
3381                condlPrintln("", false);
3382            }
3383
3384            condlPrintln("\n</TABLE>\n<HR>", true);
3385        } finally {
3386            try {
3387                if (r != null) {
3388                    r.close();
3389
3390                    r = null;
3391                }
3392
3393                statement.close();
3394            } catch (Exception   e) {}
3395        }
3396    }
3397
3398    public static String  [] getTokenArray(String   inString) {
3399
3400        // I forget how to code a String array literal outside of a
3401        // definition.
3402        String  [] mtString = {};
3403
3404        if (inString == null) {
3405            return mtString;
3406        }
3407
3408        StringTokenizer   toker = new StringTokenizer  (inString);
3409        String  []        sa    = new String  [toker.countTokens()];
3410
3411        for (int i = 0; i < sa.length; i++) {
3412            sa[i] = toker.nextToken();
3413        }
3414
3415        return sa;
3416    }
3417
3418    private boolean eval(String  [] inTokens) throws BadSpecial {
3419
3420        // dereference *VARNAME variables.
3421        // N.b. we work with a "copy" of the tokens.
3422        boolean  negate = inTokens.length > 0 && inTokens[0].equals("!");
3423        String  [] tokens = new String  [negate ? (inTokens.length - 1)
3424                                            : inTokens.length];
3425
3426        for (int i = 0; i < tokens.length; i++) {
3427            tokens[i] = (inTokens[i + (negate ? 1
3428                                              : 0)].length() > 1 && inTokens[i + (negate ? 1
3429                                                                                         : 0)].charAt(
3430                                                                                         0) == '*') ? ((String  ) userVars.get(
3431                                                                                             inTokens[i + (negate ? 1
3432                                                                                                                  : 0)]
3433                                                                                                                  .substring(
3434                                                                                                                      1)))
3435                                                                                                    : inTokens[i + (negate ? 1
3436                                                                                                                           : 0)];
3437
3438            if (tokens[i] == null) {
3439                tokens[i] = "";
3440            }
3441        }
3442
3443        if (tokens.length == 1) {
3444            return (tokens[0].length() > 0 &&!tokens[0].equals("0")) ^ negate;
3445        }
3446
3447        if (tokens.length == 3) {
3448            if (tokens[1].equals("==")) {
3449                return tokens[0].equals(tokens[2]) ^ negate;
3450            }
3451
3452            if (tokens[1].equals("!=") || tokens[1].equals("<>")
3453                    || tokens[1].equals("><")) {
3454                return (!tokens[0].equals(tokens[2])) ^ negate;
3455            }
3456
3457            if (tokens[1].equals(">")) {
3458                return (tokens[0].length() > tokens[2].length() || ((tokens[0].length() == tokens[2].length()) && tokens[0].compareTo(tokens[2]) > 0))
3459                       ^ negate;
3460            }
3461
3462            if (tokens[1].equals("<")) {
3463                return (tokens[2].length() > tokens[0].length() || ((tokens[2].length() == tokens[0].length()) && tokens[2].compareTo(tokens[0]) > 0))
3464                       ^ negate;
3465            }
3466        }
3467
3468        throw new BadSpecial("Unrecognized logical operation");
3469    }
3470
3471    private void closeQueryOutputStream() {
3472
3473        if (pwQuery == null) {
3474            return;
3475        }
3476
3477        if (htmlMode) {
3478            pwQuery.println("</BODY></HTML>");
3479            pwQuery.flush();
3480        }
3481
3482        pwQuery.close();
3483
3484        pwQuery = null;
3485    }
3486
3487    /**
3488     * Print to psStd and possibly pwQuery iff current HTML mode matches
3489     * supplied printHtml.
3490     */
3491    private void condlPrintln(String   s, boolean printHtml) {
3492
3493        if ((printHtml &&!htmlMode) || (htmlMode &&!printHtml)) {
3494            return;
3495        }
3496
3497        psStd.println(s);
3498
3499        if (pwQuery != null) {
3500            pwQuery.println(s);
3501            pwQuery.flush();
3502        }
3503    }
3504
3505    /**
3506     * Print to psStd and possibly pwQuery iff current HTML mode matches
3507     * supplied printHtml.
3508     */
3509    private void condlPrint(String   s, boolean printHtml) {
3510
3511        if ((printHtml &&!htmlMode) || (htmlMode &&!printHtml)) {
3512            return;
3513        }
3514
3515        psStd.print(s);
3516
3517        if (pwQuery != null) {
3518            pwQuery.print(s);
3519            pwQuery.flush();
3520        }
3521    }
3522
3523    private static String   formatNicely(Map   map, boolean withValues) {
3524
3525        String         key;
3526        StringBuffer   sb = new StringBuffer  ();
3527        Iterator       it = (new TreeMap  (map)).keySet().iterator();
3528
3529        if (withValues) {
3530            sb.append("The outermost parentheses are not part of "
3531                      + "the values.\n");
3532        } else {
3533            sb.append("Showing variable names and length of values "
3534                      + "(use 'listvalue' to see values).\n");
3535        }
3536
3537        while (it.hasNext()) {
3538            key = (String  ) it.next();
3539
3540            String   s = (String  ) map.get(key);
3541
3542            sb.append("    " + key + ": " + (withValues ? ("(" + s + ')')
3543                                                        : Integer.toString(
3544                                                        s.length())) + '\n');
3545        }
3546
3547        return sb.toString();
3548    }
3549
3550    /**
3551     * Ascii file dump.
3552     */
3553    private void dump(String   varName,
3554                      File   dumpFile) throws IOException  , BadSpecial {
3555
3556        String   val = (String  ) userVars.get(varName);
3557
3558        if (val == null) {
3559            throw new BadSpecial("Variable '" + varName
3560                                 + "' has no value set");
3561        }
3562
3563        OutputStreamWriter   osw =
3564            new OutputStreamWriter  (new FileOutputStream  (dumpFile), charset);
3565
3566        osw.write(val);
3567
3568        boolean terminated = false;
3569
3570        if (val.length() > 0) {
3571            char lastChar = val.charAt(val.length() - 1);
3572
3573            if (lastChar != '\n' && lastChar != '\r') {
3574                terminated = true;
3575
3576                osw.write('\n');    // I hope this really writes \r\n for DOS
3577            }
3578        }
3579
3580        osw.flush();
3581        osw.close();
3582
3583        // Since opened in overwrite mode, since we didn't exception out,
3584        // we can be confident that we wrote all the bytest in the file.
3585        stdprintln("Saved " + dumpFile.length() + " characters to '"
3586                   + dumpFile + "'");
3587    }
3588
3589    byte[] binBuffer = null;
3590
3591    /**
3592     * Binary file dump
3593     */
3594    private void dump(File   dumpFile) throws IOException  , BadSpecial {
3595
3596        if (binBuffer == null) {
3597            throw new BadSpecial("Binary SqlFile buffer is currently empty");
3598        }
3599
3600        FileOutputStream   fos = new FileOutputStream  (dumpFile);
3601
3602        fos.write(binBuffer);
3603
3604        int len = binBuffer.length;
3605
3606        binBuffer = null;
3607
3608        fos.flush();
3609        fos.close();
3610        stdprintln("Saved " + len + " bytes to '" + dumpFile + "'");
3611    }
3612
3613    private String   streamToString(InputStream   is) throws IOException   {
3614
3615        char[]            xferBuffer   = new char[10240];
3616        StringWriter        stringWriter = new StringWriter  ();
3617        InputStreamReader   isr          = new InputStreamReader  (is, charset);
3618        int               i;
3619
3620        while ((i = isr.read(xferBuffer)) > 0) {
3621            stringWriter.write(xferBuffer, 0, i);
3622        }
3623
3624        return stringWriter.toString();
3625    }
3626
3627    private byte[] streamToBytes(InputStream   is) throws IOException   {
3628
3629        byte[]                xferBuffer = new byte[10240];
3630        ByteArrayOutputStream   baos       = new ByteArrayOutputStream  ();
3631        int                   i;
3632
3633        while ((i = is.read(xferBuffer)) > 0) {
3634            baos.write(xferBuffer, 0, i);
3635        }
3636
3637        return baos.toByteArray();
3638    }
3639
3640    /**
3641     * Ascii file load.
3642     */
3643    private void load(String   varName, File   asciiFile) throws IOException   {
3644
3645        char[]       xferBuffer   = new char[10240];
3646        StringWriter   stringWriter = new StringWriter  ();
3647        InputStreamReader   isr =
3648            new InputStreamReader  (new FileInputStream  (asciiFile), charset);
3649        int i;
3650
3651        while ((i = isr.read(xferBuffer)) > 0) {
3652            stringWriter.write(xferBuffer, 0, i);
3653        }
3654
3655        isr.close();
3656        userVars.put(varName, stringWriter.toString());
3657    }
3658
3659    /**
3660     * Binary file load
3661     */
3662    private void load(File   binFile) throws IOException   {
3663
3664        byte[]                xferBuffer = new byte[10240];
3665        ByteArrayOutputStream   baos       = new ByteArrayOutputStream  ();
3666        FileInputStream         fis        = new FileInputStream  (binFile);
3667        int                   i;
3668
3669        while ((i = fis.read(xferBuffer)) > 0) {
3670            baos.write(xferBuffer, 0, i);
3671        }
3672
3673        fis.close();
3674
3675        binBuffer = baos.toByteArray();
3676
3677        stdprintln("Loaded " + binBuffer.length
3678                   + " bytes into Binary buffer");
3679    }
3680
3681    /**
3682     * This method is used to tell SqlFile whether this Sql Type must
3683     * ALWAYS be loaded to the binary buffer without displaying.
3684     *
3685     * N.b.:  If this returns "true" for a type, then the user can never
3686     * "see" values for these columns.
3687     * Therefore, if a type may-or-may-not-be displayable, better to return
3688     * false here and let the user choose.
3689     * In general, if there is a toString() operator for this Sql Type
3690     * then return false, since the JDBC driver should know how to make the
3691     * value displayable.
3692     *
3693     * The table on this page lists the most common SqlTypes, all of which
3694     * must implement toString():
3695     *     http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html
3696     *
3697     * @see java.sql.Types
3698     */
3699    public static boolean canDisplayType(int i) {
3700
3701        /* I don't now about some of the more obscure types, like REF and
3702         * DATALINK */
3703        switch (i) {
3704
3705            //case java.sql.Types.BINARY :
3706            case java.sql.Types.BLOB :
3707            case java.sql.Types.JAVA_OBJECT :
3708
3709            //case java.sql.Types.LONGVARBINARY :
3710            //case java.sql.Types.LONGVARCHAR :
3711            case java.sql.Types.OTHER :
3712            case java.sql.Types.STRUCT :
3713
3714                //case java.sql.Types.VARBINARY :
3715                return false;
3716        }
3717
3718        return true;
3719    }
3720
3721    // won't compile with JDK 1.3 without these
3722    private static final int JDBC3_BOOLEAN  = 16;
3723    private static final int JDBC3_DATALINK = 70;
3724
3725    public static String   sqlTypeToString(int i) {
3726
3727        switch (i) {
3728
3729            case java.sql.Types.ARRAY :
3730                return "ARRAY";
3731
3732            case java.sql.Types.BIGINT :
3733                return "BIGINT";
3734
3735            case java.sql.Types.BINARY :
3736                return "BINARY";
3737
3738            case java.sql.Types.BIT :
3739                return "BIT";
3740
3741            case java.sql.Types.BLOB :
3742                return "BLOB";
3743
3744            case JDBC3_BOOLEAN :
3745                return "BOOLEAN";
3746
3747            case java.sql.Types.CHAR :
3748                return "CHAR";
3749
3750            case java.sql.Types.CLOB :
3751                return "CLOB";
3752
3753            case JDBC3_DATALINK :
3754                return "DATALINK";
3755
3756            case java.sql.Types.DATE :
3757                return "DATE";
3758
3759            case java.sql.Types.DECIMAL :
3760                return "DECIMAL";
3761
3762            case java.sql.Types.DISTINCT :
3763                return "DISTINCT";
3764
3765            case java.sql.Types.DOUBLE :
3766                return "DOUBLE";
3767
3768            case java.sql.Types.FLOAT :
3769                return "FLOAT";
3770
3771            case java.sql.Types.INTEGER :
3772                return "INTEGER";
3773
3774            case java.sql.Types.JAVA_OBJECT :
3775                return "JAVA_OBJECT";
3776
3777            case java.sql.Types.LONGVARBINARY :
3778                return "LONGVARBINARY";
3779
3780            case java.sql.Types.LONGVARCHAR :
3781                return "LONGVARCHAR";
3782
3783            case java.sql.Types.NULL :
3784                return "NULL";
3785
3786            case java.sql.Types.NUMERIC :
3787                return "NUMERIC";
3788
3789            case java.sql.Types.OTHER :
3790                return "OTHER";
3791
3792            case java.sql.Types.REAL :
3793                return "REAL";
3794
3795            case java.sql.Types.REF :
3796                return "REF";
3797
3798            case java.sql.Types.SMALLINT :
3799                return "SMALLINT";
3800
3801            case java.sql.Types.STRUCT :
3802                return "STRUCT";
3803
3804            case java.sql.Types.TIME :
3805                return "TIME";
3806
3807            case java.sql.Types.TIMESTAMP :
3808                return "TIMESTAMP";
3809
3810            case java.sql.Types.TINYINT :
3811                return "TINYINT";
3812
3813            case java.sql.Types.VARBINARY :
3814                return "VARBINARY";
3815
3816            case java.sql.Types.VARCHAR :
3817                return "VARCHAR";
3818        }
3819
3820        return "Unknown type " + i;
3821    }
3822
3823    /**
3824     * Validate that String is safe to display in a CSV file.
3825     *
3826     * @throws SQLException (should throw something else, since this is
3827     * not an SQL problem.  Fix the caller!)
3828     */
3829    public void csvSafe(String   s) throws SQLException   {
3830
3831        if (pwCsv == null || csvColDelim == null || csvRowDelim == null
3832                || csvNullRep == null) {
3833            throw new RuntimeException  (
3834                "Assertion failed.  \n"
3835                + "csvSafe called when CSV settings are incomplete");
3836        }
3837
3838        if (s == null) {
3839            return;
3840        }
3841
3842        if (s.indexOf(csvColDelim) > 0) {
3843            throw new SQLException  (
3844                "Table data contains our column delimiter '" + csvColDelim
3845                + "'");
3846        }
3847
3848        if (s.indexOf(csvRowDelim) > 0) {
3849            throw new SQLException  ("Table data contains our row delimiter '"
3850                                   + csvRowDelim + "'");
3851        }
3852
3853        if (s.indexOf(csvNullRep) > 0) {
3854            throw new SQLException  (
3855                "Table data contains our null representation '" + csvNullRep
3856                + "'");
3857        }
3858    }
3859
3860    public static String   convertEscapes(String   inString) {
3861
3862        if (inString == null) {
3863            return null;
3864        }
3865
3866        String   workString = new String  (inString);
3867        int    i;
3868
3869        i = 0;
3870
3871        while ((i = workString.indexOf("\\n", i)) > -1
3872                && i < workString.length() - 1) {
3873            workString = workString.substring(0, i) + '\n'
3874                         + workString.substring(i + 2);
3875        }
3876
3877        i = 0;
3878
3879        while ((i = workString.indexOf("\\r", i)) > -1
3880                && i < workString.length() - 1) {
3881            workString = workString.substring(0, i) + '\r'
3882                         + workString.substring(i + 2);
3883        }
3884
3885        i = 0;
3886
3887        while ((i = workString.indexOf("\\t", i)) > -1
3888                && i < workString.length() - 1) {
3889            workString = workString.substring(0, i) + '\t'
3890                         + workString.substring(i + 2);
3891        }
3892
3893        return workString;
3894    }
3895
3896    /**
3897     * Name is self-explanatory.
3898     *
3899     * If there is user demand, open file in random access mode so don't
3900     * need to load 2 copies of the entire file into memory.
3901     * This will be difficult because can't use standard Java language
3902     * features to search through a character array for multi-character
3903     * substrings.
3904     */
3905    public void importCsv(String   filePath) throws IOException  , BadSpecial {
3906
3907        char[] bfr  = null;
3908        File     file = new File  (filePath);
3909
3910        if (!file.canRead()) {
3911            throw new IOException  ("Can't read file '" + file + "'");
3912        }
3913
3914        int fileLength = (int) (file.length());
3915
3916        try {
3917            bfr = new char[fileLength];
3918        } catch (RuntimeException   re) {
3919            throw new IOException  (
3920                "SqlFile can only read in your CSV file in one chunk at this time.\n"
3921                + "Please run the program with more RAM (try Java -Xm* switches).");
3922        }
3923
3924        InputStreamReader   isr =
3925            new InputStreamReader  (new FileInputStream  (file), charset);
3926        int retval = isr.read(bfr, 0, bfr.length);
3927
3928        isr.close();
3929
3930        if (retval != bfr.length) {
3931            throw new IOException  ("Didn't read all characters.  Read in "
3932                                  + retval + " characters");
3933        }
3934
3935        String   string = null;
3936
3937        try {
3938            string = new String  (bfr);
3939        } catch (RuntimeException   re) {
3940            throw new IOException  (
3941                "SqlFile converts your entire CSV file to a String at this time.\n"
3942                + "Please run the program with more RAM (try Java -Xm* switches).");
3943        }
3944
3945        ArrayList   headerList = new ArrayList  ();
3946        String      recordString;
3947
3948        // N.b.  ENDs are the index of 1 PAST the current item
3949        int recEnd;
3950        int colStart;
3951        int colEnd;
3952
3953        // First read header line
3954        int recStart = 0;
3955
3956        recEnd = string.indexOf(csvRowDelim, recStart);
3957
3958        if (recEnd < 0) {
3959
3960            // File consists of only a header line
3961            recEnd = string.length();
3962        }
3963
3964        colStart = recStart;
3965        colEnd   = -1;
3966
3967        while (true) {
3968            if (colEnd == recEnd) {
3969
3970                // We processed final column last time through loop
3971                break;
3972            }
3973
3974            colEnd = string.indexOf(csvColDelim, colStart);
3975
3976            if (colEnd < 0 || colEnd > recEnd) {
3977                colEnd = recEnd;
3978            }
3979
3980            if (colEnd - colStart < 1) {
3981                throw new IOException  ("No column header for column "
3982                                      + (headerList.size() + 1));
3983            }
3984
3985            headerList.add(string.substring(colStart, colEnd));
3986
3987            colStart = colEnd + csvColDelim.length();
3988        }
3989
3990        String  []  headers   = (String  []) headerList.toArray(new String  [0]);
3991        boolean[] autonulls = new boolean[headers.length];
3992        String      tableName = (String  ) userVars.get("*CSV_TABLENAME");
3993
3994        if (tableName == null) {
3995            tableName = file.getName();
3996
3997            int i = tableName.lastIndexOf('.');
3998
3999            if (i > 0) {
4000                tableName = tableName.substring(0, i);
4001            }
4002        }
4003
4004        StringBuffer   tmpSb = new StringBuffer  ();
4005
4006        for (int i = 0; i < headers.length; i++) {
4007            if (i > 0) {
4008                tmpSb.append(", ");
4009            }
4010
4011            tmpSb.append(headers[i]);
4012        }
4013
4014        StringBuffer   sb = new StringBuffer  ("INSERT INTO " + tableName + " ("
4015                                           + tmpSb + ") VALUES (");
4016        StringBuffer   typeQuerySb = new StringBuffer  ("SELECT " + tmpSb
4017            + " FROM " + tableName + " WHERE 1 = 2");
4018
4019        try {
4020            int ctype;
4021            ResultSetMetaData   rsmd = curConn.createStatement().executeQuery(
4022                typeQuerySb.toString()).getMetaData();
4023
4024            if (rsmd.getColumnCount() != autonulls.length) {
4025                throw new BadSpecial("Metadata mismatch for columns");
4026            }
4027
4028            for (int i = 0; i < autonulls.length; i++) {
4029                ctype = rsmd.getColumnType(i + 1);
4030
4031                // I.e., for VAR* column types, "" in CSV file means
4032                // to insert "".  Otherwise, we'll insert null for "".
4033                autonulls[i] = (ctype != java.sql.Types.VARBINARY
4034                                && ctype != java.sql.Types.VARCHAR);
4035            }
4036        } catch (SQLException   se) {
4037            throw new BadSpecial("Failed to get metadata for query: "
4038                                 + se.getMessage());
4039        }
4040
4041        for (int i = 0; i < headers.length; i++) {
4042            if (i > 0) {
4043                sb.append(", ");
4044            }
4045
4046            sb.append('?');
4047        }
4048
4049        //System.out.println("INSERTION: (" + sb + ')');
4050        try {
4051            PreparedStatement   ps = curConn.prepareStatement(sb.toString()
4052                + ')');
4053            String  [] dataVals = new String  [headers.length];
4054            int      recCount = 0;
4055            int      colCount;
4056
4057            // Insert data rows 1-row-at-a-time
4058            while (true) {
4059                recStart = recEnd + csvRowDelim.length();
4060
4061                if (recStart >= string.length()) {
4062                    break;
4063                }
4064
4065                recEnd = string.indexOf(csvRowDelim, recStart);
4066
4067                if (recEnd < 0) {
4068
4069                    // Last record
4070                    recEnd = string.length();
4071                }
4072
4073                colStart = recStart;
4074                colEnd   = -1;
4075                colCount = 0;
4076
4077                recCount++;
4078
4079                while (true) {
4080                    if (colEnd == recEnd) {
4081
4082                        // We processed final column last time through loop
4083                        break;
4084                    }
4085
4086                    colEnd = string.indexOf(csvColDelim, colStart);
4087
4088                    if (colEnd < 0 || colEnd > recEnd) {
4089                        colEnd = recEnd;
4090                    }
4091
4092                    if (colCount == dataVals.length) {
4093                        throw new IOException  (
4094                            "Header has " + headers.length
4095                            + " columns.  CSV record " + recCount
4096                            + " has too many column values.");
4097                    }
4098
4099                    dataVals[colCount++] = string.substring(colStart, colEnd);
4100                    colStart             = colEnd + csvColDelim.length();
4101                }
4102
4103                if (colCount != dataVals.length) {
4104                    throw new IOException  ("Header has " + headers.length
4105                                          + " columns.  CSV record "
4106                                          + recCount + " has " + colCount
4107                                          + " column values.");
4108                }
4109
4110                for (int i = 0; i < dataVals.length; i++) {
4111
4112                    //System.err.println("ps.setString(" + i + ", "
4113                    //      + dataVals[i] + ')');
4114                    ps.setString(
4115                        i + 1,
4116                        (((dataVals[i].length() < 1 && autonulls[i]) || dataVals[i].equals(csvNullRep))
4117                         ? null
4118                         : dataVals[i]));
4119                }
4120
4121                retval = ps.executeUpdate();
4122
4123                if (retval != 1) {
4124                    curConn.rollback();
4125
4126                    throw new BadSpecial("Insert of row " + recCount
4127                                         + " failed.  " + retval
4128                                         + " rows modified");
4129                }
4130
4131                possiblyUncommitteds.set(true);
4132            }
4133
4134            stdprintln("Successfully inserted " + recCount
4135                       + " rows into table '" + tableName + "'");
4136        } catch (SQLException   se) {
4137            try {
4138                curConn.rollback();
4139            } catch (SQLException   se2) {}
4140
4141            throw new BadSpecial(
4142                "SQL error encountered when inserting CSV data: " + se);
4143        }
4144    }
4145}
4146
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags