package nxm.database.prim;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import nxm.sys.lib.Message;
import nxm.sys.lib.MidasException;
import nxm.sys.lib.Primitive;
import nxm.sys.lib.StringUtil;
import nxm.sys.lib.Table;

/**
    Primitive to allow macros to utilize JDBC.
    @author jwb Jim Bauer / Neon Ngo
 */
public class nmdb extends Primitive {
  protected String      myDriverString = "oracle.jdbc.driver.OracleDriver";
  protected String      myHost         = "localhost";
  protected int         myPort         = 1521; // Oracle default port = 1521
  protected String      myDBName       = null;
  protected String      myNode         = null;
  protected String      myUserName     = null;
  protected String      myUserPassword = null;
  protected String      myURLString    = null;

  protected Connection  myConnection   = null;
  protected boolean     isConnected    = false;

  protected boolean     useLowerCase   = false;
  protected String      myTable        = null;

  /** Types of results to return to the user.
      The boolean's are switches for that result type.
      Default is true for all result types.
   */
  protected String              myResult      = null; // Results variable from the macro
  private   String              columnNames   = null;
  private   String              description   = null;
  private   ArrayList<String[]> resultArray   = new ArrayList<>();
  private   boolean             doArray       = true;
  private   String              resultXML     = null;
  private   boolean             doXML         = true;
  private   Table               resultTable   = null;
  private   boolean             doTable       = true;

  /** Legacy hsqldb.jar gives result sets with high number rows first - this keeps that order */
  static boolean reverseCSVOrder4hsqldb = false; // NXM-4044 This should be set to true when using new hsqldb jars to give old behavior

  private boolean exiting = false;

  /** generic constructor */
  public nmdb() {}

  /** Get the values for the variables and opens the Database Connection
      @return status The NeXtMidas processing status (from {@link nxm.sys.inc.Commandable})
   */
  public int open () {
    myResult            = MA.getU("RESNAME");
    useLowerCase        = MA.getState("/USELOWERCASE");
    boolean connectToDb = MA.getState("/CONNECT", true); // optional
    int status          = setProps(MA.getTable("PROPS"), connectToDb);

    return status;
  }

  /** The MIDAS process method. Not applicable here.  */
  public int process () { return (exiting)? FINISH : NOOP; }

  /** Close the database connection and return to the MIDAS prompt  */
  public int close () {
    int status = disConnect();
    return status;
  }

  /** Sets the configuration properties for connecting to the Database
      @param config Table with properties info passed in from the Macro
      @param connect Flag to declare whether or not to connect to the database
      @return status The NeXtMidas processing status (from {@link nxm.sys.inc.Commandable})
   */
  public int setProps (Table config, boolean connect) {
    int status = NORMAL;

    myNode          =       config.getS("DBNODE",     myNode    );
    myHost          =       config.getS("DBHOST",     myHost    );
    myDBName        =       config.getS("DBNAME",     myDBName    );
    myUserName      =       config.getS("DBACCOUNT",  myUserName );
    myUserPassword  =       config.getS("DBPASSWORD", myUserPassword);
    myDriverString  =       config.getS("DBDRIVER",   myDriverString  );
    myPort          =       config.getL("DBPORT",     myPort    );
    myURLString     =       config.getS("DBCONNECT",  myURLString );

    if (useLowerCase) {
      myUserName      =       myUserName.toLowerCase();
      myUserPassword  =       myUserPassword.toLowerCase();
    }

    if (connect) status = connect();

    MR.put(myResult, status);

    return status;
  }

  /** Process messages sent from the Macro
      @param msg The message from the Macro.
      @return status The NeXtMidas processing status (from {@link nxm.sys.inc.Commandable})
   */
  public synchronized int processMessage (Message msg) {
    int status = NORMAL;
    if (!thisIsMe()) {
        MQ.put(msg);
        return 0;
    }
    if (msg.name.startsWith("CONNECT")) {
      Table props = MA.getTable("PROPS");
      setProps(props, true);
    } else if (msg.name.startsWith("DISCONN")) {
      int connStatus = this.disConnect();
      MR.put(myResult, connStatus);
    } else if ((msg.name.startsWith("QUERY"))  ||
               (msg.name.startsWith("SELECT")) ||
               (msg.name.startsWith("SSQL")))  {
      String sql = (msg.data+"").trim();
      executeQuery(sql);
    } else if ((msg.name.startsWith("DESC")) ||
               (msg.name.startsWith("GETDESC"))) { // describe table/database
      String tableName = (msg.data+"").trim();
      if (msg.data == null || tableName.length()==0) {
        MR.put(myResult, getTablesInfo()); // get all tables info from database
      }
      else {
        MR.put(myResult, describeTable(tableName)); // get column info for specified database table
      }
    } else if ((msg.name.startsWith("INSERT"))   ||
               (msg.name.startsWith("DROP"))     ||
               (msg.name.startsWith("TRUNCATE")) ||
               (msg.name.startsWith("CREATE"))   ||
               (msg.name.startsWith("DELETE"))   ||
               (msg.name.startsWith("UPDATE")))   {
      String sql = (msg.data+"").trim();
      int rowsUpdated = updateQuery(sql);
      MR.put(myResult, rowsUpdated);
    } else if (msg.name.startsWith("GETARRAY")) {
      String resultArrayString = getComplexArrayList(resultArray);
      MR.put(myResult, resultArrayString);
    } else if (msg.name.startsWith("GETXML")) {
      MR.put(myResult, resultXML);
    } else if (msg.name.startsWith("GETTABLE")) {
      MR.put(myResult, resultTable);
    } else if (msg.name.startsWith("GETCSV")) {
      String temp = getCSV();
      MR.put(myResult, temp);
    } else if (msg.name.startsWith("GETCOL")) {
      MR.put(myResult, columnNames);
    } else if (msg.name.startsWith("GETROW")) {
      int numRows = getNumRows();
      MR.put(myResult, numRows);
    } else if (msg.name.equals("EXIT")) {
      exiting = true;
    } else {
      status = super.processMessage(msg);
    }
    return status;
  }

  /** Establish a connection with the database. Since this class has no
      knowledge of what type of db it is accessing, everything needs to be
      explicitly defined in order to connect.
      @return status The NeXtMidas processing Status (from {@link nxm.sys.inc.Commandable})
   */
  public int connect () {
    int status = NORMAL;
    try {
      if ((myURLString != null) && (myUserName != null) && (myUserPassword != null)) {
        Class.forName(myDriverString);
        myConnection = DriverManager.getConnection(myURLString, myUserName, myUserPassword);
        isConnected = true;
        status = OPEN;
      } else if (myURLString != null) {
        Class.forName(myDriverString);
        myConnection = DriverManager.getConnection(myURLString);
        isConnected = true;
        status = OPEN;
      } else {
        if (verbose) {
          M.info("Required Connection Information Not Set. Unable to Connect");
        }
      }
    } catch (SQLException sqle) {
      handleSQLException (sqle);
      status = FINISH;
    } catch (Exception e) {
      status = FINISH;
      e.printStackTrace();
    }
    if ((verbose) && (isConnected)) {
      M.info("Got Connection...");
      this.printConnectionInfo();
    }
    return status;
  }

  /**  Disconnect and close the database connection  */
  private int disConnect ()  {
    int status = NORMAL;
    if (myConnection != null) {
      status = OPEN;
      try {
        myConnection.close();
        isConnected = false;
        status = NORMAL;
      } catch (SQLException sqle) {
        status = FINISH;
        handleSQLException(sqle);
      } catch (Exception e) {
        status = FINISH;
        e.printStackTrace();
      }
    }
    return status;
  }

  /** Run a generic SELECT SQL statement. Creates the Result Set.
      Set the number of rows return into the &lt;RESNAME&gt; results (-1 on error).
      @param query The SQL query to execute
   */
  public void executeQuery (String query) {
    ResultSet rs;
    try {
      Statement stmt = myConnection.createStatement();
      rs = stmt.executeQuery(query);
      this.toResults(rs);
      MR.put(myResult, getNumRows()); // save number of rows return into result variable
    } catch (SQLException e) {
      MR.put(myResult, -1);           // set -1 to signal error
      throw new MidasException("Unable to execute query: "+query, e);
    }
  }

  /** Gets the column names for a given table.
      Similar to describeTable but only gets the column names.
      @param table The name of the table.
      @return colNames The column names in an ArrayList.
   */
  public ArrayList<String> getColumnNames (String table) {
    ArrayList<String> colNames = new ArrayList<>();
    try {
      DatabaseMetaData dbm = myConnection.getMetaData();
      if (dbm.storesUpperCaseIdentifiers()) { table = table.toUpperCase(); } // Table names is most likely stored in all UPPER CASE
      else if (myDriverString.startsWith("jdbc:oracle")) { table = table.toUpperCase(); } // Table names in Oracle are in all UPPER CASE
      ResultSet rs = dbm.getColumns(null,"%",table,"%");
      while (rs.next()) {
        colNames.add(rs.getString("COLUMN_NAME"));
      }
    } catch (SQLException sqle){
      handleSQLException(sqle);
    } catch (Exception e){
      e.printStackTrace();
    }
    return colNames;
  }

  /** Describes a given table.
      Since this must be generic to all database types, we cannot simply use
      the "DESCRIBE" command, since that is Oracle SQL*Plus, not standard SQL.
      Returned Table will have KEYS set to column names in the table (in upper-case)
      The values will be a Table with following:
      *<code>
        TABLE_NAME  - Table Name that was specified
        COLUMN_NAME - Column (e.g. field) Name
        TYPE        - Column Data Type
        COLUMN_SIZE - Data Type Size
        NULLABLE    - YES if field is nullable, NO if it is NOT NULL, or UNKNOWN.
        REMARKS     - Explanatory comment on the column
      </code>
      @param table The name of the table to describe
      @return Table describing all the tables in the database
      @since NeXtMidas 2.7.2
   */
  public Table describeTable (String table) {
    Table tableInfo = new Table();
    try {
      DatabaseMetaData dbm = myConnection.getMetaData();
      if (dbm.storesUpperCaseIdentifiers()) { table = table.toUpperCase(); } // Table names is most likely stored in all UPPER CASE
      else if (myDriverString.startsWith("jdbc:oracle")) { table = table.toUpperCase(); } // Table names in Oracle are in all UPPER CASE
      ResultSet rs = dbm.getColumns(null,"%",table,"%");

      while (rs.next()) {
        Table colInfo = new Table();
        String colName  = rs.getString("COLUMN_NAME");
        String dataType = rs.getString("TYPE_NAME");
        int    dataSize = rs.getInt("COLUMN_SIZE");
        int    nullable = rs.getInt("NULLABLE");
        String nullableDesc;
        switch (nullable) {
          case DatabaseMetaData.columnNullable: nullableDesc = "YES";     break;
          case DatabaseMetaData.columnNoNulls:  nullableDesc = "NO";      break;
          case DatabaseMetaData.columnNullableUnknown: // fall-through
          default:                              nullableDesc = "UNKNOWN"; break;
        }

        colInfo.put("TABLE_NAME",  table);
        colInfo.put("COLUMN_NAME", colName);
        colInfo.put("TYPE",        dataType);
        colInfo.put("COLUMN_SIZE", dataSize);
        colInfo.put("NULLABLE",    nullableDesc);
        colInfo.put("REMARKS",     StringUtil.convertNullsToEmpty(rs.getString("REMARKS")));
        tableInfo.put(colName.toUpperCase(), colInfo);
      }
    }
    catch (SQLException sqle){
      handleSQLException(sqle);
    }
    return tableInfo;
  }

  /** Shows all the TABLEs and VIEWs in the database.
      Must be connected to database before calling this.
      Returned Table will have KEYS set to table names in the database (in upper-case)
      The values will be a Table with following:
      <code>
        NAME    - Table Name
        CATALOG - Table Catalog (may be null)
        SCHEMA  - Table Schema  (may be null)
        TYPE    - Table Type (e.g. TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, etc.)
        REMARKS - Explanatory comment on the table
      </code>
      @return Table describing all the tables in the database
      @since NeXtMidas 2.7.2
   */
  public Table getTablesInfo () {
    Table tablesInfo = new Table();
    try{
      DatabaseMetaData dbm = myConnection.getMetaData();
      String tableTypes[]  = {"TABLE", "VIEW"};
      ResultSet rs = dbm.getTables(null, null, "%", tableTypes);
      while (rs.next()){
        Table tab = new Table();
        String tableName = rs.getString("TABLE_NAME");
        tab.put("NAME",    tableName);
        tab.put("CATALOG", rs.getString("TABLE_CAT"));
        tab.put("SCHEMA",  rs.getString("TABLE_SCHEM"));
        tab.put("TYPE",    rs.getString("TABLE_TYPE"));
        tab.put("REMARKS", StringUtil.convertNullsToEmpty(rs.getString("REMARKS")));
        tablesInfo.put(tableName.toUpperCase(), tab);
      }
    }
    catch (SQLException sqle) {
      handleSQLException(sqle);
    }
    return tablesInfo;
  }

  /** Run a SQL statement that produces no Result Set, such as
      UPDATE, INSERT, DELETE, TRUNCATE, CREATE, etc.
      @param query The update SQL statement
      @return numUpdate The number of rows updated
   */
  public int updateQuery (String query) {
    int numUpdate;
    try {
      Statement stmt = myConnection.createStatement();
      numUpdate = stmt.executeUpdate(query);
      commit();
    } catch (SQLException sqle) {
      handleSQLException(sqle);
      numUpdate = 0;
    }
    return(numUpdate);
  }

  /** Load the results of the query into the following formats:
      ArrayList
      XML
      Table (if the doTable flag is set to true, this is derived from the XML)
      @param rs The query ResultSet.
      @throws SQLException SQLExceptions handled by {@link #handleSQLException}
   */
  private void toResults (ResultSet rs) throws SQLException {
    this.clearResults();  // clear out any old results
    StringBuilder xml = new StringBuilder(1024);
    StringBuilder colNames = new StringBuilder(255);
    ArrayList<String[]> myArrayList = new ArrayList<>();
    Table resultstab = new Table();
    try {
      ResultSetMetaData rsmd = rs.getMetaData();
      int cols = rsmd.getColumnCount();
      String columns[] = new String[cols];
      for (int i = 1; i <= cols; i++) {
        String columnName = rsmd.getColumnName(i).trim().toUpperCase();
        if (columnName==null || columnName.length() == 0) {
          columnName = "COL_"+i;
          M.warning("Database driver did not return column name for column "+i+", using "+columnName+ " as column name.");
        }
        String santizedColName = StringUtil.TABLE_AND_XML_TAG_NAME_STRIP_INVALID_REGEX.matcher(columnName).replaceAll("");
        if (!columnName.equals(santizedColName)) {
          M.warning("Column name contains invalid characters for XML and/or Table, it has been santized from ["+columnName+"] to ["+santizedColName+"]. Use 'AS' modifier to control returned column name.");
          columnName = santizedColName;
        }
        columns[i-1] = columnName;
        colNames.append(columnName);
        if (i!=cols) colNames.append(","); // add comma if not at last column
      } // end for loop (to get column names from RS metadata)

      if (doXML) xml.append("<Results>");
      int count = 0;
      while (rs.next()) {
        Table tab = null;
        if (doTable) {
          tab = new Table();
          resultstab.put("ROW_"+count, tab);
        }
        count++;
        if (doXML) xml.append("<Row>");
        String aRowStr[] = new String[cols];
        for (int i = 1; i <= cols; i++) {
          String value = aRowStr[(i-1)] = rs.getString(i);
          String columnName = columns[i-1];
          if (doTable) tab.put(columnName, rs.getObject(i));
          if (doXML) {
            xml.append("<").append(columnName).append(">");
            if (value != null) { xml.append(value.trim()); }
            xml.append("</").append(columnName).append(">");
          }
        }
        if (doXML) xml.append("</Row>");
        myArrayList.add(aRowStr);
      } // end while (rs.next()) loop
      if (this.doXML) xml.append("</Results>");
      rs.close();
    } catch (SQLException sqle) {
      handleSQLException(sqle);
      rs = null;
    } catch (Exception e) {
      e.printStackTrace();
    }
    resultArray = myArrayList;
    columnNames = colNames.toString();
    if (doXML) resultXML = xml.toString();
    if (doTable) resultTable = resultstab;
//ORIG 2.7.3   if (doXML && doTable) { resultTable = XmlFile.fastXmlToTable(resultXML, null, null, 0, true); }
    if (verbose) {
      M.info("Number of rows in results: " + getNumRows());
    }
  }

  /** Get all the results as an ArrayList. As such, it is necessary to
      run a query ({@link #executeQuery(String)}) prior to calling this method.
      @return resultArray The results of the SQL statement
   */
  public ArrayList<String[]> getResultArray () { return resultArray; }

  /** Gets a single element of the results
      @param row The row number (index 1)
      @param col The field in the row (index 1)
      @return element The element of the results at the given position
   */
  public String getResultArrayElement (int row, int col) {
    row -= 1;   // convert to zero index
    col -= 1;   // convert to zero index
    if (verbose && (row > resultArray.size())) {
      M.warning("Invalid element (only " +resultArray.size()+ " rows)");
      return null;
    }
    String element = resultArray.get(row)[col] ;
    if (verbose) {
      M.info("Row: " + (row +1) + ", Col: " +
          (col +1) + ", Element: " + element);
    }
    return element;
  }

  /** Get the query results in Table form.
      @return resultTable The Table.
   */
  public Table getResultTable () { return resultTable; }

  /** Get the query results in XML form. As such, it is necessary to
      run a query ({@link #executeQuery(String)}) prior to calling this method.
      @return resultXML The XML of the results.
   */
  public String getResultXML () { return resultXML; }

  /** Converts an ArrayList of String[] to a String.
      @param list ArrayList of String[] to convert
      @return newline and comma separated String of input list of String arrays
   */
  public String getComplexArrayList (ArrayList<String[]> list) {
    StringBuilder resultArrayString = new StringBuilder();
    for (int i = 0; i < list.size(); i++) {
      String aRow[] = list.get(i);
      for (int x =0; x < aRow.length; x++) {
        if (x > 0) resultArrayString.append(", ");
        resultArrayString.append(aRow[x]);
      }
      if (i < (list.size() - 1)) resultArrayString.append("\n");
    }
    return resultArrayString.toString();
  }

  /** Converts an ArrayList to a String.
      @param list ArrayList of String to convert
      @return newline separated String of input list of String
    */
  public String getArrayList (ArrayList<String> list) {
    StringBuffer resultArrayString = new StringBuffer();
    for (int i = 0; i < list.size(); i++) {
      String aRow = list.get(i);
      resultArrayString.append(aRow);
      if (i < (list.size() - 1)) resultArrayString.append("\n");
    }
    return resultArrayString.toString();
  }

  /** Clears the results which were loaded from {@link #executeQuery(String)} */
  public void clearResults () {
    clearResultArray();
    clearResultTable();
    this.resultXML = null;
  }

  /** Clears the result table which was loaded from {@link #executeQuery(String)} */
  public void clearResultTable () {
    if (resultTable  != null) this.resultTable.clear();
  }

  /** Clears the result table which was loaded from {@link #executeQuery(String)} */
  public void clearResultArray () {
    resultArray.clear();
  }

  /** Commits the SQL transaction. In most cases, commits are done
      automatically and it is not necessary to call this method.
   */
  public void commit () {
    try {
      myConnection.commit();
      if (verbose) M.info("Transaction committed");
    } catch (SQLException ignored) {}
  }

  /** Gets the String form of the Driver
      @return myDriverString The Driver in String form
   */
  public String getDriverString () { return myDriverString; }

  /** Get the host (server) used by the database.
      "localhost" if running on same machine
      @return myHost The host (server).
   */
  public String getHost () { return myHost; }

  /** Get the port number used by the database.
      Usually 1521 for Oracle
      @return myPort The port number.
   */
  public int getPort () { return myPort; }

  /** Get whether the database is connected.
      @return isConnected True or False.
   */
  public boolean getIsConnected () { return isConnected; }

  /** Get the name of the database.
      @return myDBName The name of the database.
   */
  public String getDBName () { return myDBName; }

  /** Get the username used to connect to the database.
      @return myUserName The username.
   */
  public String getUserName () { return myUserName; }

  /** Gets the password used to connect to the database.
      @return myUserPassword. The password.
   */
  public String getUserPassword () { return myUserPassword; }

  /** Sets the verboseness of the class to allow for greater feedback
      of exceptions to the users.
      @param verb The verbosity.
   */
  public void setVerbose (boolean verb) { verbose = verb;  }

  /** Gets the verboseness (true/false) of the class
      @return verbose The verbosity (true/false)
   */
  public boolean getVerbose () { return verbose; }

  /** Gets the description of a table.
      Set by {@link #describeTable(String)}.
      @return description The description.
   */
  public String getDescription () { return description; }

  /** Sets the URL String used for making the JDBC connection.
      @param URLString The URL String.
   */
  public void setURLString (String URLString) {
    myURLString = URLString;
    if (verbose) M.info("URLString: " + myURLString);
  }

  /** Gets the URL String used for making the JDBC connection.
      @return myURLString The URL String.
   */
  public String getURLString () { return myURLString; }

  /** Set whether or not to load the query results in an Array
      @param flag The flag.
   */
  public void setDoArray (boolean flag) { doArray = flag; }

  /** Get the flag for loading the results into an Array
      @return doArray The flag.
   */
  public boolean getDoArray () {return doArray; }

  /** Set whether or not to load the query results in XML
      @param flag The flag.
   */
  public void setDoXML (boolean flag) { doXML = flag; }

  /** Get the flag for loading the results in XML.
      @return doXML The flag.
   */
  public boolean getDoXML () { return doXML; }

  /** Set whether or not to load the query results in a Table
      @param flag The flag.
   */
  public void setDoTable (boolean flag) {
    doTable = flag;
    if (doTable) doXML = true;
  }

  /** Get the flag for loading the results in a Table.
      @return doTable The flag.
   */
  public boolean getDoTable () { return doTable; }

  /** Returns the row count from the result set.
      @return resultArray.size The number of rows in the ArrayList resultArray.
   */
  public int getNumRows () {  return resultArray.size();  }

  /** When getCSV is called with no argument, it returns the CSV (String)
      of the {@link #executeQuery(String)} results.
      @return Results of {@link #executeQuery(String)} in CSV (String) form.
   */

  public String getCSV () {
    return getCSV(resultArray);
  }

  /** Put the resultArray in CSV format.
      @param list ArrayList of String[] to convert to CSV format
      @return resultCSV The results of {@link #executeQuery(String)} in CSV format
   */
  public String getCSV (ArrayList<String[]> list) {
    if (reverseCSVOrder4hsqldb) return getCSVLegacyOrder(list);
    StringBuffer resultCSV = new StringBuffer(1024); // 1k initial buffer size

    for (int i = 0; i < list.size(); i++) {
      String aRow[] = list.get(i);
      for (int x =0; x < aRow.length; x++) {
        if (x > 0) resultCSV.append(",");
        resultCSV.append(aRow[x]);
      }
      if (i < (list.size() - 1)) resultCSV.append("\n");
    }
    return resultCSV.toString();
  }

  /** Put the resultArray in CSV format.
      @param list ArrayList of String[] to convert to CSV format
      @return resultCSV The results of {@link #executeQuery(String)} in CSV format
   */
  public String getCSVLegacyOrder (ArrayList<String[]> list) {
     StringBuffer resultCSV = new StringBuffer(1024); // 1k initial buffer size

     for (int i = list.size() - 1; i >= 0; i--) {
       String aRow[] = list.get(i);
       for (int x =0; x < aRow.length; x++) {
         if (x > 0) resultCSV.append(",");
         resultCSV.append(aRow[x]);
       }
       if (i > 0) resultCSV.append("\n");
     }
     return resultCSV.toString();
  }

  /** Set if keeping legacy CSV order, which involved compensating
   *  for the fact that result sets now come in 0 to highest row,
   *  rather than highest row to 0 (needed for legacy behavior with
   *  new hsqldb jars NXM-4044)
   *
   *  @param forceLegacyOrder force getCSV to return rows in the same
   *         it did under legacy versions of hs1ldb (default is true)
   */
  public static void setReverseCSVOrder4hsqldb (boolean forceLegacyOrder) {
    nmdb.reverseCSVOrder4hsqldb = forceLegacyOrder;
  }

  /** Get if keeping legacy CSV order, which involved compensating
   *  for the fact that result sets now come in 0 to highest row,
   *  rather than highest row to 0 (needed for legacy behavior with
   *  new hsqldb jars NXM-4044)
   *
   *  @return whether or not forcing getCSV to return rows in the same order
   *          it did under legacy versions of hs1ldb (default is false)
   */
  public static boolean isReversedCSVOrder4hsqldb () {
    return nmdb.reverseCSVOrder4hsqldb;
  }

  /** Generic method for handling SQL Exceptions.
      @param sqle The SQL Exception.
   */
  private void handleSQLException (SQLException sqle) {
    M.printStackTrace("SQL statement is not executed!", sqle);
  }

  /** Prints the database connection information (except password)
   */
  public void printConnectionInfo () {
    String details = "Connection Info: " +            "\n" +
                     "Driver: "    +myDriverString+   "\n" +
                     "URL: "       +myURLString+      "\n" +
                     "Host: "      +myHost+           "\n" +
                     "Port: "      +myPort+           "\n" +
                     "Database: "  +myDBName+         "\n" +
                     "Username: "  +myUserName+       "\n";

    M.info(details);
  }

}