Softpedia >Mac >Developer Tools >UCanAccess >  Changelog

UCanAccess Changelog

What's new in UCanAccess 5.0.1

Jul 30, 2021
  • Fix updates failed in tables with Blob/Ole not null
  • Insert/Update of Blob/Ole objects in this simplified way: preparedStatement.setObject(1, new File("c:\"));

New in UCanAccess 5.0.0 (Jul 30, 2021)

  • BLOB insert failed for table with multi-column PK
  • Add "Currency" as named format for Format function
  • CREATE TABLE with underscore in table name could cause error
  • Upgrade to hsqldb-2.5.0 and jackcess 3.0.1
  • Support for java.time.LocalTime parameters to prepared statements
  • ParametersTest failures under HSQLDB 2.4.x
  • Date/​Time values corrupted by JVM timezone
  • It now requires Java 8 or later to run

New in UCanAccess 4.0.4 (Jan 8, 2020)

  • Upgrade to Jackcess 2.1.11
  • Support Access_2016 "version 5" file format
  • Initial support for "Large Number" (BIGINT) columns: CRUD, DDL
  • Fix issue with NOT NULL columns created by UCanAccess DDL not respected by ACE/Jet
  • Fix issue with multiple FK constraints between the same two tables
  • Reduce HSQLDB resource consumption by lazy-loading "OLE Object" (BLOB) fields

New in UCanAccess 4.0.3 (Jan 8, 2020)

  • Upgrade to Jackcess 2.1.9
  • Enable arbitrary AutoNumber insert values = 7 compilers
  • Fix getBlob for null values
  • Fix constraint breach warning referring to wrong row
  • Fix regional settings issue under non-US locales

New in UCanAccess 4.0.2 (Jun 26, 2017)

  • Optimized the preparser
  • Fix problem with remap= under Linux
  • Fix bug with built-in functions used in calculated field expressions. Added fake left$ and right$ functions
  • Patched mapping of CHAR type when used in DDL
  • Fix insert of arbitrary AutoNumber values
  • Fix bug with the setting of Null values on complex type columns(i.e., Attachment, Multivalue)
  • Fix bug on column aliases containing an accented character (aliases generated by Hibernate)
  • Enhanced the console features:
  • Better escaping of exported CSV fields with embedded delimiters and quotes.
  • Add -d flag to change delimiter
  • Add -t flag to export large tables directly
  • Add --big_query_schema flag to export the Google BigQuery schema file
  • Add --newlines flag to preserve embedded newlines when exporting to CSV
  • Print UTF-8 byte order mark if --bom flag is given
  • Fix incorrect SimpleDateFormat which outputs 12:00:00 for midnight in the "export" command

New in UCanAccess 4.0.1 (Jan 31, 2017)

  • Fixed bug that could affect columns order when creating a new table via ALTER TABLE statement and using a particular DDL statements sequence.
  • Minor fix on the UCanAccess console

New in UCanAccess 4.0.0 (Jan 14, 2017)

  • Table renaming, e.g.: ALTER TABLE [My old name] RENAME TO [My new name]
  • Add new Column to existing table, e.g.: ALTER TABLE xxx ADD COLUMN yyy TEXT ALTER TABLE zzz ADD COLUMN kkk DATETIME NOT NULL DEFAULT now() ALTER TABLE [222 crazy name] ADD COLUMN [another crazy name] numeric (23,6) default 13.031955 not null
  • Add new Index to existing table, e.g.: CREATE unique INDEX [index name] on [table name] (a ASC,c ASC ) CREATE INDEX [index name] on bbb (a ASC,c ASC )
  • Add Primary Key to existing table, e.g.: ALTER TABLE xxx add Primary key (column1,column2)
  • Foreign Key creation, e.g.: ALTER TABLE x add constraint constr foreign key (colx1,colx2) references y (coly1, coly2) ON delete cascade Now you can also create FKs while creating a new table, e.g., CREATE TABLE Babe( k LONG , y LONG, PRIMARY KEY(k,y),FOREIGN KEY (y) REFERENCES Parent (x) )
  • Upgrade to Jackcess 2.1.6

New in UCanAccess 3.0.7 (Oct 17, 2016)

  • Fixed aggregate functions on the datatype DATE (e.g. select first(date_init) from ...)
  • Fixed update queries having a parametric condition in the where clause (e.g. UPDATE COL1 SET COL1=[PAR1] WHERE COL2=[PAR2])
  • Optimized the CLNG function
  • Fixed a few bugs the happened when the parameter keepMirror is being used: see ReloadPersistentMirrorTest, a bug on the ORDER BY case-sensitivity when the access db is updated externally

New in UCanAccess 3.0.6 (Jun 11, 2016)

  • Enhanced DataSource implementation
  • Fixed bug that could have effect when a column name contained both a question mark '?' and a space ' ' characters
  • Fixed bug with APPEND or UPDATE access queries which used a variable of type TEXT as parameter
  • Fixed naming-conflict bug that could have effect when updating tables with the same name and a different structure from multiple databases connections

New in UCanAccess 3.0.5 (May 14, 2016)

  • Fixed major bug on single-precision numeric column management: in particular conditions it could cause misalignment between hsqldb mirror and access db
  • Enhanced DDL(Create Table) compatibility with PreparedStatement for use with other ORM tools
  • Fixed bug on keyword "ROW" (it wasn't listed as keyword)
  • When using the net.ucanaccess.jdbc.UcanloadDriver and the loader.jar, the setting
  • DUCANACCESS_HOME=D:\301\UCanAccess-3.0.1-bin is no more needed if the loader.jar of your classpath is picked up directly from a standard ucanaccess distribution (i.e. from the loader folder in the unziped content of the ucanaccess-xxx.bin.zip)

New in UCanAccess 3.0.4 (Mar 7, 2016)

  • Implemented MS Access parametric SELECT queries. They will be available, where possibile, using the "table()" syntax, e.g., select * from table(queryWithParameters(#1971-03-13#,'hi babe')) A default query, parametrized with all parameters to null, will be available as well.
  • Implemented APPEND, DELETE and UPDATE MS Access queries. They will be available, where possibile, as stored precedures and via CallableStatement, e.g.: CallableStatement cs= ucanaccess.prepareCall("{call insert_xxx(?,?,?)}");
  • fixed issue(in the connecting phase) with table/column names starting with the $ character
  • fixed issue(in querying) with table/column names within both a single quote character and a blank space character
  • fixed bug that happened in subsequent drop and create table statements when executed without calling a commit between them
  • fixed dlookup function
  • fixed capitalisation in column metadata for MS Access queries(views) like SELECT tablename.* FROM ....
  • fixed getBestRowIdentifier DatabaseMatadata method
  • the proper exception is thrown when calling executeQuery method for update, insert and delete executions
  • fixed bug on conflicting table names(e.g., my table and my_table)

New in UCanAccess 3.0.3.1 (Dec 28, 2015)

  • Fixed major bug: in several cases exclamation mark in literal textual values (i.e., if you didn't use a PreparedStatement) was saved as point.

New in UCanAccess 3.0.1 (Sep 15, 2015)

  • Fixed bug in "create table as select..." statement where there is a name conflict in columns defined in the select.
  • Fixed bug in "create table" where one or more column names are the same names of specific access types(e.g., create table ttt(counter counter primary key, text text))
  • Classloading isolation: in order to avoid conflicts in dependencies jars used by user development environment a new jar named ucanload.jar has been added to this distribution. This is strongly suggested for the use of ucanaccess with LibreOffice/OpenOffice.
  • In other words, this allows you to use in your applications/tools the version of commons-lang, commons-logging, jackcess and hsqldb you need, without any issue or impact on UCanAccess. If you are facing with a library conflict issue, you MUSTN'T add neither ucanaccess.jar nor any of the dependencies jars to your classpath but: 1. unzip the UCanAccess-3.X.X-bin.zip distribution. 2. add to your classpath just the ucanload.jar from the "loader" folder of the distribution 3. set the system variable UCANACCESS_HOME so that it points the UCanAccess-3.x.x-bin folder (e.g. -DUCANACCESS_HOME=D:\301\UCanAccess-3.0.1-bin) 4. when required (e.g. with java 6) use the net.ucanaccess.jdbc.UcanloadDriver (e.g., Class.forName("net.ucanaccess.jdbc.UcanloadDriver")) instead of the original one net.ucanaccess.jdbc.UcanloadDriver. See also the updates to the LibreOffice/OpenOffice configuration on the ucanaccess web site.

New in UCanAccess 2.0.9.5 (Apr 29, 2015)

  • Upgrade to Jackcess 2.1.0
  • Re-implemented SWITCH function with a different approach
  • Fixed 2.0.9.4 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

New in UCanAccess 2.0.9.4 (Mar 11, 2015)

  • 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.

New in UCanAccess 2.0.9.3 (Jan 19, 2015)

  • 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

New in UCanAccess 2.0.9.2 (Oct 10, 2014)

  • 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.

New in UCanAccess 2.0.9.1 (Oct 2, 2014)

  • 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.

New in UCanAccess 2.0.9 (Sep 11, 2014)

  • A major bug has been fixed for several damaged mdb: the table data was incorrectly and partially loaded in previous versions. A table in an access database has a row count in its metadata, 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.
  • 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

New in UCanAccess 2.0.8 (Aug 2, 2014)

  • Added support to not standard syntax(accepted by MS Access) DELETE * FROM TABLENAME (besides the always supported standard syntax DELETE FROM TABLENAME).
  • Fixed behavior 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.

New in UCanAccess 2.0.7.1 (Jul 17, 2014)

  • 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.

New in UCanAccess 2.0.7 (Jul 3, 2014)

  • 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.

New in UCanAccess 2.0.6.2 (Jun 2, 2014)

  • 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.

New in UCanAccess 2.0.6.1 (May 26, 2014)

  • 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).

New in UCanAccess 2.0.5 (May 3, 2014)

  • 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.

New in UCanAccess 2.0.4.1 (Apr 28, 2014)

  • 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.

New in UCanAccess 2.0.4 (Apr 10, 2014)

  • 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)

New in UCanAccess 2.0.3 (Mar 4, 2014)

  • Fix bug 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.

New in UCanAccess 2.0.2 (Jan 24, 2014)

  • Added connection parameter remap. It allows to remap the paths to one or more external linked databases.
  • 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. See NoRomanTest junit class.
  • Added "END" to the keywords list.
  • Fixed minor bugs on DatabaseMetadata. Thanks to the SQLeo team.

New in UCanAccess 2.0.1 (Nov 28, 2013)

  • 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)

New in UCanAccess 2.0.0 (Oct 22, 2013)

  • 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

New in UCanAccess 1.0.4.1 (Sep 17, 2013)

  • 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)

New in UCanAccess 1.0.4 (Sep 3, 2013)

  • Improved sql conversion so that ms 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

New in UCanAccess 1.0.3 (Aug 2, 2013)

  • 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

New in UCanAccess 1.0.2 (Jul 2, 2013)

  • 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

New in UCanAccess 1.0.1 (Jun 5, 2013)

  • 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). Tables with one or more colums of those types are readable but readonly in this development stage. Using ResultSet.getObject you can get array of net.ucanaccess.complex.Version, net.ucanaccess.complex.Attachment and so on.
  • 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

New in UCanAccess 1.0.0 (Jun 5, 2013)

  • 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 "on 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

New in UCanAccess 0.1.0 Alpha (Apr 3, 2013)

  • Jackess upgrade to version 1.2.10
  • Fixed some bugs(ccur function, #d/d/yyyy# date format, index names escaping, improved access query conversion and import)

New in UCanAccess 0.0.2 Alpha (Jan 25, 2013)

  • JDBC 4.1 compliance (so that you can compile with Java 7).
  • Integration with Maven as building tool.
  • You can customize Jackcess DB Opener(i.e., for opening and reading Microsoft Money files): see related documentation at http://ucanaccess.sourceforge.net/site.html.