/*
* This software and supporting documentation were developed by
*
* Siemens Corporate Technology
* Competence Center Knowledge Management and Business Transformation
* D-81730 Munich, Germany
*
* Authors (representing a really great team ;-) )
* Stefan B. Augustin, Thorbj�rn Hansen, Manfred Langen
*
* This software is Open Source under GNU General Public License (GPL).
* Read the text of this license in LICENSE.TXT
* or look at www.opensource.org/licenses/
*
* Once more we emphasize, that:
* THIS SOFTWARE IS MADE AVAILABLE, AS IS, WITHOUT ANY WARRANTY
* REGARDING THE SOFTWARE, ITS PERFORMANCE OR
* FITNESS FOR ANY PARTICULAR USE, FREEDOM FROM ANY COMPUTER DISEASES OR
* ITS CONFORMITY TO ANY SPECIFICATION. THE ENTIRE RISK AS TO QUALITY AND
* PERFORMANCE OF THE SOFTWARE IS WITH THE USER.
*
*/
// TsqlInterpreter
package mod.tsql;
import java.io.*;
import java.sql.*;
import java.text.MessageFormat;
import java.util.Date;
import java.util.Stack;
import java.util.Vector;
import java.net.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import KFM.Converter;
import KFM.DB.SQLUtils;
import KFM.SwingWorker;
/** Our improved version of `isql�.
*
*
* <H1>Interactive usage</H1>
*
* <P>For the documentation to the interactive usage, see `cHelpText�.
* Or start Tsql and enter `!help�.</P>
*
*
* <H1>Java usage</H1>
*
* <P>Get a `TsqlInterpreter� and initialize it with `initialize�.
* Then you can use `execute� to execute any commands from Java.
* For an example, see `Tsql.example�.</P>
*
* <P>@@@ Tsql should be refactored so that the Java usage is neater.
* The Java interface does not need the GUI, the history, etc..</P>
*/
public class TsqlInterpreter
{
// ************************************************************
// Classes
// ************************************************************
/** Thrown to get out of `!run�. */
static class StopOnErrorException extends Throwable { }
private static class SyntaxError extends Exception {}
// ************************************************************
// Constants
// ************************************************************
/** Default value for the DB handler class, currently Oracle. */
static final String cJdbcDriverDefault="oracle.jdbc.driver.OracleDriver";
// static final String
// cVersion = cVssVersion.substring(cVssVersion.indexOf("$Revision: 1 $Revision: ".length(), cVssVersion.lastIndexOf(" $")),
// cDate = cVssDate.substring(cVssDate.indexOf("$Date: 12.08.03 17:28 $Date: ".length(), cVssDate.lastIndexOf(" $")),
// cAuthor = cVssAuthor.substring(cVssAuthor.indexOf("$Author: Schuster_s $Author: ".length(), cVssAuthor.lastIndexOf(" $"));
static final String cWelcomeText = "Welcome to tsql by Thorbjoern Hansen.\n"
// + "Version " + cVersion + ", last change by " + cAuthor + " on " + cDate + ".\n"
+ "Enter !help for help.\n"
+ "\n"
;
static final String cUsageHelpText =
"Usage:\n"
+ " java mod.tsql.Tsql [-g] [-D <DRIVER>] -S <CONNECTIONURL> -U <USER> -P <PASSWORD> [OPTIONS]\n";
static final String
cDbPasswordHelpText = " -P X Set DB user password to X, required.\n",
cDbUserHelpText = " -U X Set DB user name to X, required.\n",
cDbConnectionHelpText =
" -S X Set DB connection URL to X, required.\n"
+ " E.g. 'jdbc:oracle:thin:@<HOST>:<PORT>:<DBNAME>'.\n"
+ " E.g. 'jdbc:sybase:Tds:<HOST>:<PORT>/<DBNAME>'.\n";
/**
* 2003-04-08: Wir haben mehrere Alternativen bei der Syntax von Strings mit Zeilenwechesel
* besprochen, aber `!nl� hat gewonnen.
*
* Die Alternativen waren (sortiert nach Beliebtheit):
*
* (1) `!nl� als "Fortsetzer" wie unten beschrieben.
* Die `!nl�s mu� man zwar per Hand hinschreiben, aber so kann der Parser bei einer ungeraden
* Anzahl Quotes sofort Tippfehler und Absicht unterschieden.
* (2) !set multiline on|off|default
* (Wobei als Goodie `default� bei `!data� an und sonst aus bedeutet.)
* Das hat den Nachteil, da� es schwer ist, nach einem Zeilenwechsel zu suchen, sowie
* Vertipper (ein ' zu viel) zu finden.
* (3) Eine Syntaxerweiterung
* {ml 'a
* b'}
* Hier steht `ml� f�r Multiline. Diese Syntax ist analog zur Syntax von JDBC bei DATE, TIME
* und TIMESTAMP mit `{d ...}�, `{t ...}`, `{ts ...}`.
* (4) Eine Syntaxerweiterung f�r Java String quoting
* {s 'a\nb\u00ffc'}
* Diese L�sung hat den Nachteil, da� man aufpassen mu�, jeden Backslash durch verdoppeln zu quoten.
*
* Abschlie�end ist noch zu Bemerken, da� Excel bei CSV Dateien innerhalb von Quotes erlaubt.
*/
static final String cMultiLineStringHelpText =
" - Multiline strings (which contain newlines) will soon be available using `!nl� as follows:\n"
+ " !set separator ;\n"
+ " INSERT INTO tab (col) VALUES ('a\n"
+ " !nl b');\n"
+ " Note that `!nl� is a *feature* and not an ugly hack.\n"
+ " Without it, tsql's parser cannot find typos.\n";
static final String cHelpText =
cUsageHelpText
+ "\n"
+ "* Switches:\n"
+ " -c X Executes one command X from the command line and exits.\n"
+ " To run several commands, use -c with !run.\n"
+ " -g as !graphics\n"
+ " -s X as !set separator X.\n"
+ " -t as !set format terse\n"
+ " -v as !set format verbose\n"
+ " -D X Set DB driver to X, defaults to '" + cJdbcDriverDefault + "'.\n"
+ cDbPasswordHelpText
+ cDbConnectionHelpText
+ cDbUserHelpText
+ " -T X as !set title X.\n"
+ "\n"
+ "* Syntax:\n"
+ " - tsql ignores comments between -- and newline\n."
+ cMultiLineStringHelpText
+ "\n"
+ "* Commands:\n"
+ " !! Comment at beginning of line (deprecated, use --).\n"
+ " !dblog on Log all SQL statements into DB table TsqlHistory. (Default)\n"
+ " !dblog off Do not log...\n"
+ " !dump TABLE Dump content of table TABLE using csv format.\n"
+ " !describe TABLE describe the structure of TABLE.\n"
+ " !echo TEXT Fisplay text.\n"
+ " !execute PROC(...) Execute stored procedure.\n"
+ " !exit Quit program.\n"
+ " !graphics Opens graphic user interfac, the console still works.\n"
+ " !help Display this text.\n"
+ " !help sybase Display useful commands in Sybase.\n"
+ " !help oracle Display useful commands in Oracle.\n"
+ " !history show Show the history in the output window.\n"
+ " !history clear Clear the history.\n"
+ " !log [append] 'FILENAME'\n"
+ " Log output and errors to file.\n"
+ " !log console Stop logging to file, log to console. (Default)\n"
+ " !log off Do not log, neither to file nor to console.\n"
+ " !log statements default|on|off\n"
+ " Log statments also?\n"
+ " Default is ca. 'on for console and off in logs'.\n"
+ " !log timing on|off Log timing info also?\n"
+ " !quit Quit program.\n"
+ " !reopen Reopen DB.\n"
+ " !repeat X Repeat each command X times.\n"
+ " !run 'FILENAME' Execute commands from file.\n"
+ " !set format terse Terse output format, values are concatenated.\n"
+ " !set format usual Usual output format, values are comma separated.\n"
+ " !set format verbose Verbose output format, one line for each value.\n"
+ " !set format csv Comma Separated format, all strings are quoted.\n"
+ " !set format data ???.\n"
+ " !set format sqldump ???.\n"
+ " !set separator off Use Enter as separator.\n"
+ " !set separator X Use X as separator instead of Enter, e.g. ';' or 'go'.\n"
+ " !set stop on error on|off\n"
+ " Should !run stop on errors?\n"
+ " !set title Set window title.\n"
+ " !set error count zero Reset error count.\n"
+ " !set tablemodel on|off\n"
+ " Create a table model, to display in table.\n"
+ " !time X Output current time, prefixed by X.\n"
+ "\n"
+ "* Features of Shell and GUI:\n"
+ "- Enter executes a query, unless a separator is set with -s or !set separator.\n"
+ "- The number of errors is displayed in the prompt,\n"
+ " reset it with '!set error count zero'.\n"
+ "* Features of GUI only:\n"
+ "- The GUI has a Table View and does not print to console, unless\n"
+ " explicitly requested by pressing the Print button.\n"
+ "- Shift+Enter Inserts a line break without executing query.\n"
+ "- PageUp Goto previous command history.\n"
+ "- PageDown Goto next command in history.\n"
+ "- Ctrl-h Show history in separate window,\n"
+ " select with Enter or doubleclick.\n"
+ "\n"
;
static final String cHelpTextSybase =
"* Useful comands in Sybase\n"
+ " sp_help\n"
+ " sp_tables\n"
+ " sp_columns \"table_name\"\n"
+ "\n"
;
static final String cHelpTextOracle =
"* Useful comands in Oracle\n"
+ " select TABLE_NAME from USER_TABLES\n"
+ " select * from USER_TAB_COLUMNS where TABLE_NAME = '...'\n"
+ " select INDEX_NAME, TABLE_NAME from USER_INDEXES\n"
+ " select TABLE_NAME, CONSTRAINT_NAME, STATUS from USER_CONSTRAINTS where TABLE_NAME = '...'\n"
+ " select * from USER_TRIGGERS\n"
+ "\n"
+ "* Define a procedure in Oracle, e.g.:\n"
+ "create or replace procedure testproc (\n"
+ " tNum in number)\n"
+ "is begin\n"
+ " insert into test values (tNum);\n"
+ "end;\n"
+ "\n"
;
// ************************************************************
// Variables
// ************************************************************
boolean mIsStopOnError = false;
long mErrorCount = 0;
Connection mCons[] = new Connection[1];
long[] mTimes = new long[1];
/** Is the command `!run� being executed? */
boolean mIsRun = false;
/** Set by -c. */
boolean mIsCommand;
/** True when `execute� called.
*
* This was intended to change the output behaviour, but that's complicated,
* so we'll do it some other time.
*/
boolean mIsExecute = false;
/** True iff statements should also be logged. */
final byte cLogStatsDefault = 0, cLogStatsOff = 1, cLogStatsOn = 2;
byte mIsLogStatements = cLogStatsDefault;
byte mIsLogDbStatements = cLogStatsDefault;
/** True iff timing should also be logged. */
boolean mIsLogTiming = false;
/** WHAT IS THIS FOR?
*
* !log off sets it to false
* !log console sets it to true
*/
boolean mIsOutputOn = true;
BufferedReader mStdIn;
/** May be set to mStdIn or to a file. */
BufferedReader mIn;
/** Stack of `mIn�s. */
Stack /*of BufferedReader*/ mInStack;
/** May be set to null or to a file. */
BufferedWriter mOut;
String mSeparator = null;
String mAccumulatedCmd = "";
public static class FORMAT
{
public static final int USUAL = 0;
public static final int TERSE = 1;
public static final int VERBOSE = 2;
public static final int CSV = 3;
public static final int SQLDUMP = 4;
public static final int DATA = 5;
}
int mFormat = FORMAT.USUAL;
// ************************************************************
// Layout variables.
String mCell;
String mRowBreak;
String mFinish; // See `mPostfix� below.
String mNoResults;
boolean mHeaders;
String mPrefix = "";
String mPostfix = ""; // We should use `mFinish� here.
// ************************************************************
// DB variables.
/** The DB handler class.
*
* Tested values are:
* - Sybase via jConnect 4.1: com.sybase.jdbc.SybDriver
* - Oracle: oracle.jdbc.driver.OracleDriver (see `cJdbcDriverOracle�)
*/
String mJdbcDriver;
/** The DB connection URL. */
String mJdbcConnectionURL;
/** The DB user name. */
String mJdbcUser;
/** The DB user password. */
String mJdbcPassword;
// ************************************************************
// GUI variables.
TsqlFrame mTsqlFrame = null;
private static boolean mGraphics = false;
/** Hack to get `cmdRun� to work from GUI.
*
* For the hack, see `cmdRun�, `handleLoop�, `handle� and `handleGui�.
* JD: This variable has nothing to do with whether Graphics are enabled or not!
* It is only used to get a HACK to work.
* @see #isGraphics() it will tell you if you are allowed to write the gui or not.
*/
boolean mIsGuiCommand = false;
// JFrame mFrame = null;
String mFrameTitle = "tsql";
// TextArea mTextArea = null;
// TextArea mInTextArea = null;
/** History. */
Vector mHistory = null;
/** Location of current history element. */
int mHistoryIndex;
JList mHistoryList = null;
JFrame mHistoryFrame = null;
private boolean mInQuery = false;
private StringBuffer mInQuerySB = new StringBuffer();
private String mLoadTable = null;
private String mLoadColumns = null;
public boolean mUseTableModel = true;
// ************************************************************
// Public Methods
// ************************************************************
/** Constructor.
*
* @@@ Stuff from `initialize� should be moved up here.
*/
public TsqlInterpreter ()
{
}
/** Handles `Tsql.main�. */
public void handleMain (String[] args)
throws IOException
{
initialize(args);
try {
if(mIsCommand) {
System.err.println("Executing: " + args[args.length-1]);
handle(args[args.length-1]);
return;
}
} catch(StopOnErrorException e) {
System.exit(1);
}
// If a `StopOnErrorException� is thrown, restart the `handleLoop�.
// Else exit.
while(true) {
try {
handleLoop(/*QuitOnEOF*/ false, /*ReturnOnEOF*/ false);
break;
} catch(StopOnErrorException e) {
if(mIsRun) {
error("Script aborted due to error.\n");
}
mIsRun = false;
initializeStreams();
printPrompt();
}
}
}
void printPrompt ()
throws IOException
{
String tPrompt = "tsql (" + mErrorCount+ ")> ";
if(mIsLogStatements == cLogStatsOn) {
// User always wants prompt.
print(tPrompt);
} else if(mIsLogStatements == cLogStatsDefault && mOut == null) {
// User does not have logging, so he presumably wants prompts.
print(tPrompt);
} else if(mIsLogStatements == cLogStatsDefault && mOut != null && ! mIsRun) {
// User does have logging, but is working interactively, so
// he only wants a prompt in the GUI, not in the log.
System.out.println(tPrompt);
if(isGraphics()) {
_outputGui(tPrompt);
}
} else {
// No prompt.
}
}
private void assertTrue(String aErr, boolean aB) {
if(! aB) {
System.err.println(aErr);
System.exit(1);
}
}
/** Initialize.
*
* @@@ Some of this stuff belongs into constructor.
*/
public void initialize (String[] args)
throws IOException
{
// Static initializer to initialize `mHistory� and `mHistoryIndex�.
// When `tsql� is restructured to use an object instead of `static� methods,
// then this must move into the constructor.
initializeHistory();
mIsCommand = false;
cmdSetFormatUsual();
for(int i = 0; i < args.length; ++ i) {
final String cErr1 = "Parameter required.";
if (args[i].equals("-S")) { assertTrue(cErr1, i+1<args.length); mJdbcConnectionURL = args[++i]; }
else if(args[i].equals("-U")) { assertTrue(cErr1, i+1<args.length); mJdbcUser = args[++i]; }
else if(args[i].equals("-P")) { assertTrue(cErr1, i+1<args.length); mJdbcPassword = args[++i]; }
else if(args[i].equals("-D")) { assertTrue(cErr1, i+1<args.length); mJdbcDriver = args[++i]; }
else if(args[i].equals("-c")) { mIsCommand = true; }
else if(args[i].equals("-g")) { cmdGraphics(); }
else if(args[i].equals("-s")) { cmdSetSeparator(args[++i]); }
else if(args[i].equals("-t")) { cmdSetFormatTerse(); }
else if(args[i].equals("-T")) { cmdSetTitle(args[++i]); }
else if(args[i].equals("-v")) { cmdSetFormatVerbose(); }
else if(args[i].equals("-h")) { cmdHelp(); System.exit(0); }
}
// Check for required parameters and default driver.
// We do not quit after the first error, instead we try to give all error messages at once.
{
String tErrText = ""; // If not empty, then holds a description of error occured.
if(mJdbcConnectionURL == null) {
tErrText += "Please supply connection URL with switch -S:\n" + cDbConnectionHelpText;
}
if(mJdbcUser == null) {
tErrText += "Please supply user with switch -U:\n" + cDbUserHelpText;
}
if(mJdbcPassword == null) {
tErrText += "Please supply password with switch -P:\n" + cDbPasswordHelpText;
}
if(! tErrText.equals("")) {
System.err.print(cUsageHelpText + tErrText);
}
if(mJdbcDriver == null) {
// Not an error, select default driver and write a message to `System.err�.
System.err.println("Using default driver '" + cJdbcDriverDefault + "'. To change, supply -D parameter.");
mJdbcDriver = cJdbcDriverDefault;
}
if(! tErrText.equals("")) {
System.err.println();
System.exit(1);
}
}
initializeStreams();
try {
Class.forName(mJdbcDriver);
// According to JDBC book, it is not necessary to call `DriverManager.registerDriver�.
} catch (java.lang.ClassNotFoundException ex) {
System.err.println("ClassNotFoundException: " + ex.getMessage());
System.exit(1);
}
print(cWelcomeText
+ "Driver = " + mJdbcDriver + "\n"
+ "ConnectionURL = " + mJdbcConnectionURL + "\n"
+ "User = " + mJdbcUser + "\n"
+ "\n");
cmdReopen();
if(! mIsCommand) {
printPrompt();
}
}
/** Public interface to execute commands from Java.
*
* See the class documentation.
*
* @@@ Oops, we do not get SQLExceptions here, but we should.
*/
public void execute (String s)
throws IOException
{
mInStack.push(mIn);
mIn = new BufferedReader(new StringReader(s));
mIsExecute = true;
try {
handleLoop(/*QuitOnEOF*/ false, /*ReturnOnEOF*/ true);
} catch(StopOnErrorException e) {
// @@@ What should we do here?
} finally {
mIsExecute = false;
}
}
// ************************************************************
// Package and Private Methods
// ************************************************************
void initializeHistory ()
{
mHistory = new Vector();
mHistory.addElement("");
mHistoryIndex = 0;
}
void initializeStreams ()
{
mStdIn = new BufferedReader(new InputStreamReader(System.in));
mIn = mStdIn;
mInStack = new Stack();
}
/** Interpreters main loop.
*
* Note: To allow using `!run� inside scripts started by `!run�,
* `cmdRun� will call `handleLoop� recursively and push `mIn� onto a stack.
* @@@ Using both recursion and a stack is messy, it should get cleaned up.
* Note that `cmdRun� may also be called by `TsqlInputArea�, which complicates
* things so that the `mIsGuiCommand� hack is needed.
*
* For the hack, see `cmdRun�, `handleLoop�, `handle� and `handleGui�.
*/
void handleLoop (boolean aQuitOnEOF, boolean aReturnOnEOF)
throws StopOnErrorException, IOException
{
try {
while(true) {
String tLine = mIn.readLine();
if(tLine == null) {
// EOF reached.
if(mInStack.empty()) {
cmdQuit();
} else {
print("Finished running file.\n");
mIn = (BufferedReader) mInStack.pop();
if(mInStack.empty()) {
print("Back to cmd line.\n");
mIsRun = false;
}
if (aQuitOnEOF) cmdQuit();
else if (aReturnOnEOF) return;
else continue;
}
}
handle(tLine);
}
} catch(IOException e) {
;
}
}
/** Handle one command from GUI.
*
* As `handle�, but with an additional hack to get `cmdRun� to work from GUI.
*
* For the hack, see `cmdRun�, `handleLoop�, `handle� and `handleGui�.
*/
void handleGui (String aLine)
throws StopOnErrorException, IOException
{
mIsGuiCommand = true; // Hack to get `cmdRun� to work from GUI.
try {
handle(aLine);
} finally {
mIsGuiCommand = false;
}
}
/** Handle one command.
*
* See also `handleGui�.
*/
void handle (String aLine)
throws StopOnErrorException, IOException
{
if(mIsLogStatements == cLogStatsOn
|| (mIsLogStatements == cLogStatsDefault && mOut == null))
{
print(aLine + "\n");
} else if(mIsLogStatements == cLogStatsDefault && mOut != null && ! mIsRun) {
// User does have logging, but is working interactively, so
// he only wants to see the queries in the GUI, not in the log.
System.out.println(aLine + "\n");
if(isGraphics()) {
_outputGui(aLine + "\n");
}
}
String tLine = aLine.trim();
if(tLine.equals("!quit") || tLine.equals("!exit")) { cmdQuit(); }
if (tLine.equals("" )) { ; }
else if(tLine.startsWith("!dump " )) { cmdDump(tLine.substring("!dump ".length())); }
else if(tLine.startsWith("!!")
|| tLine.startsWith("--") ) { /* Comments at beginning of line needs no handling. */ }
else if(tLine.startsWith("!echo " )) { cmdEcho(tLine.substring("!echo ".length())); }
else if(tLine.startsWith("!execute " )) { cmdExecute(tLine.substring("!execute ".length())); }
else if(tLine.equals ("!graphics" )) { cmdGraphics(); }
else if(tLine.equals ("!help" )) { cmdHelp(); }
else if(tLine.equals ("!help sybase" )) { cmdHelpSybase(); }
else if(tLine.equals ("!help oracle" )) { cmdHelpOracle(); }
// Note: "!log " must be tested last.
else if(tLine.equals ("!log console" )) { cmdLogConsole(); }
else if(tLine.equals ("!log off" )) { mIsOutputOn = false; }
else if(tLine.equals ("!log statements on" )) { mIsLogStatements = cLogStatsOn; }
else if(tLine.equals ("!log statements off" )) { mIsLogStatements = cLogStatsOff; }
else if(tLine.equals ("!log statements default")) { mIsLogStatements = cLogStatsDefault; }
else if(tLine.equals ("!log timing on" )) { mIsLogTiming = true; }
else if(tLine.equals ("!log timing off" )) { mIsLogTiming = false; }
else if(tLine.startsWith("!log append " )) { cmdLog(tLine.substring("!log append ".length()), /*Append*/ true); }
else if(tLine.startsWith("!log " )) { cmdLog(tLine.substring("!log ".length()), /*Append*/ false); }
else if(tLine.equals ("!dblog on" )) { mIsLogDbStatements = cLogStatsOn; }
else if(tLine.equals ("!dblog off" )) { mIsLogDbStatements = cLogStatsOff; }
else if(tLine.equals ("!history show" )) { cmdHistoryShow(); }
else if(tLine.equals ("!history clear" )) { cmdHistoryClear(); }
else if(tLine.startsWith("!repeat " )) { cmdRepeat(tLine.substring("!repeat ".length())); }
else if(tLine.equals ("!reopen" )) { cmdReopen(); }
else if(tLine.startsWith("!run " )) { cmdRun(tLine.substring("!run ".length())); }
else if(tLine.startsWith("!describe " )) { cmdDescribe(tLine.substring("!describe ".length())); }
else if(tLine.equals ("!set format terse" )) { cmdSetFormatTerse(); }
else if(tLine.equals ("!set format usual" )) { cmdSetFormatUsual(); }
else if(tLine.equals ("!set format verbose")) { cmdSetFormatVerbose(); }
else if(tLine.equals ("!set format csv" )) { cmdSetFormatCsv(); }
else if(tLine.equals ("!set format sqldump")) { cmdSetFormatSQLDump(); }
else if(tLine.equals ("!set format data" )) { cmdSetFormatData(); }
// Note: "!set separator off" must be tested before "!set separator ".
else if(tLine.equals ("!set separator off" )) { cmdSetSeparator(null); }
else if(tLine.startsWith("!set separator " )) { cmdSetSeparator(tLine.substring("!set separator ".length())); }
else if(tLine.equals ("!set stop on error on" )) { mIsStopOnError = true; }
else if(tLine.equals ("!set stop on error off")) { mIsStopOnError = false; }
else if(tLine.equals ("!set error count zero" )) { mErrorCount = 0; }
else if(tLine.startsWith("!set title " )) { cmdSetTitle(tLine.substring("!set title ".length())); }
else if(tLine.startsWith("!set tablemodel " )) { cmdSetTableModel(tLine.substring("!set tablemodel ".length())); }
else if(tLine.startsWith("!time " )) { cmdTime(tLine.substring("!time ".length())); }
else if(tLine.equals ("!time" )) { cmdTime(""); }
else if(tLine.startsWith("!data table " )) { mLoadTable = tLine.substring("!data table ".length()); }
else if(tLine.startsWith("!data columns " )) { mLoadColumns = tLine.substring("!data columns ".length()); }
else if(tLine.equals ("!data end" )) {
mLoadTable = null;
mLoadColumns = null;
}
else if(tLine.startsWith("!data " )) { cmdLoadData(tLine.substring("!data ".length())); }
// This is crap, aehm not a very good idea:
// else if(tLine.startsWith("!") && (mSeparator == null || !tLine.startsWith(mSeparator))) {
// output("The command " + tLine + " is unknown in Tsql.\n");
// }
// Example:
// !set separator !go
// select * from members where nick like '
// !dummy' <-- this will be taken as unknown command.
// !go
else {
if(mSeparator != null) {
// A separator is set.
if(aLine.endsWith(mSeparator)) {
// @todo bitte hier auf "!nl" pr�fen.
tLine = mAccumulatedCmd + aLine.substring(0, aLine.length() - mSeparator.length());
mAccumulatedCmd = "";
} else {
// Accumulate, but do not do anything.
// Oops, it was wrong to accumulate with " ", we need "\n"
// because the Newline may be *inside* a Datum.
// mAccumulatedCmd += aLine + " ";
// @@@ This might go wrong with "\r", but that should not be important right now.
// @todo bitte hier auf "!nl" pr�fen.
mAccumulatedCmd += aLine + "\n";
return;
}
}
query(tLine);
}
printPrompt();
}
/** Send query through all open connections in parallel.
*
* @param aQuery the query string
*/
void query (final String aQuery)
throws StopOnErrorException, IOException
{
// Case 1: Handle one query.
if(mCons.length == 1) {
if (mIsLogDbStatements != cLogStatsOff)
logHistory(aQuery);
query(aQuery, 0);
return;
}
// Case 2: Handle several queries in parallel.
Thread[] tQueryThreads = new Thread[mCons.length];
for(int i=0; i < mCons.length; i++) {
final int x = i;
tQueryThreads[i] = new Thread() {
public void run () {
try {
query(aQuery, x);
} catch(IOException e) {
e.printStackTrace();
} catch(StopOnErrorException e) {
e.printStackTrace();
}
}
};
}
for(int i=0; i < mCons.length; i++) {
tQueryThreads[i].start();
}
waitWithPrompt(tQueryThreads);
if(mIsLogStatements == cLogStatsOn
|| (mIsLogStatements == cLogStatsDefault && mOut == null))
{
print("\n");
}
}
/** Execute query (using a specific connection).
*
* @param aQuery the query string
* @param aConnection index of the connection array
*/
public void query (String aQuery, int aConnectionNr)
throws StopOnErrorException, IOException
{
String tQueryNoComments;
try {
tQueryNoComments = Parser.removeComments(aQuery);
} catch(Parser.ParseException e) {
++ mErrorCount;
error("Error while parsing: Unmatched single double quote in query:\n "
+ aQuery + "\n");
return;
}
Statement tStat = null;
try {
Date tBefore = null, tAfter = null;
if(mIsLogTiming) {
output("Performing command...\n");
tBefore = new Date();
}
tStat = mCons[aConnectionNr].createStatement();
boolean tHasResultSet = tStat.execute(tQueryNoComments);
if(mIsLogTiming) {
tAfter = new Date();
mTimes[aConnectionNr] = tAfter.getTime() - tBefore.getTime();
output("Command " + aConnectionNr + " performed in " + mTimes[aConnectionNr] + " ms.\n");
}
boolean tFirst = true;
while(true) {
if(tHasResultSet) {
// We have a ResultSet.
queryResultSet(tStat.getResultSet());
} else {
int tUpdateCount = tStat.getUpdateCount();
if(tUpdateCount > 0) {
output("OK, " + tUpdateCount + " rows updated.\n");
// Oracle's `getMoreResults� seems to be buggy.
if(mJdbcConnectionURL.indexOf("oracle") > 0) {
output("Alert: Due to a bug in Oracle, "
+ "only the output of the first statement is shown.\n");
break;
}
} else if(tUpdateCount == 0) {
// Due to a bug in JConnect, `tUpdateCount == 0� does not always happen.
// `getUpdateCount� may return -1 instead of 0.
output("OK (0).\n");
/* It is not really correct to put a break here!
Read the Documentation about how Statement.execute works.
But Oracle is buggy, so what can we do?
*/
if(mJdbcConnectionURL.indexOf("oracle") > 0) {
output("Alert: Due to a bug in Oracle, "
+ "only the output of the first statement is shown.\n");
break;
}
} else if(tUpdateCount == -1) {
// Due to a bug in JConnect, `tUpdateCount == 0� does not always happen.
// `getUpdateCount� may return -1 instead of 0.
if(tFirst) { output("OK (-1).\n"); }
// No more ResultSets. Quit the loop.
break;
} else {
System.err.println("This should never happen.");
}
}
tFirst = false;
tHasResultSet = tStat.getMoreResults();
}
} catch(SQLException e) {
error("SQL Exception:\n"
+ " Error code: " + e.getErrorCode() + "\n"
+ " SQL state: " + e.getSQLState() + "\n"
+ " Localized message: " + e.getLocalizedMessage() + "\n"
+ " Query " + aQuery + "\n"
+ "\n"
);
if(mIsStopOnError) {
throw new StopOnErrorException();
} else {
++ mErrorCount;
return;
}
} catch(Exception e) {
e.printStackTrace();
error(e.toString());
return;
} finally {
// We must close the statement, or we might run out of cursors on Oracle.
try {
if(tStat != null) tStat.close();
} catch(SQLException e) {
error("SQL Exception while closing statement:\n"
+ " Error code: " + e.getErrorCode() + "\n"
+ " SQL state: " + e.getSQLState() + "\n"
+ " Localized message: " + e.getLocalizedMessage() + "\n"
+ " Query " + aQuery + "\n"
+ "\n"
);
}
}
}
/**
* Make a persistent history log entry into table TsqlHistory.
* Can be switched off with "!dblog off" and switched on again with "!dblog on".
*
* The query is logged together with a timestamp and the hostname of the computer
* where TSQL runs on.
*
* @param aQueryToLog The query to log.
*/
private void logHistory(String aQueryToLog)
throws StopOnErrorException, IOException
{
Statement tStat = null;
long tTimestamp = System.currentTimeMillis();
String tHost = getLocalHostName();
// truncate query, if it it too long for logging
if (aQueryToLog.length() > 4000)
aQueryToLog = aQueryToLog.substring(0, 4000-3) + "...";
String tInsert = "INSERT INTO TsqlHistory (timestamp, host, query) VALUES ("
+ tTimestamp + ", "
+ "'" + tHost + "', "
+ "'" + SQLUtils.doubleApostrophes(aQueryToLog) + "'"
+ ")";
try {
tStat = mCons[0].createStatement();
boolean tHasResultSet = tStat.execute(tInsert);
} catch(SQLException e) {
error("SQL Exception:\n"
+ " Error code: " + e.getErrorCode() + "\n"
+ " SQL state: " + e.getSQLState() + "\n"
+ " Localized message: " + e.getLocalizedMessage() + "\n"
+ " Query " + tInsert + "\n"
+ "\n"
);
if(mIsStopOnError) {
throw new StopOnErrorException();
} else {
++ mErrorCount;
return;
}
} catch(Exception e) {
e.printStackTrace();
error(e.toString());
return;
} finally {
// We must close the statement, or we might run out of cursors on Oracle.
try {
if(tStat != null) tStat.close();
} catch(SQLException e) {
error("SQL Exception while closing statement:\n"
+ " Error code: " + e.getErrorCode() + "\n"
+ " SQL state: " + e.getSQLState() + "\n"
+ " Localized message: " + e.getLocalizedMessage() + "\n"
+ " Query " + tInsert + "\n"
+ "\n"
);
}
}
}
/**
* Find out the local host name.
*
* @return local host name (e.g. MHPA6V7C), or IP address, if name can't be determined
*/
private String getLocalHostName()
{
String tHostName = null;
InetAddress tLocalHost = null;
try {
tLocalHost = InetAddress.getLocalHost();
tHostName = tLocalHost.getHostName();
}
catch (UnknownHostException e) {
// if name can't be determined, the IP address is still good enough
tHostName = tLocalHost.getHostAddress();
}
return tHostName;
}
/**
* Closes all connections.
*/
public void closeConnections()
throws SQLException
{
for (int i=0; i < mCons.length; i++) {
Connection c = mCons[i];
if (c != null && !c.isClosed())
c.close();
}
}
/**
* Example of FORMAT.CSV output:
* ID,NAME,PHONE
* 43,'John','53539'
* 88,'Klaus','555-6363'
*/
/* synchronized */ void printResultSet(TsqlTableModel aModel, ResultSet aRS)
throws SQLException, IOException
{
boolean tUseTableModelOld = mUseTableModel;
if(aModel == null) {
mUseTableModel = false;
}
TsqlProgressDialog tProgress = null;
if(isGraphics()) {
tProgress = TsqlProgressDialog.getInstance();
}
// When not using table model
int mColumnType[] = null;
String mColumnName[] = null;
// END
int tColumnCount = 0;
String tMetaData = "";
ResultSetMetaData tMD = null;
if(mUseTableModel) {
tColumnCount = aModel.getColumnCount();
} else {
// When not using table model
tMD = aRS.getMetaData();
tColumnCount = tMD.getColumnCount();
}
if(mHeaders) {
if(mFormat != FORMAT.CSV && mFormat != FORMAT.SQLDUMP && mFormat != FORMAT.DATA) {
output("[Headers] ");
}
if(mUseTableModel) {
tColumnCount = aModel.getColumnCount();
} else {
// When not using table model
mColumnType = new int[tColumnCount];
mColumnName = new String[tColumnCount];
for(int i = 1; i <= tColumnCount; i++) {
mColumnName[i-1] = tMD.getColumnName(i);
mColumnType[i-1] = tMD.getColumnType(i);
}
// END
}
for(int i = 0; i < tColumnCount; i++) {
String tColName;
if(mUseTableModel) {
tColName = aModel.getColumnName(i);
} else {
// When not using table model
tColName = mColumnName[i];
// END
}
if(mFormat == FORMAT.CSV) {
output(tColName);
if(i < tColumnCount-1) {
output(",");
}
} else if(mFormat == FORMAT.SQLDUMP || mFormat == FORMAT.DATA) {
tMetaData += tColName;
if(i < tColumnCount-1) {
tMetaData += ",";
}
} else {
output(MessageFormat.format("{0}, ",
new Object[] { tColName }
));
}
}
}
if(mFormat == FORMAT.DATA) {
output("!data columns " + tMetaData);
}
StringBuffer tSB = new StringBuffer();
int j = 0;
while(mUseTableModel ? (j < aModel.getRowCount()) : aRS.next() ) {
tSB.setLength(0);
if(mFormat != FORMAT.SQLDUMP && mFormat != FORMAT.DATA) {
tSB.append(MessageFormat.format(mRowBreak, new Object[] { "" + (j+1) } ));
} else {
tSB.append("\n");
tSB.append(MessageFormat.format(mPrefix, new Object[] { tMetaData } ));
}
for(int i = 0; i < tColumnCount; i++) {
String tValue;
if(mUseTableModel) {
tValue = (String) aModel.getValueAt(j, i);
} else {
tValue = aRS.getString(i + 1);
}
if(mFormat != FORMAT.CSV && mFormat != FORMAT.SQLDUMP && mFormat != FORMAT.DATA) {
if(null == tValue) {
tValue = "NULL";
}
String tColumnName;
if(mUseTableModel) {
tColumnName = aModel.getColumnName(i);
} else {
tColumnName = mColumnName[i];
}
tSB.append(MessageFormat.format(mCell,
new Object[] { "" + i, tColumnName, tValue } ));
} else {
if(null == tValue) {
tSB.append("NULL");
} else {
boolean isStringType;
if(mUseTableModel) {
isStringType = aModel.isStringType(i);
} else {
isStringType = (mColumnType[i] == Types.CHAR ||
mColumnType[i] == Types.VARCHAR ||
mColumnType[i] == Types.LONGVARCHAR);
}
if(isStringType) {
tSB.append(singleQuoteString(tValue));
} else {
tSB.append(tValue);
}
}
if(i < tColumnCount-1) {
tSB.append(",");
}
}
}
if(mFormat == FORMAT.SQLDUMP || mFormat == FORMAT.DATA) {
tSB.append(mPostfix);
}
output(tSB.toString());
j++;
if(tProgress != null) {
tProgress.setCounter(j);
if(tProgress.isAborted()) {
break;
}
try { // give swing some time to repaint; 10ms should do
Thread.sleep(10);
} catch(InterruptedException e) {
// nothing
}
}
}
if(mAbortedQuery || (isGraphics() && TsqlProgressDialog.getInstance().isAborted())) {
output(mAbortMessage);
}
if(mFormat == FORMAT.DATA) {
output("\n!data end");
}
if(j == 0) {
output(mNoResults);
}
output(mFinish);
mUseTableModel = tUseTableModelOld;
}
/* all this to have these two abort windows */
TsqlTableModel mModel;
ResultSet mRS;
String mAbortMessage = "\n!! USER ABORTED";
boolean mAbortedQuery = false;
/**
* I made this method non-synchronized, because one of the SwingWorkers
* calls printResultSet, which was also synchronized, therefore a deadlock occurs.
*/
/* synchronized */ void queryResultSet(ResultSet aRS)
throws SQLException, IOException
{
if(isGraphics()) {
try {
// here we should disable the output textarea
mInQuery = true;
// start the counter dialog
TsqlProgressDialog tDialog = TsqlProgressDialog.getInstance();
tDialog.setTitle("Processing Resultset");
/*
Rectangle tRect = mTsqlFrame.getBounds();
Rectangle tR2 = tDialog.getBounds();
tDialog.setLocation(tRect.x + (tRect.width - tR2.width) / 2,
tRect.y + (tRect.height - tR2.height) / 2);
*/
SwingWorker tWorker;
mRS = aRS;
if(mUseTableModel) { // build table model
tWorker = new SwingWorker() {
public Object construct() {
try {
mModel = new TsqlTableModel(mRS);
} catch(SQLException e) {
System.err.println("new TableModel: " + e);
}
TsqlProgressDialog.getInstance().hide();
return null;
}
};
tWorker.start();
tDialog.show();
mAbortedQuery = tDialog.isAborted();
} else {
mModel = null;
}
tWorker = new SwingWorker() {
public Object construct() {
try {
printResultSet(mModel, mRS);
} catch(Exception e) {
System.err.println("printResultSet: " + e);
}
TsqlProgressDialog.getInstance().hide();
return null;
}
};
tDialog.setTitle("Printing Resultset");
tWorker.start();
tDialog.show();
} finally {
// enable output textarea
mInQuery = false;
_outputGui(mInQuerySB.toString());
mInQuerySB.setLength(0);
}
} else {
printResultSet(null, aRS); // use ResultSet directly
}
if(isGraphics()) {
if(mModel == null) {
mTsqlFrame.setModel(new DefaultTableModel());
} else {
mTsqlFrame.setModel(mModel);
}
mTsqlFrame.getInputArea().requestFocus();
}
}
private String getClobValue (
ResultSet tResult,
int tColumn)
throws SQLException
{
String tValue = null;
Reader tReader = new InputStreamReader(tResult.getAsciiStream(tColumn));
int j = 0;
StringBuffer tSb = new StringBuffer();
try{
while ((j = tReader.read()) != -1){
tSb.append((char)j);
}
}
catch (IOException ex){
System.err.println("IOException : " + ex.getMessage());
System.exit(1);
}
finally{
if (tReader != null)
try{
tReader.close();
}
catch (IOException exx){
//ignore
}
}
if (tSb.length() > 0)
tValue = tSb.toString();
return tValue;
}
/** Reopen one connection.
*
* Called by `cmdReopen�.
*
* Note: Later on, allow changing DB.
*
* @param aConnectionNr Index in the connection array.
*/
void reopen (int aConnectionNr)
throws IOException
{
try {
if(mCons[aConnectionNr] != null) mCons[aConnectionNr].close();
Date tBefore = null, tAfter = null;
if(mIsLogTiming) {
output("Opening connection...\n");
tBefore = new Date();
}
mCons[aConnectionNr] = DriverManager.getConnection(mJdbcConnectionURL, mJdbcUser, mJdbcPassword);
if(mIsLogTiming) {
tAfter = new Date();
mTimes[aConnectionNr] = tAfter.getTime() - tBefore.getTime();
output("Connection " + aConnectionNr + " opened in " + mTimes[aConnectionNr] + " ms.\n");
}
} catch(SQLException ex) {
System.err.println("SQLException : " + ex.getMessage());
}
}
/** Wait with prompt until all threads finished. */
private void waitWithPrompt (Thread[] aThreads)
{
while (true) {
boolean alive = false;
for (int i=0; i < mCons.length; i++) {
if (aThreads[i].isAlive()) {
alive = true;
break;
}
}
if (!alive) {
break;
}
try { Thread.sleep(500); } catch (Exception e) {}
}
// dump statistics
long max = 0;
long min = Long.MAX_VALUE;
long sum = 0;
for (int i=0; i < mCons.length; i++) {
if (mTimes[i] > max) max = mTimes[i];
if (mTimes[i] < min) min = mTimes[i];
sum += mTimes[i];
}
long average = sum / mCons.length;
try {
if(mIsLogTiming) {
print("Number of threads: " + mCons.length + "\n"
+ "Min: " + min + "ms\n"
+ "Max: " + max + "ms\n"
+ "Average: " + average + "ms\n");
}
} catch (IOException e) {}
}
// ************************************************************
// Command methods
//
// Note that currently, all `cmd� methods take a String containing the rest of the cmd line.
// This will be refactored later.
/** Dump whole table into standard SQL INSERT statements.
*
* Produces an output that can be used to import the whole table with standard SQL.
* Example:
*
* tsql>!dump test
* INSERT INTO test(name,num) VALUES ('hi',5)
* INSERT INTO test(name,num) VALUES (NULL,6)
*
* Idea for future additions: Optional SELECT statement so you can dump
* parts of a table, e.g. `!dump TABLENAME SELECT name FROM test�.
*
* Idea for a totally different approach: Use `!set format csv� and a SELECT query,
* and provide a command `!load TABLENAME� that can load the dumped format into any table.
*/
private void cmdDump(String aTableName)
throws IOException, StopOnErrorException
{
int tOldFormat = mFormat;
cmdSetFormat(FORMAT.SQLDUMP);
mPrefix = "INSERT INTO " + aTableName + "({0}) VALUES (";
mPostfix = ")";
query("SELECT * FROM " + aTableName);
mPrefix = "";
mPostfix = "";
cmdSetFormat(tOldFormat);
}
private void cmdEcho (String aArgLine)
throws IOException
{
output(aArgLine + "\n");
}
/** Execute a stored procedure. */
private void cmdExecute (String aArgLine)
throws IOException, StopOnErrorException
{
if(mCons.length != 1) {
error("Do not use !execute with several connections.");
return;
}
// Todo: Think about proper parsing routing.
// Right now, just suppose the rest is the procedure call, e.g. `name(arg1, arg2)�.
String tProcName = aArgLine;
String tCallStr = "{call "+ tProcName +"}";
CallableStatement tCs = null;
try {
tCs = mCons[0].prepareCall(tCallStr);
tCs.execute();
} catch(SQLException e) {
// This code copied from `query�.
error("SQL Exception:\n"
+ " Error code: " + e.getErrorCode() + "\n"
+ " SQL state: " + e.getSQLState() + "\n"
+ " Localized message: " + e.getLocalizedMessage() + "\n"
+ " Stored procedure: " + tProcName + "\n"
+ "\n"
);
if(mIsStopOnError) {
throw new StopOnErrorException();
} else {
++ mErrorCount;
return;
}
} catch(Exception e) {
// This code copied from `query�.
e.printStackTrace();
error(e.toString());
return;
} finally {
// This code copied from `query�.
// We must close the statement, or we might run out of cursors on Oracle.
try {
if(tCs != null) tCs.close();
} catch(SQLException e) {
error("SQL Exception while closing statement:\n"
+ " Error code: " + e.getErrorCode() + "\n"
+ " SQL state: " + e.getSQLState() + "\n"
+ " Localized message: " + e.getLocalizedMessage() + "\n"
+ " Stored procedure: " + tProcName + "\n"
+ "\n"
);
}
}
}
private void cmdGraphics()
{
mGraphics = true;
mTsqlFrame = new TsqlFrame(mFrameTitle, this);
mTsqlFrame.setVisible(true);
mTsqlFrame.getInputArea().requestFocus();
}
private void cmdHelp ()
throws IOException
{
print(cHelpText);
}
private void cmdHelpSybase ()
throws IOException
{
print(cHelpTextSybase);
}
private void cmdHelpOracle ()
throws IOException
{
print(cHelpTextOracle);
}
private void cmdHistoryClear ()
throws IOException
{
initializeHistory();
}
private void cmdHistoryShow ()
throws IOException
{
output("Start history:\n");
// Leave last 2 elements (they are "" and "!show history").
for(int i = 0; i < mHistory.size() - 2; ++i) {
output((String) mHistory.elementAt(i) + "\n");
}
output("End history.\n\n");
}
private void cmdLog (String aArgLine, boolean aAppend)
throws IOException
{
String tFileName = null;
try {
tFileName = getQuotedArg(aArgLine);
} catch(SyntaxError e) {
error("Usage: !log 'FILENAME'|console|off\n"
+ "or !log statements default|on|off\n"
+ "or !log timing on|off\n");
return;
}
print("Logging to file '" + tFileName + "'.\n");
try {
mOut = new BufferedWriter(new FileWriter(tFileName, aAppend));
} catch(IOException e) {
error("Opening file caused exception: " + e.toString());
}
}
private void cmdLogConsole ()
throws IOException
{
mIsOutputOn = true;
if (mOut != null) {
mOut.close();
}
mOut = null;
print("Logging to console.\n");
}
private void cmdQuit () {
if(mTsqlFrame != null) { mTsqlFrame.dispose(); }
System.exit(0);
}
/** Reopens all connections in parallel.
*/
void cmdReopen ()
throws IOException
{
Thread[] tReopenThreads = new Thread[mCons.length];
for (int i=0; i < mCons.length; i++) {
final int x = i;
tReopenThreads[i] = new Thread() {
public void run () {
try {
reopen(x);
} catch (IOException e) {
e.printStackTrace();
}
}
};
}
for (int i=0; i < mCons.length; i++) {
tReopenThreads[i].start();
}
waitWithPrompt(tReopenThreads);
}
private void cmdRepeat (String aArgLine)
throws IOException
{
int tNumber = 1;
try {
tNumber = Integer.parseInt(aArgLine);
} catch(NumberFormatException e) {
error("Not a number: '" + aArgLine + "', defaultet to 1.\n");
}
mCons = new Connection[tNumber];
mTimes = new long[tNumber];
print("Repeat set to " + tNumber + ".\n");
cmdReopen();
}
/** Handle command `!run�.
*
* Note: To allow using `!run� inside scripts started by `!run�,
* `cmdRun� will call `handleLoop� recursively and push `mIn� onto a stack.
* @@@ Using both recursion and a stack is messy, it should get cleaned up.
* Note that `cmdRun� may also be called by `TsqlInputArea�, which complicates
* things so that the `mIsGuiCommand� hack is needed.
*
* For the hack, see `cmdRun�, `handleLoop�, `handle� and `handleGui�.
*/
private void cmdRun (String aArgLine)
throws StopOnErrorException, IOException
{
String tFileName = null;
try {
tFileName = getQuotedArg(aArgLine);
} catch(SyntaxError e) {
error("Usage: !run 'FILENAME'.\n");
return;
}
print("Running file '" + tFileName + "'.\n");
mInStack.push(mIn);
try {
mIn = new BufferedReader(new FileReader(tFileName));
} catch(IOException e) {
error("Opening file caused exception: " + e.toString() + "\n");
mIn = (BufferedReader) mInStack.pop();
return;
}
mIsRun = true;
if(mIsCommand) {
// Hack to allow "-c !run ...".
// Call `handleLoop� recursively.
handleLoop(/*QuitOnEOF*/ true, /*ReturnOnEOF*/ false);
} else if(mIsGuiCommand) {
// Hack to get `cmdRun� to work from GUI.
// Here we need to switch `mIsGuiCommand� off, because a `!run� from inside a `!run�
// does not count as a `!run� from the GUI.
mIsGuiCommand = false;
// Call `handleLoop� recursively.
handleLoop(/*QuitOnEOF*/ false, /*ReturnOnEOF*/ true);
mIsGuiCommand = true;
} else {
// Call `handleLoop� recursively.
handleLoop(/*QuitOnEOF*/ false, /*ReturnOnEOF*/ true);
}
}
private void cmdSetSeparator (String aArgLine)
throws IOException
{
mSeparator = aArgLine;
print("Separator set to "
+ (mSeparator == null ? "Enter" : "'" + mSeparator + "'")
+ ".\n");
}
private void cmdSetFormat(int aFormat)
{
if(aFormat == mFormat) {
return;
}
switch(mFormat) {
case FORMAT.USUAL: cmdSetFormatUsual(); break;
case FORMAT.TERSE: cmdSetFormatTerse(); break;
case FORMAT.VERBOSE: cmdSetFormatVerbose(); break;
case FORMAT.CSV: cmdSetFormatCsv(); break;
case FORMAT.SQLDUMP: cmdSetFormatSQLDump(); break;
case FORMAT.DATA: cmdSetFormatData(); break;
default:
System.err.println("Unknown Format " + aFormat);
return;
}
mFormat = aFormat;
}
private void cmdSetFormatTerse ()
{
mHeaders = false;
mCell = "{2}";
mRowBreak = "\n";
mFinish = "\n";
mNoResults = "";
mFormat = FORMAT.TERSE;
}
private void cmdSetFormatUsual ()
{
mHeaders = true;
mCell = "{2}, ";
mRowBreak = "\n[{0}] ";
mFinish = "\n";
mNoResults = "\nNo results.";
mFormat = FORMAT.USUAL;
}
private void cmdSetFormatVerbose ()
{
mHeaders = false;
mCell = " #{0} {1}: {2}\n";
mRowBreak = "\n[{0}]\n";
mFinish = "";
mNoResults = "No results.";
mFormat = FORMAT.VERBOSE;
}
private void cmdSetFormatCsv ()
{
mHeaders = true;
mRowBreak = "\n";
mFinish = "\n";
mNoResults = "";
mFormat = FORMAT.CSV;
}
private void cmdSetFormatSQLDump ()
{
mHeaders = true;
mRowBreak = "\n";
mFinish = "\n";
mNoResults = "";
mFormat = FORMAT.SQLDUMP;
}
private void cmdSetFormatData ()
{
mHeaders = true;
mRowBreak = "\n";
mFinish = "\n";
mNoResults = "";
mFormat = FORMAT.DATA;
mPrefix = "!data ";
mPostfix = "";
}
private void cmdSetTitle (String aArgLine)
throws IOException
{
if(mTsqlFrame == null) return;
mTsqlFrame.setTitle(aArgLine);
}
private void cmdTime (String aArgLine)
throws IOException
{
output(aArgLine + " "
+ java.text.DateFormat.getDateTimeInstance().format(
new java.util.Date())
+ "\n");
}
// ************************************************************
// Helpers
/** Output to GUI and/or console and/or file. */
private /* synchronized */ void _output (String s, boolean aIsOutput, boolean aIsError, boolean aIsGui)
throws IOException
{
if(mOut != null) {
// File output.
// SQL output and errors go to file.
if(aIsOutput || aIsError) {
mOut.write(s);
mOut.flush();
}
// When requested, GUI output goes to file.
if(mIsLogStatements == cLogStatsOn && aIsGui) {
mOut.write(s);
mOut.flush();
}
// GUI output and errors go to console.
if(aIsGui ) { System.out.print(s); }
if(aIsError) { System.err.print(s); }
} else if (mIsOutputOn || aIsGui) {
// Console + GUI output.
System.out.print(s);
// Moved this here because TextArea only displays ca 32.000 chars.
if(mTsqlFrame != null && mTsqlFrame.getOutputArea() != null) {
// GUI output
_outputGui(s);
} else {
// Console output.
// Moved this up because TextArea only displays ca 32.000 chars.
// System.out.print(s);
}
}
}
/** Output SQL data. */
/* synchronized */ void output (String s) throws IOException { _output(s, /*isOutput*/ mIsOutputOn, false, false); }
/** Output GUI stuff. */
void print (String s) throws IOException { _output(s, false, false, /*isGui*/ true); }
/** Output error. */
void error (String s) throws IOException { _output(s, false, /*isError*/ true, false); }
private String singleQuoteString(String aString)
{
// replace all ' with '' and return 'aString'
return "'" + Converter.replaceString("'", "''", aString, false) + "'";
}
/** reverse function of singleQuoteString */
private String singleUnquoteString(String aString)
{
// replace all '' with ' and return aString
return Converter.replaceString("''", "'",
aString.substring(1, aString.length() - 2), false);
}
private String getQuotedArg (String s)
throws SyntaxError
{
int p1 = s.indexOf("'");
int p2 = s.lastIndexOf("'");
if(p1 == -1 || p2 == -1 || p1 == p2) {
throw new SyntaxError();
}
return s.substring(p1+1, p2);
}
void _outputGui(String s)
{
if(mInQuery) {
mInQuerySB.append(s);
} else {
JTextArea tOutputArea = mTsqlFrame.getOutputArea();
tOutputArea.append(s);
tOutputArea.setCaretPosition(0);
tOutputArea.setCaretPosition(
tOutputArea.getText().length());
mTsqlFrame.setVisible(true);
// mTsqlFrame.toFront(); // I guess this is at fault for Tsql always poping to the front
}
}
public void cmdDescribe(String aName) throws IOException
{
try {
Statement tStat = mCons[0].createStatement();
boolean tHasResultSet = tStat.execute("SELECT * FROM " + aName);
ResultSetMetaData tMD = tStat.getResultSet().getMetaData();
for(int i = 0; i < tMD.getColumnCount(); i++) {
this.print(
tMD.getColumnLabel(i+1) + "\t" +
tMD.getColumnTypeName(i+1) +
"(" + tMD.getPrecision(i+1) + ")" + "\n"
);
}
tStat.close();
} catch(SQLException e) {
print(e.toString());
}
}
public void cmdLoadData(String aData)
throws IOException, StopOnErrorException
{
query("INSERT INTO " + mLoadTable + " (" + mLoadColumns + ") VALUES (" + aData + ")\n");
}
/*
CREATE DUMP:
!set format dump
select * from members where nick like 'tab.%'
LOAD DUMP:
!table dump to define table for imports
!head name,warte,summe,nix,nix2
!data 'asdf','asdf','asdf',null,null
!data 'asdf','asdf','asfd',null,null
insert into dump (name,warte,summe,nix,nix2) values(...)
*/
public void dump(String aTableName)
throws IOException, StopOnErrorException, SQLException
{
cmdSetFormatCsv();
query("SELECT * FROM " + aTableName);
}
public void load(String aTableName)
throws IOException, StopOnErrorException, SQLException
{
query("INSERT INTO " + aTableName + " VALUES (" + "..." + ")");
}
public void cmdSetTableModel(String aOnOff)
{
mUseTableModel = aOnOff.startsWith("on");
}
public static boolean isGraphics()
{
return mGraphics;
}
}