<!-------------------------------------------------------------------
$name: QueryPortal.jsp
$version: 1.0
$date_modified: 03/00/2001
$description: Implements an SQL reporting portal using Novell's JDBC driver.
$owner: JDBC Driver Product Manager
Copyright (c) 2001 Novell, Inc. All Rights Reserved.

THIS WORK IS SUBJECT TO U.S. AND INTERNATIONAL COPYRIGHT LAWS AND TREATIES.
USE AND REDISTRIBUTION OF THIS WORK IS SUBJECT TO THE LICENSE AGREEMENT
ACCOMPANYING THE SOFTWARE DEVELOPMENT KIT (SDK) THAT CONTAINS THIS WORK.
PURSUANT TO THE SDK LICENSE AGREEMENT, NOVELL HEREBY GRANTS TO DEVELOPER A
ROYALTY-FREE, NON-EXCLUSIVE LICENSE TO INCLUDE NOVELL'S SAMPLE CODE IN ITS
PRODUCT. NOVELL GRANTS DEVELOPER WORLDWIDE DISTRIBUTION RIGHTS TO MARKET,
DISTRIBUTE, OR SELL NOVELL'S SAMPLE CODE AS A COMPONENT OF DEVELOPER'S
PRODUCTS. NOVELL SHALL HAVE NO OBLIGATIONS TO DEVELOPER OR DEVELOPER'S
CUSTOMERS WITH RESPECT TO THIS CODE.
---------------------------------------------------------------------->

<!-- This Java Server Page is a sample of using JDBC to create a personalized
portal.  It is creates a portal that will let a user log in, run SQL queries,
and read schema's classes and attributes (as Tables and Rows).

  The intent of this program is to help rapid development of similar portals
that need LDAP directory content.  This is a complex example and for basic JDBC
programming with LDAP we recommend looking at other examples.

In the Framework of this program, there exist four states or views:
1)  Login: No connection exists.  A form is presented to gather the information
    needed to create a connection.  (the parameters are 'URL', 'USERDN',
    and 'PASSWORD')

2)  Create Connection: No connection exists; but the information needed to
    create a connection has been passed in.  A connection is attempted.

3)  Query Forms:  A connection exists.  A list of tables and an SQL query form
    are presented.

4)  Query Results:  A connection exists.  In addition to the forms from state
    3, query results are displayed.  If the parameter 'TABLE' is defined then
    the columns of that table are listed.  If the parameter 'SQL' is defined
    then the sql query is processed and displayed.

(if the parameter 'LOGOUT' exists then the connection is cleared and set back
to state 1.a)       -->

<%@ page
    errorPage="err.jsp"  
    language="java"
    import="java.sql.*"
%>
<html>
<Title>Novell OneNet: JDBC Query Portal</Title>
<h1><center><font color="red">Novell</font> OneNet</center></h1>
<h2><center>JDBC Query Portal</center></H2>
<%!
    String ipaddress = "localhost:8080";   //host of this page
    String jspPath = ipaddress + "/examples/jsp/QueryPortal.jsp";
%>

<%
	//Optional: Place the driver name in the Properties file, instead of
	//loading it here.
    Class.forName("com.novell.sql.LDAPDriver");

        //The following objects are not put in the declaration code because
        //they need to be unique to each thread:
    Connection conn;
    Statement statement;
    ResultSet rs;
    ResultSetMetaData rsmd;
    DatabaseMetaData  dbmd;
    String sql, url, userdn, password, tableName;

    sql = request.getParameter("SQL");
    conn = (Connection)session.getValue("CONNECTION");

        //if a Logout was requested then reset everything
        //thus the login view will show up only
    if (request.getParameter("LOGOUT") != null){
        if (conn != null)
            conn.close();
        session.invalidate();
        conn = null;
    }
    if (conn == null){
        url     = request.getParameter("URL");
        userdn  = request.getParameter("USERDN");
        password= request.getParameter("PASSWORD");
        if (url == null) {  %>
            <!-- STATE 1, Login -->
            <form method="Post" action="http://<%= jspPath %>">
                <center>
                <Table border=0>
                    <TR><TD>JDBC URL<TD>
                 <TEXTAREA name="URL" wrap="soft" cols=40
rows=4>JDBC:LDAP://localhost
baseDN=dc=com;
useCleartext=true</TEXTAREA>
                    </TR>
                    <TR><TD>Distinguished name<TD><input name="USERDN"
                        value="cn=Reporter,dc=company,dc=com" size=30></TR>
                    <TR><TD>Password <TD><input type="password" name="PASSWORD"
                        size=30></TR>
                    <TR><TD><input type="reset" value="Reset Values"><TD><input
                        type="submit" value="Submit"></TR>
                </Table>
                </center>
            </form>
        <%
        }
        else{
        	//STATE 2, Create Connection
            conn = DriverManager.getConnection(url, userdn, password);
            session.putValue("CONNECTION", conn);
	}
    }

    if (conn != null){  %>
        <!--STATE 3, Query Forms for Tables and SQL -->
        <center>
            <form method="Post" action="http://<%= jspPath %>" align="CENTER">
            <input type="hidden" name="LOGOUT" value="LOGOUT">
            <input type="submit" value="Log Out">
            </form>
        </center><HR>
        <table align="CENTER"> <!--Forms are put in a table for appearences -->
            <TR>
                <TH>Tables</TH><TH>SQL Query</TH>
            </TR>
            <TR>
                <TD align="CENTER">
                    <!--Table form -->
                    <form method="Post" action="http://<%= jspPath %>">
                    <Select name="TABLE" size=10>
                        <%
                          //Display all the table names in an option list
                        dbmd = conn.getMetaData();

                          //"?" is a wild card to retrieve all tables
                        rs = dbmd.getTables(null, null, "?", null);

                        while (rs.next()){
                           tableName = rs.getString("NAME");   %>
                           <Option value="<%= tableName %>"><%= tableName %>
                        <% } %>
                    </select>
                    <P><input type="submit" value="Get Columns">
                    </form>
                </TD>
                <TD align="CENTER">
                    <!--SQL Form -->
                    <form method="Post" action="http://<%=jspPath%>">
                    <textarea rows=10 cols=30 wrap="soft" name="SQL" ><%
                    	  //use the previous SQL statement, or supply a sample one.
                        if (sql != null) {
                            %><%= sql %>
                        <% }
                        else {
                            %>SELECT cn, sn, fullName FROM inetOrgPerson<%
                        } %>
                     </TEXTAREA>
                     <p><input type="submit" value="Submit">
                    </form>
                </TD>
            </TR>
        </table>
<HR><BR>
<%
    //STATE 4: Query results
        if (sql != null) {
        //Execute SQL Query:
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            rsmd = rs.getMetaData();
%>
            <BR>
            <Table width="95%" Border="+5" align="CENTER">
            <TR>
                <TH colspan="<%= rsmd.getColumnCount() %>">
                    <Font color="red">Novell</font> eDirectory
            </TR>
            <TR>
               <% for(int i=1; i <= rsmd.getColumnCount() ; i++){ %>
                    <TH><%= rsmd.getColumnLabel(i) %> </TH>
               <% } %>
            </TR>
            <% while(rs.next()) { %>
                <TR>
                <% for(int i=1; i <=rsmd.getColumnCount() ; i++) { %>
                        <TD> <%= rs.getString(i) %> </TD>
                <% } %>
                </TR>
            <% } %>
            </table>

<%      }//end Execute SQL Query

        tableName = request.getParameter("TABLE");
        if (tableName != null){
        //LIST TABLE COLUMNS:    %>
            <Table width="70%" Border="+5" align="CENTER">
            <TR><TH colspan=2>Table: <%=tableName %></TR>
            <TR><TH>Column<TH>Type</TR>
         <% rs = dbmd.getColumns(null, null, tableName, "?");
            while (rs.next()){   %>
              <TR>
                 <TD><%= rs.getString(4) %><%--column #4 equals column name --%>
                 <TD><%= rs.getString(6) %><%--column #6 equals column type --%>
              </TR>
            <%
            }//end while
        }//end List TABLE COLUMNS
    }//end else conn != null
%>
</body></html>
