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.

The OP wants to know what causes the error, specific to use of ucanaccess with ms-access databases via hsqldb. It is not a general questions about the meaning of precision and scale. — fredt, Dec 27 '18 at 21:04
Exactly, thank you, precision and scale definitions are not too far off of what I originally understood, but why would this be an issue in this scenario? — LeedMx, Dec 27 '18 at 21:08
Can you provide a [mcve] including download link(s) for sample .mdb and .accdb files that can be used to reproduce the issue? — Gord Thompson, Dec 28 '18 at 10:37
Just added the example, the link was dead for a bit there, it should be working now — LeedMx, Dec 28 '18 at 18:34

score 3 · Answer 1 · answered Dec 27 '18 at 23:17

I took the liberty of updating your title. Hopefully the new wording conveys what you mean.

Please do the following:

a) Query the column definitions in question directly. For example:

// GetMetaData():
try (ResultSet rsMD = connChem.getMetaData().getTables(null, null, null, null)) {
   while (rsMD.next()) {
       String tblName = rsMD.getString("TABLE_NAME");
       System.out.println(tblName);
   }
}

... or ...

-- SQL "select" from MSysObjects
SELECT Name
FROM MSysObjects
WHERE Left([Name],1)<>"~"
AND Left([Name],4)<>"MSys"
AND Type In (1,4,6)
ORDER BY Name;

b) Compare the DB column definitions (above) with the corresponding Hibernate class members (for example, in your favorite IDE).

c) Compare the results for an "old" .mdb (which "works") vs. a "new" .accdb.

Please update your post with the results.

Thanks for the renaming, so far the table information does not throw anything different between the two, will try to get column metadata and let you know — LeedMx, Dec 28 '18 at 15:59

score 2 · Answer 2 · answered Dec 27 '18 at 20:24

Precision is the maximum total number of digits in a decimal. Scale is the number of digits to the right of the decimal point (ref). So if you had decimal(20,10) then it would be a decimal with 10 digits to the left, and 10 digits to the right of the decimal for a total of 20 digits. i.e. 1234567890.1234567890. The maximum precision is 38, default is 18.

Examples

decimal(38,0): '12345678912345678912345678912345678900'
decimal(38,1): '1234567891234567891234567891234567890.0'
decimal(38,2): '123456789123456789123456789123456789.00'
decimal(38,3): '12345678912345678912345678912345678.900'
decimal(10,8): '12.12345678'

Or `1234567.89` has `precision: 9` and `scale: 2`; `decimal(9,2)`. — paulsm4, Dec 27 '18 at 20:30

Gord Thompson · Accepted Answer · 2019-01-05T16:33:14.460

2

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.

edited Jan 05 '19 at 16:33

answered Dec 29 '18 at 14:17

Gord Thompson

116,920
32
215
418

Thank you, as an update: UCanAccess still throws the exception using jackcess-2.2.1 – LeedMx Jan 04 '19 at 19:59
@LeedMx - There appears to be a typo in James' comment on the ticket. The patch was committed after 2.2.1 was released, so the fix will be included in 2.2.2. – Gord Thompson Jan 04 '19 at 20:15
And indeed it does!, Thank you, hope all open source endevours are filled with good will man like yourself sir! – LeedMx Jan 05 '19 at 16:32

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

3 Answers3