Why NamedQuery can't run in Script

wangjun_ming_1996 · October 24, 2022, 2:56am

Using ‘NameQuery’ to run in script program will report an error

The Script:

params = {'bay':'BAY4', 'lot':'593301'}
system.db.runNamedQuery("Get_Prev_Thick", params)

the 'Get_Prev_Thick' content is :

SELECT 
 	attribute_value 
FROM 
	NULLID.WIP_TASK  
WHERE 
	lot_number != :lot 
	AND bldg_subsection = :bay  
	AND task_code = '0010' 
ORDER BY production_date DESC 
FETCH FIRST 1 ROW ONLY

The 'Get_Prev_Thick' content picture:

Run it in script:

But I can run it in NamedQuery Vision:

I took the contents of 'namedQuery' out and ran it with 'runPrepQuery' , it worked fine.

I don't know what happened? The Ignition Version is 7.9.20

wangjun_ming_1996 · October 24, 2022, 2:57am

the error code is :

Java Traceback:


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

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

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

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

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

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

	at org.python.pycode._pyx87.f$0(<buffer>:19)

	at org.python.pycode._pyx87.call_function(<buffer>)

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

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

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

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

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

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

	at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)

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

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

	at javax.swing.SwingWorker$1.call(SwingWorker.java:295)

	at java.util.concurrent.FutureTask.run(FutureTask.java:266)

	at javax.swing.SwingWorker.run(SwingWorker.java:334)

	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

	at java.lang.Thread.run(Thread.java:750)

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=SELECT 
 	attribute_value 
FROM 
;BEGIN-OF-STATEMENT;<query_expr_body>, DRIVER=4.7.85

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

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

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

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

	at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:64)

	at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:39)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:90)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:498)

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

	... 20 more

Caused by: java.lang.Exception: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=SELECT 
 	attribute_value 
FROM 
;BEGIN-OF-STATEMENT;<query_expr_body>, DRIVER=4.7.85

	at com.inductiveautomation.ignition.gateway.project.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:315)

	at com.inductiveautomation.ignition.gateway.project.NamedQueryExecutor.execute(NamedQueryExecutor.java:121)

	at com.inductiveautomation.ignition.gateway.project.ProjectManagerImpl.executeNamedQuery(ProjectManagerImpl.java:2204)

	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryGatewayRpc.execute(NamedQueryGatewayRpc.java:29)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:69)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(null)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(null)

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

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

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

	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:85)

	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.HandlerList.handle(HandlerList.java:59)

	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 "<buffer>", line 3, in <module>
 	attribute_value 
FROM 
;BEGIN-OF-STATEMENT;<query_expr_body>, DRIVER=4.7.85

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

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

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

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

	at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:64)

	at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:39)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:90)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:498)


com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=SELECT 
 	attribute_value 
FROM 
;BEGIN-OF-STATEMENT;<query_expr_body>, DRIVER=4.7.85

pturmel · October 24, 2022, 12:18pm

When it fails, where are you calling it from? It looks like your DB2 driver is choking on syntax. (Did you save your project after editing the named query?)

wangjun_ming_1996 · October 25, 2022, 1:03am

The 'NamedQuery' have runed fine for serval years in version 7.9.10
recently i update to 7.9.20, it doesn't work.
other NamedQuery are normally

pturmel · October 25, 2022, 12:28pm

Something changed in the DB2 driver. Investigate that. Open a support ticket if necessary for IA to look over your shoulder.

And you haven't answered this part.