package iDSS.database;

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.text.DateFormat;



public class DatabaseAccess {

      private Connection aConnection;
      //private DatabaseConnection databaseConnection;
      private String aQuery;

      Statement aStatement;
      ResultSet aResultSet;

      String url;


      public DatabaseAccess(String sqlQuery, String dataSource, String dataUserName,
                    String dataPassword, String databaseDriver) {

         aQuery = sqlQuery;

         url = dataSource;
         String username = dataUserName;
         String password = dataPassword;

         /****** Load the drive to allow connection to the database ********/

/**
        databaseConnection = new DatabaseConnection();
        aConnection = databaseConnection.getConnection();

        if(aConnection == null)
            System.out.println("DatabaseAccess, aConnection = null");
**/


      	try {
      	    Class.forName(databaseDriver);
      	}
      	
      	catch(java.lang.ClassNotFoundException e) {

            JOptionPane.showMessageDialog(null,"DatabaseAccess.constructor()\n"+e.getMessage(),
                                        "ClassNotFoundException",JOptionPane.ERROR_MESSAGE);

      	}


            //set connection
              try {
	    
          	    // Establish Connection to the database at URL with usename and password
                aConnection = DriverManager.getConnection(url, null, null); 
  	            System.out.println ("Connection established to the database " + url);

              }
              
              catch(SQLException ex) {
                  System.err.println("==> SQLException: ");



                    JOptionPane.showMessageDialog(null,"DatabaseAccess.constructor()\n"+
                                                ex.getMessage()+"\n"+
                                                ex.getSQLState()+"\n"+
                                                ex.getErrorCode(),
                                                "SQLException",JOptionPane.ERROR_MESSAGE);


                    return;
/**
          		  System.out.println("Message:   " + ex.getMessage ());
    	      	  System.out.println("SQLState:  " + ex.getSQLState ());
    		        System.out.println("ErrorCode: " + ex.getErrorCode ());
        		    ex = ex.getNextException();
        		    System.out.println("");
**/


              }



         try {
            aStatement = aConnection.createStatement();
            aResultSet = aStatement.executeQuery(aQuery);
         }

         catch (SQLException sqlex) {

            JOptionPane.showMessageDialog(null,"DatabaseAccess.constructor()\n"+sqlex.getMessage(),
                                        "SQLException",JOptionPane.ERROR_MESSAGE);


            //System.out.println("SQLException : " + sqlex);
         }


      } // End of DatabaseAccess()




      public ResultSet getResultSet() {

        return aResultSet;

      }





    public Vector getVector(String columnName) {

         Vector resultVector = new Vector();

         try {
            boolean moreRecords = aResultSet.next();
         

             if (! moreRecords) {


                JOptionPane.showMessageDialog (null,"DatabaseAccess.getVector()\n"+
                                                "ResultSet contained no records",
                                                "No record",JOptionPane.INFORMATION_MESSAGE);
                return null;
             }



            while(aResultSet.next()){
                resultVector.add(aResultSet.getString(columnName));
            }


         } //try

         catch (SQLException sqlex) {

            JOptionPane.showMessageDialog(null,"DatabaseAccess.getVector()\n"+sqlex.getMessage(),
                                        "SQLException",JOptionPane.ERROR_MESSAGE);


            //System.out.println("SQLException : " + sqlex);
         } //catch (SQLException sqlex)

         return resultVector;

    } //public Vector getVector()








    public Vector getColumnNamesVector() {


      Vector columnHeads = new Vector();

      try {
        ResultSetMetaData rsmd = aResultSet.getMetaData();
        System.out.println("Column count = " + rsmd.getColumnCount());
        for (int i = 1;i<=rsmd.getColumnCount();++i) {
           columnHeads.addElement(rsmd.getColumnName(i));
           System.out.println("Column name = " + rsmd.getColumnName(i));
        }
      }


      catch(SQLException sqlex) {

        JOptionPane.showMessageDialog(null,"DatabaseAccess.getColumnNamesVector()\n"+sqlex.getMessage(),
                                    "SQLException",JOptionPane.ERROR_MESSAGE);

         //sqlex.printStackTrace();
         //System.out.println("SQLException occured");
         return null;
      }


      return columnHeads;

    } //public Vector getColumnNamesVector()








    public Vector getRowDataVector() {


       Vector rows = new Vector();

       try {
         ResultSetMetaData rsmd = aResultSet.getMetaData();
         while(aResultSet.next()){
             rows.addElement(getNextRow(aResultSet,rsmd));
          }
       }

      catch(SQLException sqlex) {

        JOptionPane.showMessageDialog(null,"DatabaseAccess.getRowDataVector()\n"+sqlex.getMessage(),
                                    "SQLException",JOptionPane.ERROR_MESSAGE);


         //sqlex.printStackTrace();
         //System.out.println("SQLException occured");
         return null;
      }

       return rows;

    } //public Vector getRowDataVector()







      private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException {

         Vector currentRow = new Vector();
         for (int i = 1;i<=rsmd.getColumnCount();++i)
            switch(rsmd.getColumnType(i)) {
               case Types.VARCHAR:
                  currentRow.addElement(rs.getString(i));
                  //System.out.println(rs.getString(i));
                  break;
               case Types.CHAR:
                  currentRow.addElement(rs.getString(i));
                  break;
               case Types.INTEGER:
                  //currentRow.addElement(new Long(rs.getLong(i)));
                  currentRow.addElement(new Long(rs.getInt(i)));
                  //PoolMan doesn't support ResultSet.getLong()
                  break;
               case Types.DOUBLE:
                  currentRow.addElement(new Double(rs.getDouble(i)));
                  break;

               case Types.FLOAT:
                  currentRow.addElement(new Float(rs.getFloat(i)));
                  break;

               case 93:
                  currentRow.addElement(Timestamp.valueOf(rs.getString(i)));
                  break;

               default:
                  System.out.println ("Type was: " + rsmd.getColumnTypeName(i) +"," + rsmd.getColumnType(i));
                  
            }
         return currentRow;
            
      } //End of getNextRow








      public void shutDown() {

/***
        if(databaseConnection != null)
            databaseConnection.returnConnection();
        else
            System.out.println("DatabaseAccess.shutdown(), DatabaseConnection was never created");
**/


         try {
            aConnection.close();
            System.out.println("Database connection to " + url + " closed.");
         }
         catch(SQLException sqlex) {

            JOptionPane.showMessageDialog(null,"DatabaseAccess.shutDown()\n"+sqlex.getMessage(),
                                    "SQLException",JOptionPane.ERROR_MESSAGE);

            //System.err.println("Unable to disconnect");
            //sqlex.printStackTrace();
         }



      } //End of shutDown






      public static void main(String args[]) {

         String query = "Select * from stationList";
         System.out.println("query = " + query);

/**
         DatabaseAccess dataAccess = new DatabaseAccess (query,"jdbc:odbc:LocalTidalData",
                                     null,null,"sun.jdbc.odbc.JdbcOdbcDriver");

**/

         DatabaseAccess dataAccess = new DatabaseAccess (query,"jdbc:mysql://localhost:3306/tideDBLocal",
                                     null,null,"org.gjt.mm.mysql.Driver");

        Vector aVector = dataAccess.getVector("stationName");
        Enumeration enum = aVector.elements();

        while (enum.hasMoreElements()) {
          System.out.println((String) enum.nextElement());
        }


        dataAccess.shutDown();

      } //End of main()
      

} //End of class
