/**
 ** Conn.java
 ** Wrap database interaction in a bean
 ** @author Jack Wootton
 **/

package beans.jw;
import java.io.Serializable;
import java.sql.*;
import java.sql.Statement;

public class Conn
{
    private Connection connection = null;
    private Statement statement = null;

    public Connection getConnection() { return connection; }
    public void setConnection(Connection tmpConn) { connection = tmpConn; }
    public Statement getStatement() { return statement; }
    public void setStatement(Statement tmpState) { statement = tmpState; }
    
    public Conn()
    {	
		try
        {
             Class.forName("com.mysql.jdbc.Driver").newInstance();
			 /* use this one for university */
			 //Class.forName("org.gjt.mm.mysql.Driver");
        }
        catch (Exception e)
        {
			System.out.println("Driver error=" + e.toString());
        }
    }
    
    public boolean open()
    {
        try 
        {
            connection = DriverManager.getConnection("jdbc:mysql://localhost/forum","xxxxxx","xxxxxxx");
            /* use this one for university */
			//connection = DriverManager.getConnection("jdbc:mysql://rhino.cscs.wmin.ac.uk/w0215832","xxxxxx","xxxxxxx");
        }
        catch(Exception e)
        {
			 System.out.println("Connection error= " + e.toString());
			 return false;
        }
        return true;
    }
    
    public void close()
    {
        try
        {
            connection.close();
			connection = null;
        }
        catch(SQLException e)
        {
            System.err.println(e.toString()); 
        }
    }
    
    public boolean SQLUpdate(String sql)
    {
        try
        {
            statement = connection.createStatement();
            statement.executeUpdate(sql);
			statement.close();
			statement = null;
			return true;
        }
        catch(SQLException e)
        {
            System.err.println(e.toString());
        }
        return false;
    }
    
    public SQLResult SQL2Vec(String sql)
    {
        SQLResult query = new SQLResult();	// friendly resultset
		sql = sql.toLowerCase();			
        int columns = 0;
        ResultSet resultSet = null;			// unfriendly resultset

        try
        {
			statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);		// create the resultset
            ResultSetMetaData rsmd = resultSet.getMetaData();
            columns = rsmd.getColumnCount();
            query.setSize(columns);					// number of Field objects we need
            String[] strColNames = new String[columns];
           
			/* map database attributes to our friendly result set */
            for(int col = 0; col < columns; col++)
            {
                /* 1. create the name of Field[col] object */
				query.newAttribute(rsmd.getColumnName(col+1));
                strColNames[col] = new String(rsmd.getColumnName(col+1));
            }
            
            while(resultSet.next())	// we have data
            {
                /* map data to our attributes in friendly resultset */
				for(int col=0; col < columns; col++)
                {
					/* 2. add the data to Field[col] object */
                    query.addRecord(strColNames[col].trim(), resultSet.getString(strColNames[col]));
                }
            }
        }
        catch(SQLException e) {
            System.err.println(e.toString());	// check /Tomcat5/logs/
        }
        
        try {	// clean up after ourselves!
            resultSet.close();
            resultSet = null;
			statement.close();
			statement = null;
        } catch (Exception e) {
             System.err.println(e.toString());	// check /Tomcat5/logs/
        }
        return query;
    }
}