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
- Using ez.class to Make Applets Portable
- Loading the JOB Classes
- Connecting to an ODBC Data Source with the JOB Driver
- Easysoft JDBC URL Syntax
- Easysoft JDBC URL Attributes
- Additional Notes
- Easysoft JOB URL Examples
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 athttp://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 appropriateARCHIVE=EJOB.jar
attribute within the APPLET tag on your HTML page. Ensure that you downloadEJOB.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 theCODEBASE
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 default8831
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 theeasysoft.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 typingeasysoft.sql.jobDriver
, typeez
.You will find
ez.class
inhttp://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 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:
-
<hostname> (optional) is the name or IP address of the remote host on which the JOB Server is running.
–Or–
<hostname> is
localhost
or127.0.0.1
, or can be omitted if: - <port> (optional) is the JOB Server port. The default is 8831.
- <odbc-data-source> is the system ODBC data source you want to connect to. For example,
MY-SYSTEM-ODBC-DATA-SOURCE
,DSN=MY-SYSTEM-ODBC-DATA-SOURCE
orFILEDSN={C:\\Temp\\MY-ODBC-FILE-DSN.dsn}
. Alternatively, specify a DSN-less connection string. For example,Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Program Files\Microsoft Office\Office\1033\Nwind2000.mdb
. - <odbc-driver-attribute> (optional) is one or more connection string attributes supported by the ODBC driver.
- <easysoft-jdbc-url-attribute> (optional) is one or more Easysoft JDBC URL Attributes.
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. |