package dbcore;

import java.sql.*;

/**
 * Methods for connecting to a database, executing SQL, and reporting the result
 * 
 * @author (Rick Watson)
 * @version (2004.08.28)
 */

public class DatabaseAccess {
	// instance variables

	private Connection dbConnect;
	private Statement dbStatement;
	private ResultSet dbResultSet;
	private ResultSetMetaData dbResultSetMetaData;

	public DatabaseAccess() {

	}

	/**
	 * Load the driver class
	 */
	public void loadDriver(String driver) {

		try {
			Class.forName(driver);
		} catch (ClassNotFoundException error) {
			System.out.println("Could not load driver " + error.toString());
			System.exit(1);
		}
	}

	/**
	 * Make a connection to a database
	 */
	public void connectDatabase(String db, String user, String password) {
		try {
			dbConnect = DriverManager.getConnection(db, user, password);
		} catch (SQLException error) {
			System.out.println("Error connecting to database: "
					+ error.toString());
			System.exit(2);
		}
	}

	/**
	 * Execute a SELECT
	 */
	public void executeSQL(String query) {
		try {
			dbStatement = dbConnect.createStatement();
			System.out.println(query + "\n"); /* remove after debugging */
			dbResultSet = dbStatement.executeQuery(query);
		} catch (SQLException error) {
			System.out.println("Error executing SQL: " + error.toString());
			System.exit(3);
		}
	}

	/**
	 * Report a SELECT
	 */
	public void reportSQL() {
		int i;
		try {
			dbResultSetMetaData = dbResultSet.getMetaData();
			// Get the number of columns in the result set
			int numCols = dbResultSetMetaData.getColumnCount();
			// Fetch until end of the result set
			while (dbResultSet.next()) {
				// Loop through each column
				for (i = 1; i <= numCols; i++) {
					if (i > 1)
						System.out.print("  ");
					System.out.print(dbResultSet.getString(i));
				}
				System.out.println("");
			}
			dbResultSet.close();
		} catch (SQLException error) {
			System.out.println("Error displaying SQL results: "
					+ error.toString());
			System.exit(4);
		}
	}

	/**
	 * INSERT a row
	 */
	public void insertSQL(String sql) {
		try {
			dbStatement = dbConnect.createStatement();
			dbStatement.executeUpdate(sql);
		} catch (SQLException error) {
			System.out.println("Error inserting row: " + error.toString());
			System.exit(5);
		}
	}

	/**
	 * INSERT a row as part of a transaction
	 */
	public void insertTransactionSQL(String sql) throws InsertException {
		try {
			dbStatement = dbConnect.createStatement();
			dbStatement.executeUpdate(sql);
		} catch (SQLException error) {
			throw new InsertException();
		}
	}

	/**
	 * Get last id
	 */
	public int getLastId() {
		int key = 0;
		try {
			dbStatement = dbConnect.createStatement();
			dbResultSet = dbStatement.executeQuery("SELECT LAST_INSERT_ID()");
			if (dbResultSet.next()) {
				// Retrieve the auto generated key(s).
				key = dbResultSet.getInt(1);
			}
			dbStatement.close();
			dbResultSet.close();
		} catch (SQLException error) {
			key = -1;
			System.out.println("Error getting last id: " + error.toString());
			System.exit(6);
		}
		return (key);
	}

	/**
	 * close the database
	 */
	public void dbClose() {
		try {
			dbConnect.close();
			System.out.println("Database closed");
		} catch (SQLException error) {
			System.out.println("Error closing: " + error.toString());
			System.exit(7);
		}
	}

	/**
	 * Autocommit
	 */
	public void autoCommit(boolean commit) {
		try {
			dbConnect.setAutoCommit(false);
		} catch (SQLException error) {
			System.out.println("Error with autocommit" + error.toString());
			System.exit(11);
		}
	}

	/**
	 * Commit
	 */
	public void dbCommit() {
		try {
			dbConnect.commit();
			System.out.println("Transaction commit");
		} catch (SQLException error) {
			System.out.println("Error with commit" + error.toString());
			System.exit(12);
		}
	}

	/**
	 * Rollback
	 */
	public void dbRollback() {
		try {
			dbConnect.rollback();
			System.out.println("Transaction rollback");
		} catch (SQLException error) {
			System.out.println("Error with rollback" + error.toString());
			System.exit(13);
		}
	}
}