Easysoft JDBC-ODBC Bridge programming guide

Contents

Introduction

The Easysoft JDBC-ODBC Bridge has two components:

The JDBC-ODBC Bridge server includes a lightweight HTTP server that lets you administer and monitor the bridge by using a web interface.

Assumptions

This guide assumes the JDBC-ODBC Bridge server has been successfully installed and configured. You need to know the name or IP address of the machine where the JDBC-ODBC Bridge server is installed.

Installing the JDBC-ODBC Bridge client classes

The JDBC-ODBC Bridge classes are in the Java archive file EJOB.jar. The driver class is easysoft.sql.jobDriver.

Java applications

Java applets

For applet deployment, copy your applet and EJOB.jar to a directory that's readable by your web server. Add the appropriate ARCHIVE attribute for EJOB.jar to your APPLET tag. For example:

<APPLET CODEBASE="http://myserver/applets"
        CODE = "QueryApplet"
        ARCHIVE = "http://myserver/applets/jars/EJOB.jar"
        WIDTH = "440"
        HEIGHT = "300">
    <PARAM NAME = "jdbcUrl"
           VALUE = "jdbc:easysoft://myserver/northwind">
    </PARAM>
    <PARAM NAME = "query"
           VALUE = "select EmployeeID,FirstName,LastName from Employees">
    </PARAM>
</APPLET>

Using ez.class to make applets portable

In the example above jdbcUrl explicitly specifies the host on which the JDBC-ODBC Bridge server is running. Applet restrictions dictate that an applet can only open socket connections to a machine from which it's been downloaded.

The JDBC-ODBC Bridge includes a facility that eliminates the need to specify the hostname in your applet tag or Java code, if the web server and the JDBC-ODBC Bridge server are running on the same machine. Place ez.class in the same directory as the HTML file that runs the applet or in the directory specified with CODEBASE. The JDBC-ODBC Bridge client will now attempt to discover the name of the host that it's been loaded from and will attempt a connection to the default 8831 port.

This example APPLET element uses ez.class:

<APPLET CODEBASE="./"
        CODE = "QueryApplet"
        ARCHIVE ="../jars/EJOB.jar"
        WIDTH = 440
        HEIGHT = 300>
    <PARAM NAME = "jdbcUrl"
           VALUE = "jdbc:easysoft:northwind">
    </PARAM>
    <PARAM NAME = "query" 
           VALUE = "select EmployeeID,FirstName,LastName from Employees">
    </PARAM>
</APPLET>

ez is a subclass of easysoft.sql.jobDriver. Instead of easysoft.sql.jobDriver use ez in your applet code.

ez.class is in http://jdbc-odbc-bridge-server-machine/demo/ez.class.

Loading the JDBC-ODBC Bridge classes

To load the JDBC-ODBC Bridge client driver classes use:

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

You can also use:

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

which instantiates the easysoft.sql.jobDriver class.

Connecting to an ODBC data source with the JDBC-ODBC Bridge client

To connect to an ODBC data source with the JDBC-ODBC Bridge, 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 JDBC-ODBC Bridge client 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 name.
:password Database password.
:logonuser Remote system user name.
:logonpassword Remote system password.
:clearText=on|off Controls whether connection information (connection string, user names, and passwords) that's transmitted over the network is encrypted. (Turning this attribute on can be useful for debugging purposes.)
:limit The maximum number of rows returned in any result set.
:trace=on|off Turn on to set direct client tracing to stdout.
: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

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

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 JDBC-ODBC Bridge server (statement attribute SQL_ATTR_MAX_ROWS), so it only works if your ODBC driver supports SQL_ATTR_MAX_ROWS.

unicode turns on 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. The executeQuery method in the Statement class does not support SQL containing Unicode character strings. Note that Unicode data can also 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 JDBC-ODBC Bridge URL examples

URL Description
jdbc:easysoft:northwind Connect to the Northwind ODBC data source. The ODBC data source and driver are on the same machine as the JDBC-ODBC Bridge server.
jdbc:easysoft:DSN=northwind Equivalent to the previous example.
jdbc:easysoft:FILEDSN={C:\\Temp\\NorthwindFileDSN.dsn} Connect to a file data source.
jdbc:easysoft://:8899/northwind Connect to a non-default port.