Create Table Syntax Error

Hello,

I’m having issues when Ignition tries to create a new table, whether that be via the Tag History Browser and/or the Transaction Group. When I copy the Create Table Query and paste into SQL Management Studio I receive an error related to the syntax. SQL Doesn’t like the (`) I believe the syntax should be (’).

Full Error:

java.lang.Exception: Error running query: CREATE TABLE group_table (`group_table_ndx` int NOT NULL AUTO_INCREMENT,`RandomInteger2` int,`RandomDouble2` double,`RandomInteger1` int,`RandomShort2` int,`RandomLong1` bigint,`RandomLong2` bigint,`RandomBoolean1` int,`RandomBoolean2` int,`RandomDouble1` double,`RandomShort1` int,`t_stamp` datetime,`quality_code` int,PRIMARY KEY (`group_table_ndx`))

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.factorysql.groups.AbstractTableItemGroup.checkTableStructure(AbstractTableItemGroup.java:186)

at com.inductiveautomation.factorysql.groups.AbstractTableItemGroup.prestartCheck(AbstractTableItemGroup.java:122)

at com.inductiveautomation.factorysql.groups.AbstractGroup.startExecution(AbstractGroup.java:309)

at com.inductiveautomation.factorysql.ProjectRunner.loadGroupResource(ProjectRunner.java:281)

at com.inductiveautomation.factorysql.ProjectRunner.lambda$onResourcesModified$2(ProjectRunner.java:173)

at java.base/java.util.ArrayList.forEach(Unknown Source)

at com.inductiveautomation.factorysql.ProjectRunner.onResourcesModified(ProjectRunner.java:173)

at com.inductiveautomation.ignition.gateway.project.ProjectLifecycle$LifecycleResourceListener.resourcesModified(ProjectLifecycle.java:176)

at com.inductiveautomation.ignition.common.project.AbstractProject.notifyResourceListeners(AbstractProject.java:349)

at com.inductiveautomation.ignition.common.project.AbstractProject.updateEffectiveState(AbstractProject.java:151)

at com.inductiveautomation.ignition.common.project.RuntimeProject.applyChange(RuntimeProject.java:271)

at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.lambda$updateOrStartAffectedProjects$5(ProjectLifecycleFactory.java:181)

at java.base/java.util.ArrayList.forEach(Unknown Source)

at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.updateOrStartAffectedProjects(ProjectLifecycleFactory.java:161)

at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.projectUpdated(ProjectLifecycleFactory.java:139)

at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.fireProjectUpdated(BaseProjectManager.java:811)

at java.base/java.lang.Iterable.forEach(Unknown Source)

at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.push(BaseProjectManager.java:516)

at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.Projects.push(Projects.java:218)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225)

at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:409)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:852)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:335)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

at org.eclipse.jetty.server.Server.handle(Server.java:530)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)

at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)

at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '`'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)

at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:703)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)

at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:857)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:138)


Reverse single quotes on identifiers are correct for MySQL. You appear to be using SQL Server, which likes square brackets for identifiers. It looks like you’ve managed to set the wrong translator for your JDBC driver.

@pturmel You are 100% correct, the translator was set to MySQL.
However the translator is set to MySQL by default for The Microsoft SQL Server JDBC Driver is a Java Database Connectivity (JDBC) 7.2. compliant driver. I changed the translator to MSSQL and everything is working correctly.