UCanAccess is an open source pure Java JDBC Driver implementation for access (mdb and accdb),
licensed under LGPL 2.1.
Since the 3.0.0 release UCanAccess has been relicenced under the Apache License, Version 2.0.
-Jackcess as Ms Access Input/Output library.
-Hsqldb as synchronized DBMS.
-Supported Access formats: 2000,2002/2003,2007,2010/2013 databases (1997 read-only).
-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).
-ANSI 92 SQL, core SQL-2008.
-MS Access SQL.
-Main Access functions implementation.
-You can execute select queries defined and saved in Access.
-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.
-Access date format (e.g., #11/22/2003 10:42:58 PM#).
-Both double quote " and single quote ' as SQL string delimiters.
-Square brackets for field names, table names and view names (Access Queries).
-Command Line Console. You can run SQL commands and display their results. CSV export command.
For more details see http://ucanaccess.sourceforge.net/site.html.
You can contact the author Marco Amadei at email@example.com
UcanAccess 3.0.0 Release
- Relicense to Apache 2.0!!
- Upgrade to jackcess 2.1.2
- Solved capitalization issues in both DatabaseMetaData and ResultSetMetaData implementations.
They both have been reimplemented for this specific purpose.
Now UCanAccess can provide metadata with the correct capitalization(and no more all in upper case).
- Changed escaping of column and table names containing special characters or out-of-rules(e.g. starting with a number):
now, in these cases, you can and must use the names defined in access(obviously in a case insensitive manner).
- Supported all characterset with metadata (they were supported in data but not always in metadata elements like column or table names)
- Fixed isAutoIncrement and isCurrency ResultSetMetaData methods.
- Customized DatabaseMetaData.getColumns so that it can now provide:
the default column values in the COLUMN_DEF columm
the correct IS_AUTOINCREMENT informations
the correct IS_GENERATEDCOLUMN (used for calculated fields)
the customized column ORIGINAL_TYPE(original type in access)
- Customized DatabaseMetaData.getClientInfoProperties method that now returns ucanaccess driver available properties.
Implemented, for counters handling:
DISABLE AUTOINCREMENT ON TABLE_NAME
ENABLE AUTOINCREMENT ON TABLE_NAME.
It may be useful in import/export of data from and to different tables with the same structure, avoiding to break some FK constraint.
- Fixed memory leak in MemoryTimer. It had effect in some particular situations (using the single connection mode if you're connecting to multiple db, in the case continous update by a different process, in the case of pooling on the db with intervals greater than the InactivityTimeout).
- Optimized reloading of db instances. If and only if you're sure that your db can be accessed by the MS Access GUI and just one JVM instance (using UCanAccess), you can now use the connection parameter
PreventReloading=true. It avoid the data to be reloaded when they aren't actually changed by the GUI.
- Extended SELECT @@IDENTITY and Statement.getGeneratedKeys() features to the GUID type.
- In updatable ResultSet removed the constraint to set all columns before inserting new rows, even if they are null or autoincrement.
- Concat character operators (&,+,||) behaviour with null:
null & 'thing' -> 'thing'. It gave null in previous versions.
null + 'thing' -> 'thing'. It gave null in previous versions.
If you want to switch to the old behaviour, you have to set the new connection property concatnulls=true.
UcanAccess 184.108.40.206 Release
A fix-bugs release waiting for the 3.0.0.
-Upgrade to Jackcess 2.1.0
-Re-implemented SWITCH function with a different approach
-Fixed 220.127.116.11 regression related to databases with corrupted metadata(wrong rows number)
-Changed read-only exception message for Access 97 files
-Fixed bug on the value returned by the Statement.execute method when a ddl statement(create table) is executed
UcanAccess 18.104.22.168 Release
-Porting to HSQLDB 2.3.1.
-Fixed bug that may have effect in the case of a naming conflict between a table in the opened database and a linked table in an external database.
-Allowed connecting to a corrupted database even if data violate an Unique, Not Null or Foreign Key constraint: tables containing bad data are set read only, and a warning is logged.
-Added parameter Lobscale, to optimize memory occupation when a relevant percent of memory occupation is for Ole(Blob) data.
This may also allow, in many cases, to use the memory=true setting, whilst it was impossible before.
It should be set to a value close to the average size of Ole instances.
According with the HSQLDB documentation, the values allowed are 1,2,4,8,18,32 (the unit is Kb).
Default is 2 if memory=true, 32 (hsqldb default) otherwise.
- Added parameter Skipindexes(default=false), in order to minimize memory occupation. It allows skipping the creation of simple, untied to contraints, indexes.
It doesn't have effect on referential integrity constraints (i.e., Index Unique, Foreign Key or Primary Key).
-Solved bug related to the ß character, when used in column names.
-Fixed the error message logged when a db link metadata is broken(for metadata corruption).
-Overloaded NZ function: it can now accept numeric double values as argument.
-Speeded database loading (first connection time to a given database).
-Fixed bug that happened with table or column names containing an apostrophe or a quotation mark.
-Fixed bug in the switch function parsing.
-Deprecated and substituted parameter lockMdb: the new name for this parameter is openexclusive.
-Fixed bug on table autoreferential constraint checks(indipendence from storage order).
-Supported exclamation point, as well as in the Access SQL syntax.
UcanAccess 22.214.171.124 Release
-Upgrade to jackcess 2.0.8
-Fixed console output(it sometime showed correctly loaded queries in the list of the queries it wasn't able to load)
-Fixed issues that may happen with few unregistred keywords(e.g. 'cardinality') if used as column name with a default value.
Those keywords didn't have any effect on SQL, except for the particular case above.
-Fixed Connection setSavepoint(String spn) method(setSavepoint only worked fine with no-arguments);
-Fixed "Create table as select ..." DDL statement when using with a group by clause with two or more columns.
-Allowed DDL with PreparedStatement(for ORM libraries' use)
- Implemented ErrorHandler for invalid characters sequences: an Exception will be thrown to identify in which table, column and row the error occured
-Fixed ResultSet.getString when called on a numeric decimal column
-Fixed bug on logging/shutdown. At the vm end some unneccessary operation was performed in the case of memory=false and singleConnection=true
Logs about on-disk database were shown when program terminates (long after the latest connection was closed).
-Added mirrorFolder connection parameter that forces memory=false and allows users to set the directory in which the mirror db will be created.
Simply set this parameter to java.io.tmpdir in order to specify the system temp folder for that.
-Fixed residual issue (access 2007) creating new tables on NOT NULL property
-Added orderJet function to allow compliance with Jet sorting logic that skip hyphen minus '-', en dash '', em dash ''
and in the middle of a string:
i.e. even if "-" comes before "/"
and "a-" before "a/"
and "a- b" before "a/ b"
unexpectedly, likely to handle compound words "a/b" comes before "a-b"
WORKAROUND suggested: if you want the same behaviour of Access: select * from table2 order by orderJet( COLUMN1).
UcanAccess 126.96.36.199 Release
-Upgrade to jackcess 2.0.6.
-Fixed methods getErrorCode and getSQLState in the UCanAccess SQLException implementation (class UcanaccessSQLException).
SQL error codes and states are those gotten from hsqldb (you can handle them by using the constants in net.ucanaccess.jdbc.UcanaccessErrorCodes)
except for the internal UCanAccess or Jackcess exceptions that always give the error code UcanaccessErrorCodes.UCANACCESS_GENERIC_ERROR.
UcanAccess 188.8.131.52 Release
-Now UCanAccess completely supports calculated fields, even in insert and/or update statements.
-Speeded the first connection (loading time) when dealing with large databases, especially with the memory=false. Optimized the use of resources(memory, filesystem).
-Upgrade to jackcess 2.0.5. A later fix from the jackcess trunk to the 2.0.5 release has been included in this distribution.
It's limited to the class LongValueColumnImpl and it's about empty memo values. Source file version: http://svn.code.sf.net/p/jackcess/code/jackcess/trunk/src/main/java/com/healthmarketscience/jackcess/impl/LongValueColumnImpl.java?p=884.
UcanAccess 2.0.9 Release
-A major issue, involving some already damaged mdb, has been solved: the data of some table could be partially loaded with the previous UCanAccess versions.
A table in an access database has a row count in its metadata(saved in the database), and this issue was due to a misalignment between row count in the metadata and the real number of table rows.
So, it has been fixed in UCanAccess 2.0.9 by avoiding to rely on this metadata information while loading data.
Also, UCanAccess 2.0.9 will raise a warning in these cases, suggesting to repair the mdb file.
-Strongly improved the UCanAccess behaviour in the case of multiple processes concurrency.
-Added connection parameter columnOrder to allow to use, in your SQL, the "display" order ("data" order is still the default)
e.g., jdbc:ucanaccess://c:/db/cico.mdb;COLUMNORDER=DISPLAY. Once a column order has been setted on the first
connection to a given database, the column order setting will be the same for all the following connections to that database, in the whole VM life.
-Allowed special characters and blank spaces in DDL.
Column and table names, in this specific case, are not more escaped in the persisted table definition on the access file, but just in the mirror hsqldb database.
e.g. CREATE TABLE [result ccc]( [aa a()] autoincrement PRIMARY KEY, [---bghhaaf b aa] text(222) default 'vvv')
DROP TABLE [result ccc]
Notice that you have to use square brackets or backtick around the column/table name.
-Allowed the use of the "autoincrement" keyword as "counter" synonymous in DDL.
-Implemented @@IDENTITY: SELECT @@IDENTITY query will return the latest generated key in the context of the current connection.
-Implemented Statement.getGeneratedKeys(): it returns the latest key generated by the statement.
-Allowed filtering on complex type columns(version, attachment, multi-value).
You can do that through one of the following functions: Equals, EqualsIgnoreOrder, Contains. For more details see the UCanAccess web site.
-Implemented Partition function.
-Added support to PreparedStatement.setTime
UcanAccess 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.
UcanAccess 184.108.40.206 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 # delimiters and thus pass a date value instead of a character string.
UcanAccess 2.0.7 Release
-Strongly enhanced internationalisation 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.
UcanAccess 220.127.116.11 Release
-Fix bugs on Format and IsNumeric functions implementation: fixed the internationalization of numeric values, introduced internationalization (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.
UcanAccess 18.104.22.168 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.
UcanAccess 22.214.171.124 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).
UcanAccess 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 compatibility with the access permissive syntax), when detected in access queries.
UcanAccess 2.0.5 Release
-Fix bug on handling scale 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.
UcanAccess 126.96.36.199 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.
UcanAccess 2.0.4 Release
Fix bug 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).
UcanAccess 2.0.3 Release
-Changed UCanAccess behaviour in handling (incorrect) default column values with a size greater than the column size.
UCanAccess has now the same behaviour 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.
UcanAccess 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>
Original path and new path must be separated by '|', different pairs must by separated by '&'.;
-Added parameter keepMirror for keeping the mirror hsqldb database after the VM ends and so for reusing it in the following VM process.
It should be used with very large databases (e.g., 1GB with many OLE binary data) and when UCanaccess takes too time to establish the first connection.
-Added support for ISO no roman characters and euro symbol in column and table names.
-Added "END" to the keywords list.
-Fixed minor bugs on DatabaseMetadata. Thanks to the SQLeo team.
UcanAccess 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).
UcanAccess 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 de-sync 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
You can read the whole change log in the ucanaccess web site.