/**
 * Test case for suspected PostgreSQL JDBC driver bug
 * 
 * JDBC drivers tested: postgresql-9.3-1101.jdbc41.jar
 *                      postgresql-9.3-1101.jdbc4.jar
 *                      postgresql-9.3-1101.jdbc3.jar
 *                      postgresql-9.2-1002.jdbc4.jar
 * 
 */
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class PostgresqlJdbcTest {
   
   // This flag will run the query with a parameter when true, or the query without a parameter when false 
   public static boolean bUseParameterQuery = true;

   /* 
    * Definition of table referenced
    * 
    *   CREATE TABLE WORKS
    *      (EMPNUM   CHAR(3) NOT NULL,
    *       PNUM     CHAR(3) NOT NULL,
    *       HOURS    DECIMAL(5),
    *       PRIMARY KEY(EMPNUM,PNUM));
    *       
    *   INSERT INTO WORKS VALUES  ('E1','P1',40);
    *   INSERT INTO WORKS VALUES  ('E1','P2',20);
    *   INSERT INTO WORKS VALUES  ('E1','P3',80);
    *   INSERT INTO WORKS VALUES  ('E1','P4',20);
    *   INSERT INTO WORKS VALUES  ('E1','P5',12);
    *   INSERT INTO WORKS VALUES  ('E1','P6',12);
    *   INSERT INTO WORKS VALUES  ('E2','P1',40);
    *   INSERT INTO WORKS VALUES  ('E2','P2',80);
    *   INSERT INTO WORKS VALUES  ('E3','P2',20);
    *   INSERT INTO WORKS VALUES  ('E4','P2',20);
    *   INSERT INTO WORKS VALUES  ('E4','P4',40);
    *   INSERT INTO WORKS VALUES  ('E4','P5',80);
    *   
    *   First statement used:
    *      SELECT EMPNUM 
    *      FROM WORKS 
    *      WHERE EMPNUM > ?
    *   where the parameter is supplied the value:
    *      "E1"
    *      
    *   Second statement used:
    *      SELECT ?
    *   where the parameter is supplied the value:
    *      "E9"
    *         
    */

   // Strings for loading driver and connecting to database
   static String strDriver      = "org.postgresql.Driver";
   static String strUrl         = "jdbc:postgresql:";

   // Host variables
   static String strHostVar_1   = "E1";
   static String strHostVar_2   = "E9";

   // SQL statements
   static String strSelectSql_first_parameter = "SELECT EMPNUM FROM WORKS WHERE EMPNUM > ?"; 
   static String strSelectSql_first_no_param  = "SELECT EMPNUM FROM WORKS WHERE EMPNUM > 'E1'"; 
   static String strSelectSql_second          = "SELECT ?";

   // List of statements in the order to be executed
   static ArrayList<String> statementList = new ArrayList<>();

   // Map from statement to its prepared version
   static HashMap<String, PreparedStatement> statementMap = new HashMap<>();

   // Map from statement to its instance parameter lists
   static HashMap<String, String> parameterMap = new HashMap<>();

   // Database connection object
   static Connection connection = null;
   /**
    * Main procedure - Handles setup for database connection
    *                  and processing of SQL statements.
    *
    * @param args - Input command line arguments
    *               args[0] = the name of the database
    *               args[1] = the user name to use for logging onto the database
    *               args[2] = the password  to use for logging onto the database
    */
   public static void main(String[] args) {

      // Check for proper usage
      if (args.length != 3) {
         System.err.println("Usage: java PostgresqlJdbcTest <dbname> <user> <password>");
         return;
      }

      // Load the JDBC driver
      if (!loadDriver(strDriver)) return;

      // Connect to the database
      connection = connectURL(strUrl + args[0], args[1], args[2]);
      if (connection !=  null) {
         // Build the list of commands to be processed
         buildStatementList();

         // Process the specified SQL commands
         processSqlCommands();

         // Disconnect from database
         closeConnection();
      } // Connection established
   } // Main

   static boolean loadDriver(String strDriverName) {
      // Routine to encapsulate JDBC driver loading
      Class<?> driverClass = null;
      try {
         driverClass = Class.forName(strDriverName);
      }
      catch(ClassNotFoundException e) {
         System.err.println("Can't load driver - " + e.getMessage());
      }
      return driverClass != null;
   } // loadDriver

   static Connection connectURL(String url, String user, String pwd) {
      // Routine to encapsulate connecting to the database
      try {
         connection               = DriverManager.getConnection(url, user, pwd);
         DatabaseMetaData dbmd    = connection.getMetaData();
         String strProductName    = dbmd.getDatabaseProductName();
         String strProductVersion = dbmd.getDatabaseProductVersion();
         System.out.println("Connected to " + strProductName + " " + strProductVersion);
            String strDriverName     = dbmd.getDriverName();
            String strDriverVersion  = dbmd.getDriverVersion(); 
         System.out.println("Using " + strDriverName + " " + strDriverVersion);
      }
      
      catch(SQLException e) {
         displayDetails(e, "Can't connect to database: " + url);
      }
      return connection;
   } // connectURL

   static void closeConnection() {
      try { connection.close(); }
      catch(SQLException e) { displayDetails(e, "Can't disconnect from database"); }
   } // closeConnection

   static void buildStatementList() {
      // Build list of SQL statement in the order they should be executed
      if (bUseParameterQuery) {
         statementList.add(strSelectSql_first_parameter);
         parameterMap.put(strSelectSql_first_parameter, strHostVar_1);
      }
      else {
         statementList.add(strSelectSql_first_no_param);
         parameterMap.put(strSelectSql_first_no_param, null);
      }
      statementList.add(strSelectSql_second);
      parameterMap.put(strSelectSql_second, strHostVar_2);

      // Prepare all of the statements
      for (String strStatement: statementList) {
         prepareStatement(strStatement);
      }
      return;
   } // buildStatementList

   static void prepareStatement(String strStatement) {
      // Prepare the supplied SQL statement, and save results in statement map
      PreparedStatement pstmt = statementMap.get(strStatement);
      if (pstmt == null) {
         try {
            pstmt = connection.prepareStatement(strStatement, ResultSet.FETCH_FORWARD, ResultSet.CONCUR_UPDATABLE);
         }
         catch (SQLException e) { 
            displayDetails(e, "SQL Exception occurred while preparing statement: " + strStatement); 
         }
         statementMap.put(strStatement, pstmt);
      }
      return;
   } // prepareStatement

   static void processSqlCommands() {
      // The processing of all SQL commands is done from this procedure
      PreparedStatement pstmt;
      ResultSet rs;
      ResultSet[] aResultSet = new ResultSet[statementList.size()];
      int iCount = 0;
      for (String strStatement: statementList) {
         pstmt = statementMap.get(strStatement);
         if (pstmt != null) {
            rs = executeStatement(pstmt, strStatement);
            aResultSet[iCount] = rs;
            processResultSet(aResultSet, iCount, strStatement);
            ++iCount;
         }
      }
      return;
   } // processSqlCommands

   static ResultSet executeStatement(PreparedStatement pstmt, String strStatement) {
      // Return the result set for this statement
      ResultSet rs = null;
      try {
         System.out.println(" ");
         System.out.println("Executing statement:   " + strStatement);
         String strParameter = parameterMap.get(strStatement);
         System.out.println(" Parameter value:      " + strParameter);
         if (strParameter != null) {
            pstmt.setString(1, strParameter);
         }
         rs = pstmt.executeQuery();
      }
      catch (SQLException e) { 
         displayDetails(e, "SQL Exception occurred while executing statement: " + strStatement); 
      }
      return rs;
   } // executeStatement

   static void processResultSet(ResultSet[] aResultSet, int iCount, String strStatement) {
      // Result set processing showing problem with updating 1 of 2 open result sets
      System.out.println("Processing result set: " + iCount);
      ResultSet rs;
      String strReturn;
      String strOldValue;
      try {
         // Retrieve the first row from this result set
         rs = aResultSet[iCount];
         rs.next();
         strReturn = rs.getString(1);
         System.out.println(" Returned value:       " + strReturn);
         if (iCount % 2 == 1) {
            // Update the first result set with the value from the second result set
            rs = aResultSet[iCount - 1];
            strOldValue = rs.getString(1);
            System.out.println(" ");
            System.out.println("Updating first result set value: " + strOldValue + ", with new value: " + strReturn);
            // The next line results in SQLState: 42703 with the message:
            //    The column name pnum was not found in this ResultSet.
            rs.updateString(1, strReturn);
            System.out.println("*** Never get here for bad query ***");
         }
      }
      catch (SQLException e) {
         displayDetails(e, "SQL Exception processing result set for: " + strStatement); 
      }
      return;
   } // processResultSet

   private static void displayDetails (SQLException e, String strMessage) {
      // Display the details for this SQL exception
      System.err.println(strMessage);
      do {
         System.err.println("  SQLState: " + e.getSQLState());
         System.err.println("  SQLCode:  " + e.getErrorCode());
         System.err.println("  Message:  " + e.getMessage());
         System.err.println("");
         e = e.getNextException();
      } while (e != null);
   } // displayDetails

} // PostgresqlJdbcTest
