UcanaccessSQLException thrown when opening MS Access File

[Jefferson Poe]

Hello,

When trying to open this MS Access file (file too large to share on forum), we're getting the following exception:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.0 unexpected token: VARCHAR required: FOR

    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at Runner.testOpenProblemAccessFile(Runner.java:20)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.mockito.internal.runners.JUnit45AndHigherRunnerImpl.run(JUnit45AndHigherRunnerImpl.java:37)
    at org.mockito.runners.MockitoJUnitRunner.run(MockitoJUnitRunner.java:62)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: VARCHAR required: FOR
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
    at net.ucanaccess.converters.LoadJet.exec(LoadJet.java:1510)
    at net.ucanaccess.converters.LoadJet.access$000(LoadJet.java:74)
    at net.ucanaccess.converters.LoadJet$TablesLoader.createSyncrTable(LoadJet.java:472)
    at net.ucanaccess.converters.LoadJet$TablesLoader.createSyncrTable(LoadJet.java:399)
    at net.ucanaccess.converters.LoadJet$TablesLoader.createTable(LoadJet.java:823)
    at net.ucanaccess.converters.LoadJet$TablesLoader.createTable(LoadJet.java:767)
    at net.ucanaccess.converters.LoadJet$TablesLoader.createTables(LoadJet.java:976)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:1070)
    at net.ucanaccess.converters.LoadJet$TablesLoader.access$3200(LoadJet.java:264)
    at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1579)
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:218)
    ... 32 more
Caused by: org.hsqldb.HsqlException: unexpected token: VARCHAR required: FOR
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
    at org.hsqldb.ParserBase.readThis(Unknown Source)
    at org.hsqldb.ParserTable.readPeriod(Unknown Source)
    at org.hsqldb.ParserTable.readAndAddPeriod(Unknown Source)
    at org.hsqldb.ParserTable.readTableContentsSource(Unknown Source)
    at org.hsqldb.ParserTable.compileCreateTableBody(Unknown Source)
    at org.hsqldb.ParserTable.compileCreateTable(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 45 more

Here is the code snippet used to open it:

String connectionString = "jdbc:ucanaccess://C:/Users/jpoernomo/Downloads/Modat.accdb";
        Properties props = new Properties();
        props.setProperty("immediatelyReleaseResources", "true");
        props.setProperty("memory", "true");
        try (Connection connection = DriverManager.getConnection(connectionString, props)) {
            DatabaseMetaData metaData = connection.getMetaData();
            metaData.getCatalogs();
        }

Seems the issue is from when hsqldb is making a mirror copy of the tables?
Any hints would be very much appreciated. Thanks in advance!

[Fred Toussi]

One of the tables has a column named PERIOD. This is treated by the particular version of HSQLDB bundled with UCanAccess as an SQL keyword, rather than the name of the column and an error is raised when the next token is not what it expects.

You can probably use the latest versions of HSQLDB which avoids this issue, instead of the bundled version (in the /lib directory). Alternatively, change the name of the column in the Access database.

[Marco Amadei]

Thank you Fred, I didn't treat PERIOD as keyword, next version will fix it for better compatibility.