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

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:

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]