package examples.tutorial.derby;

import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.sql.*;
import javax.servlet.jsp.jstl.sql.Result;

public class LoadCategories extends HttpServlet
{
    private String varJdbcUrl = "jdbc:derby:/home/jta/ApacheCon/Fortune/fortunes";
    private javax.servlet.jsp.jstl.sql.Result res = null;

    public void init() throws ServletException
    {

        // issue the JDBC query to get the categories in the fortunes table
        getCategories();
        // place the result object in the servletContext.
        getServletContext().setAttribute("javax.servlet.jsp.jstl.sql.Result", res);
    }

    private void getCategories() throws ServletException
    {
        String categorySQL = "select distinct(src) from fortunes order by src";

        try
        {
            ResultSet rs = null;
            Connection conn = null;

            conn = DriverManager.getConnection(varJdbcUrl);
            if (conn == null)
            {
                throw new SQLException("Invalid connection in select method.");
            }

            PreparedStatement stmt = conn.prepareStatement(categorySQL);
            rs = stmt.executeQuery();

            if (rs != null)
            {
                // convert the result set to a Result object that
                // can be used with the JSTL tags
                res = javax.servlet.jsp.jstl.sql.ResultSupport.toResult(rs);
                stmt.close();
                conn.close();
            }
            else
            {
                throw new ServletException("Database query unsuccessful\n");
            }
        }
        catch(SQLException sqle)
        {
            throw new ServletException(sqle.getMessage());
        }
  }	
}
