<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Final//EN">
<%@ page import="java.sql.*" %>
<HTML>
<BODY BGCOLOR="white">
<FORM ACTION="<%=request.getRequestURI() %>" METHOD="POST">
<INPUT TYPE="text" name="query" size="15" MAXLENGTH="30"
VALUE="<% if (request.getParameter("query") != null)
out.print(request.getParameter("query")); %>" %>
<INPUT TYPE="submit" VALUE="Search">
</FORM>
<TABLE BORDER="1" CELLPADDING="3" CELLSPACING="0">
<TR><TD><B>First Name</B></TD>
<TD><B>Last Name</B></TD>
<TD><B>Title</B></TD>
<TD><B>Phone</B></TD>
<TD><B>Building</B></TD>
<TD><B>State</B></TD>
<TD><B>Start Date</B></TD></TR>
<%
if (request.getParameter("query") != null) {
String url =
"jdbc:oracle:thin:@www.javadesktop.com:1521:JSPBOOK";
String id = "jsp";
String pass = "book";
String query = request.getParameter("query").toLowerCase();
String sql = "SELECT * FROM system_users,state where " +
"(LOWER(first_name) LIKE '%" + query +
"%' OR LOWER(last_name) LIKE '%" + query +
"%') AND system_users.state_init " +
"= state.state_init ORDER BY first_name";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, id, pass);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql);
while (rset.next()){
String phNum = "(" + rset.getString("phone").substring(0,3) +
")" + rset.getString("phone").substring(3,6) +
"." + rset.getString("phone").substring(6,10);
%>
<TR><TD><%= rset.getString("first_name") %>
</TD><TD><%= rset.getString("last_name") %>
</TD><TD><%= rset.getString("title") %>
</TD><TD><%= phNum %>
</TD><TD><%= rset.getString("building") %>
</TD><TD><%= rset.getString("state_name") %>
</TD><TD><%= rset.getDate("entry_date") %>
</TD></TR>
<%
}
stmt.close();
conn.close();
}
%>
</TABLE>
</BODY>
</HTML>