import java.sql.*;


/** Simple database connector class.
  * To test, setup an ODBC database on your machine, 
  * change DATABASE to name of ODBC data source,
  * change TableName in QUERY to a table in your database,
  * compile and run.
  *
  * This can be used to test and debug Snapplets outside of Snap environment by doing:
  *   - add to Snapplet class:
  *		DatabaseConnector test = new DatabaseConnector();
  *   - add to end of Snapplet init():
  *		test.connectAndQuery("MyDatabaseName", "SELECT * FROM MyTableName");
  *		load(test.rs, "MyPrimaryKeyName");
  *
  * This can also be used to provide database connection and querying capability 
  * in a Snapplet so that it can be used independently from Snap too.
  *
  *
  * @author Nathan Conklin - nathan@conklinfamily.net
  * @author Chris North - north@cs.vt.edu
  */
public class DatabaseConnector
{

private String SERVER = "localhost";
private String DATABASE = "data.dsn"; //data source name, setup in ODBC	
private String USER = "";
private String PASSWORD = "";
private String QUERY = "SELECT * FROM TableName";

public Connection con = null;
public Statement stm = null;
public ResultSet rs = null;


/** Establish a connection to the database.  Note server not used here for jdbc:odbc. */
public Connection getConnection(String server, String database, String user, String password)
{
	String url = "jdbc:odbc:" + database;
	Driver driver = null;
	Connection connection = null;

	try // Register the JDBC Driver
	{
		String driverClass = "sun.jdbc.odbc.JdbcOdbcDriver";
		driver = (Driver) Class.forName(driverClass).newInstance();
	}
	catch (Exception e)
	{
		String err = "Cannot load the driver, reason:\n" + e.toString(); 		
		System.err.println(err);
		return null;
	}

	try // Establish a connection
	{
		if (user.length() > 0)
			connection = DriverManager.getConnection(url, user, password);
		else
			connection = DriverManager.getConnection(url);
		con = connection;
		return connection;
	}
	catch(SQLException e)
	{
		String err = "Failed to connect to " + server + "/" +
			database + "\n" + "SQLException: " + e.toString();
		System.err.println(err);
		return null;
	}
}


/** Issues an SQL query to the database connection and returns the Statement and ResultSet. */
public ResultSet getResultSet(String sqlQuery)
{
	Statement statement = null;
	ResultSet resultset = null;

	try // Execute the query
	{
		statement = con.createStatement();
		resultset = statement.executeQuery(sqlQuery);
		stm = statement;
		rs = resultset;
		return resultset;
	}
	catch(SQLException sqlex)
	{
		String err = "Failed to issue query:\n" + sqlQuery + "\n SQLException: " + sqlex.toString(); 		
		System.err.println(err);
		sqlex.printStackTrace();
		return null;
	}
}


/** Shortcut to connect and query a database. */
public ResultSet connectAndQuery(String database, String sqlQuery)
{
	getConnection("", database, "", "");
	return getResultSet(sqlQuery);
}


/** Prints out the contents of the ResultSet. */
public String printResultSet()
{
	String data = "";

	if(rs == null)
	{
		System.out.println("ResultSet is null"); 
		return "";
	}

	try{
		int columnCount = rs.getMetaData().getColumnCount();
		while (rs.next())  // for each row
		{
			String line = "";
			for (int i = 0; i < columnCount; i++ )  // for each column
			{
				line += rs.getString(i+1);
				if (i < columnCount - 1)
					line += ", ";
			}
			data += line + "\n";
		}
	}
	catch(SQLException sqlex)
	{
		System.err.println("Error reading from the ResultSet");
		sqlex.printStackTrace();
	}

	System.out.println(data);
	return data;
}


/** Close the ResultSet and database connection. */
public void close()
{
	// Close the Statement and ResultSet
	if(stm != null){
		try{
			stm.close();
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
	}

	// Close the Connection
	if(con != null){
		try{
			con.close();
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
	}
}



public void runTest()
{
	// Connect to database
	getConnection(SERVER, DATABASE, USER, PASSWORD);

	// Execute the query
	getResultSet(QUERY);

	// Read from the ResultSet here
	printResultSet();

	// Close when done
	close();
}


public static void main(String[] args)
{
	DatabaseConnector test = new DatabaseConnector();
	test.runTest();
}

};