/**
   * Creates a connection pool (2 connections) and execututes queries on each. It then tries to get
   * a third connection.
   */
  public static void testConnectionPooling() {
    ConnectionPool conPool = new ConnectionPool(2, driverName, connURL, username, password);
    conPool.resizeConnectionPool(3);
    String out = "";
    try {
      SQLExecutor sqlExec1 = new SQLExecutor(conPool);
      SQLResults res1 = sqlExec1.runQuery("select dd * from JDBC_TEST where CODE < 'E'");
      res1.setToStringFormatWidth(11);
      out += res1.toString() + "\n\n";

      SQLExecutor sqlExec2 = new SQLExecutor(conPool);
      SQLResults res2 = sqlExec2.runQuery("select * from JDBC_TEST where CODE > 'E'");
      out += res2.toString() + "\n\n";

      // try to get a third connection via getConnection(). there are no available
      // connections so the ConnectionPool will create a new connection, add it the
      // pool, and return the new connection
      SQLExecutor sqlExec3 = new SQLExecutor(conPool);
      SQLResults res3 = sqlExec2.runQuery("select * from JDBC_TEST where CODE > 'E'");
      out += res3.toString();
    } finally {
      conPool.closeAllConnections();
    }

    System.out.println(out);
  }
  /**
   * static method testing jdbc framework with multiple updates and intentional sql exceptions that
   * are trapped in appropriate exception handling blocks
   */
  public static void testMultipleUpdatesAndTransWithException() {
    ConnectionPool conPool = getConnectionPool();
    SQLExecutor sqlExec = new SQLExecutor(conPool);
    try {
      sqlExec.setAutoCommit(false);
      sqlExec.addParam(new Integer(7));
      sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Z' WHERE TEST_ID = ?");

      sqlExec.addParam(new Integer(6));
      // integrity constraint violation
      sqlExec.runQuery("UPDATE JDBC_TEST SET TEST_ID = NULL WHERE TEST_ID = ?");

      sqlExec.commitTrans();
      System.out.println("transaction committed");
    } catch (DatabaseException e) {
      System.out.println("Error code=" + e.getSQLErrorCode() + ",  SQLState=" + e.getSQLState());
      if (e.isDataIntegrityViolation()) System.out.println("data integrity violation");
      else if (e.isBadSQLGrammar()) System.out.println("bad SQL grammar");
      else if (e.isNonExistentTableOrViewOrCol()) System.out.println("Non existent table or view");
      System.out.println(e.getMessage());
      sqlExec.rollbackTrans();
      System.out.println("transaction rolled back");
    } finally {
      sqlExec.closeConnection();
    }
  }
  /**
   * Run a parameterized query (sql) once with one parameter and then again with another parameter.
   * Since the sql doesn't change from the first call to runQuery() to the second call, the
   * runQuery() method only prepares the SQL statement once (the first time it is called). This was
   * verified with the debugger.
   */
  public static void testParameterizedQuery() {
    String sql = "SELECT SURROGATE_ID, CLASS_CD FROM INV WHERE SURROGATE_ID = ?";
    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
    sqlExec.addParam(840874);
    SQLResults res = sqlExec.runQuery(sql);
    System.out.println(res.toString());

    sqlExec.addParam(925659);
    res = sqlExec.runQuery(sql);
    System.out.println("\n" + res.toString());
  }
  /** static method testing jdbc framework with multiple updates, using transaction management */
  public static void testMultipleUpdatesAndTrans() {
    ConnectionPool conPool = getConnectionPool();
    SQLExecutor sqlExec = new SQLExecutor(conPool);
    try {
      sqlExec.setAutoCommit(false);
      sqlExec.addParam(new Integer(7));
      sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Q' WHERE TEST_ID = ?");

      sqlExec.addParam(new Integer(6));
      sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'R' WHERE TEST_ID = ?");

      sqlExec.rollbackTrans();
      System.out.println("transaction rolled back");
    } finally {
      sqlExec.closeConnection();
    }
  }
  /**
   * static method testing jdbc framework with a select for update statement (that locks a record)
   * and an update statement that generates a deadlock.
   */
  public static void testDeadlockException() {
    ConnectionPool conPool = getConnectionPool();
    SQLExecutor sqlExec1 = new SQLExecutor(conPool);
    try {
      // lock record with a select for update statement
      sqlExec1.setAutoCommit(false);
      sqlExec1.addParam(new Integer(2));
      sqlExec1.runQuery("SELECT CODE FROM JDBC_TEST WHERE TEST_ID = ? FOR UPDATE");

      System.out.println("Attempting to update a record locked by another connection...");
      SQLExecutor sqlExec2 = new SQLExecutor(getConnectionPool());
      sqlExec2.setTimeoutInSec(5); // timeout if deadlocked
      sqlExec2.addParam(new Integer(2));
      sqlExec2.runQueryCloseCon("UPDATE JDBC_TEST SET CODE = 'X' WHERE TEST_ID = ?");
    } catch (DatabaseException e) {
      System.out.println("Error code=" + e.getSQLErrorCode() + ", " + e.getMessage());
      if (e.isRowlockOrTimedOut()) System.out.println("Rowlock exception!");
    } finally {
      conPool.closeAllConnections();
    }
  }