import java.sql.*;

public class PencilsExercise {

    public static void main(String args[]) throws Exception {

        // (A) Connect to Pencils
        Connection con;
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//        con = DriverManager.getConnection("jdbc:odbc:Pencils.mdb");
         con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; "
            + "DBQ=Pencils.mdb");

        Statement sqlStatement;
        sqlStatement = con.createStatement();

        String sqlCommand;

        // (B) View the results
        ResultSet rs;
        String colour;
        int length;

        rs = sqlStatement.executeQuery("SELECT Colour, Length FROM tblPencils");

        while (rs.next()) {
            colour = rs.getString("Colour");
            length = rs.getInt("Length");
            System.out.println("Colour: " + colour);
            System.out.println("Length: " + length);
        }

        // (C) Insert data
        sqlCommand = "INSERT INTO tblPencils VALUES('GREEN',10)";
        sqlStatement.executeUpdate(sqlCommand);

        // (D) Find longest/

         rs = sqlStatement.executeQuery("SELECT max(Length) from tblPencils");
         rs.next();
         //System.out.println(rs.getMetaData().getColumnName(1));
         int maxLength = rs.getInt(1);
         System.out.println("The longest pencil is " + maxLength);





        con.close();
    }
}