# Stels MDB JDBC Driver v1.3 Documentation

Contents

Installation

Driver Classes

URL Syntax

Driver Properties

Connection Example

Driver Modes

Data type mapping

Supported SQL Syntax

User-defined SQL functions

#

# Installation

Add the driver jar files (mdbdriver.jar + required third-party libraries) to your classpath or extract these jars to the directory of your application.

#

# Driver Classes

Description | Classes
Driver class (JDBC API v1.0) | jstels.jdbc.mdb.MDBDriver
Data Source class (JDBC API v2.0) | jstels.jdbc.mdb.MDBDataSource
Connection Pool Data Source class (JDBC API v2.0) | jstels.jdbc.mdb.MDBConnectionPoolDataSource

#

# URL Syntax

The connection URL is jdbc:jstels:mdb:path_to_mdb_file, where path_to_mdb_file is an absolute or relative path to a Microsoft Access database (MDB or ACCDB) file, e.g.:

jdbc:jstels:mdb:c:/mdb_directory/test.mdb jdbc:jstels:mdb:mdb_directory/test2.mdb jdbc:jstels:mdb:mdb_directory/access2007.accdb

    Note: If the specified MDB/ACCDB file does not exist, the driver will create it. See also the format property.

# Driver Properties

The driver supports a number of parameters that change default behavior of the driver.

These properties are:

format is used to specify a version of the MS Access (Jet) format for files being created. Possible values: access2000, access2003, access2007 (Default is "access2000")

    Connection Example

The example code below shows how the driver is used. You can download it here.

import java.sql.*; public class MDBDriverTest { public static void main(String[] args) { try { // load the driver into memory Class.forName("jstels.jdbc.mdb.MDBDriver"); // create a connection. The first command line parameter is assumed to // be the MDB database in which data tables are held Connection conn = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0]); // create a Statement object to execute the query with Statement stmt = conn.createStatement(); // execute a query ResultSet rs = stmt.executeQuery("SELECT * FROM test"); // read the data and put it to the console for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) { System.out.print(rs.getMetaData().getColumnName(j) + "\t"); } System.out.println(); while (rs.next()) { for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) { System.out.print(rs.getObject(j) + "\t"); } System.out.println(); } // close the objects rs.close(); stmt.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } } }

#### Driver Modes

There are two main modes the driver can run in: the mode with data caching and the mode with data swapping. Running in the first mode (default mode), the driver caches MDB files in the RAM making it possible to achieve maximum performance while processing MDB. Keep in mind that Java Virtual Machine must have enough free memory allotted for storing large tables (use -Xms and -Xmx JVM options). You can also use the DROP TABLE <table name> FROM CACHE command to force tables to be removed from the cache.

The second mode is recommended for processing large MDB files (> 50 MB). To use this mode, set the driver property caching to false. There are also some properties for configuring this mode:

nonTransactSubMode is used to set transaction submode. The submode nonTransactSubMode = true is optimized for using SELECT queries and INSERT operations, it doesn't support transactions. The submode nonTransactSubMode = false completely supports transactions (by default this parameter is true).

tempPath - directory where temporary files will be created (by default it is a OS temporary directory, specified by JVM environment variable "java.io.tmpdir").

maxRecordsInMemoryPerTable is used to define how many records should be saved in the RAM for each table (by default - 5000 records).
maxSwapStringSize - size for MEMO columns in swap files (by default - 50 characters).

Example:

    Properties props = new java.util.Properties();
props.setProperty("caching", "false"); // switch to the swapping mode props.setProperty("tempPath", "c:/temp"); props.setProperty("maxRecordsInMemoryPerTable", "1000"); props.setProperty("maxSwapStringSize", "60"); props.setProperty("nonTransactSubMode", "true");
    Connection conn = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0], props);

Data type mapping

### The driver supports the following data types: INTEGER, FLOAT, DOUBLE, NUMERIC (BIGDECIMAL), STRING, DATETIME, BOOLEAN, MEMO.

### The table below demonstrates the mapping scheme between StelsMDB data types and native MDB data types:

StelsMDB data type | MDB data type | JDBC returned type (java.sql.Types.*) | Java class used in StelsMDB
Integer | BYTE, INTEGER, LONG INTEGER, AUTONUMBER | java.sql.Types.INTEGER | java.lang.Integer
FLOAT | SINGLE | java.sql.Types.FLOAT | java.lang.Float
Double | DOUBLE, DECIMAL (precision < 15), CURRENCY | java.sql.Types.DOUBLE | java.lang.Double
NUMERIC (BIGDECIMAL) | DECIMAL (precision > 15) | java.sql.Types.NUMERIC | java.lang.BigDecimal
STRING | TEXT, HYPERLINK, OLE OBJECT | java.sql.Types.VARCHAR | java.lang.String
Date | DATE/TIME | java.sql.Types.TIMESTAMP | java.util.Date
BOOLEAN | YES/NO (BOOLEAN) | java.sql.Types.BOOLEAN | java.lang.Boolean
MEMO | MEMO | java.sql.Types.LONGVARCHAR | java.lang.String

Notes:

1) While inserting data to an existing MDB file, StelsMDB keeps original data types and their info that were set initially for columns.

2) If you want to create an MDB column with the native DECIMAL data type, you should specify either the DOUBLE type with the precision and the scale or the NUMERIC type. In other words, DOUBLE(10, 2) and NUMERIC(10, 2) will be mapped to DECIMAL with the precision = 10 and the scale = 2.

Example of CREATE TABLE statement:

    CREATE TABLE test(
    int_col INTEGER, 	// will be mapped to the native MDB type: LONG INTEGER
    float_col FLOAT, 	// will be mapped to FLOAT
    double_col DOUBLE, 	// will be mapped to DOUBLE
    decimal_col DOUBLE(10,2), // will be mapped to DECIMAL(10,2)
    numeric_col NUMERIC(20,2), // will be mapped to DECIMAL(20,2)
    str_col STRING(25), 	// will be mapped to TEXT with the length = 25
    datetime_col DATETIME,	// will be mapped to DATE/TIME
    bool_col BOOLEAN,	// will be mapped to YES/NO (BOOLEAN)
    memo_col MEMO		// will be mapped to MEMO
    )

For more information about native MDB data types please see the corresponding MS Access documentation.

###

###

### Supported SQL Syntax

### StelsMDB supports the most part of ANSI 92 grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.

An SQL query must meet the following conditions:

- It must corespond to the SQL syntax supported by the driver. For detailed information about supported SQL syntax please see the specification here.

- À column using an SQL reserved word as a name or containing spaces and other delimiters must be quoted in double quotes in a query. For example, SELECT "Date", "My integer-column" FROM test"

- To use single quotes (') and backslashes (\) in a string constant you should use an escape character (\). For example: 'abcd\'ef\\g'

### Query examples:

### SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM test GROUP BY a HAVING AVG(a) > 30 ### SELECT name FROM salesreps WHERE ( rep_office IN ( 22, 11, 12 ) ) OR ( manager IS NULL AND hire_date >= to_date ( '01-05-2002','dd-MM-yyyy' ) OR ( sales > quota AND NOT sales > 600000.0 ) ### SELECT city, target, sales FROM offices WHERE region = 'Eastern' AND sales > target ORDER BY city ### SELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON prod.prodid = ps.prodid ### INSERT INTO salesreps (name, age, empl_num, sales, title) VALUES ('Henry Smith', 35, 111, NULL, 'Sales Mgr') ### DELETE FROM salesreps WHERE NAME LIKE 'Henry%' ### UPDATE customers SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'

###

### User-defined SQL functions

You can use your own SQL functions in the driver. To use this feature, you should do the following:

1) Create a static method that will act as an SQL function
Mind that:

- The method must have the public modifier

- The number of arguments in the method is arbitrary, but they must belong to one of the java classes specified in the data types table

- It is advisable to process the null values for arguments in the method code

- T he method can return the null value as well

For example:

package my_pack; public class MyFuncs{ // user-defined SQL function that formats the given argument into a date/time string with specified format public static String format_date( java.util.Date d, String format ) { // process the null values if (d == null || format == null) return null; java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(format); // return a function result with java.lang.String type return sdf.format(d); } }

2) Register the function using the static method jstels.jdbc.mdb.MDBDriver.registerFunction (String funcName, String handler). Where funcName argument is the name by which this method will be called in SQL queries and handler argument is the fully-qualified method name.

For example:

jstels.jdbc.mdb.MDBDriver.registerFunction ( "format_date", "my_pack.MyFuncs.format_date" )

Also, you can use the driver property function:<my_func>.

For example:

    Properties props = new java.util.Properties();
    props.put("function:formate_date","my_pack.MyFuncs.format_date");
    ...
    Connection conn = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0], props);
    // or append this property to the URL
    Connection conn2 = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0]
     + "?function:formate_date=my_pack.MyFuncs.format_date");

3) Call the function in an SQL query

For example:

Statement st = connection.createStatement(); st.execute( "select format_date( date_column , 'yyyy-MM-dd' ) from test" );

###

### [HOME] [TOP]