# Stels MDB JDBC Driver v1.0 Documentation

Contents

Installation

Driver Class

URL Syntax

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 Class

The class name of the driver is jstels.jdbc.mdb.MDBDriver.

#

# URL Syntax

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

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

    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, 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 | FLOAT | java.sql.Types.FLOAT | java.lang.Float
Double | DOUBLE, DECIMAL, CURRENCY | java.sql.Types.DOUBLE | java.lang.Double
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 the DOUBLE type with the precision and the scale. In other words, DOUBLE(15,2) will be mapped to DECIMAL with the precision = 15 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(15,2), // will be mapped to DECIMAL(15,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]