/**
 * Syed H. Hasan <haroon@cs.columbia.edu>
 * UpdateDB will send current location info the database
 **/

import java.sql.*;

public class UpdateDB
{
    // Local variables
    public int badgeID = -1;
    public int tagID = -1;
    public String status = "";

    public UpdateDB(int newBadgeID, int newTagID, String newStatus)
    {
        badgeID = newBadgeID;
        tagID = newTagID;
	status = newStatus;
	Connection conn = null;

        try
        {
	    // Identify the DB detailes here
            /*
            String userName = "root";
            String password = "root";
            String url = "jdbc:mysql://localhost/vistaseek";
            */

            String userName = "class";
            String password = "sip"; 
            String url = "jdbc:mysql://cisalpino.cs.columbia.edu/sip";

            try
            {
                Class.forName ("com.mysql.jdbc.Driver").newInstance ();
	    }
            catch(Exception ee)
            {
                System.out.println("Driver not found");
            }

            conn = DriverManager.getConnection (url, userName, password);
            System.out.println ("Database connection established");

	    //Update DB
	    updateData(conn);

            //Show contents of table location_info, for debugging
            showData(conn);
        }
        catch (Exception e)
        {
            System.err.println ("Cannot connect to database server");
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    conn.close ();
                    //System.out.println ("Database connection terminated");
                }
                catch (Exception e) {}
             }
        }

    }// constructor

    // Updates table location_info
    public void updateData(Connection conn)
    {
        try
        {
            Statement s = conn.createStatement();
            String command = "UPDATE location_info"
		    	    +" SET tag_id = " +tagID+ ", status = '"+status+"'"
		            +" WHERE badge_id= " +badgeID;

            s.executeUpdate(command);

            s.close();
	    System.out.println("Updated table location_info\n");
        }
        catch (Exception e)
        {
            System.err.println ("Update failed");
        }
    }// updateData

    // showData goes into the DB and prints out table location_info
    public static void showData(Connection conn)
    {
        try
        {
            Statement s = conn.createStatement ();
            int count;
            
            // Extract the entire table into 'rs'
            s.executeQuery ("SELECT * FROM location_info");
            ResultSet rs = s.getResultSet ();
   	    count = 0;
            
            // Print out all the rows one by one
            System.out.println("Rows extracted from table 'location_info':");
            while (rs.next ())
   	    {
                int badgeIdVal = rs.getInt ("badge_id");
       	        int tagIdVal = rs.getInt ("tag_id");
       		String statusVal = rs.getString ("status");
       		System.out.println("badge_id = " + badgeIdVal
        	       		 + ", tag_id = " + tagIdVal
        	       		 + ", status = " + statusVal);
       	        ++count;
            }
            rs.close ();
            s.close ();
            System.out.println(count + " rows were retrieved\n");
            System.out.println("-----------------------");
        }
        catch (Exception e)
        {
            System.err.println ("Cannot show data");
        }
    }// method: showData

} //UpdateDB.java