CODEWITHSUNDEEP
javams-accessjdbchsqldbucanaccess
LeedMx
LeedMx

Reputation: 434

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

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.

Upvotes: 2

Views: 1408

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123779

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.

Upvotes: 2

paulsm4
paulsm4

Reputation: 121759

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

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

  3. Please update your post with the results.

Upvotes: 3

S3S
S3S

Reputation: 25132

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'

Upvotes: 2

Related Questions