java.lang.Exception: Error executing system.db.execSProcCall()

Ignition
1

Hi guys,

I’am trying to call a stored procedure from ignition. but getting error this. can someone help me in this.

thanks in advance…

Java Traceback:
Traceback (most recent call last):
File “”, line 73, in
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:497)

at jdk.internal.reflect.GeneratedMethodAccessor47.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.execSProcCall()

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.execSProcCall(AbstractDBUtilities.java:497)

at jdk.internal.reflect.GeneratedMethodAccessor47.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:524)

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

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

at org.python.pycode._pyx525.f$0(<input>:57)

at org.python.pycode._pyx525.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.execSProcCall()

... 25 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: XPATH syntax error: ‘DATA)’

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.invoke(GatewayInterface.java:934)

at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:345)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:495)

... 23 more

Caused by: java.sql.SQLException: XPATH syntax error: ‘DATA)’

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)

at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)

at com.mysql.cj.jdbc.CallableStatement.execute(CallableStatement.java:845)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.execute(SRConnectionWrapper.java:995)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:310)

at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:42)

at jdk.internal.reflect.GeneratedMethodAccessor51.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:414)

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

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 “”, line 73, in
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:497)

at jdk.internal.reflect.GeneratedMethodAccessor47.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.execSProcCall()

2

Can you post a script where you calling this function from ?
From the Error above it seems like some kind of a syntax error.

3

here is script

global system
import system
import java.lang.Exception
global mainPath1
global deviceTags
global deviceString
global tagString


mainPath1 = "[DPK]"
deviceString = []
tagString = {}
			
def mainTags(path, filter):
	results = system.tag.browse(path, filter)
	for result in results.getResults():
		if result['hasChildren'] == True:
			secondPath1 = str(result['fullPath']).replace(mainPath1,"")
			if (secondPath1 == "_types_" or secondPath1 == "K-TRON" or secondPath1 == "AP" or secondPath1 == "HC"):
				continue
			mainPath = mainPath1  + secondPath1
			deviceTags(secondPath1, mainPath, mainPath, {})

def deviceTags(machineName, mainPath, path, filter): 
    results = system.tag.browse(path, filter)
    for result in results.getResults():
        if result['hasChildren'] == True:
            deviceTags(machineName, mainPath, result['fullPath'], filter)
        else:
        	secondPath = str(result['fullPath']).replace(mainPath,"")[1:]
        	deviceName = secondPath[:secondPath.find('/')]
        	if (deviceName == "ALARMS" or deviceName == "Alarm" or deviceName == "ALARM" or deviceName == "PRO DATA" or deviceName == "LOADVIEW"):
				continue
        	tagName = secondPath[secondPath.find('/'):][1:].replace('/','_')
    		
        	ss = str(result['value'])
        	tagValue = ss[:ss.find(',')][1:]
        	if (tagValue == ""):
				continue
        	deviceString.append(machineName + '^' + deviceName)

        	if(machineName + '^' + deviceName not in tagString):
        		tagString[machineName + '^' + deviceName] = [[tagName,tagValue]]
        	else:
        		tagString[machineName + '^' + deviceName].append([tagName,tagValue])
       		
mainTags(mainPath1, {})
list_set = set(deviceString)
unique_list = (list(list_set))
unique_list.sort()
machinestr = ""
devicestr = ""
saveDateTime = system.db.runScalarQuery("SELECT utc_timestamp()")
sitename = mainPath1[1:4]
for i in unique_list:
	machinestr = i[:i.find('^')]
	device_str = str(i[i.find('^'):])[1:]
	str1 = '<devicename id= "' + device_str + '" />'
	str2 = ""
	for key in tagString:
		if(i == key):
			for j in tagString[key]:
				str2 = str2 + '<' + device_str + ' tagname="' + j[0] + '" tagvalue="' + j[1] + '" />\n'
	call = system.db.createSProcCall("insertMachineData")
	call.registerInParam(1, system.db.VARCHAR, sitename)
	call.registerInParam(2, system.db.VARCHAR, system.date.format(saveDateTime, 'yyyy-MM-dd HH:mm:ss'))
	call.registerInParam(3, system.db.VARCHAR, machinestr)
	call.registerInParam(4, system.db.VARCHAR, "'" + str1 + "'")
	call.registerInParam(5, system.db.VARCHAR, "'" + str2 + "'")
	system.db.execSProcCall(call)
	print '[Site : ' + sitename + '] [UTCTIME : ' + system.date.format(saveDateTime, 'dd-MMM-yyyy HH:mm:ss') + '] [Machine : ' + machinestr + '] [Device : ' + device_str + ']'