From Ingres Community Wiki

Contents 1 JDBC and JSP 1.1 Ingres 1.2 Tomcat 1.2.1 iijdbc.jar 1.2.2 webapps 1.3 Test it

JDBC and JSP

One of the easiest ways to create a dynamic Web page using data from a database is to embed connections and queries in a Java Server Page (JSP) and then serve it from a Java Web server for example, Apache Tomcat.

The following example describes the use of the Apache Tomcat server 5 or later with the Ingres JDBC driver to provide a simple XML fragment of address details.

Ingres

This example uses a single table

CREATE TABLE addresslist(
    address_fullname VARCHAR(32) DEFAULT ' ',
    address VARCHAR(32) DEFAULT ' ',
    city VARCHAR(32) DEFAULT ' ',
    state VARCHAR(2) DEFAULT ' ',
    zip INTEGER DEFAULT 0
)

With some sample data

insert into addresslist values ('Laszlo','1040 Mariposa Street','San Francisco','CA',94107)
insert into addresslist values ('Emma McGrattan','2950 Express Drive South','Islandia','NY',11749)
insert into addresslist values ('Ingres Corporation','2950 Express Drive South','Islandia','NY',11749)

Tomcat

iijdbc.jar

In order for a connection to be established with an Ingres DBMS access to the JDBC driver is required. Tomcat provides a directory ${CATALINA_HOME}/common/lib of global scope where the classes exposed by Java Archive (jar) files are available from the whole server.

Make a copy or a symbolic link of iijdbc.jar in the ${CATALINA_HOME}/common/lib directory. The Tomcat server may need to be restarted to include iijdbc.jar in the server CLASSPATH.

For Tomcat 6 ${CATALINA_HOME}/lib seems to be the directory where the driver should be located.

webapps

Create a subdirectory ${CATALINA_HOME}/webapps/ingdb this is where the JSP file will be located. In this directory create another subdirectory ${CATALINA_HOME}/webapps/ingdb/WEB-INF and create a web.xml file containing:

<?xml version="1.0" encoding="ISO-8859-1"?>

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">

  <display-name>Ingres Test</display-name>
  <description>
     Welcome to Ingres Test
  </description>

</web-app>

In the ${CATALINA_HOME}/webapps/ingdb create the file getaddress.jsp containing:

<%@ page import="java.sql.*"%>
<addresslist>
<%
    Connection connection = null;

    // Connection details for creating the connection string
    String serverName="localhost";  // Machine name where Ingres is installed
    String serverPortName="II7";    // Ingres instance identifier
    String DatabaseName="laszlodb"; // Database name
    String UserName="user";         // Ingres user
    String Password="password";     // credentials

    try {
        Class.forName("com.ingres.jdbc.IngresDriver");
        String dbstring = "jdbc:ingres://" + serverName + ":" + 
            serverPortName + "/" + DatabaseName;
        connection = DriverManager.getConnection(dbstring, UserName, Password);
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT address_fullname, address, city, state, zip FROM addresslist");
        while (rs.next()) {
%>
    <address fullname="<%= rs.getString("address_fullname")%>"
        address="<%= rs.getString("address")%>"
        city="<%= rs.getString("city")%>"
        state="<%= rs.getString("state")%>"
        zip="<%= rs.getString("zip")%>"/>
<%
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            connection.close();
        } catch (SQLException e) {
        }
    }
%>
</addresslist>

Customise the connection string variables to create the correct connection URL for your environment.

Test it

It should now be a matter of viewing the page from a browser using the address http://localhost:8080/ingdb/getaddress.jsp. Since the page is XML it probably won't display anything but using the browser's view source capability you should see the following:

<addresslist> 
<address fullname="Laszlo"
    address="1040 Mariposa Street"
    city="San Francisco"
    state="CA"
    zip="94107"/>
<address fullname="Emma McGrattan"
    address="2950 Express Drive South"
    city="Islandia"
    state="NY"
    zip="11749"/>
<address fullname="Ingres Corporation"
    address="2950 Express Drive South"
    city="Islandia"
    state="NY"
    zip="11749"/>
</addresslist>