/*
 *  $Rev: 1405 $:     Revision of last commit
 *  $Author: tgutwin $:  Author of last commit
 *  $Date: 2020-11-08 11:07:46 -0800 (Sun, 08 Nov 2020) $:    Date of last commit
 *  Copyright (C) 2001-2012 WebARTS Design,
 *  North Vancouver Canada. All Rights Reserved.
 *
 *  Written by Tom Gutwin - WebARTS Design.
 *  http://www..webarts.bc.ca
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 */
package ca.bc.webarts.tools;


import ca.bc.webarts.widgets.Util;

import java.awt.*;
import java.awt.geom.Ellipse2D;
import java.awt.geom.Rectangle2D;
import java.io.File;
import java.io.PrintWriter;
import java.io.Writer;
import java.io.OutputStreamWriter;
import java.io.FileOutputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ArrayList;
import java.util.Calendar;
//import java.util.HashMap;
import java.util.Iterator;
import java.util.Locale;
import java.util.Properties;
import java.util.StringTokenizer ;
import java.util.Vector;
import java.text.NumberFormat;
import javax.servlet.http.HttpSession;
import javax.swing.event.TableModelEvent;
import javax.swing.table.AbstractTableModel;

import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.CSVWriter;
import jxl.*;
import jxl.write.*;


import org.logicalcobwebs.proxool.*;
import org.logicalcobwebs.proxool.configuration.*;

import org.jfree.data.*;
import org.jfree.chart.*;
import org.jfree.chart.annotations.CategoryTextAnnotation;
import org.jfree.chart.axis.*;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.renderer.category.CategoryItemRenderer;
import org.jfree.chart.renderer.category.LineAndShapeRenderer;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.renderer.category.StackedBarRenderer;
import org.jfree.chart.renderer.xy.StandardXYItemRenderer;
import org.jfree.chart.renderer.xy.StackedXYAreaRenderer;
import org.jfree.chart.plot.*;
import org.jfree.chart.entity.*;
import org.jfree.chart.labels.*;
import org.jfree.chart.urls.*;
import org.jfree.chart.servlet.*;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;

import org.w3c.dom.*;
import org.apache.batik.dom.GenericDOMImplementation;
import org.apache.batik.svggen.SVGGraphics2D;


/**
 *  A helper Class to encapsulate SQL queries.
 *
 * @author    TGutwin
 */
public class SqlQuery
{
  /**  A holder for this clients System File Separator.  */
  public final static String SYSTEM_FILE_SEPERATOR = java.io.File.separator;

  /**  A holder for this clients System line termination separator.  */
  public final static String SYSTEM_LINE_SEPERATOR =
                                           System.getProperty("line.separator");
  public final static String DEFAULT_COLUMN_DELIMITOR = "|";

  protected static boolean debugOut = false;

  protected static ca.bc.webarts.tools.Log log_= Log.getInstance();

  public final static short ORACLE_DB = 0;
  public final static short POSTGRE_DB = 1;
  public final static short MYSQL_DB = 2;
  public final static short DB2_DB = 3;
  public final static short DERBY_DB = 4;
  public final static short HSQL_DB = 5;
  public final static short ODBC_DB = 6;
  public final static short ORACLEDEDICATED_DB = 7;
  public final static short MARIADB_DB = 8;
  public final static short OTHER_DB = 12;
  public final static short DEFAULT_DB = MARIADB_DB;

  protected final static String oraDbDriverName = "oracle.jdbc.OracleDriver";
  protected final static String mysqlDbDriverName = "com.mysql.jdbc.Driver"; //"org.gjt.mm.mysql.Driver";
  protected final static String mariadbDbDriverName = "org.mariadb.jdbc.Driver";
  protected final static String pgDbDriverName = "org.postgresql.Driver";
  protected final static String hsqlDbDriverName = "org.hsqldb.jdbcDriver";
  protected final static String db2DbDriverName = "COM.ibm.db2.jdbc.app.DB2Driver";
  protected final static String odbcDbDriverName = "sun.jdbc.odbc.JdbcOdbcDriver";
  protected final static String proxoolDriverName = "org.logicalcobwebs.proxool.ProxoolDriver";
  protected final static String derbyDbDriverName = "org.apache.derby.jdbc.ClientDriver";

  protected final static String oraDbPort = "1521";
  protected final static String pgDbPort = "5432";
  protected final static String mysqlDbPort = "3306";
  protected final static String mariadbDbPort = "3306";
  protected final static String hsqlDbPort = "1476";
  protected final static String db2DbPort = "50000";
  protected final static String derbyDbPort = "1527";

  /** Field signifying what DB type this query will access. **/
  protected short dbType_ = DEFAULT_DB;

  protected boolean useProxoolPool_ = false;
  protected int proxoolMaximumConnectionCount_ = 10;
  protected boolean oraDedicated_ = false;

  protected static String columnDelimitor = DEFAULT_COLUMN_DELIMITOR;
  protected String sqlSelect = "";
  protected String sqlFrom = "";
  protected String sqlFrom2 = "";
  protected String sqlFrom3 = "";
  protected String sqlFrom4 = "";
  protected String sqlFrom5 = "";
  protected String sqlWhere = "";
  protected String sqlOrder = "";

  protected String dbUser = "";
  protected String dbPassword = "";
  protected String dbDriverName = "oracle.jdbc.OracleDriver";
  protected String dbIP = ""; //"edmfdpp1";
  protected String dbPort = "";
  protected String dbName = ""; //"FDPPRD";

  protected String dbConnectString = "";
  protected String testDbConnectString = "jdbc:oracle:thin:@//172.28.15.218:1521/MRSD";

  protected Connection con = null;

  protected long randLong = Math.round(Math.random()*10000000);
  protected String randStr = String.valueOf(randLong);
  protected static Calendar calendar_ = Calendar.getInstance();
  protected static int yr_ = calendar_.get(Calendar.YEAR);
  protected static int mo_ = calendar_.get(Calendar.MONTH)+1;
  protected static int dy_ = calendar_.get(Calendar.DATE);


  /**  Constructor for the SqlQuery object */
  public SqlQuery()
  {
    /*
    System.out.println("[ca.bc.webarts.SqlQuery > SqlQuery()]" +
      (new java.util.Date()).toString());
    */
  }// -- Constructor


  /**
    * Set Method for class field 'columnDelimitor'.
    *
    * @param columnDelimitor is the value to set this class field to.
    *
    **/
  public  void setColumnDelimitor(String columnDelimitor)
  {
    this.columnDelimitor = columnDelimitor;
  }  // setColumnDelimitor Method


  /**
    * Get Method for class field 'columnDelimitor'.
    *
    * @return String - The value the class field 'columnDelimitor'.
    *
    **/
  public static String getColumnDelimitor()
  {
    return columnDelimitor;
  }  // getColumnDelimitor Method


  /**
    * Set Method for class field 'sqlOrder'.
    *
    * @param sqlOrder is the value to set this class field to.
    *
    **/
  public  void setSqlOrder(String sqlOrder)
  {
    this.sqlOrder = sqlOrder;
  }  // setSqlOrder Method


  /**
    * Get Method for class field 'sqlOrder'.
    *
    * @return String - The value the class field 'sqlOrder'.
    *
    **/
  public String getSqlOrder()
  {
    return sqlOrder;
  }  // getSqlOrder Method


  /**
    * Set Method for class field 'sqlWhere'.
    *
    * @param sqlWhere is the value to set this class field to.
    *
    **/
  public  void setSqlWhere(String sqlWhere)
  {
    this.sqlWhere = sqlWhere;
  }  // setSqlWhere Method


  /**
    * Get Method for class field 'sqlWhere'.
    *
    * @return String - The value the class field 'sqlWhere'.
    *
    **/
  public String getSqlWhere()
  {
    return sqlWhere;
  }  // getSqlWhere Method


  /**
    * Set Method for class field 'sqlFrom'.
    *
    * @param sqlFrom is the value to set this class field to.
    *
    **/
  public  void setSqlFrom(String sqlFrom)
  {
    this.sqlFrom = sqlFrom;
  }  // setSqlFrom Method


  /**
    * Get Method for class field 'sqlFrom'.
    *
    * @return String - The value the class field 'sqlFrom'.
    *
    **/
  public String getSqlFrom()
  {
    return sqlFrom;
  }  // getSqlFrom Method


  /**
    * Set Method for class field 'sqlFrom2'.
    *
    * @param sqlFrom2 is the value to set this class field to.
    *
    **/
  public  void setSqlFrom2(String sqlFrom2)
  {
    this.sqlFrom2 = sqlFrom2;
  }  // setSqlFrom Method


  /**
    * Get Method for class field 'sqlFrom2'.
    *
    * @return String - The value the class field 'sqlFrom2'.
    *
    **/
  public String getSqlFrom2()
  {
    return sqlFrom2;
  }  // getSqlFrom Method


  /**
    * Set Method for class field 'sqlFrom3'.
    *
    * @param sqlFrom3 is the value to set this class field to.
    *
    **/
  public  void setSqlFrom3(String sqlFrom3)
  {
    this.sqlFrom3 = sqlFrom3;
  }  // setSqlFrom Method


  /**
    * Get Method for class field 'sqlFrom3'. It is a 3rd tablename to select from.
    *
    * @return String - The value the class field 'sqlFrom3'.
    *
    **/
  public String getSqlFrom3()
  {
    return sqlFrom3;
  }  // getSqlFrom Method


  /**
    * Set Method for class field 'sqlFrom4'.
    *
    * @param sqlFrom4 is the value to set this class field to.
    *
    **/
  public  void setSqlFrom4(String sqlFrom4)
  {
    this.sqlFrom4 = sqlFrom4;
  }  // setSqlFrom Method


  /**
    * Get Method for class field 'sqlFrom4'. It is a 4thablename to select from.
    *
    * @return String - The value the class field 'sqlFrom4'.
    *
    **/
  public String getSqlFrom4()
  {
    return sqlFrom4;
  }  // getSqlFrom Method


  /**
    * Set Method for class field 'sqlFrom3'.
    *
    * @param sqlFrom5 is the value to set this class field to.
    *
    **/
  public  void setSqlFrom5(String sqlFrom5)
  {
    this.sqlFrom5 = sqlFrom5;
  }  // setSqlFrom Method


  /**
    * Get Method for class field 'sqlFrom3'. It is a 3rd tablename to select from.
    *
    * @return String - The value the class field 'sqlFrom3'.
    *
    **/
  public String getSqlFrom5()
  {
    return sqlFrom5;
  }  // getSqlFrom Method


  /**
    * Set Method for class field 'sqlSelect'.
    *
    * @param sqlSelect is the value to set this class field to.
    *
    **/
  public  void setSqlSelect(String sqlSelect)
  {
    this.sqlSelect = sqlSelect;
  }  // setSqlSelect Method


  /**
    * Get Method for class field 'sqlSelect'.
    *
    * @return String - The value the class field 'sqlSelect'.
    *
    **/
  public String getSqlSelect()
  {
    return sqlSelect;
  }  // getSqlSelect Method


  /**
    * Set Method for class field 'dbConnectString'.
    *
    * @param dbConnectString is the value to set this class field to.
    *
    **/
  public  void setDbConnectString(String dbConnectString)
  {
    this.dbConnectString = dbConnectString;
  }  // setDbConnectString Method


  /**
    * Get Method for class field 'dbConnectString'.
    *
    * @return String - The value the class field 'dbConnectString'.
    *
    **/
  public String getDbConnectString()
  {
    String retVal = dbConnectString;

    switch (dbType_)
    {
    case ORACLEDEDICATED_DB:
    case ORACLE_DB:
      if(oraDedicated_)
        retVal = "jdbc:oracle:thin:@(DESCRIPTION="   +
                       "(ADDRESS_LIST="              +
                           "(ADDRESS=(PROTOCOL=TCP)" +
                                    "(HOST="+
                                      getDbIP()+")"  +
                                    "(PORT="+
                                      (!dbPort.equals("")?dbPort:oraDbPort)+")"    +
                           ")"                       +
                       ")"                           +
                       "(CONNECT_DATA="              +
                           "(SERVICE_NAME="          +
                             getDbName()+")"         +
                           "(SERVER=DEDICATED)"      +
                       ")"                           +
                     ")";
      else
        retVal = "jdbc:oracle:thin:@"+getDbIP()+":"+(!dbPort.equals("")?dbPort:oraDbPort)+":"+getDbName();
      break;

    case POSTGRE_DB:
       retVal = "jdbc:postgresql://"+getDbIP()+":"+(!dbPort.equals("")?dbPort:pgDbPort)+"/"+getDbName();
      break;

    case MYSQL_DB:
      //jdbc:mysql://<hostname>[<:3360>]/<dbname>
      retVal = "jdbc:mysql://"+getDbIP()+":"+(!dbPort.equals("")?dbPort:mysqlDbPort)+"/"+getDbName();
      break;

    case MARIADB_DB:
      //jdbc:mysql://<hostname>[<:3360>]/<dbname>
      retVal = "jdbc:mariadb://"+getDbIP()+":"+(!dbPort.equals("")?dbPort:mysqlDbPort)+"/"+getDbName();
      break;

    case HSQL_DB:
      //jdbc:hsqldb:hsql://<server>[:<1476>]
      retVal = "jdbc:hsqldb:hsql://"+getDbIP()+":"+(!dbPort.equals("")?dbPort:hsqlDbPort);
      break;

    case DB2_DB:
      // jdbc:db2://<host_name>:<port_number>/<database_name>
      retVal = "jdbc:db2://"+getDbIP()+":"+(!dbPort.equals("")?dbPort:db2DbPort)+"/"+getDbName();
      break;

    case ODBC_DB:
      // jdbc:odbc:<alias>
      retVal = "jdbc:odbc:"+getDbName();
      break;

    case DERBY_DB:
       // jdbc:derby://<server>[:<port>]/<databaseName>[;<URL attribute>=<value>]
       //jdbc:derby://warp4:1527/wTracker;create=true
       retVal = "jdbc:derby://"+getDbIP()+":"+(!dbPort.equals("")?dbPort:derbyDbPort)+"/"+getDbName();
      break;

    case OTHER_DB:
    default:
      // use the defaults or what the user has set with the class set methods
      retVal = dbConnectString;
    }
    return retVal;
  }  // getDbConnectString Method


  /**
    * Set Method for class field 'dbDriverName'.
    *
    * @param dbDriverName is the value to set this class field to.
    *
    **/
  public  void setDbDriverName(String dbDriverName)
  {
    this.dbDriverName = dbDriverName;
  }  // setDbDriverName Method


  /**
    * Get Method for class field 'dbDriverName'.
    *
    * @return String - The value the class field 'dbDriverName'.
    *
    **/
  public String getDbDriverName()
  {
    return dbDriverName;
  }  // getDbDriverName Method


  /**
    * Set Method for class field 'dbPassword'.
    *
    * @param dbPassword is the value to set this class field to.
    *
    **/
  public  void setDbPassword(String dbPassword)
  {
    this.dbPassword = dbPassword;
  }  // setDbPassword Method


  /**
    * Get Method for class field 'dbPassword'.
    *
    * @return String - The value the class field 'dbPassword'.
    *
    **/
  public String getDbPassword()
  {
    return dbPassword;
  }  // getDbPassword Method


  /**
    * Set Method for class field 'dbUser'.
    *
    * @param dbUser is the value to set this class field to.
    *
    **/
  public  void setDbUser(String dbUser)
  {
    this.dbUser = dbUser;
  }  // setDbUser Method


  /**
    * Get Method for class field 'dbUser'.
    *
    * @return String - The value the class field 'dbUser'.
    *
    **/
  public String getDbUser()
  {
    return dbUser;
  }  // getDbUser Method


  /**
    * Get Method for class field 'dbIP'.  This method should get overridden in any extending classes.
    *
    * @return String - The value the class field 'dbIP'.
    *
    **/
  public String getDbIP()
  {
    return this.dbIP;
  }  // getDbIP Method


  /**
    * Set Method for class field 'dbIP'.
    *
    * @param dbIP is the value to set this class field to.
    *
    **/
  public  void setDbIP(String dbIP)
  {
    this.dbIP = dbIP;
  }  // setDbUser Method


  /**
    * Set Method for class field 'dbName'.
    *
    * @param dbName  The value the class field 'dbName'.
    *
    **/
  public void setDbName(String dbName)
  {
     this.dbName = dbName;
  }  // setDbName Method


  /**
    * Get Method for class field 'dbName'.
    *
    * @return String - The value the class field 'dbUser'.
    *
    **/
  public String getDbName()
  {
    return this.dbName;
  }  // getDbUser Method


  /**
   *  Executes a SQL statement onto the JDBC accessed <b>TEST</b>database
   *  defined by the class driver and connection string.
   *
   * @param sql is the SQL to execute
   * @return a StringBuffer containing the ResultSet, one result per line.
   **/
  public StringBuffer testDbQuery(String sql)
  {
    System.out.println("Query on test DB: ");
    return query(sql, false);
  }


  /**
   *  Converts a ResultSet StringBuffer to a String [][] that holds the results cell by cell ColumnxRow.
   *  It also strips the 1st row with the column headers. For example results[4][8] would get col4 row 8 data.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a String Array containing the ResultSet, colsxrows (Without the coulmn header 1st row.
   **/
  public static String [][] sbToDataArray(StringBuffer rs)
  {
    String [] rows = (rs.substring(rs.indexOf("\n")+1)).toString().split("\n");
    String [] columns = (rows[0]).split("\\"+getColumnDelimitor());
    String [][] retVal = new String[columns.length][rows.length];
    //System.out.println("Results array - Columns: "+columns.length+"   Rows:"+rows.length);
    if (rs != null)
    {
      for (int row=0; row<rows.length; row++)
      {
        columns = rows[row].split("\\"+getColumnDelimitor());
        //System.out.println("Row "+row+" =  "+java.util.Arrays.toString(columns));
        for (int column=0; column<columns.length; column++)
        {
          retVal[column][row] = columns[column];
          //retVal[column][row] = Util.tokenReplace(columns[column],"\n", " ");
        }
      }
    }
    if (rs.indexOf("\n")+1 == 0) retVal = null;
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuilder to a String [][] that holds the results cell by cell ColumnxRow.
   *  It also strips the 1st row with the column headers. For example results[4][8] would get col4 row 8 data.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a String Array containing the ResultSet, colsxrows (Without the coulmn header 1st row.
   **/
  public static String [][] sbToDataArray(StringBuilder rs)
  {
    String [] rows = (rs.substring(rs.indexOf("\n")+1)).toString().split("\n");
    String [] columns = (rows[0]).split("\\"+getColumnDelimitor());
    String [][] retVal = new String[columns.length][rows.length];
    //System.out.println("Results array - Columns: "+columns.length+"   Rows:"+rows.length);
    if (rs != null)
    {
      for (int row=0; row<rows.length; row++)
      {
        columns = rows[row].split("\\"+getColumnDelimitor());
        //System.out.println("Row "+row+" =  "+java.util.Arrays.toString(columns));
        for (int column=0; column<columns.length; column++)
        {
          retVal[column][row] = columns[column];
          //retVal[column][row] = Util.tokenReplace(columns[column],"\n", " ");
        }
      }
    }
    if (rs.indexOf("\n")+1 == 0) retVal = null;
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a String [][] that holds the results cell by cell RowxColumn.
   *  It also strips the 1st row with the column headers. For example results[4][8] would get row4 col8 data.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a String Array containing the ResultSet, rowsxcols (Without the coulmn headers in the 1st row).
   **/
  public static String [][] sbToDataRowArray(StringBuffer rs)
  {
    String [] rows = (rs.substring(rs.indexOf("\n")+1)).toString().split("\n");
    String [] columns = (rows[0]).split("\\"+getColumnDelimitor());
    String [][] retVal = new String[rows.length][columns.length];
    //System.out.println("Results array - Columns: "+columns.length+"   Rows:"+rows.length);
    if (rs != null)
    {
      for (int row=0; row<rows.length; row++)
      {
        columns = (rows[row]).split("\\"+getColumnDelimitor());
        //System.out.print("Row "+row+" =  "+java.util.Arrays.toString(columns));
        for (int column=0; column<columns.length; column++)
        {
          retVal[row][column] = columns[column];
        }
      }
    }
    if (rs.indexOf("\n")+1 == 0) retVal = null;
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuilder to a String [][] that holds the results cell by cell RowxColumn.
   *  It also strips the 1st row with the column headers. For example results[4][8] would get row4 col8 data.
   *
   * @param rs is the SQL reusltSet (in StringBuilder Form as returned from any of the query methods) to convert
   * @return a String Array containing the ResultSet, rowsxcols (Without the coulmn headers in the 1st row).
   **/
  public static String [][] sbToDataRowArray(StringBuilder rs)
  {
    String [] rows = (rs.substring(rs.indexOf("\n")+1)).toString().split("\n");
    String [] columns = (rows[0]).split("\\"+getColumnDelimitor());
    String [][] retVal = new String[rows.length][columns.length];
    //System.out.println("Results array - Columns: "+columns.length+"   Rows:"+rows.length);
    if (rs != null)
    {
      for (int row=0; row<rows.length; row++)
      {
        columns = (rows[row]).split("\\"+getColumnDelimitor());
        //System.out.print("Row "+row+" =  "+java.util.Arrays.toString(columns));
        for (int column=0; column<columns.length; column++)
        {
          retVal[row][column] = columns[column];
        }
      }
    }
    if (rs.indexOf("\n")+1 == 0) retVal = null;
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a String [] that holds one result per line.
   *  It also strips the 1st row with the column headers
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a String Array containing the ResultSet, one result per line (Without the coulmn header 1st row.
   **/
  public static String [] sbToArray(StringBuilder rs)
  {
    String [] retVal = null;
    if (rs != null && rs.indexOf("\n")+1 >0)
      retVal = (rs.substring(rs.indexOf("\n")+1)).toString().split("\n");
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a String [] that holds one result per line.
   *  It also strips the 1st row with the column headers
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a String Array containing the ResultSet, one result per line (Without the coulmn header 1st row.
   **/
  public static String [] sbToArray(StringBuffer rs)
  {
    String [] retVal = null;
    if (rs != null && rs.indexOf("\n")+1 >0)
      retVal = (rs.substring(rs.indexOf("\n")+1)).toString().split("\n");
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a saved file that holds one result per line.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param fileName is the name of the file to save
   * @return a String containing the filename if suucessful, null if not.
   **/
  public static String  sbToFile(StringBuffer rs, String fileName)
  {
    String retVal = null;
    if (rs != null)
      retVal = Util.writeStringToFile(rs.toString(), fileName);
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a saved CSV file that holds one result per line.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param fileName is the name of the file to save
   * @return a String containing the filename if suucessful, null if not.
   **/
  public static String  sbToCsv(StringBuffer rs, String fileName)
  {
    //String noCommas = Util.tokenReplace(rs.toString(),",","(comma)");


    String noCommas = rs.toString().replace(",","(comma)");
    return sbToFile( new StringBuffer(noCommas.replace(getColumnDelimitor(),
                                                         ",")),
                     fileName);
  }




  public static WritableWorkbook  copyIntoWorkbook(StringBuffer sb, WritableWorkbook wbkIn, int shtPosition, String sheetName)
     throws  java.io.IOException
  {
    //WritableWorkbook retVal = wbkIn;
    System.out.println("\nCreating new sheet '"+sheetName+"' into existing workbook at position="+shtPosition);
    WritableSheet sheet0 = wbkIn.createSheet(sheetName, shtPosition);
    String[] data = null;
    String sbStr = "";
    try
    {
      if (sb !=null && sb.length()>0)
      {
        sbStr = sb.toString();

        // remove empty columns with no heading at the end of a row
        //sbStr=sbStr.replace(getColumnDelimitor()+"\n","\n");

        String [] colHeadings = sbStr.substring(0,sbStr.indexOf("\n")).split("\\"+getColumnDelimitor());

        data = sbToArray(sb); // One row per line
        int numRows = data.length;
        int numCols = colHeadings.length;

        jxl.write.Number number = null;
        int i=0;
        int j=0;
        Integer cellInt = null;
        Float cellFloat = null;
        Double cellDouble = null;
        jxl.write.Label label = null;
        boolean cellIsInt = false;
        boolean cellIsFloat = false;
        boolean cellIsDouble = false;

        // Add Column Headings
        //System.out.print("ColHeaders:");
        for (i=0;i<numCols;i++)
        {
          label = new jxl.write.Label(i, 0, colHeadings[i]);
          sheet0.addCell(label);
          //System.out.print(" "+i+":"+colHeadings[i]);
        }

        // Add ResultData
        String [] currRow = null;
        for (j=0;j<numRows;j++)
        {
          if (data[j]!=null)
          {
            currRow = data[j].split("\\"+getColumnDelimitor());
            numCols = currRow.length;
            for (i=0;i<numCols;i++)
            {
              if (!currRow[i].equals("null"))
              {
                try
                {
                  cellIsInt = false;
                  cellIsFloat = false;
                  cellIsDouble = false;
                    final String Digits     = "(\\p{Digit}+)";
                    final String HexDigits  = "(\\p{XDigit}+)";
                    // an exponent is 'e' or 'E' followed by an optionally
                    // signed decimal integer.
                    final String Exp        = "[eE][+-]?"+Digits;
                    final String fpRegex    = ("[\\x00-\\x20]*"+  // Optional leading "whitespace"
                                                "[+-]?(" + // Optional sign character
                                                "NaN|" +           // "NaN" string
                                                "Infinity|" +      // "Infinity" string

                    // A decimal floating-point string representing a finite positive
                    // number without a leading sign has at most five basic pieces:
                    // Digits . Digits ExponentPart FloatTypeSuffix
                    //
                    // Since this method allows integer-only strings as input
                    // in addition to strings of floating-point literals, the
                    // two sub-patterns below are simplifications of the grammar
                    // productions from the Java Language Specification, 2nd
                    // edition, section 3.10.2.

                    // Digits ._opt Digits_opt ExponentPart_opt FloatTypeSuffix_opt
                                                "((("+Digits+"(\\.)?("+Digits+"?)("+Exp+")?)|"+

                    // . Digits ExponentPart_opt FloatTypeSuffix_opt
                                                "(\\.("+Digits+")("+Exp+")?)|"+

                    // Hexadecimal strings
                                                "((" +
                   // 0[xX] HexDigits ._opt BinaryExponent FloatTypeSuffix_opt
                                                "(0[xX]" + HexDigits + "(\\.)?)|" +

                   // 0[xX] HexDigits_opt . HexDigits BinaryExponent FloatTypeSuffix_opt
                                                "(0[xX]" + HexDigits + "?(\\.)" + HexDigits + ")" +

                                                ")[pP][+-]?" + Digits + "))" +
                                                "[fFdD]?))" +
                                                "[\\x00-\\x20]*");// Optional trailing "whitespace"

                   if (java.util.regex.Pattern.matches(fpRegex, currRow[i]))
                   {
                     cellDouble = Double.valueOf(currRow[i]); // Will not throw NumberFormatException
                     cellIsDouble = true;
                   }
                   else
                   {
                     // Perform suitable alternative action
                   }

                  if (!cellIsDouble)
                  {
                    try
                    {
                       // my slow method
                       cellInt = new Integer(currRow[i].trim());
                       cellIsInt = true;
                       if (!cellIsInt)
                       {
                         cellFloat= new Float(currRow[i].trim());
                         cellIsFloat = true;
                       }
                    }
                    catch (NumberFormatException numEx)
                    {
                       // thesae should already be set correctly, but ensure they are
                      cellIsInt = false;
                      cellIsFloat = false;
                      cellIsDouble = false;
                      // deal with this as a label below
                      //System.out.println(currRow[i]+ " is NOT a number");
                    }
                  }

                  if (cellIsInt)
                  {
                    number = new jxl.write.Number(i, j+1, cellInt);
                    sheet0.addCell(number);
                  }
                  else if (cellIsFloat)
                  {
                    number = new jxl.write.Number(i, j+1, cellFloat);
                    sheet0.addCell(number);
                  }
                  else if (cellIsDouble)
                  {
                    number = new jxl.write.Number(i, j+1, cellDouble);
                    sheet0.addCell(number);
                  }
                  else
                  {
                    if (currRow[i].startsWith("="))
                    {
                      jxl.write.Formula formula =  new jxl.write.Formula(i, j+1, currRow[i]);
                      sheet0.addCell(formula);
                    }
                    else
                    {
                      label = new jxl.write.Label(i, j+1, currRow[i]);
                      sheet0.addCell(label);
                    }
                  }
                }
                catch (Exception ex)
                {
                  // something funky happened... don't add but keep processing
                }
              }
              else // cell == null
              {
                // don't add
              }
            } // columns
          }
        }  // rows

        //wbkIn.write();
      }
      else
      {
        System.out.println("Empty resultset StringBuffer?");
      }
    }
    catch (Exception ex)
    {
      System.out.println("\nXLS File ERROR: A problem in the application has caused an error. "+ sheetName);
      System.out.println("RawData: "+sbStr);
      ex.printStackTrace();
    }

    return wbkIn;
  }


  /**
   *  Adds a ResultSet StringBuffer into a jxl.Workbook Excel workbook (new or existing) with the specified sheet name.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param wbkIn is an existing jxlWorkbook to save the StringBuffer resultSet into - If this is null then it creates and returns a new Workbook
   * @param sheetName name of the new sheet in the workbook
   * @return a jxl.Workbook containing the data.
   **/
  public static WritableWorkbook  sbIntoWorkbook(StringBuffer sb, WritableWorkbook wbkIn, String sheetName) throws  java.io.IOException
  {
    //jxl.Sheet sht = sbToWorksheet(sb);
    if (sheetName == null || sheetName.trim().equals("")) sheetName = "Results";

    if (wbkIn==null)
    {
      wbkIn = Workbook.createWorkbook(new File("."+File.separator+"sqlResultSet_"+sheetName+"-"+
                                              Calendar.getInstance().getTimeInMillis()+".xls"));
      System.out.println("\nwbkIn=null - IMPORTING into NEW workbook: "+"."+File.separator+"sqlResultSet_"+sheetName+"-"+
                                              Calendar.getInstance().getTimeInMillis()+".xls");
    }
    else
    {
      System.out.println("\nIMPORTING '"+sheetName+"' into existing workbook");
    }

    String [] existingSheetNames = wbkIn.getSheetNames();
    boolean foundSheet = false;
    for (int i = 0; !foundSheet && i< existingSheetNames.length; i++)
      if (existingSheetNames[i].equals(sheetName)) foundSheet= true;
    if (foundSheet) sheetName = sheetName+"-"+Calendar.getInstance().getTimeInMillis();

    //System.out.println("     SHEET(null?"+(sht==null?"yes":"no")+"): "+ sheetName);
    //wbkIn.importSheet(sheetName, existingSheetNames.length+1, sht);
    wbkIn = copyIntoWorkbook(sb, wbkIn, existingSheetNames.length+1, sheetName);
    //wbkIn.write();
    return wbkIn;
  }


  /**
   *  Adds a ResultSet StringBuffer into a NEW jxl.Workbook Excel workbook with the specified workbookName and sheetname.
   *
   * @param sb is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param wbkName is the name of the new jxlWorkbook file to save
   * @param sheetName name of the new sheet in the workbook
   * @return a jxl.Workbook containing the data.
   **/
  public static WritableWorkbook  sbIntoWorkbook(StringBuffer sb, String wbkName, String sheetName) throws  java.io.IOException
  {
    //jxl.Sheet sht = sbToWorksheet(sb);
    if (sheetName == null || sheetName.trim().equals("")) sheetName = "Results";
    if (wbkName == null || wbkName.trim().equals(""))
      wbkName = "."+File.separator+"sqlResultSet_"+sheetName+"-"+
                Calendar.getInstance().getTimeInMillis()+".xls";

    System.out.println("\nCreating *NEW* workbook - "+wbkName);
    WritableWorkbook wbkIn = Workbook.createWorkbook(new File(wbkName));
    return sbIntoWorkbook(sb, wbkIn, sheetName);
  }


  /**
   *  Converts a ResultSet StringBuffer to a saved jxl.Workbook Excel workbook with one sheet named 'Results' that holds one result per line.
   *
   * @param sb is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a jxl.Workbook containing the data.
   **/
  public static jxl.Workbook  sbToWorkbook(StringBuffer sb)
  {
    jxl.Workbook retVal = null;
    String  tempFilename = System.getProperty("java.io.tmpdir")+"tmpWrkSht-"+
                                              Calendar.getInstance().getTimeInMillis()+ ".xls";
    //System.out.println("Writing results to TEMP file: "+tempFilename);
    try
    {
      sbToXLS(sb, tempFilename);
      //Util.writeStringToFile(sqlResultSet.toString(),rsFilename + ".csv");
      retVal  =Workbook.getWorkbook(new java.io.File(tempFilename ));
    }
    catch( Exception ioEx)
    {
      System.out.println("\nFile ERROR: A problem in the application has caused an error. "+ tempFilename);
      ioEx.printStackTrace();
    }
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a saved jxl.Sheet Excel worksheet that holds one result per line.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @return a jxl.Sheet containing the data.
   **/
  public static jxl.Sheet  sbToWorksheet(StringBuffer sb)
  {
    jxl.Sheet retVal = null;
    jxl.write.WritableWorkbook wbk = null;
    String  tempFilename = System.getProperty("java.io.tmpdir")+"tmpWrkSht-"+
                                              Calendar.getInstance().getTimeInMillis()+ ".xls";
    //System.out.println("Writing results to TEMP file: "+tempFilename);
    try
    {
      wbk=sbToWbk(sb, tempFilename);
      wbk.write();
      wbk.close();
      jxl.Workbook workbook = Workbook.getWorkbook(new java.io.File(tempFilename ));
      if (workbook!=null)
      {
        retVal = workbook.getSheet("Results");
      }
      else
        System.out.println("\nERROR saving results xls file: "+ tempFilename);
    }
    catch( Exception ioEx)
    {
      System.out.println("\nSqlQuery.sbToWorksheet(StringBuffer sb)");
      System.out.println("File ERROR: A problem in the application has caused an error. "+ tempFilename);
      System.out.println("            Could Not convert sb=\n"+ sb.toString());
      ioEx.printStackTrace();
    }
    return retVal;
  }


  /**
   *  Converts a ResultSet StringBuffer to a saved Excel file with one sheet named 'Results' that holds one result per line.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param fileName is the name of the file to save
   * @return a String containing the filename if suucessful, null if not.
   **/
  public static String  sbToXLS(StringBuffer sb, String fileName)
  {
    String retVal = null;
    String[] data = null;

    try
    {
      if (sb !=null && sb.length()>0)
      {
        data = sbToArray(sb); // One row per line
        String [] colHeadings = sb.toString().substring(0,sb.toString().indexOf("\n")).split("\\"+getColumnDelimitor());
        int numRows = data.length;
        int numCols = colHeadings.length;

        WritableWorkbook  workbook = Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet0 = workbook.createSheet("Results", 0);
        jxl.write.Number number = null;
        int i=0;
        int j=0;
        Integer cellInt = null;
        Float cellFloat = null;
        Double cellDouble = null;
        jxl.write.Label label = null;
        boolean cellIsInt = false;
        boolean cellIsFloat = false;
        boolean cellIsDouble = false;

        // Add Column Headings
        for (i=0;i<numCols;i++)
        {
          label = new jxl.write.Label(i, 0, colHeadings[i]);
          sheet0.addCell(label);
        }

        // Add ResultData
        String [] currRow = null;
        for (j=0;j<numRows;j++)
        {
          if (data[j]!=null)
          {
            currRow = data[j].split("\\"+getColumnDelimitor());
            numCols = currRow.length;
            for (i=0;i<numCols;i++)
            {
              if (!currRow[i].equals("null"))
              {
                cellIsInt = false;
                cellIsFloat = false;
                cellIsDouble = false;
                  final String Digits     = "(\\p{Digit}+)";
                  final String HexDigits  = "(\\p{XDigit}+)";
                  // an exponent is 'e' or 'E' followed by an optionally
                  // signed decimal integer.
                  final String Exp        = "[eE][+-]?"+Digits;
                  final String fpRegex    = ("[\\x00-\\x20]*"+  // Optional leading "whitespace"
                                              "[+-]?(" + // Optional sign character
                                              "NaN|" +           // "NaN" string
                                              "Infinity|" +      // "Infinity" string

                  // A decimal floating-point string representing a finite positive
                  // number without a leading sign has at most five basic pieces:
                  // Digits . Digits ExponentPart FloatTypeSuffix
                  //
                  // Since this method allows integer-only strings as input
                  // in addition to strings of floating-point literals, the
                  // two sub-patterns below are simplifications of the grammar
                  // productions from the Java Language Specification, 2nd
                  // edition, section 3.10.2.

                  // Digits ._opt Digits_opt ExponentPart_opt FloatTypeSuffix_opt
                                              "((("+Digits+"(\\.)?("+Digits+"?)("+Exp+")?)|"+

                  // . Digits ExponentPart_opt FloatTypeSuffix_opt
                                              "(\\.("+Digits+")("+Exp+")?)|"+

                  // Hexadecimal strings
                                              "((" +
                 // 0[xX] HexDigits ._opt BinaryExponent FloatTypeSuffix_opt
                                              "(0[xX]" + HexDigits + "(\\.)?)|" +

                 // 0[xX] HexDigits_opt . HexDigits BinaryExponent FloatTypeSuffix_opt
                                              "(0[xX]" + HexDigits + "?(\\.)" + HexDigits + ")" +

                                              ")[pP][+-]?" + Digits + "))" +
                                              "[fFdD]?))" +
                                              "[\\x00-\\x20]*");// Optional trailing "whitespace"

                 if (java.util.regex.Pattern.matches(fpRegex, currRow[i]))
                 {
                   cellDouble = Double.valueOf(currRow[i]); // Will not throw NumberFormatException
                   cellIsDouble = true;
                 }
                 else
                 {
                   // Perform suitable alternative action
                 }

                if (!cellIsDouble)
                {
                  try
                  {
                     // my slow method
                     cellInt = new Integer(currRow[i].trim());
                     cellIsInt = true;
                     if (!cellIsInt)
                     {
                       cellFloat= new Float(currRow[i].trim());
                       cellIsFloat = true;
                     }
                  }
                  catch (NumberFormatException numEx)
                  {
                    //System.out.println(currRow[i]+ " is NOT a number");
                  }
                }

                if (cellIsInt)
                {
                  number = new jxl.write.Number(i, j+1, cellInt);
                  sheet0.addCell(number);
                }
                else if (cellIsFloat)
                {
                  number = new jxl.write.Number(i, j+1, cellFloat);
                  sheet0.addCell(number);
                }
                else if (cellIsDouble)
                {
                  number = new jxl.write.Number(i, j+1, cellDouble);
                  sheet0.addCell(number);
                }
                else
                {
                  label = new jxl.write.Label(i, j+1, Util.tokenReplace(Util.tokenReplace(currRow[i],"\r"," _ "),"\n"," _ "));
                  sheet0.addCell(label);
                }

              }
              else // cell == null
              {
                // don't add
              }
            } // columns
          }
        }  // rows

        workbook.write();
        workbook.close();
        System.out.println("XLS file "+fileName+" Successfully written");
        retVal = fileName;
      }
      else
      {
        System.out.println("XLS file "+fileName+" NOT written - NO Data.");
        retVal = null;
      }
    }
    catch (jxl.write.WriteException wEx)
    {
      wEx.printStackTrace();
      retVal = null;
    }
    catch (java.io.IOException ioEx)
    {
      ioEx.printStackTrace();
      retVal = null;
    }

    return retVal;
  }

  /**
   *  Converts a ResultSet StringBuffer to a saved Excel file with one sheet named 'Results' that holds one result per line.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param fileName is the name of the file to save
   * @return a String containing the filename if suucessful, null if not.
   **/
  public static WritableWorkbook  sbToWbk(StringBuffer sb, String fileName)
  {
    String retVal = null;
    String[] data = null;
    WritableWorkbook  workbook = null;

    try
    {
      if (sb !=null && sb.length()>0)
      {
        data = sbToArray(sb); // One row per line
        String [] colHeadings = sb.toString().substring(0,sb.toString().indexOf("\n")).split("\\"+getColumnDelimitor());
        int numRows = data.length;
        int numCols = colHeadings.length;

        workbook = Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet0 = workbook.createSheet("Results", 0);
        jxl.write.Number number = null;
        int i=0;
        int j=0;
        Integer cellInt = null;
        Float cellFloat = null;
        Double cellDouble = null;
        jxl.write.Label label = null;
        boolean cellIsInt = false;
        boolean cellIsFloat = false;
        boolean cellIsDouble = false;

        // Add Column Headings
        for (i=0;i<numCols;i++)
        {
          label = new jxl.write.Label(i, 0, colHeadings[i]);
          sheet0.addCell(label);
        }

        // Add ResultData
        String [] currRow = null;
        for (j=0;j<numRows;j++)
        {
          if (data[j]!=null)
          {
            currRow = data[j].split("\\"+getColumnDelimitor());
            for (i=0;i<numCols;i++)
            {
              if (!currRow[i].equals("null"))
              {
                cellIsInt = false;
                cellIsFloat = false;
                cellIsDouble = false;
                  final String Digits     = "(\\p{Digit}+)";
                  final String HexDigits  = "(\\p{XDigit}+)";
                  // an exponent is 'e' or 'E' followed by an optionally
                  // signed decimal integer.
                  final String Exp        = "[eE][+-]?"+Digits;
                  final String fpRegex    = ("[\\x00-\\x20]*"+  // Optional leading "whitespace"
                                              "[+-]?(" + // Optional sign character
                                              "NaN|" +           // "NaN" string
                                              "Infinity|" +      // "Infinity" string

                  // A decimal floating-point string representing a finite positive
                  // number without a leading sign has at most five basic pieces:
                  // Digits . Digits ExponentPart FloatTypeSuffix
                  //
                  // Since this method allows integer-only strings as input
                  // in addition to strings of floating-point literals, the
                  // two sub-patterns below are simplifications of the grammar
                  // productions from the Java Language Specification, 2nd
                  // edition, section 3.10.2.

                  // Digits ._opt Digits_opt ExponentPart_opt FloatTypeSuffix_opt
                                              "((("+Digits+"(\\.)?("+Digits+"?)("+Exp+")?)|"+

                  // . Digits ExponentPart_opt FloatTypeSuffix_opt
                                              "(\\.("+Digits+")("+Exp+")?)|"+

                  // Hexadecimal strings
                                              "((" +
                 // 0[xX] HexDigits ._opt BinaryExponent FloatTypeSuffix_opt
                                              "(0[xX]" + HexDigits + "(\\.)?)|" +

                 // 0[xX] HexDigits_opt . HexDigits BinaryExponent FloatTypeSuffix_opt
                                              "(0[xX]" + HexDigits + "?(\\.)" + HexDigits + ")" +

                                              ")[pP][+-]?" + Digits + "))" +
                                              "[fFdD]?))" +
                                              "[\\x00-\\x20]*");// Optional trailing "whitespace"

                 if (java.util.regex.Pattern.matches(fpRegex, currRow[i]))
                 {
                   cellDouble = Double.valueOf(currRow[i]); // Will not throw NumberFormatException
                   cellIsDouble = true;
                 }
                 else
                 {
                   // Perform suitable alternative action
                 }

                if (!cellIsDouble)
                {
                  try
                  {
                     // my slow method
                     cellInt = new Integer(currRow[i].trim());
                     cellIsInt = true;
                     if (!cellIsInt)
                     {
                       cellFloat= new Float(currRow[i].trim());
                       cellIsFloat = true;
                     }
                  }
                  catch (NumberFormatException numEx)
                  {
                    //System.out.println(currRow[i]+ " is NOT a number");
                  }
                }

                if (cellIsInt)
                {
                  number = new jxl.write.Number(i, j+1, cellInt);
                  sheet0.addCell(number);
                }
                else if (cellIsFloat)
                {
                  number = new jxl.write.Number(i, j+1, cellFloat);
                  sheet0.addCell(number);
                }
                else if (cellIsDouble)
                {
                  number = new jxl.write.Number(i, j+1, cellDouble);
                  sheet0.addCell(number);
                }
                else
                {
                  label = new jxl.write.Label(i, j+1, currRow[i]);
                  sheet0.addCell(label);
                }

              }
              else // cell == null
              {
                // don't add
              }
            } // columns
          }
        }  // rows

        workbook.write();
        //workbook.close();
        System.out.println("XLS file "+fileName+" Successfully written");
        retVal = fileName;
      }
      else
      {
        System.out.println("XLS file "+fileName+" NOT written - NO Data.");
        retVal = null;
      }
    }
    catch (jxl.write.WriteException wEx)
    {
      wEx.printStackTrace();
      retVal = null;
    }
    catch (java.io.IOException ioEx)
    {
      ioEx.printStackTrace();
      retVal = null;
    }

    return workbook;
  }


  /**
   *  Converts a ResultSet StringBuffer to a saved Excel file with one sheet named 'Results' that holds one result per line.
   *
   * @param rs is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convert
   * @param fileName is the name of the file to save
   * @return a String containing the filename if suucessful, null if not.
   **/
  public static WritableSheet  sbToSheet(StringBuffer sb, String fileName)
  {
    String retVal = null;
    String[] data = null;
    WritableWorkbook  workbook = null;
    WritableSheet sheet0 = null;

    try
    {
      if (sb !=null && sb.length()>0)
      {
        data = sbToArray(sb); // One row per line
        String [] colHeadings = sb.toString().substring(0,sb.toString().indexOf("\n")).split("\\"+getColumnDelimitor());
        int numRows = data.length;
        int numCols = colHeadings.length;

        workbook = Workbook.createWorkbook(new File(fileName));
        sheet0 = workbook.createSheet("Results", 0);
        jxl.write.Number number = null;
        int i=0;
        int j=0;
        Integer cellInt = null;
        Float cellFloat = null;
        Double cellDouble = null;
        jxl.write.Label label = null;
        boolean cellIsInt = false;
        boolean cellIsFloat = false;
        boolean cellIsDouble = false;

        // Add Column Headings
        for (i=0;i<numCols;i++)
        {
          label = new jxl.write.Label(i, 0, colHeadings[i]);
          sheet0.addCell(label);
        }

        // Add ResultData
        String [] currRow = null;
        for (j=0;j<numRows;j++)
        {
          if (data[j]!=null)
          {
            currRow = data[j].split("\\"+getColumnDelimitor());
            for (i=0;i<numCols;i++)
            {
              if (!currRow[i].equals("null"))
              {
                cellIsInt = false;
                cellIsFloat = false;
                cellIsDouble = false;
                  final String Digits     = "(\\p{Digit}+)";
                  final String HexDigits  = "(\\p{XDigit}+)";
                  // an exponent is 'e' or 'E' followed by an optionally
                  // signed decimal integer.
                  final String Exp        = "[eE][+-]?"+Digits;
                  final String fpRegex    = ("[\\x00-\\x20]*"+  // Optional leading "whitespace"
                                              "[+-]?(" + // Optional sign character
                                              "NaN|" +           // "NaN" string
                                              "Infinity|" +      // "Infinity" string

                  // A decimal floating-point string representing a finite positive
                  // number without a leading sign has at most five basic pieces:
                  // Digits . Digits ExponentPart FloatTypeSuffix
                  //
                  // Since this method allows integer-only strings as input
                  // in addition to strings of floating-point literals, the
                  // two sub-patterns below are simplifications of the grammar
                  // productions from the Java Language Specification, 2nd
                  // edition, section 3.10.2.

                  // Digits ._opt Digits_opt ExponentPart_opt FloatTypeSuffix_opt
                                              "((("+Digits+"(\\.)?("+Digits+"?)("+Exp+")?)|"+

                  // . Digits ExponentPart_opt FloatTypeSuffix_opt
                                              "(\\.("+Digits+")("+Exp+")?)|"+

                  // Hexadecimal strings
                                              "((" +
                 // 0[xX] HexDigits ._opt BinaryExponent FloatTypeSuffix_opt
                                              "(0[xX]" + HexDigits + "(\\.)?)|" +

                 // 0[xX] HexDigits_opt . HexDigits BinaryExponent FloatTypeSuffix_opt
                                              "(0[xX]" + HexDigits + "?(\\.)" + HexDigits + ")" +

                                              ")[pP][+-]?" + Digits + "))" +
                                              "[fFdD]?))" +
                                              "[\\x00-\\x20]*");// Optional trailing "whitespace"

                 if (java.util.regex.Pattern.matches(fpRegex, currRow[i]))
                 {
                   cellDouble = Double.valueOf(currRow[i]); // Will not throw NumberFormatException
                   cellIsDouble = true;
                 }
                 else
                 {
                   // Perform suitable alternative action
                 }

                if (!cellIsDouble)
                {
                  try
                  {
                     // my slow method
                     cellInt = new Integer(currRow[i].trim());
                     cellIsInt = true;
                     if (!cellIsInt)
                     {
                       cellFloat= new Float(currRow[i].trim());
                       cellIsFloat = true;
                     }
                  }
                  catch (NumberFormatException numEx)
                  {
                    //System.out.println(currRow[i]+ " is NOT a number");
                  }
                }

                if (cellIsInt)
                {
                  number = new jxl.write.Number(i, j+1, cellInt);
                  sheet0.addCell(number);
                }
                else if (cellIsFloat)
                {
                  number = new jxl.write.Number(i, j+1, cellFloat);
                  sheet0.addCell(number);
                }
                else if (cellIsDouble)
                {
                  number = new jxl.write.Number(i, j+1, cellDouble);
                  sheet0.addCell(number);
                }
                else
                {
                  label = new jxl.write.Label(i, j+1, currRow[i]);
                  sheet0.addCell(label);
                }

              }
              else // cell == null
              {
                // don't add
              }
            } // columns
          }
        }  // rows

        workbook.write();
        //workbook.close();
        System.out.println("XLS file "+fileName+" Successfully written");
        retVal = fileName;
      }
      else
      {
        System.out.println("XLS file "+fileName+" NOT written - NO Data.");
        retVal = null;
      }
    }
    catch (jxl.write.WriteException wEx)
    {
      wEx.printStackTrace();
      retVal = null;
    }
    catch (java.io.IOException ioEx)
    {
      ioEx.printStackTrace();
      retVal = null;
    }

    return sheet0;
  }


  /** Converts a sql resultSet in the form of a deliminated string to a JSON format.
    *
    * @param content is the sql resultSet in the form of a deliminated string
    * @param separator the split char
    **/
  public static String csvToJSON(String content, String separator){ return Util.csvToJSON(content,  separator);}


    /** Converts a sql resultSet StringBuffer in the form of a default delimited string to a JSON format.
    *
    * @param contentSB is the sql resultSet in the form of a deliminated string
    **/
  public static String sbToJSON(StringBuffer contentSB){ return Util.csvToJSON(contentSB.toString(),  DEFAULT_COLUMN_DELIMITOR);}



  /**
   *  Converts a ResultSet StringBuffer to a saved file that holds one result per line.
   *
   * @param rs is the SQL resultSet to convert
   * @param fileName is the name of the file to save
   * @return a String containing the filename if suucessful, null if not.
   **/
  public static String  resultSetToCSVFile(java.sql.ResultSet rs, String fileName, boolean includeColumnsHeaders)
  {
    String retVal = "";

    try
    {
      CSVWriter csvW = new CSVWriter(new java.io.FileWriter(fileName), columnDelimitor.charAt(0));
      if (rs != null)
        csvW.writeAll(rs, includeColumnsHeaders);

      if ( csvW!=null )
        csvW.close();

      retVal = fileName;
    }
    catch ( Exception ex)
    {
      System.out.println("Could Not write CSV File: "+fileName);
    }

    return retVal;
  }


  /**
    Set the database to use Oracle DB with a dedicated connection to the server. Use this method before doing db access calls.
   **/
  public void setUseOracleDedicatedDB()
  {
    this.dbType_=ORACLEDEDICATED_DB;
    oraDedicated_=true;

    dbDriverName = oraDbDriverName;
    dbPort = oraDbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use Oracle DB. Use this method before doing db access calls.
   **/
  public void setUseOracleDB()
  {
    this.dbType_=ORACLE_DB;

    dbDriverName = oraDbDriverName;
    dbPort = oraDbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use Oracle DB. Use this method before doing db access calls.
   **/
  public void setUseDb2DB()
  {
    this.dbType_=DB2_DB;

    dbDriverName = db2DbDriverName;
    dbPort = db2DbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use Oracle DB. Use this method before doing db access calls.
   **/
  public void setUseDerbyDB()
  {
    this.dbType_=DERBY_DB;

    dbDriverName = derbyDbDriverName;
    dbPort = derbyDbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use PG. Use this method before doing db access calls.
   **/
  public void setUsePostgreDB()
  {
    this.dbType_=POSTGRE_DB;

    dbDriverName = pgDbDriverName;
    dbPort = pgDbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use Mysql. Use this method before doing db access calls.
   **/
  public void setUseMysqlDB()
  {
    this.dbType_=MYSQL_DB;

    dbDriverName = mysqlDbDriverName;
    dbPort = mysqlDbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use Mysql. Use this method before doing db access calls.
   **/
  public void setUseMariadbDB()
  {
    this.dbType_=MARIADB_DB;

    System.out.println("SqlQuery.setUseMariadbDB(): setting dbDriverName = "+mariadbDbDriverName);
    dbDriverName = mariadbDbDriverName;
    dbPort = mysqlDbPort;
    dbConnectString = getDbConnectString();
    System.out.println("SqlQuery.setUseMariadbDB(): dbConnectString = "+getDbConnectString());
  }


  /**
    Set the database to use PG. Use this method before doing db access calls.
   **/
  public void setUseHsqlDB()
  {
    this.dbType_=HSQL_DB;

    dbDriverName = hsqlDbDriverName;
    dbPort = hsqlDbPort;
    dbConnectString = getDbConnectString();
  }


  /**
    Set the database to use the JDBC-ODBC bridge. Use this method before doing db access calls.
   **/
  public void setUseOdbcDB()
  {
    this.dbType_=ODBC_DB;

    dbDriverName = odbcDbDriverName;
    dbPort = "";
    dbConnectString = getDbConnectString();
  }


  /**
   *  Sets the queries to be case insensitive.this is an Oracle Var as of 10gR2
   *  This is a very quick query.
   * <br /><br />
   **/
  public void setCaseInsensitive()
  {
    // this is an Oracle Var as of 10gR2
    String sql = "alter session set NLS_SORT=BINARY_CI";

    StringBuffer rs = query(sql);
  }


  /**
   *  Sets the queries to be case sensitive.this is an Oracle Var as of 10gR2
   *  This is a very quick query.
   * <br /><br />
   **/
  public void setCaseSensitive()
  {
     // this is an Oracle Var as of 10gR2
    String sql = "alter session set NLS_SORT=BINARY";

    StringBuffer rs = query(sql);
  }


  /**
   *  Tests the Production DB connection.
   *
   * @return boolean whether the DB is actively available.
   **/
  public boolean canConnect()
  {
    return canConnect(true);
  }


  /**
   *  Tests the DB connection.
   *
   * @param useProductionDb flags whether the production or test/developmentDB get used.
   * @return boolean whether the DB is actively available.
   **/
  public boolean canConnect(boolean useProductionDb)
  {
    boolean retVal = false;
    try
    {
      // Step 1. Load the JDBC driver
      Class.forName(dbDriverName);

      // Step 2. Create a Connection object  - Now this is a class var
      // Connection con = null;

      if (con == null || con.isClosed()) // else use the existing
        try
        {
          if (debugOut) System.out.println("  Testing JDBC Connection" );
          if (debugOut) System.out.println("    Using Driver: " + getDbDriverName());
          if (debugOut) System.out.println("      connectStr: " + getDbConnectString());
          if (debugOut) System.out.println("            user: " + getDbUser());
          if (debugOut) System.out.println("        password: " + getDbPassword());
          if (useProductionDb)
            con = DriverManager.getConnection(getDbConnectString(), getDbUser(), getDbPassword());
          else
            con = DriverManager.getConnection(this.testDbConnectString, getDbUser(), getDbPassword());

          con.close();

          retVal = true;
          if (debugOut) System.out.print("got connection to DB " + getDbName());
          if (debugOut) System.out.println(" with user " + getDbUser());
        }
        catch (SQLException e2)
        {
          // Exception when executing java.sql related commands, print error message to the console
          System.out.println(e2.toString());
          //e2.printStackTrace();
          System.out.println("Did not connect to DB... bailing out.");
          System.out.println("dbConnect = "+getDbConnectString()+" "+ getDbUser()+" / "+getDbPassword());
        }
    }
    catch (ClassNotFoundException e1)
    {
      // JDBC driver class not found, print error message to the console
      if (debugOut) System.out.println(e1.toString());
    }
    catch (SQLException e2)
    {
      // Exception when executing java.sql related commands, print error message to the console
      System.out.println(e2.toString());
      //System.out.println("SQL: "+ sql);
      e2.printStackTrace();
    }
    catch (Exception e3)
    {
      // other unexpected exception, print error message to the console
      System.out.println(e3.toString());
    }

    return retVal;
  }


  /**
   *  Creates a delta timestamp for the current time to the passed timestamp in the form of
   *  'hour + "-" + min + "-" + sec + "-" + millis'.
   *
   * @return    The CurrentTimeStamp - fromTimeStamp value.
   */
  static public String getTimeDelta(String fromTimeStamp)
  {
    String currTimeStamp = createCurrentTimeStamp();
    String [] currSplits = currTimeStamp.split("-");
    String [] fromSplits = fromTimeStamp.split("-");
    int currMillis = Integer.parseInt(currSplits[3]);
    int currSec = Integer.parseInt(currSplits[2]);
    int currMin = Integer.parseInt(currSplits[1]);
    int currHr = Integer.parseInt(currSplits[0]);
    int fromMillis = Integer.parseInt(fromSplits[3]);
    int fromSec = Integer.parseInt(fromSplits[2]);
    int fromMin = Integer.parseInt(fromSplits[1]);
    int fromHr = Integer.parseInt(fromSplits[0]);

    String millis = "000";
    String sec = "00";
    String min = "00";
    String hour = "00";
    int carryOver = 0;

    if (currMillis-fromMillis>=0)
    {
      millis = String.valueOf(currMillis-fromMillis);
      carryOver = 0;
    }
    else
    {
      millis = String.valueOf(1000+currMillis-fromMillis );
      carryOver = 1;
    }

    if (currSec-carryOver-fromSec>=0)
    {
      sec = String.valueOf(currSec-carryOver-fromSec);
      carryOver = 0;
    }
    else
    {
      sec = String.valueOf(60+currSec-carryOver-fromSec );
      carryOver = 1;
    }

    if (currMin-carryOver-fromMin>=0)
    {
      min = String.valueOf(currMin-carryOver-fromMin);
      carryOver = 0;
    }
    else
    {
      min = String.valueOf(60+currMin-carryOver-fromMin );
      carryOver = 1;
    }

    if (currHr-carryOver-fromHr>=0)
    {
      hour = String.valueOf(currHr-carryOver-fromHr);
      carryOver = 0;
    }
    else
    {
      hour = String.valueOf(60+currHr-carryOver-fromHr );
      carryOver = 1;
    }

    return (String)hour + "-" + min + "-" + sec + "-" + millis;
  }


  /**
   *  Creates a timestamp for the current time in the form of
   *  'hour + "-" + min + "-" + sec + "-" + millis'.
   *
   * @return    The CurrentTimeStamp value.
   */
  static public String createCurrentTimeStamp()
  {
    String value = "";
    Calendar calendar = Calendar.getInstance();
    int currMillis = calendar.get(calendar.MILLISECOND);
    String millis = String.valueOf(currMillis);
    if (currMillis < 10)
    {
      millis = "00" + currMillis;
    }
    else if (currMillis < 100)
    {
      millis = "0" + currMillis;
    }
    int currSec = calendar.get(calendar.SECOND);
    String sec = String.valueOf(currSec);
    if (currSec < 10)
    {
      sec = "0" + currSec;
    }
    int currMin = calendar.get(calendar.MINUTE);
    String min = String.valueOf(currMin);
    if (currMin < 10)
    {
      min = "0" + currMin;
    }
    int currHr = calendar.get(calendar.HOUR);
    String hour = String.valueOf(currHr);
    if (currHr < 10)
    {
      hour = "0" + currHr;
    }

    return (String)hour + "-" + min + "-" + sec + "-" + millis;
  }


  /**
   *  Executes a SQL statement onto the JDBC accessed <b>PRODUCTION</b> database
   *  defined by the class driver and connection string.
   *
   * @param sql is the SQL to execute
   * @return a StringBuffer containing the ResultSet, one result per line.
   **/
  public StringBuffer query(String sql)
  {
    return query(sql, true);
  }


  /**
   *  Executes a SQL statement onto the JDBC accessed database defined by the
   *  class driver and connection string.
   *  the resultSet is converted to a StringBuffer that looks similar:<pre>
   *   FISCAL_YEAR|SUBSTATION_CD|FEEDER|FEEDER_CUSTOMERS|DTOR|CUSTS_OUT|CUST_OUTAGE_MIN|DEMAND_MW
   *   2007|NVR|1260 NVR|218|P3835151.A|12|2172|null
   *   2007|NVR|1260 NVR|218|P3868231.A|12|3864|null
   *   2007|NVR|1261 NVR|2555|P3806671.B|152|21432|null
   *   </pre>
   *  <u><b>Other Helper Methods:</b></u><br />
   *  <ul>
   *    <li> the delimiter can be set with the
   *         {@link #setColumnDelimitor(String) setColumnDelimitor} method.</li>
   *    <li> the returned StringBuffer (resultSet) can be converted to an String array with the
   *         {@link #sbToArray(StringBuffer) sbToArray} method.</li>
   *    <li> you can (optionally) test the connection 1st with the
   *         {@link #canConnect() canConnect} method.</li>
   *  </ul>
   *
   * @param sql is the SQL to execute
   * @param useProductionDb flags whether the production or test/developmentDB get used.
   * @return a StringBuffer containing the ResultSet, one result per line and includes the columnNames as the 1st row.
   **/
  public StringBuffer query(String sql, boolean useProductionDb)
  {
    StringBuffer retVal = new StringBuffer("");
    Statement s = null;
    ResultSet rs = null;
    //boolean removeEOLs = true;
    if (sql != null && !sql.equals(""))
    {
      try
      {
        if(useProxoolPool_)
        {
          if (con == null || con.isClosed()) // else use the existing
          try
          {
            Class.forName(proxoolDriverName);
            Properties info = new Properties();
            info.setProperty("proxool.maximum-connection-count", ""+proxoolMaximumConnectionCount_);
            info.setProperty("proxool.house-keeping-test-sql", "select CURRENT_DATE");
            info.setProperty("user", getDbUser());
            info.setProperty("password",  getDbPassword());
            String alias = "pool";
            String driverClass = dbDriverName;
            String driverUrl = getDbConnectString();
            String url = "proxool." + alias + ":" + driverClass + ":" + driverUrl;
            try{ProxoolFacade.registerConnectionPool(url, info);}
            catch (ProxoolException pEx) {/* already registered */}

            con = DriverManager.getConnection("proxool."+alias);
            if (debugOut) System.out.print("got Pooled connection to DB " + getDbName());
            if (debugOut) System.out.println(" with user " + getDbUser());
          }
          catch (SQLException e2)
          {
            // Exception when executing java.sql related commands, print error message to the console
            System.out.println(e2.toString());
            //e2.printStackTrace();
            System.out.println("Did not connect to ProxoolPool DB... bailing out.");
          }
        }
        else
        {
          // Step 1. Load the JDBC driver
          Class.forName(dbDriverName).newInstance();

          // Step 2. Create a Connection object  - Now this is a class var
          // Connection con = null;

          if (con == null || con.isClosed()) // else use the existing
            try
            {
              if (useProductionDb)
                con = DriverManager.getConnection(getDbConnectString(), getDbUser(), getDbPassword());
              else
                con = DriverManager.getConnection(getDbConnectString(), getDbUser(), getDbPassword());

              if (debugOut) System.out.print("got connection to DB " + getDbName());
              if (debugOut) System.out.println(" with user " + getDbUser());
            }
            catch (SQLException e2)
            {
              // Exception when executing java.sql related commands, print error message to the console
              //System.out.println(e2.toString());
              System.out.println("SQLException: " + e2.getMessage());
              System.out.println("SQLState: " + e2.getSQLState());
              System.out.println("VendorError: " + e2.getErrorCode());
              e2.printStackTrace();
              System.out.println("Did not connect to DB... bailing out.");
              System.out.print(""+getDbName()+" DB Connection "+" ("+getDbUser()+":"+getDbPassword()+ "): ");
            }
        }

        if (con != null)
        {
          // Step 3. Create a Statement object and call its executeUpdate
          // method to insert a record
          s = con.createStatement();
          if (debugOut) System.out.println("SQL Query: \n"+sql);
          String startTimeStamp = createCurrentTimeStamp();
          //System.out.println("Start Time = "+startTimeStamp);
          if (sql.toUpperCase().startsWith("INSERT")
              || sql.toUpperCase().startsWith("UPDATE")
              || sql.toUpperCase().startsWith("REPLACE")
              || sql.toUpperCase().startsWith("DELETE")
              || sql.toUpperCase().startsWith("DROP")
              || sql.toUpperCase().startsWith("CREATE")
            )
            s.executeUpdate(sql); // any query that returns nothing
          else
          {
            rs = s.executeQuery(sql);
            //System.out.println("End Time = "+createCurrentTimeStamp());
            //System.out.print("_#");
            if (debugOut) System.out.println("\nElapsed Time (hr-min-sec-milli) = "+getTimeDelta(startTimeStamp));
            if ( rs != null)
            {
              int numRows = 0;
              try
              {
                ResultSetMetaData rsmd = rs.getMetaData();
                //System.out.print("_#");
                int numCols = rsmd.getColumnCount();
                for (int i=1; i<= numCols; i++)
                {
                  retVal.append(rsmd.getColumnLabel(i));
                  if (i<numCols)  retVal.append(columnDelimitor);
                }
                retVal.append("\n");
                while (rs.next())
                {
                  for (int i=1; i<= numCols; i++)
                  {
                   //if (rs!=null && rs.getString(i)!=null && rs.getString(i).contains("\n")) System.out.println(rs.getString(i));
                   if (rs!=null && rs.getString(i)!=null && rs.getString(i).contains("\r\n"))
                   {
                     //System.out.println("!YES!");
                     retVal.append(rs.getString(i).replace("\r\n","[crlf]"));
                   }
                   else if (rs!=null && rs.getString(i)!=null && rs.getString(i).contains("\n"))
                   {
                     //System.out.println("!YES!");
                     retVal.append(rs.getString(i).replace("\n","[eol]"));
                   }
                   else
                   {
                     //System.out.println("!YES!");
                    retVal.append(rs.getString(i));
                   }
                    if (i<numCols)  retVal.append(columnDelimitor);
                  }
                  numRows++;
                  retVal.append("\n");
                }
              }
              catch (SQLException noResultsEx)
              {
                //System.out.println(noResultsEx.toString());
                retVal = new StringBuffer(noResultsEx.toString());
              }
              catch (java.lang.NullPointerException npEx)
              {
                //System.out.println(npEx.toString());
                retVal = new StringBuffer(npEx.toString());
              }
              catch (java.lang.OutOfMemoryError meapMemEx)
              {
                System.out.println(meapMemEx.toString());
                System.out.println("Truncating RS to "+numRows+" rows");
              }
              finally
              {
                rs.close();
              }
            }
          }
          // clean up
          //if (rs != null && !rs.isClosed()) rs.close();
          //if (s != null && !s.isClosed()) s.close();
          if (con != null && !con.isClosed()) con.close();
        }
      }
      catch (ClassNotFoundException e1)
      {
        // JDBC driver class not found, print error message to the console
        System.out.println(e1.toString());
      }
      catch (SQLException e2)
      {
        // Exception when executing java.sql related commands, print error message to the console
        System.out.println(e2.toString());
        System.out.println("SQL:\n"+ sql);
        //e2.printStackTrace();
      }
      catch (Exception e3)
      {
        // other unexpected exception, print error message to the console
        System.out.println(e3.toString());
      }
      finally
      {
        // close and clean up
        try{if (con != null && !con.isClosed()) con.close();}catch (Exception e6){}
      }

    }

    return retVal;
  }


  /**
   *  Converts the results from the 'query' method into a Comma Seperated String.
   *
   * @param  results   The sql results from the 'query' method
   * @return           the csv StringBuffer Value
   */
  public String convertSqlResultsToCSV(StringBuffer results)
  {
    StringBuffer retVal = new StringBuffer("");
    StringTokenizer tokens = new StringTokenizer(results.toString(),SYSTEM_LINE_SEPERATOR);
    String currentToken = "";
    StringTokenizer columnsTokens;
    String [] colNames = null;
    int numCols = 0;
    int i=0;
    // Get the Col names
    if (tokens.hasMoreTokens())
    {
      currentToken = tokens.nextToken();
      columnsTokens =  new StringTokenizer(currentToken,DEFAULT_COLUMN_DELIMITOR);
      colNames = new String[columnsTokens.countTokens()];
      while (columnsTokens.hasMoreTokens())
      {
        colNames[i] = columnsTokens.nextToken().trim();
        if (colNames[i].contains(","))
        {
          retVal.append("\"");
          retVal.append(colNames[i]);
          retVal.append("\"");
        }
        else
          retVal.append(colNames[i]);
        if (columnsTokens.hasMoreTokens()) retVal.append(",");
        i++;
      }
      retVal.append("\n");
    }
    numCols = i;
    String currentColValue = "";
    while (tokens.hasMoreTokens())
    {
      currentToken = tokens.nextToken();
      columnsTokens =  new StringTokenizer(currentToken,DEFAULT_COLUMN_DELIMITOR);
      while (columnsTokens.hasMoreTokens())
      {
        currentColValue = columnsTokens.nextToken().trim();
        //if (currentColValue.contains("PREPLANNED")) System.out.println(currentColValue);
        if (currentColValue!=null && currentColValue.contains(","))
        {
          retVal.append("\"");

          if (currentColValue!=null && currentColValue.contains("\r\n"))
          {
            retVal.append((currentColValue!=null?currentColValue.replace("\r\n","[crlf]"):null));
          }
          else if (currentColValue!=null && currentColValue.contains("\n"))
          {
            retVal.append((currentColValue!=null?currentColValue.replace("\n","[eol]"):null));
          }
          else
          retVal.append(currentColValue);

          retVal.append("\"");
        }
        else if (currentColValue!=null && currentColValue.contains("\r\n"))
        {
          retVal.append((currentColValue!=null?currentColValue.replace("\r\n","[crlf]"):null));
        }
        else if (currentColValue!=null && currentColValue.contains("\n"))
        {
          retVal.append((currentColValue!=null?currentColValue.replace("\n","[eol]"):null));
        }
        else
          retVal.append(currentColValue);

        if (columnsTokens.hasMoreTokens()) retVal.append(",");
      }
      retVal.append("\n");
    }
    return retVal.toString();
  }


  /**
   *  Converts the results from the 'query' method into an xls file.
   *
   * @param  results   The sql results from the 'query' method
   * @param  xlsFilename   The resulting xls filename
   * @return           the xls filename
   */
  public boolean convertSqlResultsToXLS(StringBuffer results, String xlsFilename)
  {
    boolean retVal = true;
    try
    {
      jxl.write.WritableWorkbook workbook = Workbook.createWorkbook(new File(xlsFilename));
      jxl.write.WritableSheet sheet = workbook.createSheet("sqlResults", 0);

      StringTokenizer tokens = new StringTokenizer(results.toString(),"\n");
      String currentToken = "";
      StringTokenizer columnsTokens;
      String [] colNames = null;
      boolean removeEOLs = true;
      int numCols = 0;
      int i=0;
      int currCol=0;
      int rows=0;
      // Get the Col names
      if (tokens.hasMoreTokens())
      {
        currentToken = tokens.nextToken();
        columnsTokens =  new StringTokenizer(currentToken,"|");
        colNames = new String[columnsTokens.countTokens()];
        while (columnsTokens.hasMoreTokens())
        {
          colNames[i] = columnsTokens.nextToken().trim();
          sheet.addCell(new jxl.write.Label(i, rows, colNames[i]));
          i++;
        }
      }
      numCols = i;
      String currentColValue = "";
      double currentColDbl = 0.0;
      boolean isDouble = false;
      // write the data
      while (tokens.hasMoreTokens())
      {
        rows++;
        currCol=0;
        currentToken = tokens.nextToken();
        columnsTokens =  new StringTokenizer(currentToken,"|");
        while (columnsTokens.hasMoreTokens())
        {
          isDouble = false;
          currentColValue = columnsTokens.nextToken().trim();
          try
          {
            currentColDbl = Double.parseDouble(currentColValue);
            isDouble = true;
          }
          catch (NumberFormatException numEx)
          {
            isDouble = false;
          }
          if (isDouble)
            sheet.addCell(new jxl.write.Number(currCol++, rows, currentColDbl));
          else
            if ( removeEOLs)
              sheet.addCell(new jxl.write.Label(currCol++, rows, (currentColValue!=null?currentColValue.replace("\n","\\n"):null)));
            else
            sheet.addCell(new jxl.write.Label(currCol++, rows, currentColValue));
        }
      }
      workbook.write();
      workbook.close();
    }
    catch (java.io.IOException ioEx)
    {
      retVal = false;
    }
    catch (jxl.write.WriteException jlWriteEx)
    {
      retVal = false;
    }

    return retVal;
  }


  /**
   * Save chart as SVG file.
   * Required libs: Apache Batik (batik-svggen.jar, batik-dom.jar, dom.jar).
   *
   * @param chart JFreeChart to save.
   * @param fileName Name of file to save chart in.
   * @param width Width of chart graphic.
   * @param height Height of chart graphic.
   * @throws Exception if failed.
   */
  public void saveChartToSVG(JFreeChart chart,
                              String fileName,
                              int width,
                              int height)
  {
    if (chart != null)
    {
      if (fileName == null)
      {
        fileName = "chart.svg";
      }
      try
      {
        DOMImplementation domImpl = GenericDOMImplementation.getDOMImplementation();
        Document document = domImpl.createDocument(null, "svg", null);
        SVGGraphics2D svgGenerator = new SVGGraphics2D(document);
        chart.draw(svgGenerator, new Rectangle2D.Double(0, 0, width, height), null);

        boolean useCSS = true;
        File newSVGFile = new File(fileName);
        newSVGFile.createNewFile();
        Writer out = new OutputStreamWriter(new FileOutputStream(newSVGFile),"UTF-8");
        svgGenerator.stream(out, useCSS);
      }
      catch (Exception ex)
      {
        System.out.println("Exception - " + ex.toString());
        ex.printStackTrace(System.out);
      }
    }
  }//saveChartToSVG()


  /**
   * A helper method to take the reaults from a DBquery and parse them into a
   * CategoryDataset for use in making a chart. The SQL must produce a resultset
   * with the 1st column containing the categories. It must also have at least a
   * 2nd column that contains values to use to create the bar. a 3rd optional
   * column can be returned to ptrovide a name for the value in the 2nd column.
   * <br />
   * Example<pre>
   *
   * Coulmn1     Column2     Column3
   * Apr         10          task1
   * Apr         6           task2
   * Apr         8           task3
   * Apr         3           task4
   * Apr         25          task5
   * May         10          task1
   * May         6           task2
   * May         8           task3
   * May         3           task4
   * May         25          task5
   * Jun         10          task1
   * Jun         6           task2
   * Jun         8           task3
   * Jun         3           task4
   * Jun         25          task5
   * </pre><br />Would produce a stacked bar chart showing the the stacked
   * time spent on each task, categorized/grouped per month.
   *
   * @param sqlResults is the sqlresults to use as the basis for the dataset
   * @return the resultant dataset from the results from the sql query
   **/
  private DefaultCategoryDataset createDatasetFromSqlResults(StringBuffer sqlResults)
  {
    DefaultCategoryDataset dataset = null;

    try
    {
      if (sqlResults.length() > 0)
      {
        StringTokenizer tokens = new StringTokenizer(sqlResults.toString(),"\n");
        String currentToken = "";
        StringTokenizer columnsTokens;
        String  colVal = null;
        int numCols = 0;
        int i=0;

        // *****************************************
        // Create the Main Dataset for the task bars
        // *****************************************
        dataset = new DefaultCategoryDataset();
        double value;
        java.lang.Comparable rowKey;
        java.lang.Comparable columnKey;
        int j=0;
        // Get Rid of the Column Headings
        if (tokens.hasMoreTokens())  currentToken = tokens.nextToken();

        // go through the resultRows ( one row per item in a group)
        while (tokens.hasMoreTokens())
        {
          currentToken = tokens.nextToken();
          //System.out.println(currentToken);
          columnsTokens =  new StringTokenizer(currentToken,columnDelimitor);
          //System.out.println("Num columns: "+columnsTokens.countTokens());
          if (columnsTokens.hasMoreTokens())
          {
            columnKey = columnsTokens.nextToken().trim();
            value = Double.parseDouble(columnsTokens.nextToken().trim());
            if (columnsTokens.hasMoreTokens())
              rowKey = columnsTokens.nextToken().trim();
            else
              rowKey = "";
            dataset.addValue(value, rowKey, columnKey);
          }
          i++;
        }
      }
    }
    catch (Exception e)
    {
      System.out.println("Exception - " + e.toString());
      e.printStackTrace(System.out);
      dataset = null;
    }

    return dataset;
  }


  /**
   * Generates a JFreeChart Categorized Bar Chart based on the SQL that is passed
   * in and executed.  The SQL must produce a resultset with the 1st column
   * containing the categories. It must also have  a 2nd column that
   * contains values to use to create the bar. The 3rd column is the name
   * for the col2 value<br>
   *
   * Example<pre>
   *
   * Coulmn1     Column2     Column3
   * Apr         10          task1
   * Apr         6           task2
   * Apr         8           task3
   * Apr         3           task4
   * Apr         25          task5
   * May         10          task1
   * May         6           task2
   * May         8           task3
   * May         3           task4
   * May         25          task5
   * Jun         10          task1
   * Jun         6           task2
   * Jun         8           task3
   * Jun         3           task4
   * Jun         25          task5
   * </pre><br />Would produce a bar chart showing the column2 bars grouped by
   *  month (column1).
   *
   * @return the filename for the genetated chart.
   **/
  public String generateCategoryBarChart(String sql,
                                         HttpSession session,
                                         PrintWriter pw,
                                         String chartTitle,
                                         String xAxisTitle,
                                         String yAxisTitle,
                                         boolean showLegend)
  {
    String filename = "";
    int chartdX = 800;
    int chartdY = 600;
    Color chartBackgroundFromColor = new Color(220,255,255);
    Color chartBackgroundToColor = new Color(160,255,240);
    try {
      //  Retrieve SQL Results
      StringBuffer sqlResults = this.query(sql);

      System.out.println("Finished Bar Chart Query");
      //System.out.println("Results: ");
      //System.out.println(sqlResults.toString());

      //  Throw a custom NoDataException if there is no data
      if (sqlResults.length() == 0)
      {
        System.out.println("No data has been found");
        //throw new NoDataException();
      }

      StringTokenizer tokens = new StringTokenizer(sqlResults.toString(),"\n");
      String currentToken = "";
      StringTokenizer columnsTokens;
      String  colVal = null;
      int numCols = 0;
      int i=0;

      // *****************************************
      // Create the Main Dataset for the task bars
      // *****************************************
      DefaultCategoryDataset dataset = new DefaultCategoryDataset();
      double value;
      java.lang.Comparable rowKey;
      java.lang.Comparable columnKey;
      int j=0;
      // Get Rid of the Column Headings
      if (tokens.hasMoreTokens())  currentToken = tokens.nextToken();

      // go through the resultRows ( one row per item in a group)
      while (tokens.hasMoreTokens())
      {
        currentToken = tokens.nextToken();
        //System.out.println(currentToken);
        columnsTokens =  new StringTokenizer(currentToken,"|");
        //System.out.println("Num columns: "+columnsTokens.countTokens());
        if (columnsTokens.hasMoreTokens())
        {
          columnKey = columnsTokens.nextToken().trim();
          value = Double.parseDouble(columnsTokens.nextToken().trim());
          rowKey = columnsTokens.nextToken().trim();
          dataset.addValue(value, rowKey, columnKey);
        }
        i++;
      }

      // ***********************************************
      // Create the Main Plot
      // ***********************************************
      BarRenderer renderer = new BarRenderer();
      StandardCategoryToolTipGenerator generator = new StandardCategoryToolTipGenerator();
      //renderer.setBaseLabelGenerator(generator);
      renderer.setBaseItemLabelsVisible(new java.lang.Boolean(true), true);
      //renderer.setItemURLGenerator(new StandardCategoryURLGenerator("/goto.jsp?page=ProjectUpdate","&task","&month"));

      // org.jfree.text.TextBlock.calculateDimensions(Ljava/awt/Graphics2D;)Ljava/awt/geom/Dimension2D;
      renderer.setBaseToolTipGenerator(generator);

      CategoryAxis categoryAxis = new CategoryAxis(xAxisTitle);
      categoryAxis.setTickLabelsVisible(true);
      //categoryAxis.setLabelAngle(3.1415);
      ValueAxis valueAxis = new NumberAxis(yAxisTitle);

      CategoryPlot plot = new CategoryPlot(dataset, categoryAxis, valueAxis, renderer);
      plot.setOrientation(PlotOrientation.VERTICAL);
      plot.setRangeGridlinesVisible(true);
      plot.setDomainGridlinesVisible(false);


      // Creat the Charty based on the Plot
      JFreeChart chart = new JFreeChart(chartTitle,
                                        JFreeChart.DEFAULT_TITLE_FONT,
                                        plot,
                                        showLegend); // draw legend
      //chart.getLegend().setAnchor(chart.getLegend().EAST);
      chart.setBackgroundPaint(
          new GradientPaint(
              0,
              0,
              chartBackgroundFromColor,
              chartdX,
              chartdY,
              chartBackgroundToColor));

      //find a place to save the chart graphic
      String saveDir = ".";
      //System.out.println("Final Chart Dir: "+saveDir);

      //  Write the chart image to the temporary directory
      ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());

      filename = ServletUtilities.saveChartAsPNG(chart,
                                                 chartdX,
                                                 chartdY,
                                                 info,
                                                 session);

      //  Write the image map to the PrintWriter
      ChartUtilities.writeImageMap(pw, filename, info, false);
      pw.flush();

      //System.out.println("Moving chart to file: "+filename);
      ca.bc.webarts.widgets.Util.moveFile(
          System.getProperty("java.io.tmpdir")+filename,
          saveDir);

//    } catch (NoDataException e) {
//      System.out.println(e.toString());
//      filename = "public_nodata_500x300.png";
    } catch (Exception e) {
      System.out.println("Exception - " + e.toString());
      e.printStackTrace(System.out);
      filename = "public_error_500x300.png";
    }
    return filename;
  }



  /**
   * Generates a JFreeChart Pie Chart based on the SQL that is passed
   * in and executed.  The SQL must produce a resultset with the 1st column
   * containing the categories. It must also have  a 2nd column that
   * contains values to use to create the pie.<br>
   *
   * Example<pre>
   *
   * Coulmn1     Column2
   * Apr         10
   * May         10
   * Jun         10
   * </pre><br />Would produce a pie chart showing the time spent each month.
   *
   * @return the filename for the genetated chart.
   **/
  public String generatePieChart(String sql,
                                 HttpSession session,
                                 PrintWriter pw,
                                 String chartTitle,String xAxisTitle,String yAxisTitle)
  {
    int chartdX = 640;
    int chartdY = 480;
    Color chartBackgroundFromColor = new Color(220,255,255);
    Color chartBackgroundToColor = new Color(160,255,240);
    String filename = "";
    try {
      //  Retrieve SQL Results
      StringBuffer sqlResults = this.query(sql);

      System.out.println("Finished Pie Query");
      //System.out.println("Results: ");
      //System.out.println(sqlResults.toString());

      //  Throw a custom NoDataException if there is no data
      if (sqlResults.length() == 0)
      {
        System.out.println("No data has been found");
        //throw new NoDataException();
      }

      //  Create and populate a CategoryDataset
      StringTokenizer tokens = new StringTokenizer(sqlResults.toString(),"\n");
      String currentToken = "";
      StringTokenizer columnsTokens;
      String  colVal = null;
      int numCols = 0;
      int i=0;
      DefaultPieDataset dataset = new DefaultPieDataset();
      double value;
      java.lang.Comparable rowKey;
      // Get Rid of the Column Headings
      if (tokens.hasMoreTokens())  currentToken = tokens.nextToken();

      // go through the resultRows
      while (tokens.hasMoreTokens())
      {
        currentToken = tokens.nextToken();
        columnsTokens =  new StringTokenizer(currentToken,"|");
        while (columnsTokens.hasMoreTokens())
        {
          rowKey = columnsTokens.nextToken().trim();
          value = Double.parseDouble(columnsTokens.nextToken().trim());
          dataset.setValue(rowKey, value);
        }
        i++;
      }

      PiePlot plot = new PiePlot(dataset);
      plot.setShadowXOffset(15.0);
      plot.setShadowYOffset(18.0);
      plot.setShadowPaint(new Color(210,210,210));
      JFreeChart chart = new JFreeChart(chartTitle,
                                        JFreeChart.DEFAULT_TITLE_FONT,
                                        plot,
                                        false);
      chart.setBackgroundPaint(
          new GradientPaint(
              0,
              0,
              chartBackgroundFromColor,
              chartdX,
              chartdY,
              chartBackgroundToColor));

      //find a place to save the chart graphic
      String saveDir = ".";
      //System.out.println("Final Chart Dir: "+saveDir);

      //  Write the chart image to the temporary directory
      ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());

      filename = ServletUtilities.saveChartAsPNG(chart, 640, 480, info, session);

      //  Write the image map to the PrintWriter
      ChartUtilities.writeImageMap(pw, filename, info, false);
      pw.flush();

      //System.out.println("Moving chart to file: "+System.getProperty("java.io.tmpdir")+filename);
      ca.bc.webarts.widgets.Util.moveFile(
          System.getProperty("java.io.tmpdir")+filename,
          saveDir);

//    } catch (NoDataException e) {
//      System.out.println(e.toString());
//      filename = "public_nodata_500x300.png";
    } catch (Exception e) {
      System.out.println("Exception - " + e.toString());
      e.printStackTrace(System.out);
      filename = "public_error_500x300.png";
    }
    return filename;
  }



  public static void main(String [] args)
  {
    StringBuffer helpMsg = new StringBuffer(SYSTEM_LINE_SEPERATOR);
    helpMsg.append("---  WebARTS Database Query Helper Class  ---------------------\n");
    helpMsg.append("---------------------------------------------------------------\n");
    helpMsg.append(SYSTEM_LINE_SEPERATOR);
    helpMsg.append("ca.bc.webarts.tools.SqlQuery dbType server dbName username password sql\n");

    SqlQuery instance = new SqlQuery();
    if (args ==null || args.length<4)
    {
      System.out.println(helpMsg.toString());
    }
    else
    {
      if (args[0].toLowerCase().equals("oracle"))
        instance.setUseOracleDB();
      else if (args[0].toLowerCase().equals("oraclededicated"))
        instance.setUseOracleDedicatedDB();
      else if (args[0].toLowerCase().equals("mysql"))
        instance.setUseMysqlDB();
      else if (args[0].toLowerCase().equals("hsql"))
        instance.setUseHsqlDB();
      else if (args[0].toLowerCase().equals("postgre"))
        instance.setUsePostgreDB();
      else if (args[0].toLowerCase().equals("derby"))
        instance.setUseDerbyDB();
      else if (args[0].toLowerCase().equals("db2"))
        instance.setUseDb2DB();
      else if (args[0].toLowerCase().equals("odbc"))
        instance.setUseOdbcDB();

      if (args.length >2)
      {
        instance.setDbIP(args[1]);
        instance.setDbName(args[2]);
        instance.setDbUser(args[3]);
        instance.setDbPassword(args[4]);
        if (args.length >5)
        {
          StringBuffer sql = null;
          int i = 5;

          while (i < args.length)
          {
            sql = new StringBuffer();
            for (; i < args.length; i++)
            {
              //String [] splits = args[i].split(" |;");
              // build the sql query until a ';' is found
              sql.append(args[i]);
              sql.append(" ");
              //System.out.print(args[i] + " ");
              if (args[i].endsWith(";"))
                break;
            }

            System.out.print("Executing SQL query: ");
            System.out.println(sql.toString());
            System.out.print("DB Connection ("+instance.getDbUser()+":"+instance.getDbPassword()+ "): ");
            System.out.println(instance.getDbConnectString());
            StringBuffer sqlResultSet = instance.query(sql.toString());
            System.out.println(sqlResultSet.toString());
            i++;
          }
        }
      }
    }
  }

}// -- end class ca.bc.webarts.SqlQuery