Easysoft JDBC-ODBC Bridge Programming Guide

This guide is aimed at Java developers who wish to add JDBC connectivity to their applications using the Easysoft JDBC-ODBC Bridge.

Introduction

The Easysoft JDBC-ODBC Bridge (JOB) comes in two components:

The JOB Server incorporates a lightweight HTTP server ('webserver') that provides access to administration, monitoring and test facilities through a standard web browser interface.

Assumptions

This guide assumes the JOB Server has been successfully installed and configured. You will also need to know the hostname for the JOB Server to which you intend to connect.

If you need to install the JOB Server refer to the Getting Started Guide.

Installing the JOB Driver Classes

The classes that make up the driver are in the Java archive file EJOB.jar. The driver class is easysoft.sql.jobDriver.

Java Applications

Java Applets

Loading the JOB Classes

You can load the JOB Driver classes in your code using:

Class.forName("easysoft.sql.jobDriver").newInstance();

Alternatively you could use:

Class.forName("ez").newInstance();

which in turn will instantiate the easysoft.sql.jobDriver class.

Connecting to an ODBC Data Source with the JOB Driver

To connect to an ODBC data source with the JOB driver, use a connection URL of the form:

jdbc:easysoft://[<hostname>][:<port>]/<odbc-data-source>[;<odbc-driver-attribute>=<value>]*[:<easysoft-jdbc-url-attribute>=<value>]*

where:

Example URLs:

// Connect to a Microsoft Access ODBC Data Source
String jobUrl = "jdbc:easysoft://localhost/Northwind-ODBC-Data-Source";
jobUrl = jobUrl + ":logonuser=mywindowsuser:logonpassword=mywindowspassword";
Connection con = DriverManager.getConnection(jobUrl);
// Connect to a Microsoft Access ODBC Data Source and pass an ODBC driver attribute
// on the connection string.
String jobUrl = "jdbc:easysoft://localhost/DSN=Northwind-ODBC-Data-Source;ReadOnly=1";
jobUrl = jobUrl + ":logonuser=mywindowsuser:logonpassword=mywindowspassword";
Connection con = DriverManager.getConnection(jobUrl);
// Connect to a Microsoft Access database with a DSN-less connection.
String jobUrl = "jdbc:easysoft://localhost/Driver={Microsoft Access Driver (*.mdb)};";
jobUrl = jobUrl + "DBQ=C:\\Program Files\\Microsoft Office\\Office\\1033\\Nwind2000.mdb;";
jobUrl = jobUrl + "ReadOnly=1;logonuser=mywindowsuser;logonpassword=mywindowspassword";
Connection con = DriverManager.getConnection(jobUrl);
<-- Connect to a Microsoft Access database with a DSN-less connection. -->
<PARAM NAME = "jobUrl" VALUE = "jdbc:easysoft://localhost/Driver={Microsoft Access Driver (*.mdb)};
                                DBQ=C:\Program Files\Microsoft Office\Office\1033\Nwind2000.mdb;
                                ReadOnly=1;logonuser=mywindowsuser;logonpassword=mywindowspassword"></PARAM>
// Connect to a Microsoft Access ODBC File Data Source
String jobUrl = "jdbc:easysoft://localhost/FILEDSN={C:\\Temp\\NorthwindFileDSN.dsn}";
jobUrl = jobUrl +  ":logonuser=mywindowsuser:logonpassword=mywindowspassword";
Connection con = DriverManager.getConnection(jobUrl);

Easysoft JDBC URL Syntax

The JOB Driver uses the easysoft subprotocol. URLs for this subprotocol are of the form:

<url> ::= jdbc:easysoft:[<server spec>][<database>]{:<attribute>=<value>}*
  <server spec> ::= //[<host name>][:<port>]/
  <database> ::= <dsn> | DSN=<dsn> | FILEDSN={<filedsn>} | <dsn-less connection> 

where | separates optional items, [ ] denotes an optional item and { }* denotes zero or more occurrences.

Easysoft JDBC URL Attributes

Attribute Description
:user Database user ID
:password Database password
:logonuser Remote system user ID
:logonpassword Remote system password
:clearText=on|off Turn on to disable the default encrypted transmission of connection information (connection string, user names and passwords) over the network (useful for debugging purposes)
:limit The maximum number of rows returned in any result set
:trace=on|off Turn on to set tracing to stdout in the client
:multi=on Prefix each line in a trace with the thread ID
:fetchSize=0 Turn off retrieval of columns in blocks
:unicode=on|off Turn on to retrieve Unicode data from Unicode fields

Additional Notes

host name and port describe how to access the remote machine where the JOB Server is running. They must be specified explicitly in the JDBC URL if you wish to connect to a remote JOB Server.

user and password are given to the ODBC driver as UID and PWD. They must be used if the database engine requires authentication.

logonuser and logonpassword specify an existing database user on the target machine if the database engine requires that the connecting process (in this case the JOB Server) be logged in as a particular user. This will only work if the JOB Server is run as a service using Logon As: System Account on Windows or root on Unix.

limit is used to restrict the number of rows that will be returned in any result set. The limit is actually set in the ODBC driver by the JOB Server as statement attribute SQL_ATTR_MAX_ROWS, so it is supported only if your ODBC driver supports it.

unicode enables the use of one of the two getString methods in the ResultSet class to read Unicode data and the setString method in the PreparedStatement class to write Unicode data. Read and write statements to metadata table, row and column names containing Unicode characters are not supported, unless those names are restricted to ASCII characters only and the executeQuery method in the Statement class does not support SQL containing Unicode character strings. Note that Unicode data can also be be read by using one of the two getUnicodeStream methods in the ResultSet class and written by using the setUnicodeStream method in the PreparedStatement class in addition to the getstring and setstring methods.

Easysoft JOB URL Examples

URL Description
jdbc:easysoft:northwind Connect to the host from which the ez.class resource is obtained. This could be the local host for applications or the host from which the applet is downloaded, and will establish a connection to an ODBC data source named "northwind", in the same way as the Sun JDBC-ODBC Bridge.
jdbc:easysoft:DSN=northwind Equivalent to the above.
jdbc:easysoft: Connect to the host, but since no database is specified, a list of available DSNs will be presented in a dialog box.
jdbc:easysoft:FILEDSN={C:\\Temp\\NorthwindFileDSN.dsn} Connect to a file DSN data source, rather than a system DSN data source.
jdbc:easysoft://:8899/northwind Connect to a non-default port.
jdbc:easysoft://demo.easysoft.com/northwind Connect to the Easysoft JDBC-ODBC Bridge server at Easysoft.

Minimal Java Applet Demonstration

This is a very simple demonstration of an applet connecting to an ODBC data source using the Easysoft JDBC-ODBC Bridge. The APPLET tag used is shown here:

<APPLET CODEBASE = "./"
        CODE = "QueryApplet"
        ARCHIVE = "../jars/EJOB.jar" 
        WIDTH = "340"
        HEIGHT = "240">
     <PARAM NAME = "jdbcUrl" VALUE = "jdbc:easysoft:" />
     <PARAM NAME = "query" VALUE = "tables" />
</APPLET>

The CODE tag specifies the QueryApplet.class class file for the applet code.

The ARCHIVE tag specifies the "../jars/EJOB.jar" Java archive file that contains the Easysoft JDBC-ODBC Bridge driver's classes.

The applet takes two parameters:

You can view the source in HTML format.

When the page is loaded the applet is started and if the JOB Server is running it will popup a dialog box allowing a selection of available ODBC data sources to which to connect:

Query applet ODBC data source dialog box

Upon successful connection, the result of running the pseudo-query tables is displayed:

Pseudo-query result