HSQLDB "precision or scale out of range" error when UCanAccess loads database

Question

I'm writing tools in java that connects to different access databases. I have a particular scenario that throws the following exception -5592:UCAExc:::4.0.4 precision or scale out of range

The exception is listed under HSQLDB exceptions: 5592=42592 precision or scale out of range (so I know who generates it) and according to this answer, this other answer, and the definitions in here, both precision and scale refer to characteristics of a double.

The exception occurs only when I use ucanaccess to connect to an old mdb (pre-access 2003) trhough a frontend database with linked tables to the mdb. However, ucanaccess can connect to the old mdb directly without any issue.

If I use the frontend in Access to modify the database it works fine, so I assume MS worked around the problem at least within Access.

My guess is that my ucanaccess connection is attempting to treat the database as a more modern one, being fooled by the front-end facade. But why this exception?

Minimal Complete Verifiable Example: Here is a minimal example to replicate the issue containing the offending mdb, is a gzip tarball which includes the following jar, the databases involved and a helpful readme file.

public static void main(String[] args) {
String query= "SELECT nombre FROM encemp where cveemp=1";
    try {
        Connection frontEndToAccdb = DriverManager.getConnection("jdbc:ucanaccess://FrontEndPointingToAccdb.accdb");            
        PreparedStatement statement = frontEndToAccdb.prepareStatement(query);
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) System.out.println("Querying Accdb BackEnd through front end OK");            
        Connection directConnectionToMdb = DriverManager.getConnection("jdbc:ucanaccess://X:/BackendOld.mdb");
        statement = directConnectionToMdb.prepareStatement(query);
        resultSet = statement.executeQuery();
        if (resultSet.next()) System.out.println("Querying mdb BackEnd directly OK");            
        //This is the one that will generate the exception
        Connection frontEndToMdb = DriverManager.getConnection("jdbc:ucanaccess://FrontEndPointingToMdb.accdb");
        statement = frontEndToMdb.prepareStatement(query);
        resultSet = statement.executeQuery();
        if (resultSet.next()) System.out.println("Querying mdb BackEnd through front end OK");
    } catch (SQLException ex) {
        System.out.printf("%s:%s\n", ex.getErrorCode(),ex.getMessage());
    }

I've been struggling navigating the DatabaseMetaData and still have no clue why the offending mdb is generating the exception.

Answer 1

As it turns out, this particular issue has nothing to do with DECIMAL numbers, or numeric columns at all. It is a bug in UCanAccess caused by a bug in Jackcess where the width of an Access_97 TEXT field is mis-reported as half of its actual width.

The database in question has a table with a column defined as TEXT(1), so Jackcess getLengthInUnits reports zero (because of integer division). UCanAccess tries to create a table in the HSQLDB backing database as

CREATE  CACHED TABLE DETCAD(SERIE VARCHAR(0), ...

and the VARCHAR(0) is triggering the "precision or scale out of range" exception.

jackcess-2.2.2.jar fixes the issue. You can use it to replace the jackcess-2.1.11.jar file that ships with UCanAccess 4.0.4.