Easysoft JDBC-ODBC Bridge

Programming Guide

Statistics
Configuration
Demo
Security
Information

Data Access by Easysoft

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.

Assumptions

This guide assumes the JOB server has been successfully installed and configured. You will need to know the hostname for the JOB server you intend to connect to. If you need to install the JOB server refer to the Getting Started Guide.

Introduction

The Easysoft JDBC-ODBC Bridge (JOB) consists of 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.

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

Download EJOB.jar to the client machine. The EJOB.jar file can be found on the JOB server system at http://hostname:8031/jars/EJOB.jar if you use the default web server port for your installation.

On the client you must either amend or create the 'CLASSPATH' setting in the Environment Variables, which are found under Start > Settings > Control Panel > System > Advanced in Windows 2000 (this may vary with other versions of Windows). If 'CLASSPATH' already contains a value then you must add a semi-colon and then the full path and file name of your EJOB.jar file to the existing value. To add a new 'CLASSPATH' click on the Environment Variables tab and click New. Put 'CLASSPATH' in the Variable Name and the full path and file name (as above) in the Variable Value. Then click OK. Please note that you need to update either the User or System Environment Variable list as your application requires.

You can read sample code for a simple Java Application here, or save the source for it as a file on your system, compile it and test connecting to a Easysoft JDBC-ODBC Bridge Server.

Java Applets

For applet deployment you are advised to place the EJOB.jar file with your applet in a directory readable by your full-featured web server and to add the appropriate ARCHIVE=EJOB.jar attribute to the APPLET tag on your HTML page. Ensure that you download the EJOB.jar from your installed server pages. You can do this by right clicking on EJOB.jar or following the link on the Easysoft JDBC-ODBC Bridge Information page.

Example Applet Tag:

Using ez.class to make applets portable

In the example above the jdbcUrl explicitly specifies the host on which the JOB server is running. Applet restrictions dictate that an applet can only open socket connections to a machine from which they have been downloaded. There is no browser independent way of determining where a jar file has been downloaded from. The Easysoft driver includes a facility that enables a short form of the URL to be used, eliminating the need to specify the hostname in your applet tag or Java code. To achieve this the web server must be installed on the same machine as the JOB server. Ensure the file ez.class is placed in the same directory as the HTML file that is used to run your applet or in the directory specified as the CODEBASE attribute in your applet tag. The driver will now attempt to discover the name of the host that it is loaded from, and will attempt a connection to the default 8831 port.

Example Applet Tag using ez.class:

ez is a subclass of the easysoft.sql.jobDriver and as such it can be used as an alternative short name in interactive applications which require you to enter the JDBC driver class name. Instead of typing easysoft.sql.jobDriver, type ez.

You will find ez.class in http://hostname/demo/ez.class.

The code is simply this:

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 the JOB Driver

Connect with an URL following the Easysoft subprotocol. For example:

     String jobUrl= "jdbc:easysoft://demo.easysoft.com/pubs";
     Connection con = DriverManager.getConnection(jobUrl);

The general form of a JOB URL is:

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

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

DSNless connection

 jdbc:easysoft://<host>/Driver={<driver name>}[;<attr>=<value>]+ 
e.g. jdbc:easysoft://<host>/Driver={Microsoft Access Driver (*.mdb)};DBQ=C\:tsmall.mdb;trace=on Note that when using DSNless connections the JOB JDBC URL attribute separator ':' is changed to ';' so that ':' can be accepted as part of the ODBC connection attributes (as in driver names on windows)

Easysoft JDBC URL Attributes

:user|UIDDatabase User ID

:password|PWDDatabase Password

:logonuser Remote System User ID

:logonpasswordRemote System Password

:clearText=on | off  Disable the default encrypted transmission of Connection information (connection string, usernames and passwords) over the network (useful for debugging purposes) by setting the clearText attribute to on

:limitThe maximum number of rows returned in any result set

:trace=on | off Turn on/off tracing to stdout in the client

:traceFile=<filespec> Turn on tracing and redirect output to name file. Note that `:' is used as the attribute separator in JOB Urls. Hence you need to use the escape characters %3A for `:' in the file specs for traceFile attributes on Windows, e.g.
     :traceFile=C%3A\logs\job.log


: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 retrieval of 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 user or account on the target machine. These parameters will be required to gain access to the server, unless the authentication requirement is disabled in the configuration. If the target database requires that the connecting process, in this case the JOB server, to be authenticated and running as a particular user or account, these will enable that to be set. This will only work if the JOB Server is run as:

a Service using Logon As: System Account on Windows
root on *nixes

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.

Easysoft JOB URL Examples

jdbc:easysoft:northwind

Driver will 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 named ODBC data source 'northwind' in the same way as Sun's JDBC bridge.

jdbc:easysoft:DSN=northwind

Equivalent to the above.

jdbc:easysoft:

Driver will connect to the host as above. Since no database is specified, a list of available DSNs will be presented in a dialog box.

jdbc:easysoft:FILEDSN=myfiledsn

As before, but connecting to a file DSN data source, rather than a system DSN data source.

jdbc:easysoft://:8899/northwind

Connect the JOB server to a non-default port.

jdbc:easysoft://demo.easysoft.com/pubs  

In an application connect to the JOB 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:

The CODE tag specifies the QueryApplet.class class file for the applet's 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, or read and download (by right clicking and selecting Save Target As) the Java source.

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:

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

You can run the applet.

You may like to edit the HTML that launches the applet to specify your own query and jdbcUrl parameter. Use File > Save As from your browser on the page that runs the applet.