Error executing Stored Procedure Call

Ignition
1

I have the following code in the actionPerformed handler of a push button.

call = system.db.createSProcCall("sprBatchLogHeaderInsert",'Elwood_Process')

call.registerReturnParam(system.db.INTEGER)
call.registerInParam("RecipeNumber",system.db.INTEGER,event.source.parent.RecipeNumber)
call.registerInParam('RecipeName',system.db.VARCHAR,event.source.parent.RecipeName)
call.registerInParam("RecipeDescription",system.db.VARCHAR,event.source.parent.RecipeDescription)
call.registerInParam("StartTime",system.db.TIMESTAMP,system.date.now())
call.registerInParam("TankListID",system.db.INTEGER,event.source.parent.TankListID)
call.registerInParam("BriteSched1",system.db.VARCHAR,event.source.parent.getComponent('txtScheduleNumber').text)
call.registerInParam("PSSLP1",system.db.VARCHAR,event.source.parent.getComponent('txtPSSLP').text)
call.registerInParam("DateCode1",system.db.VARCHAR,event.source.parent.getComponent('lblDayCode').text)
call.registerInParam("BriteSched2",system.db.VARCHAR,'')
call.registerInParam("PSSLP2",system.db.VARCHAR,'')
call.registerInParam("DateCode2",system.db.VARCHAR,'')
call.registerInParam("BriteSched3",system.db.VARCHAR,'')
call.registerInParam("PSSLP3",system.db.VARCHAR,'')
call.registerInParam("DateCode3",system.db.VARCHAR,'')
call.registerInParam("BriteSched4",system.db.VARCHAR,'')
call.registerInParam("PSSLP4",system.db.VARCHAR,'')
call.registerInParam("DateCode4",system.db.VARCHAR,'')
call.registerInParam("BriteSched5",system.db.VARCHAR,'')
call.registerInParam("PSSLP5",system.db.VARCHAR,'')
call.registerInParam("DateCode5",system.db.VARCHAR,'')
call.registerOutParam("BatchLogID",system.db.INTEGER)

system.db.execSProcCall(call)

event.source.parent.getComponent('txtBatchID').text = call.getOutParamValue("BatchLogID")
event.source.parent.getComponent('ntxBatchCount').intValue += 1

Nothing to difficult there, however, when the script runs it gives me the following error

 Traceback (most recent call last):
  File "<event:actionPerformed>", line 28, in <module>
	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:438)
	at org.python.core.PyObject.__call__(PyObject.java:387)
	at org.python.core.PyObject.__call__(PyObject.java:391)
	at org.python.pycode._pyx259.f$0(<event:actionPerformed>:31)
	at org.python.pycode._pyx259.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:657)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:183)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:284)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
	at com.sun.proxy.$Proxy25.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()
	... 56 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Parameter RecipeName was not defined for stored procedure sprBatchLogHeaderInsert.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:332)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:306)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:263)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:889)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:203)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:490)
	... 54 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter RecipeName was not defined for stored procedure sprBatchLogHeaderInsert.
	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:1458)
	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.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:209)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:391)
	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.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	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.9.3 (b2017060210)
Java: Oracle Corporation 1.8.0_201

Which would seem to indicate that I have made a typo or something in my script while registering the parameters. I can’t seem to find this error. Here is the definition of the stored procedure for reference.

ALTER PROCEDURE [dbo].[sprBatchLogHeaderInsert]
	(@RecipeNumber INT,
	@RecipeName VARCHAR(50),
	@RecipeDescription VARCHAR(100), 
	@StartTime DATETIME, 
	@TankListID INT,
	@BriteSched1 VARCHAR(15),
	@PSSLP1 VARCHAR(15),
	@DateCode1 VARCHAR(15),
	@BriteSched2 VARCHAR(15),
	@PSSLP2 VARCHAR(15),
	@DateCode2 VARCHAR(15),
	@BriteSched3 VARCHAR(15),
	@PSSLP3 VARCHAR(15),
	@DateCode3 VARCHAR(15),
	@BriteSched4 VARCHAR(15),
	@PSSLP4 VARCHAR(15),
	@DateCode4 VARCHAR(15),
	@BriteSched5 VARCHAR(15),
	@PSSLP5 VARCHAR(15),
	@DateCode5 VARCHAR(15),
	@BatchLogID INT OUTPUT)
AS
DECLARE
	@ErrMessage varchar(512),
	@ErrCode1 int,
	@OperatorName varchar(10),
	@QAName varchar(10),
	@QAComments varchar(10)

SET	@OperatorName = ''
SET	@QAName = ''
SET	@QAComments = ''
SET @ErrCode1 = 0

INSERT INTO tblBatchComplete
	(RecipeNumber, RecipeDescription, StartTime, TankListID, RecipeName, OperatorName, 
	 QAApprovedBy, QAComments, Brite_Schedule_1, PSSLP_1, DateCode_1, Brite_Schedule_2, PSSLP_2, DateCode_2,
	 Brite_Schedule_3, PSSLP_3, DateCode_3, Brite_Schedule_4, PSSLP_4, DateCode_4, Brite_Schedule_5, PSSLP_5, DateCode_5)
VALUES(@RecipeNumber, @RecipeDescription, @StartTime, @TankListID, @RecipeName, @OperatorName,
	 @QAName, @QAComments, @BriteSched1, @PSSLP1, @DateCode1, @BriteSched2, @PSSLP2, @DateCode2,
	 @BriteSched3, @PSSLP3, @DateCode3, @BriteSched4, @PSSLP4, @DateCode4, @BriteSched5, @PSSLP5, @DateCode5)

The real interesting thing is I have almost this exact same script in other places and it doesn’t produce a similar error. The only difference being that those are all tag event scripts and this is a button.

Any help pointing me in the direction of what I am sure is a simple mistake is much appreciated.

2

Look at the chain of “Caused by” for additional detail. In this case:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter RecipeName was not defined for stored procedure sprBatchLogHeaderInsert.
1 Like
3

Phil,

Thanks for the reply. That is actually why I need help, because as you can see by the stored procedure definition, RecipeName is defined for stored procedure sprBatchLogHeaderInsert.

This code is replicated in other places, the only difference being how the values are attained, and doesn’t give this error. So why does it give it here?

4

No idea. Since it is used in multiple places, consider refactoring the code into a subroutine in a project script, and call it where needed. If the same function works called from some places and fails called from others, it would confirm your problem.

5

So in an attempt to use the suggestion by @pturmel. I copied this script into the script console and filled in the appropriate values, and it ran without error.

I also tried hard coding values into the script in the button click and I got the same Parameter not defined error message.

So it seems that for whatever reason, this script will not run from the actionPerformed script, but will run from other places.

6

I would start verifying the values of the parameters you are passing.
Put some print statements in the code to see if what you are passing is what is actually expected.

call.registerReturnParam(system.db.INTEGER)
call.registerInParam("RecipeNumber",system.db.INTEGER,event.source.parent.RecipeNumber)
call.registerInParam('RecipeName',system.db.VARCHAR,event.source.parent.RecipeName)
print event.source.parent.RecipeName
call.registerInParam("RecipeDescription",system.db.VARCHAR,event.source.parent.RecipeDescription)

7 
startTime = system.date.format(system.date.now(),'MM/dd/YYYY HH:mm:ss')

call = system.db.createSProcCall("sprBatchLogHeaderInsert",'Elwood_Process')

call.registerReturnParam(system.db.INTEGER)
call.registerInParam("RecipeNumber",system.db.INTEGER,event.source.parent.RecipeNumber)
print 'Recipe Number: ' + str(event.source.parent.RecipeNumber)
call.registerInParam("RecipeName",system.db.VARCHAR,event.source.parent.RecipeName)
print 'Recipe Name: ' + event.source.parent.RecipeName
call.registerInParam("RecipeDescription",system.db.VARCHAR,event.source.parent.RecipeDescription)
print 'Recipe Description: ' + event.source.parent.RecipeDescription
call.registerInParam("StartTime",system.db.TIMESTAMP,startTime)
print 'Start Time: ' + str(startTime)
call.registerInParam("TankListID",system.db.INTEGER,event.source.parent.TankListID)
print 'Tank List ID: ' + str(event.source.parent.TankListID)
call.registerInParam("BriteSched1",system.db.VARCHAR,event.source.parent.getComponent('txtScheduleNumber').text)
print 'Schedule Number: ' + event.source.parent.getComponent('txtScheduleNumber').text
call.registerInParam("PSSLP1",system.db.VARCHAR,event.source.parent.getComponent('txtPSSLP').text)
print 'PSSLP: ' + event.source.parent.getComponent('txtPSSLP').text
call.registerInParam("DateCode1",system.db.VARCHAR,event.source.parent.getComponent('txtDayCode').text)
print 'Day Code: ' + event.source.parent.getComponent('txtDayCode').text
call.registerInParam("BriteSched2",system.db.VARCHAR,'')
call.registerInParam("PSSLP2",system.db.VARCHAR,'')
call.registerInParam("DateCode2",system.db.VARCHAR,'')
call.registerInParam("BriteSched3",system.db.VARCHAR,'')
call.registerInParam("PSSLP3",system.db.VARCHAR,'')
call.registerInParam("DateCode3",system.db.VARCHAR,'')
call.registerInParam("BriteSched4",system.db.VARCHAR,'')
call.registerInParam("PSSLP4",system.db.VARCHAR,'')
call.registerInParam("DateCode4",system.db.VARCHAR,'')
call.registerInParam("BriteSched5",system.db.VARCHAR,'')
call.registerInParam("PSSLP5",system.db.VARCHAR,'')
call.registerInParam("DateCode5",system.db.VARCHAR,'')
call.registerOutParam("BatchLogID",system.db.INTEGER)

system.db.execSProcCall(call)

Output on console:

Recipe Number: 615
Recipe Name: Sriracha Test
Recipe Description: Sriracha Test Recipe
Start Time: 02/23/2019 14:49:06
Tank List ID: 20
Schedule Number: 012345
PSSLP: abcdef
Day Code: 9054

All of that information is what I would be expecting. Same error is generated from the button. However, if I hard code that same data and run the script from the script console it executes without issue.

Compact Error Mesage:

Traceback (most recent call last):

  File "<event:actionPerformed>", line 36, in <module>



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


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

Ignition v7.9.3 (b2017060210)
Java: Oracle Corporation 1.8.0_201
8

Dude, put the code in a function in a script module. Pass the values to the script via function parameters. Retrieve the out parameter in the function and return it to the caller. Use the function everywhere you need to call this stored procedure. See where it works and where it doesn’t.

9

I agree with Phil, but can you hard code the values in the event and see if it works?

10

As expected placing the code in a Script Module, produces the same results.

However, the process did make me realize one major difference I had forgotten about. The places were this code is used are on two different gateways.

The Tags which have this code in the value change event are a remote provider on the gateway where the Vision Project is located.

I’m not sure why that would make a difference though. Both gateways have the same access to the Database. In fact the connections are set up identically.

From what I can find this seems to possibly be a JDBC driver issue. Still not sure why it works in some situations and not in others.

11

Hard coding the values in the event makes no difference.

12

I would open a call with Support if I was you.
Something is going on internally on that gateway if everything is exactly the same as you say.

1 Like