<%@ page import="java.io.StringWriter" %>
<%@ page import="java.io.PrintWriter" %>
<%@ page import="java.sql.*" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<html>
<head>
<title>Welcome to the Derby Tutorial Fortune Server!</title>
</head>
<style>
.hi-lite { background-color: #CCCCFF }
</style>
<body bgcolor=white link="#0066FF" vlink="#0066FF" alink="#0066FF">

<table border="0">
<tr>
<td align=center>
</td>
<td>
<h1 align=center><font color="#0066FF">Welcome to the Derby Tutorial Fortune Server!</font></h1>
<h4 align=center>
Powered by
<a href="http://incubator.apache.org/derby">Apache Derby</a>,
<a href="http://jakarta.apache.org/regexp">Jakarta Regexp</a>,
<a href="http://jakarta.apache.org/commons/math">Jakarta Mathematics Library</a>,
and
<a href="http://jakarta.apache.org/tomcat">Jakarta Tomcat</a>.
</h4>

<p>
Apache Derby is an <a href="http://db.apache.org">Apache DB</a> incubator
project.
The Derby Tutorial Fortune Server stores fortunes in a Derby database.
This page uses <a href="http://jakarta.apache.org/tomcat">Tomcat</a>
to output a fortune selected at random 
or based on a regular expression.
</p>

</td>
</tr>
</table>
<%

String category  = request.getParameter("fortune_cat");
if (category == null) {
    category = "";
}

String regExp = request.getParameter("regexp");
if (regExp == null)
{
    regExp = "";
}

String query = request.getParameter("query");
if (query == null)
{
    query= "";
}

String compareOps = request.getParameter("compare_ops");
if (compareOps == null)
{
    compareOps= "";
}

String offenseLevel = request.getParameter("offense_level");
if (offenseLevel == null)
{
    offenseLevel= "";
}
%>

<!-- retrieve the Result object from the ServletContext.  The Result object was -->
<!-- created and loaded into the ServletContext in the LoadCategories servlet.  -->
<!-- this sets the variable, resultObj to the value of the Result object -->
<c:set var="resultObj" value="${applicationScope[\"javax.servlet.jsp.jstl.sql.Result\"]}" />
<form name="sqlaccess" action="FortuneServer2.jsp">
<center>
<table cellpadding=0>
<tr>
<td valign=bottom><small><em>Choose one below:</em></small></td>
<td> &nbsp;</td>
<td valign=bottom><small><em>If regular expression chosen,<br>enter below:</em></small></td>
<td> &nbsp;</td>
<td valign=bottom><small><em>Optional category:</em></small></td>
<td> &nbsp;</td>
<td colspan="2" valign=bottom><small><em>Offense level:</em></small></td>
</tr>

<tr>
<td>
<select size="1" name="query">
<option value="random_query">
Random
</option>
<option value="regexp_query">
Regular Expression
</option>
</td>
<td> &nbsp;</td>
<td> <input type="text" value="\b(eagle|crow)\b"name="regexp">
</text>
</td>
<td> &nbsp;</td>
<td>
<select name="fortune_cat">
<option>None</option>
<%-- for each row in the Result object --%>
<c:forEach items="${resultObj.rows}" var="row">
<option>
<c:out value="${row.src}" />
</option>
</c:forEach>
</select>
</td>

<td> &nbsp;</td>

<td>
<select size="1" name="compare_ops">
<option>=</option>
<option>&lt;=</option>
</td>

<td>
<select size="1" name="offense_level">
<option>0</option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
</td>

<tr> 
<td colspan=5>&nbsp;</td>
</tr> 
<tr> 
<td colspan=2></td>
<td align=center> <input value="Fetch Fortune" type="submit"> </td> 
<td colspan=2></td>
</tr>
</table>
</center>
</form>
<p>

<%
String varJdbcUrl = "jdbc:derby:/home/jta/ApacheCon/Fortune/fortunes";
if (varJdbcUrl.length() != 0) {
    try {
        Connection varJdbcConnection = DriverManager.getConnection(varJdbcUrl);
        Statement varJdbcStatement = null;
        String sql2 = "";
        String sql_print = "";
       
        if (query.equals("random_query"))
        {

          StringBuffer sbuf2 = new StringBuffer(200);

          sbuf2.append("select fortune \nfrom fortunes \nwhere id=tutRand((select min(id) from fortunes");

          if (category.equals("None") == false)
	  {
              sbuf2.append(" where src='");
              sbuf2.append(category); 
              sbuf2.append("'");
	  }

          sbuf2.append("), (select max(id) from fortunes"); 

          if (category.equals("None") == false)
	  {
              sbuf2.append(" where src='");
              sbuf2.append(category); 
              sbuf2.append("'");
	  }


          sbuf2.append(")) \nand offense");

          sbuf2.append(compareOps);

          sbuf2.append(offenseLevel);

          sql2 = sbuf2.toString();
          sql_print = sbuf2.toString();
        }
        else if (query.equals("regexp_query"))
        {
          StringBuffer sbuf2 = new StringBuffer(200);
          StringBuffer sbuf_print = new StringBuffer(200);

          sbuf2.append("select src as Category, tutReplace(fortune, '");

          sbuf2.append(regExp);

          sbuf2.append("', '..<span class=hi-lite>$0</span>') as Fortune \nfrom   fortunes \nwhere  tutMatch(fortune, '");

          sbuf_print.append(sbuf2);
          sbuf_print.append("', '..&lt;span class=hi-lite&gt;$0&lt;/span&gt;') as Fortune \nfrom   fortunes \nwhere  tutMatch(fortune, '");

          sbuf2.append(regExp);
          sbuf_print.append(regExp);

          sbuf2.append("')=1 "); 
          sbuf_print.append("')=1 "); 

          if (category.equals("None") == false)
          {
            sbuf2.append(" and src='");
            sbuf_print.append(" and src='");

            sbuf2.append(category);
            sbuf_print.append(category);

            sbuf2.append("'");
            sbuf_print.append("'");
          }

          sbuf2.append(" \nand offense");
          sbuf_print.append(" \nand offense");

          sbuf2.append(compareOps);
          sbuf_print.append(compareOps);

          sbuf2.append(offenseLevel);
          sbuf_print.append(offenseLevel);

          sql2 = sbuf2.toString();
          sql_print = sbuf_print.toString();
       }
       
        try {
            varJdbcStatement = varJdbcConnection.createStatement();
            if (sql2.length() != 0) {
                if (varJdbcStatement.execute(sql2)) {
                    java.sql.ResultSet varJdbcResultSet = varJdbcStatement.getResultSet();
                    try {
                        java.sql.ResultSetMetaData varJdbcMetaData = varJdbcResultSet.getMetaData();
                        int varColumnCount = varJdbcMetaData.getColumnCount();
                        %>
                            <table border=1>
                            <tr>
                        <%
                        for (int i = 1; i <= varColumnCount; i++) {
                            %>
                            <th bgcolor="#0066FF" class="tab"><font color="#FFFFFF"><%= varJdbcMetaData.getColumnLabel(i) %> </font></th>
                           <%
                        }
                        %>
                            </tr>
                        <%
                        int varReturnedRows=0;
                        while (varJdbcResultSet.next()) {
                            varReturnedRows++;
                            %>
                                <tr>
                            <%
                            for (int i = 1; i <= varColumnCount; i++) {
                                %>
                                    <td valign=top><pre><%= varJdbcResultSet.getString(i) %> </pre></td>
                                <%
                            }
                            %>
                                </tr>
                            <%
                        }
                            %>
                                <tr>
                            <%
                            for (int i = 1; i <= varColumnCount; i++) {
                                %>
                                    <td  bgcolor="#0066FF"> &nbsp; </td>
                                <%
                            }
                            %>
                                </tr>
                            <%
                        %>
                            </table>
                            <font size="-1">
                        <%
                        if (varReturnedRows == 1) {
                            %>
                                1 row
                            <%
                        } else {
                            %>
                                <%=varReturnedRows %> rows
                            <%
                        }
                        %>
                            returned.</font>
                        <%
                    } finally {
                        varJdbcResultSet.close();
                    }
                } else {
                    %>
                        <font size="-1">
                    <%
                    int varProcessedRows = varJdbcStatement.getUpdateCount();
                    if (varProcessedRows == 1) {
                        %>
                            1 row
                        <%
                    } else {
                        %>
                            <%=varProcessedRows %> rows
                        <%
                    }
                    %>
                        processed.</font>
                    <%

                }

                %>
                <p> <em>SQL statement executed:</em> </p>
		<blockquote>
		<pre><%= sql_print %> </pre>
		</blockquote>
                <%
                if (query.equals("random_query"))  {
                %>
                <p> The <code>tutRand()</code> SQL function 
		    obtains a randomly generated integer by calling a method
		    in the
                    <a href="http://jakarta.apache.org/commons/math">Jakarta 
		    Mathematics Library.</a>
		</p>
                <%
		} else {
                %>
                <p> The <code>tutMatch()</code> and <code>tutReplace()</code> 
		SQL functions call methods in 
                <a href="http://jakarta.apache.org/regexp">Jakarta Regexp</a>.
                <%
		}
            }
        } catch (Exception varException) {
            StringWriter varStackBuffer = new StringWriter();
            PrintWriter varPrint = new PrintWriter(varStackBuffer);
            varException.printStackTrace(varPrint);
            %>
            <p><b>An exception was caught executing a SQL statement.</b>
            <p><pre> <%=varStackBuffer %> </pre>
            <%
        } finally {
            if (varJdbcStatement != null) {
                try {
                    varJdbcStatement.close();
                } catch (Throwable e) {}
            }
            try {
                varJdbcConnection.close();
            } catch (Throwable e) {}
        }
    } catch (Exception varException) {
        StringWriter varStackBuffer = new StringWriter();
        PrintWriter varPrint = new PrintWriter(varStackBuffer);
        varException.printStackTrace(varPrint);
        %>
        <p><b>An Exception was caught while connecting to the database.</b>
        <p><pre> <%=varStackBuffer %> </pre>
        <%
    }
}

%>
</body>
</html>
