system.db.execSProcCall issue with SP call with out parameter

Ignition
,
1

I am having an issue with the Stored Procedure call with output parameter.

Here is how I execute the SP in ignition script:

call = system.db.createSProcCall("VesselData_CreateVessel", "mydatabasename")
call.registerInParam("@vessel_name",system.db.VARCHAR,"IBC005")
call.registerOutParam("@return_value", system.db.VARCHAR)
system.db.execSProcCall(call)

I get this error code when executing. Please can anyone help?

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 13, in onMessageReceived 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 java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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:545)

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 java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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:553)

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

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

at org.python.pycode._pyx1515.onMessageReceived$1(:15)

at org.python.pycode._pyx1515.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:847)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:829)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:868)

at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1010)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:950)

at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:161)

at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:98)

at com.inductiveautomation.perspective.gateway.model.MessageHandlerCollection$MessageHandlerImpl$1.lambda$invoke$0(MessageHandlerCollection.java:86)

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()

... 31 common frames omitted

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

... 30 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 is out of range.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)

at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.registerOutParameter(SQLServerCallableStatement.java:112)

at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.registerOutParameter(SQLServerCallableStatement.java:2296)

at org.apache.commons.dbcp2.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:821)

at org.apache.commons.dbcp2.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:821)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRCallableStatement.registerOutParameter(SRConnectionWrapper.java:1522)

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

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._call(GatewayDBUtilities.java:383)

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

I've never had much luck with named parameters for this case. Try using parameter index numbers.

3

Thank you for the advice. I revised the script as follows and it works now:

			call = system.db.createSProcCall("VesselData_CreateVessel","MyDBName")
			call.registerReturnParam(system.db.INTEGER)
			call.registerInParam(1,system.db.VARCHAR,"IBC005")
			system.db.execSProcCall(call)

However, it would be nice to get to the bottom of using the named parameters.

I used my parameters with the @ sign at the beginning as I do in the MSSQL server, but I've seen that the Ignition Manual uses the parameters without the @ sign in front. I tried using without @ sign to but still didn't work.

4

The SQL server JDBC driver is what's failing to find the parameter by name. In the version I skimmed, it has handling to automatically handle the presence or absence of a leading @. We're just handing the values you send over to the JDBC driver directly.

1 Like