import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
   This Servlet gets its request from a web page form which
   simply sends an SQL command.  The result is sent back as a table.
 */
public class QueryServlet extends HttpServlet {

    /**
       Location of the database.  This path is relative to
       the web app directory, in this case webapp-smit0012
     */
    private static final String DATABASE = "/Databases/sales";
    
    public void doGet(HttpServletRequest req, HttpServletResponse resp)
    	          throws ServletException, IOException {
	
	// This converts the relative path name to the absolute path for
	// this system.  Allows the web app and database to be portable
	// together

	String database = 
	    getServletConfig().getServletContext().getRealPath(DATABASE);

	resp.setContentType("text/html");
	PrintWriter out = resp.getWriter();
	Connection connection = null;

	try {
	    connection = DBConnection.getConnection(database);
	    String query = req.getParameter("select");
	    Statement statement = connection.createStatement();
	    ResultSet rs = statement.executeQuery(query);

	    // Meta data allow us to find out the number of columns in
	    // the result table

	    ResultSetMetaData rsMetaData = rs.getMetaData();
	    int cols = rsMetaData.getColumnCount();

	    // Build the html table on the fly

	    out.print("<table border = 1><tr>");
	    for (int i = 0; i < cols; i++)
		out.print("<th>" + rsMetaData.getColumnName(i+1) + "</th>");
	    out.print("</tr>");
	    while ( rs.next() ) {
		out.print("<tr>");
		for (int i = 0; i < cols; i++)
		    out.print("<td>" + rs.getString(i+1) + "</td>");
		out.println("</tr>");
	    }
	    out.print("</table>");
	}
	catch (SQLException e) {
	    e.printStackTrace();
	}

	// Make sure the database connection gets closed no matter what

	finally {
	    try {
		connection.close();
	    }
	    catch (SQLException e) {
		e.printStackTrace();
	    }
	}
	out.close();
    }

    /**
       The HTML form calls for a POST action.  We will just hand it off
       to the doGet method since that is sufficient
     */
    public void doPost(HttpServletRequest req, HttpServletResponse resp)
	           throws ServletException, IOException {
	doGet(req, resp);
    }

}