About the Easysoft JDBC-Access Gateway

The Easysoft JDBC-Access Gateway provides real-time access to Microsoft Access data from any application that supports JDBC.

Java Runtime Environment (JRE) requirements
Setting the class path
Data types
Cursors
Unicode

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

Windows versions of the JRE are available to download from:

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

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.

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. Double-click the 64-bit 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 don’t need to install aceodbc.dll. The Easysoft JDBC-Access Gateway 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 Gateway. The 32-bit version of the Easysoft JDBC-Access Gateway 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:

Include easysoft-installation-folder\Libs\esmdb.jar in the CLASSPATH environment variable value.
Include easysoft-installation-folder\Libs\esmdb.jar in the java -classpath option value.
Copy esmdb.jar to a folder that is reserved for third party .jar files by your application.

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

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

Notes

In the Microsoft Access interface, a Table Design View field type may have a different name to the Jet SQL data type it corresponds with. For example, the Table Design View field type for the MONEY data type is CURRENCY.
To create columns with the data type TINYINT or DECIMAL, or the synonym DEC, NTEXT, CHARACTER, CHARACTER VARYING, NCHAR, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, or NATIONAL CHAR VARYING, you need to set the ExtendedAnsiSQL Access ODBC driver option to 1. For example:
```
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."
You cannot use literal GUIDs in SQL queries. Literal GUIDs are needed to filter records by values contained in UNIQUEIDENTIFIER or GUID columns. The Access ODBC driver does not support the Access SQL syntax for specifying GUID literals ({GUID {nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}}). For example, this query:
```
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 can’t 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.

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.

`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:

The JDBC connection URL. For example:

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

SQL statements. For example:

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));
    }
}

Metadata (table names and so on). For example:

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"));
    }
}

SQL statement parameters in parameterized queries. For example:

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();
    }
}

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"%>.)