import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/* stored procedure
 *
 * CREATE PROCEDURE sp_GetDoctor
 * 		@Spec varchar(30)
 * AS
 * 		SELECT FNAME, LNAME
 * 		FROM DOCTOR
 * 		WHERE Speciality = @Spec
 */

/* to test
 * EXEC sp_GetDoctor @Spec = 'Dermatology';
 *
 * to give permissions to user
 * GRANT EXECUTE ON sp_GetDoctor TO Hibbert;
*/

public class Test {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String url = "jdbc:sqlserver://cit281spring18.cjntucuhbeao.us-west-2.rds.amazonaws.com:1433;database=Exam2;user=Hibbert;password=password";
		try {
			//#1 Create Connection
			Connection conn = DriverManager.getConnection(url);
			//#2 Execute query
			//Statement stmt = conn.createStatement();
			CallableStatement stmt = conn.prepareCall("{call sp_GetDoctor(?)}");
			stmt.setString(1, "Dermatology");
			//String search = "LName, FName";
			//String sql = "SELECT "+search+" FROM people.Doctor";
			//stmt.executeQuery(sql);
			stmt.executeQuery();
			//#3 Get result set
			ResultSet results = stmt.getResultSet();
			while(results.next())
				System.out.println(results.getString(2));
			//#4 Close connection
			conn.close();

			System.out.println("Connected!");
		} catch (SQLException e) {
			System.out.println("Could not connect");
			e.printStackTrace();
		}
	}
}