DDL support for HYPERLINK fields

Starting with version 4.0.3, UCanAccess DDL now supports HYPERLINK fields, e.g.,

CREATE TABLE table1 (id LONG, website HYPERLINK)

ALTER TABLE table2 ADD COLUMN website HYPERLINK

Those fields are recorded in the UCanAccess metadata as 'HYPERLINK'. We can identify such fields via the ORIGINAL_TYPE column of DatabaseMetaData#getColumns, like so:

String tableName = "urlTest";
String fieldName = "website";
ResultSet rs = conn.getMetaData().getColumns(null, null, tableName, fieldName);
if (rs.next()) {
    if (rs.getString("ORIGINAL_TYPE").equals("HYPERLINK")) {
        System.out.printf("[%s].[%s] is a HYPERLINK field.%n", tableName, fieldName);
    }
}

Multiuser concurrent write access

UCanAccess uses the Jackcess API to manipulate the Access database. Jackcess performs read and write operations directly on the Access database file – .accdb or .mdb – without using the Access Database Engine. When a Jackcess process makes modifications to the database file it does so in isolation; it has no way of controlling (or even knowing) what other processes, including other Jackcess processes, might be doing to the file.

UCanAccess does support concurrent read/write operations from multiple threads of the same JVM (Java Virtual Machine) process. So, for example, multiple concurrent users are able to update the database if they connect to a Java web application or web service that uses UCanAccess to perform the updates from a single JVM process.

However, UCanAccess cannot support separate concurrent processes making simultaneous changes to the Access database. This includes:

• Concurrent updates by several users of a stand-alone UCanAccess application that connects directly to the same Access database from different machines,
• A UCanAccess application directly updating the database while another UCanAccess application on the same machine updates the same database from a different JVM process, e.g., a background process or web application that writes to the database,
• A UCanAccess application updating the database while other processes perform updates with applications that use the Access Database Engine, e.g., Microsoft Access itself, or applications that use Microsoft’s own data-access technologies (Access ODBC, Access OLEDB, or ACE/Jet DAO).

UCanAccess does support multiple concurrent processes where:

• all processes read the database but do not write to it, or
• only one process writes to the database while all other processes just read it,

although in the latter case

1. There could be a significant performance penalty because if UCanAccess detects that the Access database has been changed by another process then it must re-load the entire database to ensure that it has the most current information, and
2. If a UCanAccess application updates a database while other users have the same database open in Microsoft Access itself then the Access users may have to close and re-open the database before they can see the changes made by the UCanAccess application.

Adding an AutoNumber primary key field to an existing table

Recent enhancements in data-definition language (DDL) handling are a welcome addition. However, there is still one case that UCanAccess DDL does not cover effectively: adding an AutoNumber primary key column to an existing table.

Yes, we can now use

st.execute("ALTER TABLE [Table1] ADD COLUMN [ID] COUNTER DEFAULT 0");

and that will in fact add an AutoNumber [ID] column to the table [Table1], but that column will be empty. Furthermore, it is not obvious how we might fill that column with sequential values, given the limitations on updatable resultsets caused by the way UCanAccess interacts with HSQLDB.

Fortunately, the solution is simple and very fast: just use Jackcess to add the AutoNumber primary key field. Since Jackcess is one of UCanAccess' dependencies it is already available to us, so all we need to do is something like this:

import java.io.File;
import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.DataType;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.IndexBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;

// ...

String dbPath = "C:/Users/Public/example.accdb";
DatabaseBuilder dbb = new DatabaseBuilder(new File(dbPath))
        .setAutoSync(false);
try (Database db = dbb.open()) {
    Table tbl = db.getTable("Table1");
    new ColumnBuilder("ID", DataType.LONG)
            .setAutoNumber(true).addToTable(tbl);
    tbl.setAllowAutoNumberInsert(true);
    int i = 1;
    for (Row r : tbl) {
        r.put("ID", i++);
        tbl.updateRow(r);
    }
    new IndexBuilder("PrimaryKey").addColumns("ID")
            .setPrimaryKey().addToTable(tbl);
}

Note that it would be a very good idea to NOT have a UCanAccess connection open to that database when running the above code.

Inserting arbitrary values into an AutoNumber field

UCanAccess 4.0.2 added the ability to insert arbitrary values into an AutoNumber field (Long Integer, incrementing). For technical reasons, UCanAccess does not enable this behaviour by default; we use the statement  DISABLE AUTOINCREMENT ON tablename  to activate the ability to perform such insertions.

Sample code:

Statement st = conn.createStatement();
st.execute("CREATE TABLE test (ID COUNTER PRIMARY KEY, TextField TEXT(50))");
st.execute("INSERT INTO test (TextField) VALUES ('Alpha')");  // -> ID=1
    
// direct insert into AutoNumber field is OFF by default
st.execute("INSERT INTO test (ID, TextField) VALUES (3, 'Bravo')");  // -> ID=2, not 3
    
// allow direct insert into AutoNumber field
st.execute("DISABLE AUTOINCREMENT ON test");
st.execute("INSERT INTO test (ID, TextField) VALUES (7, 'Golf')");  // -> ID=7
st.execute("INSERT INTO test (ID, TextField) VALUES (4, 'Delta')");  // -> ID=4
    
// revert to default behaviour
st.execute("ENABLE AUTOINCREMENT ON test");
st.execute("INSERT INTO test (TextField) VALUES ('Hotel')");  // -> ID=8

Notes:

1. Direct insert of values ≤ 0 is currently not now supported as of version 4.0.3.
2. If you use  DISABLE AUTOINCREMENT ON tablename  then you must always supply a value for the AutoNumber column. Later on, if you need AutoNumber values to be automatically assigned then you need to  ENABLE AUTOINCREMENT ON tablename  again.

Tip: Loading Selected Tables

When connecting to an Access database, UCanAccess copies all of the data from each Access table into a corresponding table in the HSQLDB backing database. So, for example, if we connect directly to the “Northwind.accdb” database then UCanAccess will copy all twenty (20) tables:

If we only need to work with a subset of those tables then we can shorten the load time and reduce memory consumption of our Java application by first creating a “linking database” that only includes the tables of interest.

Say for example that we only need to work with the [Customers], [Orders], and [Order Details] tables. If we create a new Access database named “LinkToNorthwind.accdb” and then create table links into “Northwind.accdb” …

… for just those three tables …

… then when we have UCanAccess connect to “LinkToNorthwind.accdb” it will only populate the HSQLDB backing database with the three tables we need to use …

… and ignore the other seventeen (17) tables that are of no interest to us.

Bonus Tip: If you want to dynamically achieve the same result from your Java application without first having to manually create a separate "linking database", check out the following code:

https://gist.github.com/gordthompson/434c4985a261321ae4650733151653b9

A blog about the UCanAccess project knowledge sharing.
We would like to involve more and more users in the UCanAccess JDBC driver evolution.