/*
 * DbConnect.java
 *
 * Created on December 7, 2004, 7:52 AM
 */
package uw.ischool.info340.labs;

import javax.naming.*;
import javax.sql.*;
import java.sql.*;

/* 
 * The program demonstrates how to connect to a postgresql database 
 * using JDBC. 
 *
 * The following things must already be in place
 *
 * (1)
 * You must know the name of the machine that is running postgresql 
 *      (e.g., serv55.ischool.washington.edu)
 * The machine must up and postgress must be running.
 *
 * (2)
 * You must have a postgresql user ID and password and have been 
 * assigned a database. 
 *
 * (3) 
 * You must have the following relation in the database
 *
 *            Table "public.state"
 * Column |         Type          | Modifiers 
 *  --------+-----------------------+-----------
 * sname  | character varying(40) | not null
 * scode  | character(2)          | not null
 *
 */

public class DbConnect
{
  /* 
   * This is the location/URL of the database. It conforms to this connection string: 
   *        jdbc:postgresql://<HOST>:<PORT>/<DB>
   *
   * Notes: 
   *    a) team12 == is the name of the database 
   *    b) 5432 is the port number -- it is optional
   *    c) serv55.ischool.washington.edu -- is the name of the host 
   */
  private static String cDB_URL_STRING		= 
        "jdbc:postgresql://linux.ischool.washington.edu:5432/dhendry";
  
  /*
   * NOTE: Check that the user ID and password are correct 
   *
   * To make the connection a UserID and password are required
   */
  private static String cUserID			= "<YOUR USER ID>";
  private static String cUserPassword		= "<YOUR DATABASE PASSWORD";
  
  /* This is the name of the JDBC driver for using postgresql */
  private static String cDRIVER_STRING  	= "org.postgresql.Driver";

  /* Holds the connection to database -- created by init() */
  /* By default, transactions are auto-committed */
  private Connection conn = null;

  /* A variable for holding a state code -- normally set by client */
  private String stateCode = "WA"; 

  /* A variable for holding a state name -- set by query to database */
  private String stateName = "unknown"; 

  /* A variable for holding the answer to an SQL query*/
  private String answer = "no answer"; 

  /* A variable for holding the query*/
  private String query = "no query"; 

  private String debugString = "";

  /* Returns information about the database connection */
  public String getConnInfo()
  {
	String  t	 = "["; 
		t       += "Driver:" 	+ cDRIVER_STRING 	+ ";";
		t 	+= "DB URL: " 	+ cDB_URL_STRING 	+ ";";
		t 	+= "UserID: " 	+ cUserID 		+ ";";
		t 	+= "Password: " + cUserPassword		+ ";";
		t       +=  "]<p>" + debugString;
	return t;
  }

  /* Returns information about the connection */
  public String connectInfo() 
  {
	  if (conn != null) 
		  return "Connection up! " + getConnInfo();
	  else
		  return "Connection DOWN " + getConnInfo();
  }
  
  public boolean isConnected() {
      return (conn == null) ? false : true; 
  }

  /* Return the answer and query strings */
  public String getAnswer() 	{ return answer; }
  public String getQuery() 	{ return query; }

  /* Setting and returning the value of state */
  public String getStateCode() 			{return stateCode; }
  public void   setStateCode(String t ) 	{stateCode = t; }

  public String getStateName()			{return stateName; }

  /* Make a simple query based on parameter which should be a state code */
  private String mkQuery(String stateCode) {
	String whereClause = "WHERE scode = '" + stateCode + "'";
	return "SELECT sname FROM state " + whereClause; 
  }

  /* Initializes the connection */
  public void init() {
    try {
      /* This statement implicitly loads the driver */
      Class.forName(cDRIVER_STRING);

      /* Now, attempt to create a connection */
      conn = DriverManager.getConnection(cDB_URL_STRING,cUserID,cUserPassword);

      if (conn == null) 
	throw new Exception("Could not connect to " + getConnInfo());
    }
    catch(Exception e) {
      debugString = e.toString();
      e.printStackTrace();
    }
  }

  /* Do a simple query */
  public void runQuery() {
    try {
        if(conn != null)  {
	    /*
	     * Create the SQL query that we would like to run
	     */
            query = mkQuery(stateCode); 

	    /*
	     * The class Statement is Used to send SQL statements to the database
	     */
            Statement stmt = conn.createStatement();

	    /*
	     * The class ResultSet is to hold the results of queries 
	     */
            ResultSet results; 

	    /*
	     * We execute a statement with the following method
	     */
            results = stmt.executeQuery(query); 

	    /*
	     * Advance to the first record and extract value 
	     */
            if(results.next()) {
	       /*
		* Retrieve the state name from the record set (a string in field #1)
		*/
               stateName = results.getString(1);
            }
	    else {
	       stateName = "Not found";
	    }
	}
    }
    catch(Exception e) {
      debugString = e.toString();
      e.printStackTrace();
    }
  }
}