<%@ page import="java.sql.*, java.util.*" %>
<%! String connStr, searchCondition, left, right, operator, newCondition; %>
<HTML>
<HEAD> <TITLE> The JDBCQuery JSP  </TITLE> </HEAD>
<BODY BGCOLOR=white>
<% if (connStr == null) { %>
<%@ include file="../checkConn.jsp" %>

<% } 
searchCondition = request.getParameter("cond");
if (searchCondition != null && verifyCondition(searchCondition)) {  %>
       <H3> Search results for : <I> <%= searchCondition %> </I> </H3>
       <HR><BR>
      <%= runQuery(connStr,searchCondition) %> 
<% } else if (searchCondition != null) { 
            searchCondition = ""; %>
            <h2>Invalid search condition </h2>
<%        }
     else { searchCondition = "";
     } %>

<B>Enter <a href="../conditionHelp.html">search condition</a>:</B>

<FORM METHOD=get> 
<INPUT TYPE="text" NAME="cond" SIZE=30>
<INPUT TYPE="submit" VALUE="Ask Oracle");
</FORM>
</BODY>
</HTML>
<%! 
  private String runQuery(String connStr, String cond) throws SQLException {
     Connection conn = null; 
     Statement stmt = null; 
     ResultSet rset = null; 
     try {
	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection(connStr,
                                           "scott", "tiger");

        if ((cond.equals("") || right.equalsIgnoreCase("null"))) {
          stmt = conn.createStatement();
          rset = stmt.executeQuery ("SELECT ename, sal FROM scott.emp " + 
               (newCondition == null? "" : newCondition) + " order by ename"); 
        } else {
           PreparedStatement ps = 
             conn.prepareStatement("SELECT ename, sal FROM scott.emp " + 
                                  newCondition +" order by ename");   
           ps.setString(1, right);        
           rset = ps.executeQuery();
 
        }
	return (formatResult(rset));
     } catch (SQLException e) { 
         return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
     } finally {
         if (rset!= null) rset.close(); 
         if (stmt!= null) stmt.close();
         if (conn!= null) conn.close();
     }
  }

  private String formatResult(ResultSet rset) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (!rset.next()) 	
      sb.append("<P> No matching rows.<P>\n");
    else {  sb.append("<UL><B>"); 
	    do {  sb.append("<LI>" + rset.getString(1) + 
                            " earns $ " + rset.getInt(2) + ".</LI>\n");
            } while (rset.next());
 	    sb.append("</B></UL>"); 
    }
    return sb.toString();
  }
%>
<% searchCondition=null;
   newCondition=null; %>
<%@ include file="../verifyCondition.jsp" %>
