<!doctype html public "-//w3c/dtd HTML 4.0//en">
<html>
<!-- Copyright (c) 1999-2000 by BEA Systems, Inc. All Rights Reserved.-->
<head>
<title>JDBC Table Servlet</title>
</head>

<body bgcolor="#FFFFFF">
<font face="Helvetica">

<h2><font color=#DB1260>Using JSP to retrieve database data with JDBC</font></h2>

<h4>Make a selection</h4>

<p>

Choose a JDBC driver and a database name from the drop down
lists below. 

Note that to use the 'demoPool' connection pool option, you must configure
your <font face="Courier New" size=-1>weblogic.properties</font> file. The
'demoPool' connection pool properties are provided by default, but they may be
commented out.

<p> 
<form method="post" name="JdbcTable" action="JdbcTable.jsp">

<table border=0 cellspacing=2 cellpadding=2 width=80%>
<tr> 
<td width=30%><font face="Helvetica"><b>JDBC driver :</b></td>
<td><font face="Helvetica"><select name="jdbcDriver">
  <option value="COM.cloudscape.core.JDBCDriver">COM.cloudscape.core.JDBCDriver</option>
  <option value="weblogic.jdbc.pool.Driver">weblogic.jdbc.pool.Driver</option>
</select></td>
</tr>

<tr> 
<td width=30%><font face="Helvetica"><b>Database URL / Connection Pool :</b></td>
<td><font face="Helvetica"><select name="dbURL">
  <option value="jdbc:cloudscape:demo">jdbc:cloudscape:demo</option>
  <option value="jdbc:weblogic:pool:demoPool">jdbc:weblogic:pool:demoPool</option>
</select></font></td>
</tr>

<tr>
<td width=30%><font face="Helvetica"><b>Username :</b></td>
<td><font face="Helvetica"><input type="text" name="username" size=30></font></td>
</tr>

<tr>
<td width=30%><font face="Helvetica"><b>Password :</b></td>
<td><font face="Helvetica"><input type="password" name="passwd" size=30></font></td>
</tr>

<tr> 
<td width=30%><font face="Helvetica"><b>SQL Query :</b></td>
<td><font face="Helvetica"><input type="text" name="sqlQuery" size=50 value="Select * from emp"></td>
</tr>

<tr> 
<td><font face="Helvetica"><input type="Submit" value="Submit Query" name="Submit"></td>
</tr>
</table>

</form>

<hr width=80%>

<%@ page import="
javax.naming.*,
java.util.*,
java.sql.*,
weblogic.common.*
" %>

<%
  if ("POST".equals(request.getMethod())) {

    String jdbcDriver = (String) request.getParameter("jdbcDriver");
    String dbURL = (String) request.getParameter("dbURL");
    String sqlQuery = (String) request.getParameter("sqlQuery");
    String username = (String) request.getParameter("username");
    if (username != null && username.equals(""))
        username=null;
    String passwd = (String) request.getParameter("passwd");
    if (passwd != null && passwd.equals(""))
        passwd = null;
%>

<h2>Results from previous query:</h2>

Here are the results from the previous SQL query using the these parameters:

<ul>
<li> JDBC Driver: <%= jdbcDriver==null?"No driver specified.":jdbcDriver %>
<li> Database URL: <%= dbURL==null?"No URL specified":dbURL %>
<li> SQL query: <%= sqlQuery==null?"No SQL query":sqlQuery %>
<li> Username: <%= username==null?"<i>No username supplied</i>":username %>
<li> Password: <%= passwd==null?"<i>No password supplied</i>":passwd %>
</ul>
<p>
<%

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      Class.forName(jdbcDriver).newInstance();
      if ((username != null) && (passwd != null))
        conn = DriverManager.getConnection(dbURL, username, passwd);
      else
        conn = DriverManager.getConnection(dbURL, null);
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sqlQuery);

      ResultSetMetaData rsmd = rs.getMetaData();
      int numCols = rsmd.getColumnCount();
%>

<p>
<center>
<table border=1 cellspacing=2 cellpadding=0 width=400>
<tr>

<%
    for (int i = 1; i <= numCols; i++) {
%>

<td><font face="Helvetica"><b><%= rsmd.getColumnLabel(i) %></b></td>

<%
    }
%>

</tr>

<%
    while (rs.next()) {
%>

<tr> 

<%
      for (int i = 1; i <= numCols; i++) {
%>

<td><font face="Helvetica"><%= rs.getString(i) %></td>

<%
      } 
%>

</tr>

<%
    } 
  } 
  catch (Exception e) {
%>

<p><b>There was an error executing or processing the query:</b>
<br>
Exception: <%= e %>	
<pre><%= getStackTraceAsString(e) %></pre>

<%
  } 

  finally {
    try {
      rs.close();
      stmt.close();
      conn.close();
    } 
    catch (Exception e) {
      out.print("<b>There was an error closing the database connection</b>");
      out.print("<br>Exception: " +e);	
      out.print("<br><pre>"+getStackTraceAsString(e)+"</pre>");
    } 
  } 
}
%>

</table>
</center>
<p>
<font size=-1>Copyright (c) 1999-2000 by BEA Systems, Inc. All Rights Reserved.
</font>

</font>
</body>
</html>

<%!

  String getStackTraceAsString(Exception e)
  {
    // Dump the stack trace to a buffered stream, then return it's contents
    // as a String. This is useful for printing the stack to 'out'. 
    ByteArrayOutputStream ostr = new ByteArrayOutputStream();
    e.printStackTrace(new PrintStream(ostr));
    return(ostr.toString());
  }

%>
