hello Team,
Im getting error while executing name query please help me to understand this
Error running action 'dom.onClick' on ReportEngine/Popups/GenerateStoreProcedure@PYp5Gwm-F/root/Button_0: Traceback (most recent call last): File "<function:runAction>", line 29, in runAction File "<module:StoreProcedure>", line 44, in CreateStoreProcByDynamically File "<module:StoreProcedure>", line 44, in CreateStoreProcByDynamically DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE Testing_SP001 @Id01 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Set @Result =( SELECT UserManagement_users.username , UserManagement_users.passwd FROM UserManagement_users WHERE UserManagement_users.id = @Id01 ) return @Result END '; EXEC (@sql); , TestDB1, , false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188) at jdk.internal.reflect.GeneratedMethodAccessor585.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.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE Testing_SP001 @Id01 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Set @Result =( SELECT UserManagement_users.username , UserManagement_users.passwd FROM UserManagement_users WHERE UserManagement_users.id = @Id01 ) return @Result END '; EXEC (@sql); , TestDB1, , false)
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 29, in runAction File "", line 44, in CreateStoreProcByDynamically File "", line 44, in CreateStoreProcByDynamically DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE Testing_SP001 @Id01 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Set @Result =( SELECT UserManagement_users.username , UserManagement_users.passwd FROM UserManagement_users WHERE UserManagement_users.id = @Id01 ) return @Result END '; EXEC (@sql); , TestDB1, , false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188) at jdk.internal.reflect.GeneratedMethodAccessor585.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.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE Testing_SP001 @Id01 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Set @Result =( SELECT UserManagement_users.username , UserManagement_users.passwd FROM UserManagement_users WHERE UserManagement_users.id = @Id01 ) return @Result END '; EXEC (@sql); , TestDB1, , false)
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.runUpdateQuery(AbstractDBUtilities.java:188)
at jdk.internal.reflect.GeneratedMethodAccessor585.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:477)
at org.python.core.PyObject.__call__(PyObject.java:481)
at org.python.pycode._pyx24644.CreateStoreProcByDynamically$1(:55)
at org.python.pycode._pyx24644.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:187)
at org.python.core.PyFunction.__call__(PyFunction.java:449)
at org.python.pycode._pyx24643.runAction$1(:31)
at org.python.pycode._pyx24643.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.action.ScriptAction.runAction(ScriptAction.java:80)
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.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE Testing_SP001 @Id01 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Set @Result =( SELECT UserManagement_users.username , UserManagement_users.passwd FROM UserManagement_users WHERE UserManagement_users.id = @Id01 ) return @Result END '; EXEC (@sql); , TestDB1, , false)
... 43 common frames omitted
Caused by: java.lang.Exception: Error executing system.db.runUpdateQuery( DECLARE @sql NVARCHAR(MAX); SET @sql = ' CREATE PROCEDURE Testing_SP001 @Id01 INT,@Result Decimal(16,4) output AS BEGIN SET NOCOUNT ON; Set @Result =( SELECT UserManagement_users.username , UserManagement_users.passwd FROM UserManagement_users WHERE UserManagement_users.id = @Id01 ) return @Result END '; EXEC (@sql); , TestDB1, , false)
... 42 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:898)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:793)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:733)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:341)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:341)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:867)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runUpdateQuery(SRConnectionWrapper.java:219)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runUpdateQuery(GatewayDBUtilities.java:251)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:181)
... 40 common frames omitted
Line 97 of your error log states,
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
However, even if you fix this you might still be in trouble. The JDBC driver does not support SQL scripting. See Are comments in Named Queries known to not work correctly? - #11 by pturmel, etc., for more. (There are other posts on the forum. Try searching for "JDBC".)
3 Likes