/** static method testing jdbc framework with an update statement */
 public static void testUpdate() {
   SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
   sqlExec.setAutoCommit(true);
   sqlExec.addParam(new Integer(7));
   sqlExec.runQueryCloseCon("UPDATE JDBC_TEST SET CODE = 'Z' WHERE TEST_ID = ?");
   System.out.println(sqlExec.getNumRecordsUpdated() + " record(s) updated");
 }
  /**
   * This method tests the jdbc framework with a call to the following Oracle stored function:
   *
   * <p>CREATE OR REPLACE FUNCTION jdbc_test1 RETURN NUMBER IS cnt_jdbc_test NUMBER; BEGIN
   * dbms_output.put_line ('starting jdbc_test1...');
   *
   * <p>cnt_jdbc_test := 0; SELECT COUNT(*) INTO cnt_jdbc_test FROM JDBC_TEST; RETURN cnt_jdbc_test;
   * EXCEPTION WHEN NO_DATA_FOUND THEN Null; WHEN OTHERS THEN -- Consider logging the error and then
   * re-raise RAISE; END jdbc_test1; /
   */
  public static void testOracleStoredFunction() {
    String sql = "SELECT jdbc_test1() AS TestCount from dual";
    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
    SQLResults res = sqlExec.runQueryCloseCon(sql);

    if (res.getRowCount() > 0) System.out.println("SQL Results: " + res.getInt(0, 0));
  }
  /** static method testing jdbc framework with a simple select statement using parameters */
  public static void testSimpleSelectWithParams() {
    try {
      SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
      //            sqlExec.addParam(new Integer(8));
      sqlExec.addParam(8);
      sqlExec.addParam(Date.valueOf("2003-05-10"));
      SQLResults res = sqlExec.runQueryCloseCon(sqlSelect);

      String out = "SQL Results:\n";
      for (int row = 0; row < res.getRowCount(); row++)
        out +=
            res.getLong(row, "TEST_ID")
                + " "
                + res.getString(row, "NOTES")
                + " "
                + res.getDate(row, "TEST_DT")
                + " "
                + res.getDouble(row, "AMOUNT")
                + " "
                + res.getString(row, "CODE")
                + "\n";
      System.out.println(out);
    } catch (DatabaseException e) {
      if (e.isDataIntegrityViolation()) applyDataIntegrityViolationRecovery();
    }
  }
  public static void testThis() {
    String sql = "UPDATE JEFF_TEST SET HEIGHT = ? WHERE EMPID < 4";

    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
    sqlExec.addParam(55);
    sqlExec.runQueryCloseCon(sql);
    System.out.println(sqlExec.getNumRecordsUpdated() + " records updated");
  }
  /** static method testing jdbc framework with a delete and an insert statement */
  public static void testDeleteAndInsert() {
    SQLExecutor sqlExec1 = new SQLExecutor(getConnectionPool());
    sqlExec1.setAutoCommit(true);
    sqlExec1.addParam(new Integer(7));
    sqlExec1.runQueryCloseCon("DELETE FROM JDBC_TEST WHERE TEST_ID = ?");
    System.out.println(sqlExec1.getNumRecordsUpdated() + " record(s) deleted");

    SQLExecutor sqlExec2 = new SQLExecutor(getConnectionPool());
    sqlExec2.setAutoCommit(true);
    String sql =
        "INSERT INTO JDBC_TEST (TEST_ID, NOTES, TEST_DT, AMOUNT, CODE) "
            + "VALUES (7, 'seven', SYSDATE+2, 25.245, 'E')";
    //                       "VALUES (7, 'seven', CURRENT_DATE, 25.245, 'E')";  MySQL version
    sqlExec2.runQueryCloseCon(sql);

    System.out.println(sqlExec2.getNumRecordsUpdated() + " record(s) inserted");
  }
  /**
   * static method testing jdbc framework with a simple select statement, parameters, and a maximum
   * number of rows
   */
  public static void testSimpleSelectAndMaxRows() {
    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
    sqlExec.setMaxRows(5); // limit number of rows returned to 5
    sqlExec.addParam(new Integer(8));
    sqlExec.addParam(Date.valueOf("2003-05-10"));
    SQLResults res = sqlExec.runQueryCloseCon(sqlSelect);

    System.out.println(res.toString());
  }
  /** Does a simple select from a MySQL database. */
  public static void testMySQL() {
    String driverName = "com.mysql.jdbc.Driver";
    String connURL = "jdbc:mysql://localhost/test";
    String username = "******";
    String password = "";

    ConnectionPool conPool = new ConnectionPool(1, driverName, connURL, username, password);
    SQLExecutor sqlExec = new SQLExecutor(conPool);
    SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM JDBC_TEST");
    System.out.println(res.toString());
  }
  public static void testSelectDistinctAndMixedCase() {
    ConnectionPool conPool = getConnectionPool();
    SQLExecutor sqlExec = new SQLExecutor(conPool);

    String sql =
        "SELECT distinct test_id, test_dt "
            + "FROM JDBC_TEST "
            + "WHERE test_dt = (SELECT MAX(test_dt) FROM JDBC_TEST)";
    SQLResults res = sqlExec.runQueryCloseCon(sql);
    String testID = res.getString(0, "test_id");
    Date testDate = res.getDate(0, "TEST_DT");
    System.out.println("test_id = " + testID);
    System.out.println("TEST_DT = " + testDate);
  }
  /**
   * Illustrates creating a database connection using standard JDBC and then using this connection
   * to create a ConnectionPool and execute a select statement.
   */
  public static void testCreatingOwnConnection() {
    Connection con = null;
    try {
      Class.forName(driverName).newInstance();
      con = DriverManager.getConnection(connURL, username, password);
    } catch (SQLException sqle) {
      System.out.println(sqle.getMessage() + "\n" + "SQL State: " + sqle.getSQLState());
    } catch (Exception e) {
      System.out.println(e.getMessage());
    }

    SQLExecutor sqlExec = new SQLExecutor(new ConnectionPool(con));
    SQLResults res = sqlExec.runQueryCloseCon("SELECT COUNT(*) FROM JDBC_TEST");
    System.out.println("Record count=" + res.getInt(0, 0));
  }
  /**
   * static method testing jdbc framework with a simple select statement using parameters and the
   * result set's .toString() method
   */
  public static void testSimpleSelectWithParamsAndToString() {
    try {
      driverName = "oracle.jdbc.driver.OracleDriver";
      connURL = "jdbc:oracle:thin:@SNOWMASS:1521:WDEV";
      username = "******";
      password = "******";

      ConnectionPool conPool = new ConnectionPool(1, driverName, connURL, username, password);
      SQLExecutor sqlExec = new SQLExecutor(conPool);
      //            sqlExec.addParam(new Integer(8));
      sqlExec.addParam(8);
      sqlExec.addParam(Date.valueOf("2003-05-10"));
      SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM UTILITY_STATUS");

      String out = "SQL Results:\n" + res.toString();
      System.out.println(out);
    } catch (DatabaseException e) {
      if (e.isDataIntegrityViolation()) applyDataIntegrityViolationRecovery();
    }
  }
  /**
   * 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();
    }
  }
 /** static method testing jdbc framework with a simple select statement that returns no rows */
 public static void testSelectWhichReturnsNoRows() {
   SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
   SQLResults res = sqlExec.runQueryCloseCon("select * from JDBC_TEST where CODE = '4'");
   System.out.println(res.toString());
 }