Try to add a jdbc driver for HQSLDB

Ignition
1

I try to add a jdbc driver for HSQLDB.

I’ve started a server with the following cmd :
java -cp D:/Appli/itinisere/BDD/hsqldb-2.2.5/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:D:/Appli/itinisere/BDD/test/test --dbname.0 itinisere

I success to connect with HSQLDB ManagerSwing or java application.

The database connexion is faulted from ignition gateway !
Faulted

rg.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (unexpected end of statement)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:189)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:145)com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1086)java.lang.Thread.run(Thread.java:662)
java.sql.SQLSyntaxErrorException: unexpected end of statement
org.hsqldb.jdbc.Util.sqlException(Util.java:350)org.hsqldb.jdbc.Util.sqlException(Util.java:227)org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1830)org.hsqldb.jdbc.JDBCStatement.executeQuery(JDBCStatement.java:181)org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:189)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:145)com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1086)java.lang.Thread.run(Thread.java:662)

bug_jdbc_hsqldb.png1400×949 73.6 KB

2

Your validation query, “Select 1”, is not a valid statement for HSQLDB, hence the “Unexpected end of statement” error. Try “select 1 from dual”

3

Hi.

I try to create new DataBase driver for HSQLDB but I get this error

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (unexpected end of statement)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:194)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:150)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1103)
java.lang.Thread.run(Thread.java:662)
java.sql.SQLSyntaxErrorException: unexpected end of statement
org.hsqldb.jdbc.Util.sqlException(Unknown Source)
org.hsqldb.jdbc.Util.sqlException(Unknown Source)
org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)
org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:194)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:150)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1103)
java.lang.Thread.run(Thread.java:662)

Can help me
Thanks - deduar

4

Sounds like you have the exact same problem as the original poster on this thread. Did you try the solution I posted two years ago (above)?

5

off course I tried first the solution (above) before to post the message, but don’t work
A one difference is that I running the HSQL on memory, the url that I using are : jdbc:hsqldb:hsql://localhost/dbName, is Not necessary the port

6

Ok, so what validation query are you running?

7

Hi, I’m using “select 1 from dual”

Thanks
deduar

8

First I think you need to use HSQLDB’s in memory connect url which is something like: jdbc:hsqldb:mem:aname
From my research it seems like the mem keyword is necessary to specify that you are running in memory.