2

Get the following error: UCAExc:::5.0.0-SNAPSHOT unexpected token: DOUBLE required: FOR unexpected token: DOUBLE required: FOR

My connection string is: jdbc:ucanaccess:///mnt/toolboss-plt11/sps.mdb everything else contains default values. I believe the mdb file is in Access 2000 format. I am able to connect to this file with Microsoft SSIS jet 4.0 driver.

Any help would be appreciated. Thank you.

brent groves
  • 71
  • 1
  • 5
  • Can you provide a download link for a sample .mdb file that can be use to reproduce the issue? – Gord Thompson Dec 08 '20 at 16:12
  • Thank you, Gord. It is a work database so I can't. Are you able to connect to any Access 2000 type mdb files with DBeaver? – brent groves Dec 30 '20 at 13:29
  • Yes, [I can](https://i.stack.imgur.com/pVVXM.png). Do you get the error when you first try to open the database, or only when you try to work with a particular table or view? – Gord Thompson Dec 30 '20 at 14:44
  • Thank you, Gord. Can you please show me your [connection string](https://ibb.co/31mNPQM) and [driver settings](https://ibb.co/gmQ0sjS). – brent groves Jan 27 '21 at 13:13
  • My settings are straight out-of-the-box defaults for the current version of DBeaver: [connection string](https://i.stack.imgur.com/INjaw.png) and [driver properties](https://i.stack.imgur.com/GLvlf.png). – Gord Thompson Jan 27 '21 at 13:56
  • Thank you again, Gord. Would you be willing to share that mdb file? – brent groves Jan 28 '21 at 13:05
  • Much appreciated, Gord. Your file works. Can you please try to connect to one of my databases? https://www.dropbox.com/s/scksrioo6kntk93/test.mdb?dl=0 – brent groves Feb 09 '21 at 13:12

1 Answers1

4

You have encountered a bug in UCanAccess/HSQLDB. When I try to open your test file using UCanAccess' console.bat I see

C:\Users\Public\Downloads\UCanAccess>console.bat
java version "1.8.0_241"
Java(TM) SE Runtime Environment (build 1.8.0_241-b07)
Java HotSpot(TM) 64-Bit Server VM (build 25.241-b07, mixed mode)

Please, enter the full path to the access file (.mdb or .accdb): /home/gord/Desktop/test.mdb
Cannot execute:CREATE  CACHED TABLE RATIONING(ITEMTYPE INTEGER,ITEMKEY VARCHAR(50),ISSUETARGETTYPE INTEGER,ISSUETARGETKEY VARCHAR(50),MAXQTY INTEGER,PERIOD DOUBLE,PERIODTYPE INTEGER) unexpected token:
 DOUBLE required: FOR

It appears that PERIOD is a reserved word in HSQLDB 2.5.0 (the version that ships with UCanAccess 5.0.x). However after upgrading to HSQLDB 2.5.1 that same CREATE statement doesn't fail.

You should post a help request on SourceForge asking Marco to update the version of HSQLDB in the UCanAccess distribution. In the meantime you can work around the issue.

Download UCanAccess-5.0.1.bin.zip and unpack it. I put my copy in "/home/gord/Downloads/JDBC/"

uca1

Go into the "lib" folder and delete "hsqldb-2.5.0.jar". Download hsqldb-2.5.1.jar to replace it.

uca2

In DBeaver, right-click your connection and choose "Edit Connection". Click the "Edit Driver Settings" button and in the Libraries tab delete the "net.sf.ucanaccess.ucanaccess:RELEASE" entry that DBeaver created. Then use the "Add File" button to add the five (5) jars from your download folder

jars

Now you should be able to open your .mdb file.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418