Possible issue with QueryString parameter in runSFNamedQuery

kissamcorrina · January 14, 2021, 8:50pm

I am seeing an error when using the function system.db.runSFNamedQuery. This is on an 8.1.1 Gateway. I have 3 parameters total: 1 is a QueryString type and the other 2 are value type. When I call the function from the project or gateway scope, I get an error that the QueryString parameter is missing even though I have included it. When I change the function to system.db.runNamedQuery, everything works fine. Is this a possible bug or am I doing something incorrectly?

See named query below (which does insert a new row successfully with system.db.runNamedQuery):

Script run in script console:

params = {"DB": "HX_Ignition.dbo", "Name": "te", "OwnerID": 7}
system.db.runSFNamedQuery(path="CreateRoster", parameters=params)

Error from script:

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 2, in <module>
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:359)

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

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

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:942)

	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.executeSFquery(DesignerNamedQueryManager.java:80)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runSFNamedQuery(ClientDBUtilities.java:177)

	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)

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Missing value for query parameter {DB}


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

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

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

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

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

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

	at org.python.pycode._pyx417.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: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Missing value for query parameter {DB}

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

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

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

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:942)

	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.executeSFquery(DesignerNamedQueryManager.java:80)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runSFNamedQuery(ClientDBUtilities.java:177)

	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)

	... 18 more

Caused by: java.lang.Exception: Missing value for query parameter {DB}

	at com.inductiveautomation.ignition.common.db.namedquery.QueryParser.parseAndRebuild(QueryParser.java:75)

	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.executeSFquery(GatewayNamedQueryManager.java:173)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeSFquery(NamedQueryFunctions.java:80)

	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(null)

	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)

	at java.lang.reflect.Method.invoke(null)

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

	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 2, in <module>
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:359)

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

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

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:942)

	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.executeSFquery(DesignerNamedQueryManager.java:80)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runSFNamedQuery(ClientDBUtilities.java:177)

	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)

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Missing value for query parameter {DB}

When I remove the “SF” everything runs correctly and the new row is inserted:

params = {"DB": "HX_Ignition.dbo", "Name": "te", "OwnerID": 7}
system.db.runNamedQuery(path="CreateRoster", parameters=params)

Scott_James · June 16, 2022, 6:12pm

Corrina, did you get any addition details on this issue? I’m running into the same thing at the moment.

bkarabinchak.psi · June 16, 2022, 6:23pm

Tbh not sure I would expect this to work and think using dynamic query strings to determine the db problematic anyways/defeating the security of a named query.

But anyways with a named query you already set the db connection here -

So say you fed a different DB in the param - it’s going to try to run INSERT INTO someOtherDb.CA_Rosters but from the context of HX_Ignition, no matter what you feed to the DB. And if you fed Hx_Ignition as the db, it would double up based on the text into INSERT INTO HX_Ignition.HX_Ignition.CA_Roster which also isn’t valid. I don’t see any way to make this work inside a named query.

If you do have multiple db’s that require the same query, code it right once (without a dynamic DB part, just the table name), and then duplicate the named query, changing the db connection for each one. Then you just call the correct named query based on your context.

Scott_James · June 16, 2022, 7:20pm

True, issue im running into is not so much dynamically changing the db but when I pass the params to through system.db.runSFNamedQuery, it’s acting as if NULL values are being passed, i get a SQL db error saying Column cannot be NULL when i hard pass the value in. Same named query using system.db.runNamedQuery runs without error.

Scott_James · June 16, 2022, 7:30pm

Disregard… Helps to read subtle differences params vs parameters

system.db.runSFNamedQuery(path, params)
vs
system.db.runNamedQuery(project, path, parameters, [tx], [getKey])