Easysoft JDBC-Access Gateway User's Guide - Technical Reference

Technical Reference for the Easysoft JDBC-Access Gateway

This section contains extra information relating to the deployment of the Easysoft JDBC-Access Gateway.

Appendix Guide

Java Runtime Environment (JRE) Requirements

The Easysoft JDBC-Access Gateway requires the JRE 1.6.0 or later.

To check whether you have the JRE installed on your machine and that your JRE version is one the Easysoft JDBC-Access Gateway supports, open a Command Prompt window, and type java -version. For example:

c:\>java -version

java version "1.7.0"

Java(TM) SE Runtime Environment (build 1.7.0-b147)

Java HotSpot(TM) Client VM (build 21.0-b17, mixed mode, sharing)

If the reported JRE version is 1.5.n or earlier (or you get the error 'java' is not recognized as an internal or external command, operable program or batch file.), you need to obtain a JRE for your machine.

Windows versions of the JRE are available to download from:

http://www.oracle.com/technetwork/java/javase/downloads/index.html


Note

The Java Development Kit (JDK), which includes the JRE, is also available to download from this web page. However, unless you are going to develop a Java application to run against the Easysoft JDBC-Access Gateway, you only need to download the JRE package.


64-bit Windows

If you have installed a 64-bit version of aceodbc.dll (this is the Microsoft driver that you need if you want to connect to ACCDB database files), you need to install a 64-bit JRE. If the output produced by running java -version contains 64-Bit Server VM, you have a 64-bit JRE. To check whether aceodbc.dll is 64-bit, run ODBC Data Source Administrator, which is located in the Windows Control Panel under Administrative Tools. On Windows 8, double-click the 64-bit ODBC Data Sources icon. On other versions of Windows, double-click the ODBC Data Sources icon. In the Drivers tab, if the list contains Microsoft Access Driver (*.mdb, *.accdb), you have the 64-bit version of aceodbc.dll.

If your database is an MDB file, you do not need to install aceodbc.dll. The Easysoft JDBC-Access Driver will use odbcjt32.dll to connect to your database. odbcjt32.dll is included with the Windows operating system. Because odbcjt32.dll is a 32-bit library, you need to use a 32-bit JRE and a 32-bit version of the Easysoft JDBC-Access Driver. The 32-bit version of the Easysoft JDBC-Access Driver is installed in:

easysoft_installation_folder\Libs32

The default location for easysoft_installation_folder is drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway.

Include easysoft_installation_folder\Libs32 in your class path.

Setting the Class Path

The Easysoft JDBC-Access Gateway Java classes are contained in:

easysoft_installation_folder\Libs\esmdb.jar

The default location for easysoft_installation_folder is drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway.

Because esmdb.jar is not part of the Java platform, you need to tell Java where to find the Easysoft JDBC-Access Gateway classes. To do this, you may have to:

Data Types

The Easysoft JDBC-Access Gateway supports the following Jet SQL data types and data type synonyms

Data type Synonym

BIT

LOGICAL

LOGICAL1

YESNO

TINYINT

INTEGER1

BYTE

MONEY

CURRENCY

DATETIME

DATE

TIME

REAL

SINGLE

FLOAT4

IEEESINGLE

FLOAT

DOUBLE

FLOAT8

IEEEDOUBLE

NUMBER

SMALLINT

SHORT

INTEGER2

INTEGER

LONG

INT

INTEGER4

DECIMAL

NUMERIC

DEC

TEXT

LONGTEXT

LONGCHAR

MEMO

NOTE

NTEXT

CHAR

CHARACTER

STRING

VARCHAR

CHARACTER VARYING

NCHAR

NATIONAL CHARACTER

NATIONAL CHAR

NATIONAL CHARACTER VARYING

NATIONAL CHAR VARYING

:

Figure 1: Supported Jet SQL data types and synonyms.

Notes

String connectionUrl = "jdbc:easysoft:mdb?DBQ=C:/Users/Public/Northwind.mdb;ExtendedAnsiSQL=1";

Otherwise, your CREATE TABLE or ALTER TABLE statement will fail with the error [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition.

SELECT * from MyTable where MyUniqueIdentifierCol = {guid {019CE4C0-D57C-68A6-0000-000000000109}};

fails with the error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression:

Cursors

The set of rows returned by a SQL query consists of all the rows that satisfy the conditions of that query, and is known as the result set. Applications cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

The Easysoft JDBC-Access Gateway supports the following cursor types:

Cursor type Description

TYPE_FORWARD_ONLY

The result set is not scrollable. The cursor moves only forward, from before the first row to after the last row.

This is the default behaviour for a result set.

TYPE_SCROLL_INSENSITIVE

The result set is scrollable but not sensitive to changes to the data that underlies the result set; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

Figure 2: Supported cursor types.

TYPE_FORWARD_ONLY Example

try {

// Establish the connection.

Class.forName("easysoft.sql.esMdbDriver");

Connection con = DriverManager.getConnection("jdbc:easysoft:mdb?DBQ=C:/Users/Public/Northwind.mdb");

// Use a forward only cursor to move forwards through a result

// set. This is the default cursor type, and so the following

// line could be replaced with

// Statement stmt = con.createStatement()

Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery("SELECT SupplierID, CompanyName FROM Suppliers");

// Move forwards through the result set.

while (rs.next()) {

int id = rs.getInt("SupplierID");

String supplier = rs.getString("CompanyName");

System.out.println(id + "\t" + supplier);

}

}

// Handle any errors that may have occurred.

catch (Exception e) {

e.printStackTrace();

}

TYPE_SCROLL_INSENSITIVE Example

try {

// Establish the connection.

Class.forName("easysoft.sql.esMdbDriver");

Connection con = DriverManager.getConnection("jdbc:easysoft:mdb?DBQ=C:/Users/Public/Northwind.mdb");

// Use a scrollable cursor to move backwards through a result

// set.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet srs = stmt.executeQuery("SELECT SupplierID, CompanyName FROM Suppliers");

// Move the cursor to the last record in the result set.

srs.last();

int id = srs.getInt("SupplierID");

String supplier = srs.getString("CompanyName");

System.out.println(id + "\t" + supplier);

// Move backwards through the remaining records.

while (srs.previous()) {

id = srs.getInt("SupplierID");

supplier = srs.getString("CompanyName");

System.out.println(id + "\t" + supplier);

}

}

// Handle any errors that may have occurred.

catch (Exception e) {

e.printStackTrace();

}

Unicode

Background

Unicode is a computing industry standard designed to consistently and uniquely encode characters used in written languages throughout the world.

Java's native character encoding is Unicode. The primitive type char is a single Unicode character. The String class is a collection of characters.

The JDBC data types used to express character data, (CHAR, VARCHAR and LONGVARCHAR) can all be represented as a Java String. (The Easysoft JDBC-Access Gateway maps the Access TEXT and CHAR data types to the VARCHAR and CHAR JDBC data types.)

Unicode support was added in Jet version 4.0. A Jet version 4.0 .mdb file uses the Unicode encoding to store character data in TEXT and CHAR columns.

The Access ODBC driver supports Unicode in the form of Unicode data types and Unicode versions of the ODBC API.

Unicode and the Easysoft JDBC-Access Gateway

The Easysoft JDBC-Access Gateway allows Unicode data to be specified in:

String connectionUrl = "jdbc:easysoft:mdb?DBQ=C:/Users/Public/UnicodeCharsDB.mdb";

Connection con = DriverManager.getConnection(connectionUrl);

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("easysoft.sql.esMdbDriver");

con = DriverManager.getConnection(connectionUrl);

String SQL = "INSERT INTOUnicodeCharsTable(UnicodeCharsColumn) VALUES ('UnicodeChars')";

stmt = con.createStatement();

stmt.execute(SQL);

SQL = "SELECTUnicodeCharsColumn FROMUnicodeCharsTable";

rs = stmt.executeQuery(SQL);

while (rs.next()) {

System.out.println(rs.getString(1));

}

}

Connection con = null;

DatabaseMetaData dm = null;

ResultSet rs = null;

try {

Class.forName("easysoft.sql.esMdbDriver");

con = DriverManager.getConnection(connectionUrl);

dm = con.getMetaData();

rs = dm.getColumns(null, null, "UnicodeCharsTable", null);

System.out.println("Columns");

while (rs.next()) {

System.out.println("\t" + rs.getString("COLUMN_NAME") +

" : " + rs.getString("TYPE_NAME"));

}

}

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

ResultSetMetaData rsmd = null;

try {

Class.forName("easysoft.sql.esMdbDriver");

con = DriverManager.getConnection(connectionUrl);

String SQL = "SELECT * FROMUnicodeCharsTable whereUnicodeCharsColumn = ?";

pstmt = con.prepareStatement(SQL);

pstmt.setString(1, "UnicodeChars");

rs = pstmt.executeQuery();

rsmd = rs.getMetaData();

while (rs.next()) {

for (int i = 1; i <= rsmd.getColumnCount(); i++) {

System.out.print(rs.getString(i) + "\t");

}

System.out.println();

}

}


Note

If you experience text corruption when working with Unicode data (for example, you get a ? character instead of the expected character), you may need to explicitly specify a Unicode encoding in your client application. For example, when testing the Easysoft JDBC-Access Gateway with Eclipse and Apache Tomcat we need to set the character encoding to UTF-8. (In Eclipse, we set the Text file encoding to UTF-8; in Tomcat we added this line to our test JSP: <%@page contentType="text/html;charset=UTF-8"%>.)


Tracing

This section describes how to enable tracing when using the Easysoft JDBC-Access Gateway.

To help resolve issues and problems with the Easysoft JDBC-Access Gateway, you may need to enable JDBC tracing (also known as logging). This can be a very useful debugging aid, but it should be remembered that tracing will adversely affect performance, and so should be disabled when you have resolved your problem. You enable tracing by using one of the following methods:

try {

Class.forName("easysoft.sql.esMdbDriver");

DriverManager.setLogWriter(new PrintWriter(System.out));

}

try {

DataSource ds = (DataSource) envCtx.lookup("jdbc/Northwind");

ds.setLogWriter(new PrintWriter(System.out));

}


Note

The Easysoft JDBC-Access Gateway communicates with the Microsoft ODBC driver directly rather than via the Microsoft ODBC Driver Manager. For this reason, the ODBC Driver Manager tracing facility, accessible from the ODBC Data Source Administrator, cannot be used to log Easysoft JDBC-Access Gateway activity. Error messages returned by the ODBC driver are returned via the JDBC tracing mechanism however.