# Question on Stored Procedures **URL:** \ **Category:** Ignition\ **Created:** [May 10, 2024, 3:15am UTC](https://forum.inductiveautomation.com/t/question-on-stored-procedures/90203 "2024-05-10T03:15:28Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Eric\_Ervin](https://avatars.discourse-cdn.com/v4/letter/e/ecd19e/32.png) [@Eric\_Ervin](https://forum.inductiveautomation.com/u/Eric_Ervin)\ **Post date:** [May 10, 2024, 3:15am UTC](https://forum.inductiveautomation.com/t/question-on-stored-procedures/90203/1 "2024-05-10T03:15:28Z")
Having an issue with a stored procedure call. Here is the stored procedure in SQL Server ```python create procedure test.spIAFaultInsert @station nvarchar(50), @faultID nvarchar(20), @faultDescription nvarchar(200) as begin insert into test.IA_Fault (station, faultID, faultDescription) values (@station, @faultID, @faultDescription); end ``` ```python call = system.db.createSProcCall("test.IAFaultInsert", "SqlServer") call.registerInParam("station", system.db.VARCHAR, "Assy") call.registerInParam("faultID", system.db.VARCHAR, "F0") call.registerInParam("faultDescription", system.db.VARCHAR, resultRow) system.db.execSProcCall(call) ``` Here is my call I've been working on for a bit this evening. I've just been trying to get it to work on the Script Console. End goal would be to call on tag value change. Here is the Error Log I'm getting. ```python Java Traceback: Traceback (most recent call last): File "", line 25, in 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._pyx72.f$0(:25) at org.python.pycode._pyx72.call_function() at org.python.core.PyTableCode.call(PyTableCode.java:173) at org.python.core.PyCode.call(PyCode.java:18) at org.python.core.Py.runCode(Py.java:1703) at org.python.core.Py.exec(Py.java:1747) at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277) at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130) at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:628) at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616) 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() ... 26 more Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: The index 5 is out of range. at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360) at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334) at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287) at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:967) at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:356) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:528) ... 24 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 5 is out of range. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1131) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setObject(SQLServerCallableStatement.java:1596) 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:313) at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:45) at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at jdk.internal.reflect.NativeMethodAccessorImpl.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:228) at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435) at javax.servlet.http.HttpServlet.service(HttpServlet.java:523) at javax.servlet.http.HttpServlet.service(HttpServlet.java:590) at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86) at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764) at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51) at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) at org.eclipse.jetty.server.Server.handle(Server.java:563) at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505) at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100) at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53) at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416) at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385) at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272) at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969) at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194) at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149) at java.lang.Thread.run(null) Traceback (most recent call last): File "", line 25, in 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() ``` I'm not really sure where to go as far as what I'm missing to get this to work ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 10, 2024, 11:21am UTC](https://forum.inductiveautomation.com/t/question-on-stored-procedures/90203/2 "2024-05-10T11:21:47Z")
There are known issues with some JDBC drivers and named parameters. Use `1`, `2`, and `3` in your parameter registration instead of the names. ---
**Author:** ![devty](https://avatars.discourse-cdn.com/v4/letter/d/8797f3/32.png) [@devty](https://forum.inductiveautomation.com/u/devty)\ **Post date:** [May 10, 2024, 4:56pm UTC](https://forum.inductiveautomation.com/t/question-on-stored-procedures/90203/3 "2024-05-10T16:56:33Z")
I am running all my db insert logic through stored procedures like this ```python db="my_config_db" args = ["Assy", "F0", resultRow] query = "EXEC test.spIAFaultInsert ?,?,?" system.db.runPrepUpdate(query,args,database=db) ```