Hello Ignition Community,
I'm using Ignition 7.9.7 and am tasked with connecting to a MS Access database (.accdb)....yes, I'd much rather it was MSSQL but the guy who has the Access db will not change it. Options to install SQL Server Express and use a Linked Server or to purchase a JDBC-ODBC bridge seem like a lot of extra work and/or money. I tried both ucanaccess version 4.0.4 and 3.0.4 but to no luck using this excellent forum post:
following the post above, everything seems to work fine except when I make my database connection, Ignition takes forever to connect and then gives the following error message (for 3.0.4) here:
#######################
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (UCAExc:::3.0.4 unexpected end of statement)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:242)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:196)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1014)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 unexpected end of statement
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
... 12 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected end of statement
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.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
... 16 more
Caused by: org.hsqldb.HsqlException: unexpected end of statement
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(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)
... 19 more
7.9.7 (b2018032914)
Oracle Corporation 1.8.0_172
######################
Not sure if I need a specific version of ucanaccess to work with Ignition version 7.9.7 or if there is something that I'm missing...it seemed pretty straight forward...here's what I did (for version 4.0.4...did same thing with 3.0.4 and got the same result)
- Download ucanaccess version 4.0.4 from the following:
-
Extract the zip files to a directory you will be using (i.e. MyDocuments)
-
extract ucanaccess-4.0.4.jar using WINRAR or https://extract.me/ (this creates a "net" and "META-INF" directory)
-
Create a new Database Driver in Ignition from the Configure menu
Name: MSAccessDB
add the following JAR files
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\commons-lang-2.6.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\commons-logging-1.1.3.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\hsqldb.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\lib\jackcess-2.1.11.jar;
C:\Users\xxxx\Documents\AccessDB\UCanAccess-4.0.4-bin\ucanaccess-4.0.4.jar;
Classname: net.ucanaccess.jdbc.UcanaccessDriver
Driver type: Generic
URL Format: jdbc:ucanaccess://
default validation query: do not use SELECT 1....this needs to be SELECT 1 DUAL (very important does not work otherwise)
default translator: MSSQL
- Add database Connection in Ignition
Name: RepairsDB
Connect URL: jdbc:ucanaccess://C:\xxxxx\yyyyy.accdb
#############
anyway, any helpful guidance would be much appreciated.
FOLLOW - UP 11/26/2018
I changed the "Default Validation Query" to "SELECT 1 FROM DUAL" from "SELECT 1" as was shown in the example and able to query Access databases in Ignition......this works great!!!! 