/*
 * Sample update program
 * The program changes the section id of a section
 * It then changes the section id in all of the enroll records with that section id
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

class ChangeSectionID
{
  public static void main (String args []) throws SQLException
  {
    if(args.length!=2){
	System.out.println("Usage:  java ChangeSectionID <oldid> <newid>");
	System.exit(1);
    }

    int oldid=0;
    int newid=0;
    
    try {
	oldid = Integer.parseInt(args[0]);
	newid = Integer.parseInt(args[1]);
    }
    catch (NumberFormatException e){
	System.out.println("Section id must be an integer");
	System.exit(1);
    }

    // Get the user's name and password
    java.io.Console cons = System.console();
    cons.printf("Enter username: ");
    String uname = cons.readLine();
    cons.printf("Enter password: ");
    char[] pword = cons.readPassword();
    String pwd = "";
    for(char c : pword)
	pwd += c;

    Connection conn=null;
    try {
        // Connect to the database
	String url = "jdbc:mysql://sql.cs.oberlin.edu/studentdb?";
	String parms = "user="+uname+"&password="+pwd+"&useSSL=false";
	
	conn = DriverManager.getConnection (url+parms);

	conn.setAutoCommit(false);

	// Create a Statement
	Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
					       ResultSet.CONCUR_UPDATABLE);

	String checkString = "select count(*) from section where sectid = "+newid;
	ResultSet rset = stmt.executeQuery(checkString);
	rset.next();
	int c = rset.getInt(1);
	if(c>0){
	    System.out.println("section id "+newid+" already exists.  update aborted.");
	    System.exit(1);
	}
	rset.close();
    
	String updateString = "update section set sectid = "+newid+" where sectid = "+oldid;
	int x = stmt.executeUpdate(updateString);

	if(x==0){
	    System.out.println("No matching section tuples found");
	}
	else {
	    System.out.println(x+" section tuple updated");
	    rset = stmt.executeQuery ("select * from enroll");
	    int n = 0;
	    while (rset.next()){
		int sid = rset.getInt("sectionid");
		if(sid==oldid){
		    ++n;
		    rset.updateInt("sectionid",newid);
		    rset.updateRow();
		}
	    }
	    System.out.println(n+" enroll tuples updated");
	}
	
	// Close the Statement
	stmt.close();
	
	// Close the connection
	conn.commit();
	conn.setAutoCommit(true);
	conn.close();
    }
    catch(SQLException e) {
	conn.rollback();
	conn.setAutoCommit(true);
	conn.close();
	e.printStackTrace();
    }
    catch(Exception ee){
	conn.rollback();
	conn.setAutoCommit(true);
	conn.close();
	ee.printStackTrace();
    }
  }
}