Easysoft JDBC-ODBC Bridge User Guide - Programming Guide

Easysoft JDBC-ODBC Bridge Programming Guide

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

It is assumed that the JOB Server has been successfully installed and configured and that you know the hostname for the server to which you wish to connect.

Chapter Guide

Introduction

The Easysoft JDBC-ODBC Bridge comes in two components:

The JOB Server incorporates a Web Administrator (see "The Web Administrator" on page 76) that provides access to administration, monitoring and test facilities through a standard web interface.

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

Java applications

Download EJOB.jar to the client machine from the JOB Server at http://hostname:8031/jars/EJOB.jar(where 8031 is the web server port).

On the client machine 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, add a semi-colon and 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, the full path and file name in the Variable Value and then click OK.

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

http://hostname:8031/doc/output/TEST.java.html contains sample code for a simple Java application.

The source may be saved from http://hostname:8031/demo/TEST.java, compiled and used to test the connection to an Easysoft JDBC-ODBC Bridge Server (assuming that 8031 is the Web Server port).

Java applets

For applet deployment, place the EJOB.jar file with your applet in a directory readable by your web server and add the appropriate ARCHIVE=EJOB.jar attribute to the APPLET tag on your HTML page.

EJOB.jar can be downloaded from the link on the installed Easysoft JDBC-ODBC Bridge Web Administrator Information page:

Figure 32: An example applet tag

Making applets portable

In Figure 46 the jdbcUrl explicitly specifies the host on which the JOB Server is running.

Applet restrictions dictate that applets can only open socket connections to machines from which they have been downloaded. There is no browser independent way of determining from where a jar file has been downloaded.

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 file ez.class is placed in the same directory as the HTML file 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.

Figure 33: An example applet tag using ez.class

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

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

import easysoft.sql.*

public class ez extendsjobDriver {

public ez() throwsSQLException {

}

}

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

An example connection to an URL with the Easysoft subprotocol:

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>

<DSNlessconnection string >

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

DSNless connections

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 used as part of the ODBC connection attributes (as in Windows driver names).

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 Disable the default encrypted transmission of connection information (connection string, user names and passwords) over the network (useful for debugging purposes)
:limit = n The 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 a named 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

Figure 34: 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 explicitly specified 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 system user on the target machine.

They must be used if the database engine requires the connecting process (in this case the JOB Server) to be logged in as a particular user..


Caution!

Running the Windows service as a speciifed user (other than local admin) means that authentication must be disabled and that any user can then therefore connect, creating a security risk. You will also be unable to connect to trusted data sources (such as Microsoft SQL Server) as a system account.


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.

fetchSize should be set to zero to avoid the retrieval of far more columns (the default is 4k) in a results set than will actually be needed by the application.

Easysoft JOB URL examples



JOB Server Platform
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 the Sun JDBC bridge.
jdbc:easysoft:DSN=northwind Equivalent to jdbc:easysoft:northwind.
jdbc:easysoft: Driver will connect to the host as with jdbc:easysoft:northwind. Since no database is specified, a list of available DSNs will be displayed in a dialog box.
jdbc:easysoft:FILEDSN=myfiledsn As with jdbc:easysoft:northwind., 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.

Figure 35: 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:

Figure 36: A minimal Java applet demonstration

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 classes for the Easysoft JDBC-ODBC Bridge.

The applet takes two parameters:

You can view the source in HTML format at http://localhost:8031/doc/output/QueryApplet.java.html or read and download (by right clicking and selecting Save Target As) the http://localhost:8031/demo/QueryApplet.java. These URLs assume that 8031 is the web server port.

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:

Figure 37: The ODBC data source selection dialog box

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

Figure 38: Displaying the result of the tables pseudo-query

You can run the applet at http://localhost:8031/doc/QueryApplet.html.

You may like to edit the HTML that launches the applet to specify your own query and JDBC URL parameter.

Use File > Save As from your browser on the page that runs the applet.