SProcCall not recognizing named parameters

Ignition
1

I’m getting the following error when trying to register parameters to an SProcCall by name:

[quote]Traceback (most recent call last):

File “event:actionPerformed”, line 3, in

File “”, line 53, in upsertHiddenDanger

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

caused by Exception: Error executing system.db.execSProcCall()
caused by GatewayException: Parameter coordinator was not defined for stored procedure dbo.UpsertHiddenDanger.
caused by SQLServerException: Parameter coordinator was not defined for stored procedure dbo.UpsertHiddenDanger.

Ignition v7.8.4 (b2016082217)
Java: Oracle Corporation 1.8.0_121
[/quote]

The parameter is “coordinator”, and is one of the parameters of the stored procedure. The procedure has NULL defaults for many parameters, and this error is the result when I tried to register them all. When I have left out the “coordinator” parameter, the same error is thrown for a different parameter (“employee” or “action” have both popped up).

I found an earlier forum post for a similar issue. That user seemed to think that the problem was using a SQL Server login to connect the gateway to the database. That can’t actually be the problem, can it?

Here is the jython code. As the error above states, it is in a custom function on a container:

[code] HDID = self.HDID
conditionID = self.getComponent(‘ddCondition’).selectedValue
BREWERYID = self.BREWERYID
employee = self.getComponent(‘ddEmployee’).selectedStringValue

description = self.getComponent('txtAreaDescription').text
if len(description) == 0:
	description = None
cause = self.getComponent('txtAreaCause').text
if len(cause) == 0:
	cause = None
action = self.getComponent('txtAreaAction').text
if len(action) == 0:
	action = None

workOrder = None
workOrderTxt = self.getComponent('WorkOrder').getComponent('txt').text
if len(workOrderTxt) > 0:
	try:
		workOrder = int(workOrderTxt)
	except ValueError, err:
		import sys, traceback
		etype,value,tbList = sys.exc_info()
		tb = traceback.extract_tb(tbList)
		raise shared.exceptions.IgnitionException("ValueError %s, invalid WorkOrder='%s' Traceback=%s" % (str(err), workOrderTxt, tb), event)

coordinator = self.getComponent('SafetyCoordinator').getComponent('dd').selectedStringValue
signoff = self.getComponent('SafetyCoordinator').getComponent('cal').date
if len(coordinator) == 0:
	coordinator = None
	signoff = None

upsert = system.db.createSProcCall("dbo.UpsertHiddenDanger",
	database="ProductionSwitchboardSafety")
upsert.registerInParam("HDID", system.db.INTEGER, HDID)
upsert.registerInParam("conditionID", system.db.INTEGER, conditionID)
upsert.registerInParam("BREWERYID", system.db.INTEGER, BREWERYID)
upsert.registerInParam("employee", system.db.VARCHAR, employee)
upsert.registerInParam("description", system.db.NVARCHAR, description)
upsert.registerInParam("cause", system.db.NVARCHAR, cause)
upsert.registerInParam("action", system.db.NVARCHAR, action)
upsert.registerInParam("workOrder", system.db.INTEGER, workOrder)
upsert.registerInParam("coordinator", system.db.VARCHAR, coordinator)
upsert.registerInParam("signoff", system.db.DATE, signoff)

system.db.execSProcCall(upsert)
HDID = upsert.getResultSet()

[/code]

And here is the code for the SQL Server stored procedure:

[code]ALTER PROCEDURE dbo.UpsertHiddenDanger
@HDID int
, @conditionID int
, @BREWERYID int
, @employee varchar(255)
, @description nvarchar(4000)=NULL
, @cause nvarchar(4000)=NULL
, @action nvarchar(4000)=NULL
, @workOrder int=NULL
, @coordinator varchar(255)=NULL
, @signoff datetime=NULL
AS

SET NOCOUNT ON;

IF ISNULL(@HDID, -1) < 0
BEGIN
DECLARE @tableVar table (NewHiddenDangerID int)
INSERT INTO dbo.tblHiddenDangers (ConditionID, BREWERYID, [Description], Cause, [Action]
, WorkOrder, employee, coordinator, signoff)
OUTPUT Inserted.HDID INTO @tableVar
VALUES (@conditionID, @BREWERYID, @description, @cause, @action
, @workOrder, @employee, @coordinator, @signoff);

SELECT @HDID=NewHiddenDangerID
FROM @tableVar

END

ELSE
BEGIN
UPDATE dbo.tblHiddenDangers
SET ConditionID = @conditionID
, [Description] = @description
, Cause = @cause
, [Action] = @action
, employee = @employee
, WorkOrder = @workOrder
, coordinator = @coordinator
, signoff = @signoff
WHERE HDID = @HDID;
END

RETURN @HDID[/code]

And just in case it will help, here’s the stack trace:

[quote]Traceback (most recent call last):
File “event:actionPerformed”, line 3, in
File “”, line 53, in upsertHiddenDanger
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)

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

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

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at 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:495)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:492)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:430)
at org.python.core.PyObject.__call__(PyObject.java:387)
at org.python.core.PyObject.__call__(PyObject.java:391)
at org.python.pycode._pyx229.upsertHiddenDanger$1(<custom-function upsertHiddenDanger>:55)
at org.python.pycode._pyx229.call_function(<custom-function upsertHiddenDanger>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyBaseCode.call(PyBaseCode.java:134)
at org.python.core.PyFunction.__call__(PyFunction.java:317)
at org.python.core.PyMethod.__call__(PyMethod.java:109)
at org.python.pycode._pyx215.f$0(<event:actionPerformed>:3)
at org.python.pycode._pyx215.call_function(<event:actionPerformed>)
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 com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:626)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:168)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:265)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at com.sun.proxy.$Proxy32.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.execSProcCall()
… 62 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Parameter coordinator was not defined for stored procedure dbo.UpsertHiddenDanger.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:301)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:884)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:203)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:490)
… 60 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter coordinator was not defined for stored procedure dbo.UpsertHiddenDanger.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.findColumn(SQLServerCallableStatement.java:1180)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setObject(SQLServerCallableStatement.java:1351)
at org.apache.commons.dbcp.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java:247)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRCallableStatement.setObject(SRConnectionWrapper.java:1377)
at com.inductiveautomation.ignition.gateway.datasource.query.SQLType.setParam(SQLType.java:161)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:216)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:42)
at sun.reflect.GeneratedMethodAccessor120.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:209)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
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.handle(ServletHolder.java:837)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
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.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
at java.lang.Thread.run(null)

Ignition v7.8.4 (b2016082217)
Java: Oracle Corporation 1.8.0_121
[/quote]

Thanks!
Dan

2

For what it’s worth, I was able to get around this, by executing the stored procedure in a system.db.runPrepUpdate() command, like so:

[code] upsert = “EXEC dbo.UpsertHiddenDanger @HDID=?, @conditionID=?, @BREWERYID=?, @employee=?”
params = [HDID, conditionID, BREWERYID, employee]
if description:
upsert += “, @description=?”
params.append(description)
if cause:
upsert += “, @cause=?”
params.append(cause)
if action:
upsert += “, @action=?”
params.append(action)
if workOrder:
upsert += “, @workOrder=?”
params.append(workOrder)
if coordinator:
upsert += “, @coordinator=?”
params.append(coordinator)
if signoff:
upsert += “, @signoff=?”
params.append(signoff)

system.db.runPrepUpdate(upsert, params, database="ProductionSwitchboardSafety")[/code]

Still, it would be nice to be able to take advantage of the built-in sproc commands.

Thanks,
Dan

3

Apologies for bumping this post, but I wanted to leave a record of our fix for anyone having this issue in the future.

For us the issue had to do with our SP using a different character set for one of the parameters. We didn’t realize this until we copied our stored procedure from SSMS into Notepad++.

Once deleting and retyping in the affected parts of the query and using that to alter our SP in SSMS everything worked perfectly again in Ignition.

Hope this helps someone in the future

2 Likes
4

For posterity, this could be a permissions issue as well. Try running a stored procedure that does not require any parameters. If you get a “permissions to execute” error then you would have to configure security for that specific Stored Procedure on the MSSQL side. You can follow directions here: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-stored-procedure?view=sql-server-ver15#SSMSProcedure