/** 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();
    }
  }
  /**
   * This procecure calls the following Oracle stored procedure which inserts a new row into the
   * JDBC_TEST table.
   *
   * <p>CREATE OR REPLACE PROCEDURE jdbc_proc_test1(pTEST_ID NUMBER, pNOTES VARCHAR2) IS BEGIN
   * INSERT INTO JDBC_TEST(TEST_ID, NOTES, TEST_DT, AMOUNT, CODE) VALUES (pTEST_ID, pNOTES, SYSDATE,
   * 44.44, 'Z'); END; /
   */
  public static void testCallingStoredProc() {
    SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());

    sqlExec.addParam(new Integer(8));
    sqlExec.addParam("This record inserted via stored proc call");
    sqlExec.runStoredProcCloseCon("jdbc_proc_test1");
  }
  /**
   * 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();
    }
  }
 /**
  * This procedure calls the following Oracle stored procedure and gets the value from the OUT
  * param pFULLNAME.
  *
  * <p>CREATE OR REPLACE PROCEDURE jdbc_proc_test2( pFIRSTNAME IN VARCHAR2, pLASTNAME IN VARCHAR2,
  * pFULLNAME OUT VARCHAR2) IS BEGIN pFULLNAME := pFIRSTNAME || ' ' || pLASTNAME; END;
  */
 public static void testCallingStoredProcWithAnOUTParam() {
   SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
   sqlExec.addParam("Jeff");
   sqlExec.addParam("Smith");
   sqlExec.addStoredProcOutParam("fullname");
   SQLResults res = sqlExec.runStoredProcCloseCon("jdbc_proc_test2");
   System.out.println("Your full name is " + res.getString(0, 2));
   System.out.println(res.toString());
 }
  /**
   * 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());
  }
  /**
   * 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 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");
 }
  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 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 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 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");
  }