import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import sun.jdbc.odbc.JdbcOdbcDriver;

/**
 * A flat database of sales information.
 *
 * @author Modifed by Matt Boutell, from an example used for CSSE220.
 *         Created Sep 23, 2006.
 */
public class SalesFlatDBSolution {

	/**
	 * Exectution starts here.
	 * 
	 * @param args
	 */
   public static void main (String args[]) {
	try {
		// This anonymous driver registers itself with the list of
		// drivers in the DriverManager.
		// Drivers handle the mapping of JDBC statements to implementation
		// specific ones.
		new JdbcOdbcDriver();

		// All JDBC urls are of the form jdbc:<otherprotocol>:<DBname>
		// This DBname was defined using the WindowsXP ODBC Admin tool.)
		String url = "jdbc:odbc:Sales";
	    System.out.println("Haven't made a connection yet.");

		// Searches the list of drivers for one (getting the one just
		// created above) and uses it to connect to the given url.
		Connection con = DriverManager.getConnection(url);
	    System.out.println("Made connection.");
			
	    // Performs a simple query on the database.
//ORIGINAL:	    
	    String query = "SELECT * FROM Addresses WHERE FirstName = ?";

// ANSWER TO QUESTION 1
//	    String query = "SELECT FirstName FROM Addresses "; // and comment out the stmt.setString() below.

// ANSWER TO QUESTION 2
//	    String query = "SELECT MobilePhone FROM Addresses WHERE FirstName LIKE 'J%'"; //FirstName = ?";

	    
	    PreparedStatement stmt = con.prepareStatement(query);

	    // Replaces the question mark in the query string with
	    // a parameter, to avoid SQL injection attacks
//		System.out.print("Enter a name to find: ");
//		Scanner s = new Scanner(System.in);
//		String input = s.nextLine();
//	    stmt.setString(1, input);
	    stmt.setString(1, "Lisa");

	    // Execute the query
	    stmt.execute();

	    // Process returned rows
	    ResultSet rs = stmt.getResultSet();
	    while (rs.next()) {
	    	System.out.println(rs.getString(1) /*+ " " + rs.getString(2) +
	    						" " + rs.getString(3)*/); 
	    }
	    stmt.close();
	    stmt = null;

// ANSWER TO QUESTION 3 (check insertion by looking at the DB in Access
//	    Statement stmt2 = con.createStatement();
//	    stmt2.execute("INSERT INTO Addresses VALUES ('(312) 999-5722', 'John', 'Boyle')");
//	    stmt2.close();
//	    stmt2 = null;
	    
	    con.close();
	    con = null;
	} catch (SQLException e) {
	    e.printStackTrace();
	}
  }
}