Can connect from Java to MS Access with jackcess but cannot with ucanaccess

Question

I will have to move from java 6 to java 8, so have to replace jdbc:odbc with something else.

I tried ucanaccess, but have exception:

net.ucanaccess.jdbc.UcanaccessSQLException: data exception: numeric value out of range

readAllSites2 [ucanaccess] (C:\Mines\TestDBase.mdb)
driver: net.ucanaccess.jdbc.UcanaccessDriver
database: jdbc:ucanaccess://C:\Mines\TestDBase.mdb
readAllSites2 ERROR: net.ucanaccess.jdbc.UcanaccessSQLException: data exception: numeric value out of range
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: numeric value out of range
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:247)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at Aura.ReadSites.readAllSites2(ReadSites.java:92)
    at Aura.ReadSites.readAllSites(ReadSites.java:26)

Caused by: java.sql.SQLDataException: data exception: numeric value out of range
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setObject(Unknown Source)
    at net.ucanaccess.converters.LoadJet.execInsert(LoadJet.java:1326)
    at net.ucanaccess.converters.LoadJet.access$7(LoadJet.java:1322)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:775)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTablesData(LoadJet.java:928)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:972)
    at net.ucanaccess.converters.LoadJet$TablesLoader.access$3(LoadJet.java:966)
    at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1361)
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:236)
    ... 20 more
Caused by: org.hsqldb.HsqlException: data exception: numeric value out of range
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.types.NumberType.toDouble(Unknown Source)
    at org.hsqldb.types.NumberType.convertToDouble(Unknown Source)
    at org.hsqldb.types.NumberType.convertToDefaultType(Unknown Source)
    ... 30 more

when trying to connect. the query using jackcess works, but apparently is not save. Here is the code:

private int readAllSites1(String dbName) 
{
  System.out.println("readAllSites1 [jdbc.odbc] ("+dbName+")");
  vAllSites.removeAllElements();
  String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
  String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + dbName + ";DriverID=22;READONLY=true}";  
  System.out.println("driver: "+driver);
  System.out.println("database: "+database);
  String tableName = "Configurations";
  try 
  {
     Class.forName(driver);  
     Connection con = DriverManager.getConnection( database ,"",""); 
     Statement s = con.createStatement();
     s.execute("select SiteID,SiteName,PositionX,PositionY,PositionZ,Pvelocity,Svelocity from \""+tableName+"\" order by SiteID");
     ResultSet r = s.getResultSet(); 
     if (r == null) 
     { 
        s.close(); con.close(); 
        System.out.println("readAllSites1: ResultSet is null  exiting..."); 
        return(-1);
     }
     while ( r.next() ) 
     {
        ASite ae = new ASite();
        ae.ID = r.getInt("SiteID");
        ae.Name = r.getString("SiteName");
        double X = r.getDouble("PositionX"); 
        double Y = r.getDouble("PositionY"); 
        double Z = r.getDouble("PositionZ");
        ae.convertCoords(X,Y,Z);
        ae.PVel = r.getFloat("Pvelocity");
        ae.SVel = r.getFloat("Svelocity");
        vAllSites.addElement(ae);
     }
     s.close(); 
     con.close(); 
  }
  catch (Exception ex) 
  { 
     System.out.println("readAllSites1 ERROR: " + ex); 
     ex.printStackTrace();
     vAllSites.removeAllElements();
     return(-1);
  }
  return(vAllSites.size());
}
private int readAllSites2(String dbName) 
{
  System.out.println("readAllSites2 [ucanaccess] ("+dbName+")");
  vAllSites.removeAllElements();
  String driver = "net.ucanaccess.jdbc.UcanaccessDriver";
  String database = "jdbc:ucanaccess://" + dbName;  
  System.out.println("driver: "+driver);
  System.out.println("database: "+database);
  String tableName = "Configurations";
  try 
  {
     Class.forName(driver);
     Connection con = DriverManager.getConnection( database ,"",""); //Line 92 - here is exception
     System.out.println("connected ");
     Statement s = con.createStatement();
     System.out.println("Statement created ");
     s.execute("select SiteID,SiteName,PositionX,PositionY,PositionZ,Pvelocity,Svelocity from \""+tableName+"\" order by SiteID"); // select the data from the table
     System.out.println("Statement executed ");
     ResultSet r = s.getResultSet(); 
     if (r == null) 
     { 
        s.close(); con.close(); 
        System.out.println("readAllSites2: ResultSet is null  exiting..."); 
        return(-1);
     }
     while ( r.next() ) 
     {
        ASite ae = new ASite();
        ae.ID = r.getInt("SiteID");
        ae.Name = r.getString("SiteName");
        double X = r.getDouble("PositionX"); 
        double Y = r.getDouble("PositionY"); 
        double Z = r.getDouble("PositionZ");
        ae.convertCoords(X,Y,Z);
        ae.PVel = r.getFloat("Pvelocity");
        ae.SVel = r.getFloat("Svelocity");
        vAllSites.addElement(ae);
     }
     s.close(); 
     con.close(); 
     printAllSites();
  }
  catch (Exception ex) 
  { 
     System.out.println("readAllSites2 ERROR: " + ex); 
     ex.printStackTrace();
     vAllSites.removeAllElements();
     return(-1);
  }
  return(vAllSites.size());
}
private int readAllSites3(String dbName) 
{
  System.out.println("readAllSites3 [jackcess] ("+dbName+")");
  vAllSites.removeAllElements();
  String tableName = "Configurations";
  try
  {    
     File dbFile = new File(dbName);
     Database db = new DatabaseBuilder().setReadOnly(true).open(dbFile);
//   Database db = DatabaseBuilder.open(dbFile);
     Table table = db.getTable(tableName);
     for (Row r : table) 
     {
        ASite ae = new ASite();
        ae.ID = r.getInt("SiteID");
        ae.Name = r.getString("SiteName");
        double X = r.getDouble("PositionX"); 
        double Y = r.getDouble("PositionY"); 
        double Z = r.getDouble("PositionZ");
        ae.convertCoords(X,Y,Z);
        ae.PVel = r.getFloat("Pvelocity");
        ae.SVel = r.getFloat("Svelocity");
        vAllSites.addElement(ae);
     }
     db.close();
  }
  catch (Exception ex) 
  { 
     System.out.println("readAllSites3 ERROR: " + ex); 
     ex.printStackTrace();
     vAllSites.removeAllElements();
     return(-1);
  }
  return(vAllSites.size());
}

readAllSites1 uses jdbc:odbc and works

readAllSites2 uses ucanaccess and does not work

readAllSites3 uses jackcess and works

The exception numeric value out of range is strange as it happened when trying to connect, not during reading

this could happen with access 97 and a very old ucanaccess version. What's the ucanaccess version you're using? Other reason may be the misalignment between data and metadata, but in this case I would need a partial copy of your db in order to reproduce the issue. — jamadei, Jul 23 '15 at 07:36
ucanaccess is 2.0.9.5 (downloaded last week) and Access 2002 I am running Java 1.6. — Ted Stankiewicz, Jul 23 '15 at 08:28
May you send or share the db someway? If you want,you can use the ucanaccess forum on sourceforge. — jamadei, Jul 23 '15 at 10:11
I cannot send the db right now - it belongs to customer. My question is: may I use jackcess safely (I only need to read data)? — Ted Stankiewicz, Jul 23 '15 at 11:38
(cc: @jamadei) - You don't necessarily need to share the entire database, just a small .mdb or .accdb file containing a single table with a single row will suffice provided that the file can recreate the issue. BTW have you tried using Access to do a "Compact and Repair Database" on the file to see if that helps? — Gord Thompson, Jul 23 '15 at 12:34
On surgeforce in cookbook it says Important note, always make sure you close a Database instance when you are finished with it (preferably in a finally block like any other important resource). Failure to close the Database instance could result in data loss or database corruption. — Ted Stankiewicz, Jul 23 '15 at 13:50
There is one database on the mine containing info about mining tremors. It is updated when new tremor occurred. That is done by other software. My program reads the info from data base and creates some vizualizations and reports. It can be used by several people in the same time - and unsafe means possibility of data corruption. — Ted Stankiewicz, Jul 23 '15 at 14:01
Yes I tried to do a "Compact and Repair Database" but have the same exception: data exception: numeric value out of range — Ted Stankiewicz, Jul 23 '15 at 14:04
And what did you mean with " jackcess is not save"? See also the Gord's suggestion, we can't help you otherwise. — jamadei, Jul 23 '15 at 14:21
Jackcess opens the database file directly (without using the Access Database Engine or any other "traffic manager") so it does not make any effort to accommodate multiple concurrent users. That said, if your app opens the database as read-only then there is little chance of data corruption, even with multiple users. (Simultaneous writes would be a different matter entirely.) — Gord Thompson, Jul 23 '15 at 17:31
Sorry, "jackcess is not save" was misspelling - it should be "not safe". — Ted Stankiewicz, Jul 24 '15 at 09:06
Thank you Gord, that was I hoped. Anyway I will ask my customer to prepare small db without sensitive data, which I can share with you. — Ted Stankiewicz, Jul 24 '15 at 09:10

Can connect from Java to MS Access with jackcess but cannot with ucanaccess

0 Answers0