Trying to use Quest Database as Historian DB . i am able to build connection using JDBC drive .able to read from preconfigured Table .
But , Ignition failed to write to the DB showing below errors when Ignition trying to create Tables for Historian . any advise on that .. Do we need to create special translator for Quest DB ?
Not familiar with QuestDB in particular but - was Ignition able to create the initial tables or no? If not, I suspect you have to grant some roles to whatever user you have Ignition logging in as to be allowed to create tables/write to them.
Usually DB also have separate roles for data writer/reader. You may need to grant writer permissions to your user to write to the table as well.
Ignition unable to create table due to below error , it seems we have to create special translator
java.lang.Exception: Error running query: CREATE TABLE sqlth_drv ("id" SERIAL NOT NULL,"name" varchar(255),"provider" varchar(255),PRIMARY KEY ("id"))
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:140)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.createTable(DBTableSchema.java:109)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.verifyAndUpdate(DBTableSchema.java:91)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.checkTables(TagHistoryDatasourceSink.java:1659)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.initialize(TagHistoryDatasourceSink.java:299)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:544)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: unsupported column type: SERIAL Position: 30
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:265)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:341)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:341)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:867)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:138)
There's the problem. Don't know what the solution would be unfortunately. Your Quest Database doesn't like the SERIAL part of CREATE TABLE sqlth_drv ("id" SERIAL NOT NULL.
Are you using the built-in Postgres SQL Driver? Or did you find a Quest JDBC driver and install that? Postgres uses SERIAL as a keyword but seems like Quest does not, at least not out of the box. You should see if there is a JDBC driver for Quest Databases specifically and install / use that for your gateway instead of using the Postgres driver.
There's no official JDBC driver but using Postgress as it should work ..
I can read and able to connect to QuestDB but for auto table vreation for Historian table e ignition used Postgress syntax which is wrong with Quest DB.
But when using ignition Query Browser tools and write using CREAT Table
query it's working fine using Quest Syntax
I believe the issue is that when you have Ignition auto-create these tables, it defers to the driver for their particular DB's syntax and Postgres wants to use SERIAL for the primary keys which Quest does not seem to have. In fact a little research seems to imply QuestDB doesn't have PK or FKs - Does QuestDB supports Foreign key, Primary key, references? - Stack Overflow
Is QuestDB not a RDBMS? If not, I do not think you will have much luck with making it with Ignition and the auto-create tables for things like historian as I believe those are expecting a RBDMS database like MySQL, Postgres, SQL Server etc.
Ewww! That means performance is likely to suck, too.
Yea I never heard of it before now. I know in some situations NoSQL databases can have better performance that RDBMS but they also tend to be a bit looser with data constraints. I much prefer the fixed schemas of RDBMS over how NoSQL does things.
