Connecting Microsoft Access to Apache Tomcat

Contents

• Introduction
• Installing the Easysoft JDBC-ODBC Bridge
• Installing Apache Tomcat
• Adding JDBC data sources
• Sample Easysoft JDBC-ODBC Bridge web application
• References

Introduction

To connect Microsoft Access to Tomcat, you need a JDBC driver. Although Microsoft do not produce a JDBC driver for Access, Easysoft provide two Microsoft Access JDBC drivers. Use these JDBC drivers to connect JSP pages and servlets running under Tomcat to Microsoft Access.

Apache Tomcat is a Java Servlet/JavaServer Pages (JSP) container that provides a platform for developing and deploying web applications and web services. Many web applications need to access a database through a JDBC driver and Tomcat makes a JDBC DataSource implementation available for this purpose. If your web application needs to access a database for which only an ODBC driver is either available or suitable, a JDBC-ODBC Bridge is required. Some versions of Java include a JDBC-ODBC Bridge, however this driver is not recommended for use with Tomcat. The Apache Tomcat Database FAQ contains the following entry about the JDBC-ODBC Bridge that comes with some versions of Java:

Do not use JDBC-ODBC bridge bundled with Sun's JDK with Tomcat. It was never meant for a production server environment. If you ask a question about it, everyone will tell you to not use it. If you do need to use ODBC, there are 3rd party drivers which do a pretty good job at being thread safe.

The Easysoft JDBC-ODBC Bridge is a thread-safe JDBC-ODBC driver that our customers use in production environments. The JDBC-ODBC Bridge is compatible with Tomcat and allows Tomcat applications to access any database for which an ODBC driver is available. For example, use the JDBC-ODBC Bridge to connect web applications running under Tomcat to a Microsoft Access database.

Installing the Easysoft JDBC-ODBC Bridge

Download, install and license the JDBC-ODBC Bridge server for your database platform. For example, to connect to a Microsoft Access database, download the Windows JDBC-ODBC Bridge server. On the machine where the JDBC-ODBC Bridge server is running, configure a system ODBC data source for the database you want to access. Test the data source with an ODBC application.

For information about installing, licensing and testing JDBC-ODBC Bridge, refer to the Installation and Configuration chapters of the JDBC-ODBC Bridge User Guide.

Installing Apache Tomcat

When testing Apache Tomcat with the JDBC-ODBC Bridge, we used:

• Tomcat 7.0.19 with the J2SE Runtime Environment (JRE) 6 on Ubuntu Linux.
• Tomcat 5.5.17 with the JRE 5.0 on Red Hat Linux.

Note This article follows the Apache Tomcat documentation convention of using $CATALINA_HOME to represent the root of the Tomcat installation directory. For example, if you install Tomcat 7.0.19 in /usr/local, $CATALINA_HOME represents /usr/local/apache-tomcat-7.0.19.

Apache Tomcat prerequisites

Tomcat 7 requires JRE version 6.0 or later.

We downloaded the JRE 6 Update 26 Linux Self Extracting Installer from:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

By default, Tomcat 5.5 requires JRE version 5.0 or later. However, Tomcat can be configured to run on earlier versions of the JRE. Refer to $CATALINA_HOME/RUNNING.txt if this is a requirement.

We downloaded the JRE 5.0 Linux RPM from:

http://www.oracle.com/technetwork/java/javase/downloads/index-jdk5-jsp-142662.html

To install the JRE, set execute permission on the .bin file and then run the binary. For example:

cd /usr/local
chmod a+x /tmp/jre-6u26-linux-i586.bin
/tmp/jre-6u26-linux-i586.bin

Installing and running Apache Tomcat

1. Download the Core binary distribution from:

   http://tomcat.apache.org

2. cd to the directory where you want to install Tomcat and unpack the Core binary distribution. For example:

   cd /usr/local
   gunzip /tmp/apache-tomcat-7.0.19.tar.gz
   tar -xvf /tmp/apache-tomcat-7.0.19.tar

3. Set the JAVA_HOME environment variable to the JRE directory path and export JAVA_HOME. For example:

   JAVA_HOME=/usr/local/jre1.6.0_26/
   export JAVA_HOME

   Refer to $CATALINA_HOME/RELEASE-NOTES for information about any other environment variables you need to set for your platform.

4. Start Tomcat:

   $CATALINA_HOME/bin/startup.sh

   If you unpacked the Tomcat distribution into a directory that only root has write permission to, you need to run startup.sh as root.

   If the Tomcat server is unable to start, the default Tomcat port 8080 may already be in use. Refer to the "Troubleshooting" section in $CATALINA_HOME/RUNNING.txt for information about how to solve this and other common Tomcat installation problems.

5. In a web browser, go to the Tomcat home page at:

   http://localhost:8080

   If your web browser is not running on the same server as Tomcat, use this URL: http://tomcathost:8080. Replace tomcathost with the host name or IP address of the Tomcat server.

6. Test Tomcat by running the example web applications (available from the Examples section of the Tomcat home page.)

Installing the JDBC-ODBC Bridge JDBC driver

To make JDBC-ODBC Bridge available to Tomcat's internal classes and your web applications, copy the JDBC-ODBC Bridge client (EJOB.jar) to $CATALINA_HOME/common/lib.

On Windows, EJOB.jar is located in C:\Program Files\Easysoft\Easysoft JDBC-ODBC Bridge\jars, by default. On UNIX and Linux, EJOB.jar is located in installation_directory/easysoft/job/jars where installation_directory is the Easysoft installation directory, by default, /usr/local.

Adding JDBC data sources

To enable a web application to access an ODBC database through the JDBC-ODBC Bridge, you need to create a JDBC data source. The JDBC data source needs to specify the JDBC-ODBC Bridge JDBC driver class, easysoft.sql.jobDriver, and a JDBC-ODBC Bridge connection URL that accesses the target ODBC data source. Note that you do not need to copy the JDBC-ODBC Bridge JDBC driver to your application's WEB-INF/lib directory. The Tomcat server manages JDBC connections for web applications and will load the JDBC-ODBC Bridge driver from $CATALINA_HOME/common/lib.

JDBC data sources can be created as a web application resource or a global resource. Web application resources are only available to the application in whose context they are defined. Global resources can be used by multiple applications.

To add a JDBC data source as a web application resource, create a Resource entry in the application's context.xml file.

To use the JDBC data source, your application needs to do a Java Naming and Directory Interface (JNDI) lookup on the data source name defined in the Resource entry (for example, jdbc/MyDB). All resources are placed in the java:comp/env portion of the JNDI namespace, so to access a JDBC data source, an application would do a JNDI lookup similar to the following:

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/MyDB");

Note that if you use Tomcat 5.0 or earlier, you also need to declare the JNDI name for the data source in the application's WEB-INF/web.xml file. For example, context.xml and web.xml entries, refer to Developing and deploying the web application in the Sample Easysoft JDBC-ODBC Bridge web application section.

Global resources are outside of a Web application's context. Resource links are used to make global resources available to a web application. To access a JDBC data source defined as a global resource, an application needs to do a JNDI lookup on the resource link name rather than the data source name.

Adding global JDBC data sources by editing Tomcat configuration files

Add a <Resource> element to the GlobalNamingResources component in $CATALINA_HOME/conf/server.xml. The <Resource> element should have these attributes and values.

Add a <ResourceLink> element to $CATALINA_HOME/conf/context.xml. The <ResourceLink> element should have these attributes and values.

Adding global JDBC data sources by using the Tomcat 5.5 administration web application

Tomcat 5.5 includes an Administration web app, which allows Tomcat administrators to create JDBC data sources as global resources that are available to all applications deployed on the Tomcat server. Administrators can also use the Administration web app to override settings in a web application data source. For example, when deploying a web application, an Administrator might edit a data source to connect to a production database rather than a development version.

Installing the Administration web application

The Administration web application is not distributed with the Tomcat 5.5 binaries. If you're using Tomcat 5.5, you need to download Administration web app separately and add it to your Tomcat installation.

1. Download the Administration web application binary distribution from:

   http://tomcat.apache.org/download-55.cgi

2. Unpack the Administration web application binary distribution to a temporary directory and cd into the directory created by unpacking the distribution file. For example:

   cd /tmp
   gunzip apache-tomcat-5.5.17-admin.tar.gz
   tar -xvf apache-tomcat-5.5.17-admin.tar
   cd apache-tomcat-5.5.17

3. Copy admin.xml from the conf/Catalina/localhost subdirectory to $CATALINA_HOME/conf/Catalina/localhost:

   cd conf/Catalina/localhost
   cp admin.xml $CATALINA_HOME/conf/Catalina/localhost

4. Copy the server/webapps/admin subdirectory to $CATALINA_HOME/server/webapps/admin:

   cd /tmp/apache-tomcat-5.5.17/server/webapps
   cp -r admin $CATALINA_HOME/server/webapps/admin

5. To log into the Tomcat Administration web application, you need to associate a user with the admin role. To do this, add a user with this role to $CATALINA_HOME/conf/tomcat-users.xml. For example:

   <user username="admin" password="password" roles="admin"/>

6. Restart Tomcat:

   cd $CATALINA_HOME/bin
   ./shutdown.sh
   ./startup.sh

Adding global JDBC data sources

1. In a web browser, access the Tomcat home page at http://localhost:8080.
2. In the Administration section, click Tomcat Administration.
3. Enter the user name and password for the admin user, and then click Login.
4. In the left pane of the Administration web app, under Resources, click Data Sources.
5. In the right pane of the Administration web app, in the Data Source Actions list, click Create New Data Source.

   Complete the Data Sources form.

   To make this data source available to a web application, you need to add a resource link.

6. In the left pane of the Administration web app, under Tomcat Server, click to expand the Service list. Expand the Host list, and then expand the Context list for the application
7. Under Resources, click Resource Links.
8. In the right pane of the Administration web app, in the Resource Link Actions list, click Create New Resource Link.

   Complete the Resource Links form.

9. Click Save.
10. Click Commit Changes.

Editing JDBC data sources

1. Log into the Administration web app.
2. Do one of the following:
   • To edit a JDBC data source for a particular web application, in the left pane of the Administration web app, under Tomcat Server, click to expand the Service list. Expand the Host list, and then expand the Context list for the application. Under Resources, click Data Sources.
   • To edit a global JDBC data source, in the left pane of the Administration web app, under Resources, click Data Sources.
3. In the right pane of the Administration web app, in the Data Source list, click the data source's JNDI name.
4. Make the changes you want in the Edit Data Source page.
5. Click Save.
6. Click Commit Changes.

Sample JDBC data source for the Easysoft JDBC-ODBC Bridge

jdbc:easysoft://my_windows_server:8831/My System DSN
:logonuser=my_windows_user_name:logonpassword=my_windowspassword

If you define this JDBC data source as a global resource, you need to create a resource link for the data source:

Sample Easysoft JDBC-ODBC Bridge web application

This section shows you how to develop and deploy a simple Tomcat web application that uses JDBC-ODBC Bridge to access an ODBC database.

Installing Apache Ant

The Apache Ant build tool lets you build and install web applications that you are developing for use with Tomcat. When developing the example web application, we used Apache Ant 1.6.5 and 1.8.2.

1. Download the Ant binary distribution from:

   http://ant.apache.org/bindownload.cgi

2. On a development Tomcat server, cd to the directory where you want to install Ant and unpack the Ant distribution. For example:

   cd /usr/local
   gunzip /tmp/apache-ant-1.8.2-bin.tar.gz
   tar -xvf /tmp/apache-ant-1.8.2-bin.tar

3. Set the ANT_HOME environment variable to the Ant installation directory and export ANT_HOME. For example:

   ANT_HOME=/usr/local/apache-ant-1.8.2
   export ANT_HOME

4. Add the Ant bin directory to your path:

   PATH=${PATH}:${ANT_HOME}/bin
   export PATH

5. Set the JAVA_HOME environment variable to the JDK or JRE directory path and export JAVA_HOME. For example:

   JAVA_HOME=/usr/local/jdk1.6.0_26/
   export JAVA_HOME

Setting up the Manager Ant tasks

Tomcat includes a set of custom task definitions that allow Ant to interact with the Tomcat Manager web application. Use these tasks to install, reload, and uninstall web applications from the Ant command line.

To enable Ant to execute Tomcat Manager web application commands:

Tomcat 7

• Add a user with the manager-script role to $CATALINA_HOME/conf/tomcat-users.xml. For example:

  <user username="manager" password="password" roles="manager-script"/>

  The Manager web application runs under a security constraint that requires a user with the role manager-script to be logged in.

Tomcat 5

1. Copy $CATALINA_HOME/server/lib/catalina-ant.jar to the lib directory of your Ant installation. For example:

   cd $CATALINA_HOME/server/lib
   cp catalina-ant.jar /usr/local/apache-ant-1.6.5/lib

2. Add a user with the manager role to $CATALINA_HOME/conf/tomcat-users.xml. For example:

   <user username="manager" password="password" roles="manager"/>

   The Manager web application runs under a security constraint that requires a user with the role manager to be logged in.

Developing and deploying the web application

tomcat-odbc-demo is a simple web application that uses a JSP page to connect to a JDBC data source defined in the application's context and retrieve some data. This section describes how to create and organise the source files for tomcat-odbc-demo and use ant to install the web app on a Tomcat server.

1. On a development Tomcat server, create a top-level project directory for the web application:

   mkdir tomcat-odbc-demo

2. In the project directory, create subdirectories named src, web, and web/WEB-INF:

   cd tomcat-odbc-demo
   mkdir src
   mkdir web
   mkdir web/WEB-INF

3. Download http://tomcat.apache.org/tomcat-version-doc/appdev/build.xml.txt. Rename the file to build.xml and copy it to the project source directory:

   cd tomcat-odbc-demo
   cp /tmp/build.xml.txt build.xml

4. Open build.xml in a text editor. Add these lines to the prepare target:

   <mkdir dir="${build.home}/META-INF"/>
   <copy todir="${build.home}/META-INF" file="${basedir}/context.xml"/>

5. Configure the Ant properties defined in build.xml. To do this, create a file named build.properties in the same directory as build.xml. Add the following lines to build.properties:

   # Base name of this application, used to
   # construct file names and directories.
   app.name=tomcat-odbc-demo
   
   # Tomcat installation directory
   catalina.home=/usr/local/apache-tomcat-7.0.19
   
   # Manager webapp user name and password.
   # Ant executes Manager web app commands
   # as this user.
   manager.username=manager
   manager.password=password

   Replace the catalina.home value with the directory where the Tomcat binaries are installed.

   For Tomcat 7, replace the manager.username and manager.password values with the user name and password of a user with the role manager-script, (refer to $CATALINA_HOME/conf/tomcat-users.xml).

   For Tomcat 5, replace the manager.username and manager.password values with the user name and password of a user with the role manager, (refer to $CATALINA_HOME/conf/tomcat-users.xml).

6. Create a file named context.xml in the same directory as build.xml. Open the file in a text editor and add these lines:

   <?xml version="1.0" ?>
   <Context>
     <Resource
       name="jdbc/MyDB"
       type="javax.sql.DataSource"
       driverClassName="easysoft.sql.jobDriver"
       username="my_database_user_name"
       password="my_database_password"
       url="jdbc:easysoft://my_host:8831/my_dsn
            :logonuser=my_user_name:logonpassword=my_password"/>
   </Context>

   Replace:

   • my_database_user_name and my_database_password with a valid user name and password for the target database. If your database does not require a user name and password, omit the username and password atributes.
   • my_user_name and my_password with a valid user name and password for the machine where the JDBC-ODBC Bridge server is running.
   • my_host with the host name or IP address of the JDBC-ODBC Bridge server machine.
   • my_dsn with the system ODBC data source on the JDBC-ODBC Bridge server machine that you want to access.

   Note For readability, the JDBC URL has been split over two lines. Edit the URL so that the value occupies only one line.

   This context.xml entry defines the JDBC data source that tomcat-odbc-demo uses to access the ODBC database.

   For a complete list of configuration parameters that can be set for a JDBC data source, refer to the DBCP documentation.

7. Download http://tomcat.apache.org/tomcat-version-doc/appdev/web.xml.txt. Rename the file to web.xml and copy it to tomcat-odbc-demo/web/WEB-INF:

   cd tomcat-odbc-demo/web/WEB-INF
   cp /tmp/web.xml.txt web.xml

8. Open the web.xml in a text editor and add these lines inside the <web-app> element:

   <!-- Define the application's entry point -->
   <welcome-file-list>
       <welcome-file>tomcat-odbc-demo.jsp</welcome-file>
   </welcome-file-list>

   Tomcat uses the deployment descriptor file web.xml to find out what components a web application contains.

9. If you are using Tomcat 5.0 or earlier, add the following entry to web.xml. Otherwise, skip this step.

   <resource-ref>
       <res-ref-name>
         jdbc/MyDB
       </res-ref-name>
       <res-type>
         javax.sql.DataSource
       </res-type>
       <res-auth>
         Container
       </res-auth>
   </resource-ref>

   This entry defines the JNDI name under which the web application needs to look up the data source.

10. Create a file named tomcat-odbc-demo.jsp in tomcat-odbc-demo/web. Open the file in a text editor and add these lines:

    <html>
     <head>
       <title>Sample Easysoft JDBC-ODBC Bridge JSP Page</title>
     </head>
     <body>
       <%@ page import="javax.naming.*" %>
       <%@ page import="java.sql.*" %>
       <%@ page import="javax.sql.*" %>
    
       <h1>Sample Easysoft JDBC-ODBC Bridge JSP Page</h1>
    
       <%
    
       Connection conn = null;
       Statement stmt = null;
       ResultSet rs = null;
    
       try {
    
           // Obtain our environment naming context
           Context initCtx = new InitialContext();
           Context envCtx = (Context) initCtx.lookup("java:comp/env");
    
           // Look up our data source
           DataSource ds = (DataSource) envCtx.lookup("jdbc/MyDB");
    
           // Allocate and use a connection from the pool
           conn = ds.getConnection();
    
           // Fetch and display data
           stmt = conn.createStatement();
    
           // You need to edit this query
           rs = stmt.executeQuery("SELECT LastName FROM employees");
    
           while (rs.next()) {
               // You need to edit this column name
               String s = rs.getString("LastName");
               out.print(s + "<br>");
           }
    
           rs.close();
           rs = null;
           stmt.close();
           stmt = null;
           conn.close(); // Return to connection pool
           conn = null;  // Make sure we do not close it twice
       } catch (SQLException e) {
           out.print("Throw e" + e);
       } finally {
         // Always make sure result sets and statements are closed,
         // and the connection is returned to the pool
         if (rs != null) {
           try { rs.close(); } catch (SQLException e) { ; }
           rs = null;
         }
         if (stmt != null) {
           try { stmt.close(); } catch (SQLException e) { ; }
           stmt = null;
         }
         if (conn != null) {
           try { conn.close(); } catch (SQLException e) { ; }
           conn = null;
         }
       }
    
       %>
    
     </body>
    </html>

    You need to edit the SELECT statement in the executeQuery method and the column name in the getString method.

11. Install tomcat-odbc-demo:

    ant install

    ant install copies tomcat-odbc-demo to $CATALINA_HOME/webapps and tells Tomcat to start running the application. You can test the web application at http://localhost:8080/tomcat-odbc-demo.

12. When you have tested tomcat-odbc-demo, type ant remove to stop tomcat-odbc-demo and remove the tomcat-odbc-demo directory tree from $CATALINA_HOME/webapps.

References

• Apache Tomcat Documentation:
  • JNDI Resources HOW-TO
  • JNDI Datasource HOW-TO
  • Application Developer's Guide
• Apache Tomcat Wiki: How do I use DataSources with Tomcat?
• Creating a Web Application with Ant and Tomcat 4

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.