What's new in UCanAccess 5.0.1
Mar 3, 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:\<file path>"));
New in UCanAccess 5.0.0 (Jan 8, 2020)
- 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 (Apr 10, 2018)
- 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 (Dec 19, 2017)
- Upgrade to Jackcess 2.1.9
- Enable arbitrary AutoNumber insert values <= 0
- Fix CREATE TABLE in UCanAccess (Access unable to open table when last column was declared as AUTOINCREMENT)
- Respect constraint name when adding foreign key
- Fix Query failed when Java Locale language is Turkish: uppercasing of column name caused query to fail
- Fix WHERE clause with NOT LIKE "T#####" caused error
- Fix Hyphen in DDL column name confused PreparedStatement, e.g., CREATE TABLE zzzFoo ([Req-MTI] TEXT(20))
- Explicit DDL support for Hyperlink fields, e.g. CREATE TABLE urlTest (id LONG PRIMARY KEY, website HYPERLINK)
- Maven POM Update, Patch to UcanaccessCallableStatement for Java >= 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 (Apr 26, 2017)
- Optimized the preparser
- 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 (Feb 1, 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 13, 2017)
- Before this version only limited DDL was supported (CREATE TABLE and DROP TABLE). Now we've implemented:
- 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 18, 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 (Sep 21, 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 paramenter
- 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 (Sep 21, 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:301UCanAccess-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 (Apr 9, 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 (Jan 22, 2016)
- 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.3 (Jan 22, 2016)
- Still a fix-bugs version, enhancements coming soon.
- fixed bug related to linked tables(from external db) with a complex type column.
- fixed bug on concurrent access in append on the same table by two or more different processes when using an auto increment key.
- fixed bug on iif function that now accepts null(interpreted as false) values as first argument.
- fixed bug on like criteria: now digit intervals are supported ([4-7] or [!2-6]).
- fixed bug on metadata reloading when a concurrent process does structural upgrades(e.g., when creating a table).
- fixed bug when connecting to a database with a table/column whose the name contains the reserved character $.
- fixed bug on # special character escaping in like criteria expressions (to be escaped as usual with [#]).
- About date values antecedent to the October 15, 1582 (Gregorian):
- when using a date in literal form (e.g. #1095-05-10#, or passing the string '1095-05-10' to the dateValue function ) this is interpreted in an MS Access compatible way, i.e., pure Gregorian calendar.
- also the FORMAT function is now consistent with this convention
- when passing to a PreparedStatement a date antecedent to the October 15, 1582, it's an applicative duty to create it consistently within the proper Calendar, i.e. by using GregorianCalendar.setGregorianChange(new java.util.Date(Long.MIN_VALUE))
New in UCanAccess 3.0.2 (Jan 22, 2016)
- Fixed bug in CREATE TABLE DDL implementation, when using DECIMAL or NUMERIC columns on nullable(required) column property.
New in UCanAccess 3.0.1 (Sep 19, 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:
- unzip the UCanAccess-3.X.X-bin.zip distribution.
- add to your classpath just the ucanload.jar from the "loader" folder of the distribution
- 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) 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.
New in UCanAccess 3.0.0 (Sep 19, 2015)
- Relicense to Apache 2.0
- Upgrade to jackcess 2.1.2
- Metadata issues:
- 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 and 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
- Optimization issues:
- 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
- Changed behaviour:
- 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
New in UCanAccess 2.0.0 (Oct 23, 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 18, 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 18, 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 length 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 1, 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 (May 14, 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.1 Alpha (Apr 24, 2013)
- 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)
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 (Apr 3, 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