Download UCanAccess 2.0.8
Home Getting Started JDBC Client Tools Change Log Links/Licensing About

UCanAccess

This is the home page of UCanAccess, an open source Java JDBC Driver implementation which allows Java developers and jdbc client programs (e.g., DBeaver, NetBeans, SQLeo, Open Office Base, Libre Office Base, Squirrell) to read/write Microsoft Access database.

Because it is a pure java implementation it run in both Windows and non-Windows Operative Systems(e.g., linux/unix). No ODBC needed. UCanAccess uses:

Features

  • Supported Access formats: 2000, 2003, 2007, 2010.
  • SELECT, INSERT, UPDATE, DELETE statements. Transactions and savepoints.
  • Access data types: YESNO, BYTE, INTEGER, LONG, SINGLE, DOUBLE, NUMERIC, CURRENCY, COUNTER, TEXT, OLE, MEMO, GUID,DATETIME.
  • Concurrent access from multiple users (i.e., multiple application server threads).
  • Connection pooling.
  • ANSI 92 SQL, core SQL-2008.
  • MS Access SQL.
  • Main Access functions implementation.
  • You can execute select queries defined and saved in Access. Since 1.0.1 version, UCanAccess can execute crosstab queries (Transfom...Pivot...)
  • LIKE operator accepts both standard '_' and access specific '?' as wildcard character, both standard '%' and access specific '*' as jolly, # for single digit(access), [xxx][!xxx]for characters intervals.
  • Both & and || string concat.
  • Square brackets for field names, table names and view names (Access Queries) including spaces (e.g., select [my column] from [my table] ).
  • Access date format (e.g., #11/22/2003 10:42:58 PM#).
  • Both double quote " and single quote ' as SQL string delimeters.
  • Read and write support to complex types (i.e., array of Version, Attachment, SingleValue).
  • Since 2.0.1 version, UCanaccess supports both constants and functions as default column values in create table statements
    e.g., CREATE TABLE AAA ( baaaa text PRIMARY KEY,A long default 3 not null, C text(255) not null, d DATETIME default now(), e text default 'bla')
  • Command Line Console. You can run SQL commands and display their results. CSV export command.

Limitations

  • Only basic DDL is supported (create table with primary key, drop table).
  • Poor multiprocess access support. Nevertheless it tries to synchronize data when a concurrent process modifies the access file.

Requirements

UCanAccess requires Java 2 JDK6 or later to run.
When dealing with large databases and using the default memory settings (i.e., with Driver Property memory=true), it is recommended that users allocate sufficient memory to the JVM using the -Xms and -Xmx options. Otherwise it's needed to set Driver Property memory=false (Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb;memory=false");)

Dependencies

UCanAccess 2.x.x requires (at least) the following dependencies in your classpath:

  • jackcess-2.0.0.jar or later
    • commons-lang-2.4.jar
    • commons-logging-1.0.4.jar
  • hsqldb.jar(2.2.5)

In order to opening encrypted file you must use UCanAccess 2.x.x with jackcess-encrypt-2.x.x and all related dependecies

Old versions of UCanAccess (0.x.x or 1.x.x) require (at least) the following dependencies in your classpath:

  • jackcess-1.2.9.jar or later
    • commons-lang-2.4.jar
    • commons-logging-1.0.4.jar
  • hsqldb.jar(2.2.x)

Getting Started

The distribution comes with both a number of Junit Test Cases (package net.ucanaccess.test) and a simple java example class (net.ucanaccess.example.Example) which illustrate how UCanAccess may be used.

Getting UCanAccess connection

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://<mdb or accdb file path>",user, password);

for example:
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb");

Driver Properties

Memory: set if hsqldb will work only in memory. Default=true.

Newdatabaseversion: UCanAccess create a new access file in the specified version if this parameter is setted and the file specified in the jdbc url does not exist.
Valid values for this parameter are: V2000, V2003, V2007, V2010.

Lockmdb: if lockmdb is enabled (lockmdb=true), Ucanaccess locks the mdb file to prevent access from other processes. Default=false.

Showschema: if true, catalog "PUBLIC" and schema "PUBLIC" and others hsqldb schemas are shown in the DatabaseMetadata implementation. Default=false. Using showschema=true is raccomanded with Squirrel.

InactivityTimeout (since UcanAccess 0.1.1): it is used only with the default memory mode (Memory=true). If there are no active connections for the inactivityTimeout period (in minutes) hsqldb will be temporarily shutted down and also the filesystem resources will be released. It will be restarted at the next connection. Setting InactivityTimeout=0 can be used for avoiding this behavior. Default=2 minutes.

SingleConnection(since UcanAccess 1.0.0): it is used for for etl job, scheduled tasks or "one-shot" use of UCanAccess with only one connection open. All resources (memory and filesystem) will be released at the closing of the connection. Default=false.

Encrypt(since UcanaAccess 1.0.4): it allows hsqldb files encryption. You can use it in conjunction with Memory=false. Default=false.

Sysschema(since UcanaAccess 1.0.4):If true the msaccess system tables will be loaded in the "sys" readonly schema. Default=false.

Ignorecase: Disables (ignorecase = true) or enables (ignorecase = false) the case sensitivity of text comparison. Default=true.

for example:
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb;lockmdb=true;ignorecase=true");

Remap (since UCanAccess-2.0.2):It allows to remap the paths to one or more external linked databases.

The syntax is:
jdbc:ucanaccess://C:/db/<main db>;remap=<original db1 path>|<new db1 path>&<original i-db path>|<new i-db path> e.g.,
jdbc:ucanaccess://C:/db/main.mdb;remap=c:\db\linkee1.mdb|C:\pluto\linkee1.mdb&c:\db\linkee2.mdb|C:\pluto\linkee2.mdb.
Original path and new path must be separated by '|', different pairs must be separated by '&' .


KeepMirror (since UCanAccess-2.0.2) for keeping the mirror hsqldb database after the VM ends and so for reusing it in the following VM processes.
It forces memory=false.
In other words, using this parameter, the time-expensive process of creating and populating the database hsqldb (with memory=false), is executed only once.
jdbc:ucanaccess://C:/db/main.mdb;keepMirror=C:/db/mirrorName
Reusing the same keepMirror setting (e.g. keepMirror=C:/db/mirrorName) at the next VM process execution allows you to drammatically reduce the time of first connection.
It should be used only with very large databases (e.g., 1GB with many binary OLE objects) and when:
  • You have to change the default memory=true setting because you can't allocate sufficient heap space (Xmx VM paramenter).
  • UCanaccess takes too much time to establish the first connection (because it's populating the mirror hsqldb).
Pay attention! If the access database is modified by a different program (so not using UCanAccess) after the hsqldb creation, UCanAccess recreates and repopulates the whole mirror hsqldb for avoiding unchecked misalignments.

JackcessOpener (since UCanAccess-0.0.2): in order to use Jackcess Encrypt extension, you can specify a class that implements the net.ucanaccess.jdbc.JackcessOpenerInterface interface (in that case you need additional jar files in your classpath: a version of jackcess-encrypt.jar and all related dependencies).
The following example shows a custom integration with Jackcess Encrypt for opening a Money (.mny) file:

Example for UCanAccess2. Notice that you must use UCanAccess 2.x.x with jackcess-encrypt-2.x.x and all related dependecies
package yourPackage.example;

import java.io.File;
import net.ucanaccess.jdbc.JackcessOpenerInterface;
//imports from Jackcess Encrypt
import com.healthmarketscience.jackcess.CryptCodecProvider;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;

import net.ucanaccess.jdbc.JackcessOpenerInterface;

public class CryptCodecOpener implements JackcessOpenerInterface {
  public Database open(File fl,String pwd) throws IOException {
   DatabaseBuilder dbd =new DatabaseBuilder(fl);
   dbd.setAutoSync(false);
   dbd.setCodecProvider(new CryptCodecProvider(pwd));
   dbd.setReadOnly(false);
   return dbd.open();
   
  }
  //Notice that the parameter setting autosync =true is recommended with UCanAccess for performance reasons.
  //UCanAccess flushes the updates to disk at transaction end.
  //For more details about autosync parameter (and related tradeoff), see the Jackcess documentation.
}



Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection conn=
DriverManager.getConnection("jdbc:ucanaccess:///opt/prova1.mny;jackcessOpener=yourPackage.example.CryptCodecOpener", "sa", pwd);
...

For old UCanAccess versions (0.x.x,1.x.x) JackcessOpenerInterface should be implemented in a different way, because of the jackcess API changes from jackcess1 to jackcess2.
package yourPackage.example;

import java.io.File;
import java.io.IOException;
//imports from Jackcess Encrypt
import com.healthmarketscience.jackcess.CryptCodecProvider;
import com.healthmarketscience.jackcess.Database;

import net.ucanaccess.jdbc.JackcessOpenerInterface;

public class CryptCodecOpener implements JackcessOpenerInterface {
  public Database open(File fl,String pwd) throws IOException {
   return Database.open(fl, true, true, null, null, new CryptCodecProvider(pwd));
  }
}

...

Identifiers escaping

You should escaping identifiers including a blank space using square brackets or backtick character (`).
Also you shold escaping identifiers that equal a SQL keyword (e.g. SELECT * FROM [SELECT] or SELECT * FROM `SELECT`).
SQL Keywords are: ALL, AND,ANY, AS, AT, AVG, BETWEEN, BOTH, BY, CALL, CASE, CAST, COALESCE, CONSTRAINT, CORRESPONDING, CONVERT, COUNT, CREATE, CROSS, DEFAULT, DISTINCT,DO, DROP, ELSE, EVERY, EXISTS, EXCEPT, FOR, FROM, FULL, GRANT, GROUP, HAVING, IN, INNER , INTERSECT, INTO, IS, JOIN, LEFT, LEADING, LIKE, MAX , MIN, NATURAL, NOT, NULLIF, ON, ORDER, OR, OUTER, PRIMARY, REFERENCES, RIGHT, SELECT, SET, SOME, STDDEV_POP, STDDEV_SAMP, SUM, TABLE, THEN, TO, TRAILING, TRIGGER, UNION, UNIQUE, USING, USER, VALUES, VAR_POP, VAR_SAMP, WHEN, WHERE, WITH, END.

Examples

Creating table

Statement st =conn.createStatement(); st.execute("CREATE TABLE example1 (id COUNTER PRIMARY KEY,descr text(400), number numeric(12,3), date0 datetime) ");
...st.execute("create table dkey(c counter , number numeric(23,5) , PRIMARY KEY (C,NUMBER) ");
...st.execute("create table dtrx(c text , number numeric(23,5) , unique (C,NUMBER)) ");

Also Ucanaccess supports create table as () syntax:
st.executeUpdate("CREATE TABLE copy as (select * from example1) WITH DATA ");

Executing queries

The following example shows a full outer join:
st =conn.createStatement();
ResultSet rs=st.executeQuery("SELECT * FROM example3 full outer join example4 on (example3.id=example4.id)");

Using access functions

ResultSet rs=st.executeQuery("SELECT IIf(descr='Show must go off','tizio','caio&sempronio'&'&Marco Amadei'&' '&now()& RTRIM(' I''m proud of you ')) from example1");

//Aggregate functions:
ResultSet rs=st.executeQuery("select id, DCount('*','t234','1=1') from t234");

More complete examples of access functions implementation are shown in the net.ucanaccess.test.FunctionsTest junit test suite. The following functions are already implemented:
ASC, ATN, CBOOL, CCUR, CDATE, CDBL, CDEC, CINT, CLONG, CSIGN, CSTR, CVAR, DATEADD, DATEDIFF, DATEPART, DATE, DATESERIAL, DATEVALUE, FIX, FORMAT, IIF, INSTR, INSTRREV, ISDATE, ISNUMERIC, INT, IsNull, LEN, MID, MONTHNAME, NOW, NZ, SIGN, SPACE, SQR, STR,, STRING, STRCOMP, STRCONV, STRREVERSE, SWITCH, RND, TIME, TIMESERIAL, VAL, WEEKDAY, WEEKDAYNAME;
Aggregate and Domain Functions: FIRST, LAST, DCOUNT, DAVG, DSUM, DMAX, DMIN, DFIRST, DLAST, DLOOKUP.
Financial Functions(since UCanAccess 2.0.7.1): PMT, NPER, IPMT, PPMT, RATE, PV, FV, DDB, SYD, SLN.
Also you can use the following functions from the hsqldb implementation:
COS, SIN, LTRIM, RTRIM, UCASE, LCASE;
Aggregate Functions: COUNT, AVG, SUM, MAX, MIN, STDEV, STDEVP, VAR, VARP.

User defined functions

Step 1: implementation of a new function:
public class XXX{
...
 @FunctionType(functionName="justconcat",argumentTypes={AccessType.TEXT,AccessType.TEXT},returnType=AccessType.TEXT)
  public static String concat(String s1,String s2){
  return s1+" >>>>"+s2;
 } …
}
Step 2: registration:
UcanaccessConnection uc=(UcanaccessConnection)this.ucaConn; uc.addFunctions(XXX.class);
Step 3: trying your new fuction:
ResultSet rs=st.executeQuery("SELECT justConcat(descr,''&now()) from example1");

Using criteria in like clause

ResultSet rs=st.executeQuery("select descr from example2 where descr like 'P%'");
//access * jolly
rs=st.executeQuery("select descr from example2 where descr like 'P*'");
//number and interval patterns
rs=st.executeQuery("select descr from example2 where descr like 'P[A-F]###'");
//number pattern
rs=st.executeQuery("select descr from example2 where descr like 'C#V##'");

Limit and offset SQL pagination

rs=st.executeQuery("SELECT * FROM example2 order by id desc limit 5 offset 1");
rs=st.executeQuery("SELECT TOP 10 * FROM example2 order by id");

Performing transactions

conn.setAutoCommit(false);
st =this.ucaConn.createStatement();
st.executeUpdate("update example4 set descr='Lugo di Romagna'");
st.execute("insert into example4 (ID, descr) values(5,'DALLAS')");
conn.commit();

Using updatable ResultSet

PreparedStatement ps = super.ucanaccess.prepareStatement( "SELECT * FROM T1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
rs = ps.executeQuery();
rs.next();
rs.updateString(2, "show must go off");
rs.updateRow();

Working with date type

st.execute("INSERT INTO example1 (descr,number,date0) VALUES( \"Show must go up and down\",-113.55446,#11/22/2003 10:42:58 PM#)");

Working with OLE type

InputStream isDB = rs.getBinaryStream('OleColumn');
File fl = new File("CopyElisaArt.JPG");
OutputStream outFile = new FileOutputStream(fl);
ba = new byte[4096];
while ((len = isDB.read(ba)) != -1) {
outFile.write(ba, 0, len); }
out.flush();
out.close();
System.out.println("CopyElisaArt.JPG was created in " + fl.getAbsolutePath());

About ' and " escaping

Both ' and " are valid string delimeters.
So the following SQL statements are correct:
UPDATE TABLE1 SET SURN='D''Ambrogio';(value saved=D'Ambrogio)
UPDATE TABLE1 SET SURN="""D'Ambrogio""";(value saved="D'Ambrogio")

Working with Complex Types

...
PreparedStatement ps=conn.prepareStatement("INSERT INTO TABLE1(ID , MEMO_DATA , APPEND_MEMO_DATA , MULTI_VALUE_DATA , ATTACH_DATA) VALUES (?,?,?,?,?)");
ps.setString(1, "row12");
ps.setString(2,"ciao");
ps.setString(3,"to version");
SingleValue[] svs=new SingleValue[]{new SingleValue("ccc16"),new SingleValue("ccc24") };
//Array of net.ucanaccess.complex.SingleValue

ps.setObject(4,svs);
Attachment[] atcs=new Attachment[]{new Attachment(null,"ccc.txt","txt","ddddd ddd".getBytes(), new Date(),null),
new Attachment(null,"ccczz.txt","txt","ddddd zzddd".getBytes(), new Date(),null) };
//Array of net.ucanaccess.complex.Attachment

ps.setObject(5,atcs);
ps.execute();
...


ps=conn.prepareStatement("UPDATE TABLE1 SET APPEND_MEMO_DATA='THE CAT' ");
//APPEND_MEMO_DATA is an append only column: a new net.ucanaccess.complex.Version will be added to the version array

ps.execute();
...


ps=super.ucanaccess.prepareStatement("UPDATE TABLE1 SET ATTACH_DATA=? ");
Attachment[] atc;
ps.setObject(1,atc=new Attachment[]{new Attachment(null,"cccsss.cvs","cvs","ddddd ;sssssssssssssssssssddd".getBytes(), new Date(),null) });
ps.execute();
ps=conn.prepareStatement("select * from Table1 where ATTACH_DATA=? ");
ps.setObject(1,atc);
ResultSet rs=ps.executeQuery();
while(rs.next()){
  System.out.println(rs.getObject("ATTACH_DATA"));
//rs.getObject("ATTACH_DATA") will return an array of net.ucanaccess.complex.Attachment
}
...

Using SQL command line console

Execute console.bat (windows) or console.sh (linux/unix) in the distribution (main class: net.ucanaccess.console.Main).
Commands end with ";".
You can use: export <pathToCsv> for exporting into a csv file the result set of the last executed query.

DBeaver and UcanAccess

UcanAccess 0.1.1 has been included in DBeaver(Universal Database Manager) 2.1.2 as default MS Access driver.
DBeaver Web Site: http://dbeaver.jkiss.org.

SQLeo and UcanAccess

UcanAccess 2.0.2 has been succesfully tested with the SQLeo Visual Query Builder.
SQLeo Project: http://sourceforge.net/projects/sqleo/.

Using UCanAccess with Open Office or Libre Office

Start OpenOffice.org and go to the menu Tools - Options - OpenOffice.org (LibreOffice)- General Java.
Click on the button Class Path..., then choose Add Archive... and navigate to where the
ucanacces.jar, jackcess-1.2.9.jar, commons-lang-2.4.jar, commons-logging-1.0.4.jar,hsqldb.jar(2.x.x) files are located.
For each jar file click on Open, and the file should then appear in the list of Assigned folders and Archives.
You can then click on OK and close OpenOffice.org (LibreOffice) completely.
If the OpenOffice.org quickstarter is running, you should close it as well (right-click the the OpenOffice.org Quickstarter icon located in the bottom right corner of the screen).

Now do the following :
1) File > New > Database
2) Click on the radio button Connect to an existing database and choose JDBC from the dropdown menu. Click on Next.
3) In the Data Source URL field enter the jdbc url:
jdbc:ucanaccess://<mdb or accdb file path> e.g., jdbc:ucanaccess://c://folder/pippo.accdb
4) In the Driver Class field edit: net.ucanaccess.jdbc.UcanaccessDriver
5) Now click on Next and then enter the password (if required). Click on Next and then click on Finish.
6) You can now save your settings in a odb file.
7) You are now connected and you can see tables and views (i.e.,Access Queries).

Open Office Web Site: http://www.openoffice.org
Libre Office Web Site: http://www.libreoffice.org/


Using UCanAccess as NetBeans service

Integration tested with UCanAccess 2.0.4.1. It doesn't work with previous UCanAccess versions.

1) Define a new Driver in the Netbeans "Services" Tab
Services->Databases->Drivers->new Driver...
2) In the field Driver File(s) add ucanaccess.jar and all dependencies (all jars in the lib folder of the UCanAccess distribution :commons-lang.jar, commons-logging.jar, hsqldb.jar, jackcess.jar). Notice that commons-logging.jar version must be the same used by NetBeans (in NetBeans8 the path is NetBeans 8.0\ide\modules\org-apache-commons-logging.jar). Commons-logging version in the UCanAccess 2.0.4.1 distribution is the same as in NetBeans8(commons-logging-1.1.1.jar).
Setting the field Driver Class: net.ucanaccess.jdbc.UcanaccessDriver.
3) Create a connection using the following jdbc url pattern:

URL JDBC example:
"jdbc:ucanaccess://<full path to your mdb file>;showschema=true");
Showschema connection property must be setted to true.
4) Now you can see your db tables listed in the PUBLIC schema.

NetBeans Web Site: https://netbeans.org


Using UCanAccess with Squirrel

You need to add to the Extra Class Path list in the Driver Window ucanacces.jar, jackcess-1.2.9.jar, commons-lang-2.4.jar, commons-logging-1.0.4.jar,hsqldb.jar(2.x.x).
You should explicitily set the showschema driver property to true.

URL JDBC example:
"jdbc:ucanaccess://<full path to your mdb file>;showschema=true");
Squirrel Web Site: http://www.squirrelsql.org/

2.0.8 Release

  • Added support to not standard syntax(accepted by MS Access) DELETE * FROM TABLENAME (besides the always supported standard syntax DELETE FROM TABLENAME).
  • Fixed behaviour when the USER SQL keyword is used as column name. You still need to escape it using square brackets, to avoid ambiguity(e.g. SELECT [USER] FROM USERS). Notice that the user keyword, in general, refers to the database user.
  • Fixed bug that could have effect, in the previous versions, on delete and update statements, when a SQL keyword is used as table name.

2.0.7.1 Release

  • Fix bug on byte type management (when the byte value is between 128 and 255).
  • Strongly enhanced implementation of calculated fields (support to power operator ^, return type management, fixed rounding bug in operations involving integer literals and so on). Nevertheless tables with one or more calculated field are still marked as read-only, waiting for an enhanced I/O support. Also notice that calculated fields values are currently "calculated on fly" (that's, for the moment, just a simulation) and they aren't read from the database through jackcess. As result, elaborating the expressions that depend on the Regional Settings(e.g character string parsed to date value), there could be a difference between UCanAccess output and the value saved in the database, if data were inserted with a locale setting and then they are read with another one.
  • Added financial functions (PMT, NPER, IPMT, PPMT, RATE, PV, FV, DDB, SYD, SLN), SQR and FIX function.
  • Treated some more "special characters" (e.g. °) in column and table names.
  • Fix bugs on DATEVALUE function (internationalisation).
  • Overloading of all "date functions", so that they can accept a string (VARCHAR) instead of a date as argument. Nevertheless keep in mind that this kind of calls forces a parsing operation, with a result which depend on the regional settings (locale), so you had better use the # delimeters and thus pass a date value instead of a character string.

2.0.7 Release

  • Strongly enhanced internazionalisation support in date formatting.
  • Solved residual bug on boolean type management.
  • Introduced implementation of calculated fields. Tables with one or more calculated field are still marked as read-only, waiting for an enhanced I/O support.
  • Fixed bug on keepMirror working mode.

2.0.6.3 Release

  • Fix bugs on Format and IsNumeric functions implementation: fixed the internationalization of numeric values, introduced internazionalization (related to the current locale ) in the formatting of date values, rounding mode "Round Half Up" for numeric values when a pattern is passed as argument (e.g., format(48.1425,'.###') gives the string 48.143 as result).
  • Fix bug on Int function: now it returns an Integer value (in previous versions it wrongly returned a short).
  • Added ResultSet.deleteRow implementation.

2.0.6.2 Release

  • Fix bugs on DDL implementation (in the previous version a Unique Key was created instead of a Primary Key, issue using a space string " " as default value, multiple columns Primary Key).
  • Driver version read directly from the manifest file (Package.getImplementationVersion()), pom.xml modified to include default implementation entries in the manifest file.

2.0.6.1 Release

  • Fix bug on batch implementation: consecutive SQL update operations on the same record using executeBatch().
  • Fix bug on saving column "required" property,in the case of autonumber type columns.
  • Fix bug on boolean type management (the handling of null values is different between Access and hsqldb).

2.0.6 Release

  • Fix bug on re-authentication with encrypted databases.
  • Fix bug on the size of textual columns with access-97 files (which are read-only). This bug could cause a failure while connecting to access-97 databases. Therefore, successfully tested read-only connection to .eap files.
  • "CONSTRAINTS" listed and handled as hsqldb keyword (and "patched" MAX which had a space at the end).
  • Allowed non-standard SQL operation with date values: select date()+1 from atable (instead of: select date()+1 DAY from atable) gave error before this release.
  • "select table1.sameName, table2.sameName from ..." ambiguity solved by UCanAccess(for compatibilty with the access permissive sintax), when detected in access queries.

2.0.5 Release

  • Fix bug on handling "scale" property in the case of numeric columns with dimension "decimal".
  • Improved integration with NetBeans. You can use UCanAccess 2.0.5 with NetBeans8 Reverse Engineer (e.g., "Entity classes from database", "Database Schema")
  • Fix escaping of '?' character in column names.
  • Jackcess upgrade to the 2.0.4.

2.0.4.1 Release

  • Fix bug in ResultSet.insertRow() implementation.
  • Fix bug on DatabaseMetadata.getTables implementation (this patch allows configuring UCanAccess as NetBeans service).
  • Commons-logging upgrade to the 1.1.1.

2.0.4 Release

  • Fix bug in DDL execution(create table) with the latest jet formats.
  • iif function extension for boolean and numeric and date types.
  • "DO" listed and handled as hsqldb keyword.
  • clng function implementation (it was wrongly named clong).

2.0.3 Release

  • Changed UCanAccess behavior in handling (incorrect) default column values with a size greater than the column size. UCanAccess has now the same behavior of Access: a data truncation error will be thrown at the real attempt to insert an incorrect value and no more at the accdb/mdb opening (or, in other words, at the first connection).
  • Improved performances in conversions. In the precedent versions, UCanAccess ran slow in some particular case, e.g., sql statement not parametric(not Prepared Statement), with a very long string containing a double quote (") character.
  • Fixed bug in handling column name with both numbers and spaces (e.g. 'bla 1err').
  • Fixed bug related to numbers passed in scientific notation when not using a PreparedStatement.
  • Jackcess upgrade to 2.0.3.

2.0.2 Release

  • Added connection parameter remap. It allows to remap the paths to one or more external linked databases. The syntax is:
    jdbc:ucanaccess://C:/db/<main db>remap=<original db1 path>|<new db1 path>&<original i-db path>|<new i-db path>
    e.g.,
    jdbc:ucanaccess://C:/db/main.mdb;remap=c:\db\linkee1.mdb|C:\pluto\linkee1.mdb&c:\db\linkee2.mdb|C:\pluto\linkee2.mdb.
    Original path and new path must be separated by '|', different pairs must be separated by '&'.
  • Added connection parameter keepMirror for keeping the mirror hsqldb database after the VM ends and so for reusing it in the following VM processes.
    e.g.,
    jdbc:ucanaccess://C:/db/main.mdb;keepMirror=C:/db/mirrorName
    It make sense only with memory=false (so it forces this setting). It should be used with very large databases only (e.g., 1GB with many OLE binary data) and when UCanaccess takes too much time to establish the first connection.
  • Added support for some ISO-8859 non-roman characters (e.g Euro symbol) in column and table names.
  • Added "END" to the keywords list.
  • Fixed minor bugs on DatabaseMetadata. Thanks to the SQLeo team.

2.0.1 Release

  • Added support for persisting the column properties defined in create table statements: default column value and nullability(i.e. required property). Supported both constants and functions as default values
    e.g., CREATE TABLE AAA ( baaaa text PRIMARY KEY,A long default 3 not null, C text(255) not null, d DATETIME default now(), e text default 'bla')
  • Fix method closeOnCompletion of UcanaccessStatement (patch missed in the 2.0.0)

2.0.0 Release

  • jackcess upgrade to version 2.0.1. So UCanAccess 2.0.0 is compliant with jackcess2 API
  • implemented backtick character (`) for escaping identifiers including blank characters(as alternative of using square brackets)
  • fixed major bug on update and delete statements on tables having one or more column names that contain a blank character: it caused a desync between hsqldb and the access file
  • fixed interfacing problem about using UCanAccess from Open Office or Libre Office (the office tools, in some cases, weren't able to open few tables)
  • fixed problem about turning off jackcess logging
  • added META-INF\services\java.sql.Driver file

1.0.4.1 Release

  • Fixed method closeOnCompletion of UcanaccessStatement
  • fixed memory leak in jet loading (it caused OutOfMemoryError during the loading of very large databases even with the connection parameter memory=false)

1.0.4 Release

  • Improved sql conversion so that access keywords (if used as table names, column names, query names) are no more escaped (with an x before the keyword). Old sql code(if hardcoded in your sources) is still supported so you don't need to change anything.
  • memory=false connection parameter allowed even for encrypted databases by encrypting hsqldb files (file system cache) with a random key.
  • fixed bug on yes/no constants conversion to true/false.
  • added "encrypt" connection parameter for allowing hsqldb files encryption. You can use it in conjunction with Memory=false. False by default.
  • added "sysschema" connection parameter. If this connection parameter is true the msaccess system tables will be loaded in the "sys" readonly schema. False by default.
  • jackcess upgrade to version 1.2.14.2

1.0.3 Release

    Fix bug release:
  • fixed escaping for access (no standard sql) access keywords refering to data types:COUNTER,CURRENCY,DATETIME,MEMO,OLE, SINGLE,TEXT,YESNO,GUID when they are used as name of column or table or query
  • improved escaping of column, table, query names for allowing the use of '(' and ')' character
  • fixed a 1.0.2 bug in inserting or updating string with new line or carriage return characters: under particular conditions, \n and \r characters were replaced by a blank character
  • in create table ddl statement, text columns default lenght to 255 where not specified (before the explicit setting of length of text columns was mandatory)
  • patched handling of temporary access artifacts (i.e. temporary tables, queries, foreign key and indexes with the name starting with a tilde)
  • jackcess upgrade to version 1.2.14

1.0.2 Release

  • added write support complex types (i.e., array of Version, Attachment, SingleValue)
  • added Lookup domain function
  • added support for dynamic domain function (e.g.SELECT DCount("ID","Table1","ID <=" &ID) AS Sequence, tValue FROM table1)
  • added support for count aggregate function in cross_tab functions(it was missed in the previous version)
  • added switch function
  • added support for "yes"(converted in true) and "no"(converted in false) boolean constants in queries
  • added functions int, strcomp, strconv, strreverse, rnd, timeserial, string, weekday
  • added support for non-standard subquery sintax [SELECT ... FROM ...]. As T
  • patched "create table" implementation for the right size of text columns
  • fixed regular expression support in like conditions
  • jackess upgrade to version 1.2.13

1.0.1 Release

  • crosstab queries implementation(Transform... Pivot...)
  • added support to STDEV, STDEVP, VAR, VARP aggregate functions
  • basic, readonly support to complex types (i.e., array of Version, Attachment, SingleValue)
  • fix handling of datatime values with millisecond precision(so not inserted with standard ms access api)
  • fix handling of hsqldb keywords and ms access reserved words and other problems escaping related

1.0.0 Release

  • improved csv export
  • added support to optional parameters (firstdayofweek,firstweekofyear) in DatePart function:
    DatePart(interval, date [, firstdayofweek] [, firstweekofyear] )
  • added date() function
  • added support to 'yyyy-MM-dd' and 'yyyy-MM-dd hh:mm:ss' date formats
  • extended support for non-standard naming of tables and columns
  • patched problems in the binding of a column default value to a function (during the access file opening)
  • added the singleConnection driver parameter, for etl job, scheduled tasks or "one-shot" use of UCanAccess
  • patched bug in insert/update operations with very long text values (1000+ characters) using Statement interface
  • cleared the resetting of the Connection AUTOCOMMIT property at the end of each transaction
  • patched SQL bug: '_' wildcard character misinterpreted
  • jackess upgrade to version 1.2.12
  • fixed problems with single precision numeric (float) db type

0.1.1 Release(Beta)

  • Memory usage optimisation: added inactivityTimeout driver parameter. Added a specific junit test case for this (net.ucanaccess.test.MemoryTest)
  • Fixed some bugs (ccur function scale, setted jackcess log level to Level.OFF by default)

Next step

DDL support extension.

Excel support and integration.

Sourceforge UCanAccess Home

Sourceforge Project Home

Licensing

UCanAccess is issued on under the GNU Lesser General Public License 2.1. For further information click here.

Author

Marco Amadei

Contributors

Tom Hallman contributed with bug reports, testing, and useful comments to the release of UCanAccess v1.0.0, while he was involved in the porting from odbc to jdbc of the MIST project.

Rafael Sisto contributed to the release of UCanAccess v1.0.3 by identifying bugs and suggesting patches, in particular regarding escaping of column names and handling of temporary tables.

Marcel Merkel sent a major patch about the JDBC 4.1 feature closeOnCompletion.