Wish Update UCanAccess download to include HSQLDB 2.5.1 or later.

[Chris2]

Short version:
The hsqldb-2.5.0.jar included in the ucanaccess file download from https://www.b4x.com/android/forum/threads/opening-ms-access-databases-mdb.107963/, causes an error on initialisation if the Microsoft Access database has a table field name with certain reserved words.
Using hsqldb-2.5.1.jar from https://mvnrepository.com/artifact/org.hsqldb/hsqldb/2.5.1 instead seems to solve the problem.

Hence this request to update the zip file download on the B4J UCanAccess code snippet page to include the later HSQLDB jar version. Or perhaps the latest version (2.6.1 at time of writing) assuming no compatibility issues with the other files.

Long version:
I was trying to use UCanAccess to get data from an Access database but whenever I initialised the connection with

#AdditionalJar: ucanaccess-5.0.0
#AdditionalJar: commons-lang3-3.8.1
#AdditionalJar: commons-logging-1.2
#AdditionalJar: hsqldb-2.5.0
#AdditionalJar: jackcess-3.0.1-B4J
#IgnoreWarnings: 15

Sub AppStart (Form1 As Form, Args() As String)
'change path as needed
sql.InitializeAsync("sql", "net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://C:/B4J/AccessDB/test.mdb;memory=false"$, "", "") 
End Sub

Private Sub sql_Ready (Success As Boolean)
    Log("sql_Ready, success=" & Success)
End Sub

the following error was generated:

Spoiler: Full Error

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.0-SNAPSHOT unexpected token: SMALLINT required: FOR
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:58)
at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:99)
at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:289)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: SMALLINT 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)
... 11 more
Caused by: org.hsqldb.HsqlException: unexpected token: SMALLINT 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)
... 24 more
sql_Ready, success=false

I eventually narrowed the problem down to a table in the Access database that had a field/column named 'Period'.
Changing the field name to 'Periods' or pretty much anything else solved the problem, but that won't always be possible.

Based on the suggestion at https://stackoverflow.com/questions...-using-dbeaver-and-the-default-ucanaccess-5-0 , I tried the later HSQLDB jar which solved this problem, and I guess would do so for others that have table fields named as reserved words.

Thanks.

 

[Erel]

Have you tried it with v2.6.1: https://mvnrepository.com/artifact/org.hsqldb/hsqldb/2.6.1 ?

 

[Chris2]

Not yet, but I'll try to make some time today to give it a go and report back.

 

[Chris2]

Have you tried it with v2.6.1: https://mvnrepository.com/artifact/org.hsqldb/hsqldb/2.6.1 ?

I have now, and v2.6.1 works for me too.