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
• Assumptions
• Installing the JOB Driver classes
  • Java Applications
  • Java Applets
• Using ez.class to make applets portable
• Loading the JOB Classes
• Connecting to the JOB Driver
• Easysoft JDBC URL Attributes
• Additional Notes
• Easysoft JOB URL Examples
• Minimal Java Applet Demonstration

Introduction

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

• The JOB Driver: a 100% Java JDBC compliant driver.
• The JOB Server: a server built specifically for the platform where your target ODBC System Data Source is configured.

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

• Download EJOB.jar (which can be found on the JOB Server machine at http://hostname:8031/jars/EJOB.jar if you used the default path for your installation) onto the client machine.

  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, click New, enter 'CLASSPATH' in Variable Name and the full path and file name in Variable Value and click OK.

  Please note that you need to update either the User variables or System variables list as your application requires.

Java Applets

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

  Example Applet Tag:

  <APPLET CODEBASE="http://www.easysoft.com/products/2003/demo/"
          CODE = "QueryApplet"
          ARCHIVE = "http://www.easysoft.com/products/2003/jars/EJOB.jar"
          WIDTH = "440"
          HEIGHT = "300">
      <PARAM NAME = "jdbcUrl"
             VALUE = "jdbc:easysoft://www.easysoft.com/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 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, so there is no browser independent way of determining where a jar file has been downloaded from.

  The Easysoft JDBC-ODBC Bridge 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 ez.class file 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:

  <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 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.

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>

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

Easysoft JDBC URL Attributes

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

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:

• The jdbcUrl required to connect to an ODBC data source on the machine where the JOB Server is installed and from which this HTML page is served.
• The JOB SQL pseudo-query to be executed on the specified data source.

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:

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