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