I am trying to make a call to a stored procedure to insert a record, but I am receiving a java.lang.Exception: Error executing system.db.execSProcCall(). I have seen couple of older posts with similar issues, but no resolution to the problem. I am using version 8.1.23.
This is a snippet of the script:
This is the error I am receiving:
That is not the complete error. You may need to look in the gateway log to get it. There should be one or more "Caused by" sections with additional detail.
This is the full error received in the logs. I tried looking through it, but to be honest I still don't have a clue what could be causing the error.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 91, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:530) at jdk.internal.reflect.GeneratedMethodAccessor686.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:547)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:530)
at jdk.internal.reflect.GeneratedMethodAccessor686.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:190)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:552)
at org.python.core.PyObject.__call__(PyObject.java:461)
at org.python.core.PyObject.__call__(PyObject.java:465)
at org.python.pycode._pyx104453.runAction$1(:192)
at org.python.pycode._pyx104453.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
at org.python.core.PyFunction.function___call__(PyFunction.java:474)
at org.python.core.PyFunction.__call__(PyFunction.java:469)
at org.python.core.PyFunction.__call__(PyFunction.java:464)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:846)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:828)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:832)
at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1009)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:897)
at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:147)
at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:74)
at com.inductiveautomation.perspective.gateway.action.ActionDecorator.runAction(ActionDecorator.java:18)
at com.inductiveautomation.perspective.gateway.action.SecuredAction.runAction(SecuredAction.java:44)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:263)
at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:252)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:221)
at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.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 com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()
... 37 common frames omitted
Caused by: java.lang.Exception: Error executing system.db.execSProcCall()
... 36 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 17 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1124)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setObject(SQLServerCallableStatement.java:1567)
at org.apache.commons.dbcp2.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java:1236)
at org.apache.commons.dbcp2.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java:1236)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRCallableStatement.setObject(SRConnectionWrapper.java:1607)
at com.inductiveautomation.ignition.gateway.datasource.query.SQLType.setParam(SQLType.java:179)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:312)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._call(GatewayDBUtilities.java:382)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:528)
... 34 common frames omitted
Assuming you really have all your parameter names and types correct, this can indicate a broken JDBC driver. The last time I encountered this, the work-around was to use parameter indices instead of names.
I tried using parameter indices instead, but I am still receiving the same error. I appreciate the suggestion though. I am not sure why it would be indicating that index 17 is out of range as I have 18 parameters that can be passed to this stored procedure.
Your stored procedure is expecting 18 parameters.
You are passing 16 parameters.
It is giving you an out-of-range error on parameter 17.
What do you expect to happen when you pass only 16 parameters? Does your SPROC support optional parameters? Can you add two more parameters with default values or NULL?
When using the system functions for a SProc call you must register all paramters (even if their values are NULL). You must also register any return parameters needed.
If you haven't registered all parameters input or return, you will run into issues.
Apologies, but maybe I misunderstood how optional parameters work within a stored procedure. If a parameter is already initialized with a value, I thought I wouldn't need to pass a value for it. For example this is what my stored procedure header looked like:
CREATE PROCEDURE SubmissionInsertUpdate (
@StatementType NVARCHAR(20) = '',
@SubmissionId int,
@SubmissionDate datetime2(7) = null,
@ImplementationDate datetime2(7) = null,
@ImprovementType int = null,
@TeamLeaderId int = null,
@OriginatorId int = null,
@CostSavings money = null,
@ManagerId int = null,
@DepartmentId int = null,
@LocationArea nvarchar(max) = null,
@ImprovementDesc nvarchar(max) = null,
@WorkOrderRefId nvarchar(max) = null,
@SavingTypes int = null,
@AfterDescription nvarchar(max) = null,
@BeforeDescription nvarchar(max) = null,
@SubmissionType int = null,
@TimeStamp datetime2(7) = SYSDATETIME)
That seems like a reasonable assumption and is probably where I would have started. Optional parameters are the kinds of things that don't interoperate all that well and passing them through various drivers and runtime environments and languages all developed by different organizations - it only takes one team in that chain to put "support optional parameters" on the wishlist instead of the must-have list for it to not work. And give the type of error that you are seeing.
Sounds like @lrose has seen this before in this environment.
Been there, done that. Pulled out my hair, regrew it and pulled it out again. Very frustrating.
Finally gave up and moved to transaction groups. Worked much better in my situation, but may not be applicable in this one.