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
- Download the Core binary distribution from:
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
- Set the
JAVA_HOME
environment variable to the JRE directory path and exportJAVA_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. - 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. - 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.
- 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.
- Download the Administration web application binary distribution from:
- 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
- Copy
admin.xml
from theconf/Catalina/localhost
subdirectory to$CATALINA_HOME/conf/Catalina/localhost
:cd conf/Catalina/localhost cp admin.xml $CATALINA_HOME/conf/Catalina/localhost
- 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
- 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"/>
- Restart Tomcat:
cd $CATALINA_HOME/bin ./shutdown.sh ./startup.sh
Adding global JDBC data sources
- In a web browser, access the Tomcat home page at
http://localhost:8080
. - In the Administration section, click Tomcat Administration.
- Enter the user name and password for the admin user, and then click Login.
- In the left pane of the Administration web app, under Resources, click Data Sources.
- 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.
- 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 Resource Links.
- In the right pane of the Administration web app, in the Resource Link Actions list, click Create New Resource Link.
- Click Save.
- Click Commit Changes.
Editing JDBC data sources
- Log into the Administration web app.
- 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.
- In the right pane of the Administration web app, in the Data Source list, click the data source's JNDI name.
- Make the changes you want in the Edit Data Source page.
- Click Save.
- Click Commit Changes.
Sample JDBC data source for the Easysoft JDBC-ODBC Bridge
Administration web app | <Resource> element | Value |
---|---|---|
JNDI Name | name | The name of the resource, relative to the java:comp/env context. By convention, JNDI names for JDBC data sources should resolve to the jdbc subcontext. For example, jdbc/MyDB . |
Data Source URL | url |
jdbc:easysoft://hostname:port/system_data_source
where:
For example: jdbc:easysoft://my_windows_server:8831/My System DSN :logonuser=my_windows_username:logonpassword=my_windowspassword |
JDBC Driver Class | driverClassName | easysoft.sql.jobDriver |
User Name | username | A valid user name for the target database. If your database does not require a user name, a valid user name for the JDBC-ODBC Bridge server machine. |
Password | password | The password for this user name. |
If you define this JDBC data source as a global resource, you need to create a resource link for the data source:
Administration web app | <Resourcelink> element | Value |
---|---|---|
Name | name | The local resource name, relative to the java:comp/env context. For example, ds/MyDB . |
Global | global | The global resource to link to. For example, jdbc/MyDB . |
Type | type | javax.sql.DataSource |
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.
- Download the Ant binary distribution from:
- 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
- Set the
ANT_HOME
environment variable to the Ant installation directory and exportANT_HOME
. For example:ANT_HOME=/usr/local/apache-ant-1.8.2 export ANT_HOME
- Add the Ant bin directory to your path:
PATH=${PATH}:${ANT_HOME}/bin export PATH
- Set the
JAVA_HOME
environment variable to the JDK or JRE directory path and exportJAVA_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
- Copy
$CATALINA_HOME/server/lib/catalina-ant.jar
to thelib
directory of your Ant installation. For example:cd $CATALINA_HOME/server/lib cp catalina-ant.jar /usr/local/apache-ant-1.6.5/lib
- 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.
- On a development Tomcat server, create a top-level project directory for the web application:
mkdir tomcat-odbc-demo
- In the project directory, create subdirectories named
src
,web
, andweb/WEB-INF
:cd tomcat-odbc-demo mkdir src mkdir web mkdir web/WEB-INF
- Download
http://tomcat.apache.org/tomcat-version-doc/appdev/build.xml.txt
. Rename the file tobuild.xml
and copy it to the project source directory:cd tomcat-odbc-demo cp /tmp/build.xml.txt build.xml
- Open
build.xml
in a text editor. Add these lines to theprepare
target:<mkdir dir="${build.home}/META-INF"/> <copy todir="${build.home}/META-INF" file="${basedir}/context.xml"/>
- Configure the Ant properties defined in
build.xml
. To do this, create a file namedbuild.properties
in the same directory asbuild.xml
. Add the following lines tobuild.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 username 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
andmanager.password
values with the user name and password of a user with the rolemanager-script
, (refer to$CATALINA_HOME/conf/tomcat-users.xml
).For Tomcat 5, replace the
manager.username
andmanager.password
values with the user name and password of a user with the rolemanager
, (refer to$CATALINA_HOME/conf/tomcat-users.xml
). - Create a file named
context.xml
in the same directory asbuild.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_username" password="my_database_password" url="jdbc:easysoft://my_host:8831/my_dsn :logonuser=my_username:logonpassword=my_password"/> </Context>
Replace:
my_database_username
andmy_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_username
andmy_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.
- Download
http://tomcat.apache.org/tomcat-version-doc/appdev/web.xml.txt
. Rename the file toweb.xml
and copy it totomcat-odbc-demo/web/WEB-INF
:cd tomcat-odbc-demo/web/WEB-INF cp /tmp/web.xml.txt web.xml
- 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. - 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.
- Create a file named
tomcat-odbc-demo.jsp
intomcat-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 theexecuteQuery
method and the column name in thegetString
method. - 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 athttp://localhost:8080/tomcat-odbc-demo
. - 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:
- Apache Tomcat Wiki: How do I use DataSources with Tomcat?
- Creating a Web Application with Ant and Tomcat 4