/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author ec09002
 */
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class QuestionTable {

    public static void main(String[] args) throws SQLException {
        Connection db = null;
        String user = "vf300";
        String pass = "qmulmarina27";
        String URL = "jdbc:oracle:thin:@dbprojects.eecs.qmul.ac.uk:1521:CALVIN";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        } catch (ClassNotFoundException ce) {
            System.out.println("Error class not found: " + ce.getMessage());
            System.exit(0);
        } catch (IllegalAccessException ae) {
            System.out.println("Error illegal access: " + ae.getMessage());
            System.exit(0);
        } catch (InstantiationException ie) {
            System.out.println("Error creating: " + ie.getMessage());
            System.out.println("The Driver was not found. Please check driver location, classpath, username/password and server url settings");
            System.exit(0);
        }

        try {
            db = DriverManager.getConnection(URL, user, pass);
        } catch (SQLException e) {
        }

        Statement statement = null;

        ResultSet resultSet = null;

        ResultSetMetaData resultMetaData = null;

        try {
            System.out.println("Creating QUESTION table");
            String create = "CREATE TABLE question (question_id NUMERIC(8) PRIMARY KEY, exam_id VARCHAR2(8), question_Number VARCHAR2(2), marks_Available NUMERIC(15))";
            System.out.println(create);
            statement = db.createStatement();
            statement.executeUpdate(create);
            System.out.println("Insert person 1");
            statement.executeUpdate("insert into question (question_id, exam_id, question_Number, marks_Available) values ('1', 'ex123', 1, 15)");

            resultSet = statement.executeQuery("select * from question");
            resultMetaData = resultSet.getMetaData();
            int numCols = resultMetaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= numCols; i++) {
                    String colName = resultMetaData.getColumnName(i);
                    String colVal = resultSet.getString(i);
                    if (resultSet.wasNull()) {
                        colVal = "and up";
                    }
                    System.out.println(colName + "=" + colVal);
                }
                System.out.println("\n");
            }
        } catch (SQLException e) {
            System.out.println("SQL Error: " + e.getMessage());
        } finally {
            System.out.println("Closing Connections...");
            try {
                db.close();
            } catch (SQLException e) {
                System.out.println("Can't close connection.");
            }
        }


    }
}