system.db.runQuery() - add a native ignition dt object to a datetime MSSQL date colum

tmahaffey · February 21, 2021, 3:36am

Here’s my code. Basically all i’m doing is trying to copy the contents stored in this dataset into a SQL table. I think its getting hung up on the fact that the SQL database’s date datatype might be differnet than ignition’s, or my “%s” might be hanging it up.

	ds = system.tag.read(meter + "/memory tags/notes").value
	if str(ds) != "None":
		for row in range(ds.getRowCount()):
			time_stamp = ds.getValueAt(row, "Date / Time") # this is stored as a date in my dataset
			event_time = ds.getValueAt(row, "Event Time") # this is stored as a date in my dataset
			operator = ds.getValueAt(row, "Operator") #String in ds
			note = ds.getValueAt(row, "Note")# String in ds
			station = meter.split("/")[-1] #String in ds
		system.db.runQuery("INSERT INTO NOTES (operator, time_stamp, event_time, notes, station) VALUES (%s, %s, %s, %s, %s)" %(operator, time_stamp, event_time, note, station))

My script blows up and the debug info comes up with about 100 lines of informaiton:

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 11, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)

	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)

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(INSERT INTO NOTES (operator, time_stamp, event_time, notes, station) VALUES (dgfarnsworth, Tue Jan 12 10:57:13 GMT-07:00 2021, Tue Jan 12 10:56:46 GMT-07:00 2021, PM ENGINE 01/06/2021, KINGS CANYON CHECK), , )


	at org.python.core.Py.JavaError(Py.java:552)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)

	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 org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:523)

	at org.python.core.PyObject.__call__(PyObject.java:480)

	at org.python.core.PyObject.__call__(PyObject.java:484)

	at org.python.pycode._pyx271.f$0(<input>:2)

	at org.python.pycode._pyx271.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:171)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1614)

	at org.python.core.Py.exec(Py.java:1658)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:276)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:131)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:605)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:593)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.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: java.lang.Exception: Error executing system.db.runQuery(INSERT INTO NOTES (operator, time_stamp, event_time, notes, station) VALUES (dgfarnsworth, Tue Jan 12 10:57:13 GMT-07:00 2021, Tue Jan 12 10:56:46 GMT-07:00 2021, PM ENGINE 01/06/2021, KINGS CANYON CHECK), , )

	... 26 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Incorrect syntax near 'Jan'.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:871)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:837)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runQuery(ClientDBUtilities.java:325)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:329)

	... 24 more

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

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

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

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

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

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

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

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

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

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:693)

	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:310)

	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:310)

	at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeQuery(DelegatingStatement.java:64)

	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeQuery(SRConnectionWrapper.java:820)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:80)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:68)

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

	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$NotAsyncServlet.service(ServletHolder.java:1391)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)

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

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

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

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

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

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)

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

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)

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

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

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

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<input>", line 11, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)

	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)

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(INSERT INTO NOTES (operator, time_stamp, event_time, notes, station) VALUES (dgfarnsworth, Tue Jan 12 10:57:13 GMT-07:00 2021, Tue Jan 12 10:56:46 GMT-07:00 2021, PM ENGINE 01/06/2021, KINGS CANYON CHECK), , )

I think it is a problem with the datetime because of this line in the debug info: “Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Incorrect syntax near ‘Jan’.”

Here’s my table:

Here’s my dataset:

I think I might just have to try a named query but I’m curious what might be the issue with my script here. Thanks a bunch.

Kevin.Collins · February 21, 2021, 4:38am

Take a look at system.db.runPrepUpdate(); it will be better about mapping in the data types. Also, runQuery() won’t let you perform INSERT’s/UPDATE’s, you’d need runUpdateQuery() to do that.

lrose · February 22, 2021, 1:58pm

As @Kevin.Collins says, you should be using runPrepUpdate() for a few reasons

runPrepUpdate protects against SQL Injection.
helps map to the correct Data Types
runQuery() and runPrepQuery will not allow you to run UPDATE type queries

To answer your question however, since you were using string insertion you are responsible for all syntax in the query. Since you are providing strings to the query, you would need to use the proper notation for your database engine. For MSSQL that is the single quote.

So you’re script should look like this:

system.db.runUpdateQuery("INSERT INTO NOTES (operator, time_stamp, event_time, notes, station) VALUES ('%s', '%s', '%s', '%s', '%s')" %(operator, time_stamp, event_time, note, station))

However, don’t do that. Instead as discussed use a prepared query (named queries are also a good (some would argue preferred) solution here).

system.db.runPrepUpdateQuery("INSERT INTO NOTES(operator,time_stamp,event_time,notes,station) VALUES(?,?,?,?,?)",[operator,time_stamp,event_time,note,station])