NamedQuery Failure

Ignition
,
1

Hello Everybody,
I'm using in Project named query, that ls pretty long and looks like stored procedure.
It is running well in SQL Management studio, but when I'm triggering it from the button event I get a failure that is logged in gateway system logging.
I know, that I can create a stored procedure with parameters on SQL server and trigger it from my button,
but for me it will be better to implement that query and coded inside Ignition environment.
Another thing, I just want to know why i get the failure. That is the query.
I hope the non formatted code will be clear enough

=======================================================================================================================================================

DECLARE @NewRecipeVersion VARCHAR(10)
DECLARE @NewRecipeVerID INT
DECLARE	@num INT

--FIND NEW VERSION NUMBER
SELECT @NewRecipeVersion =(
							CONCAT(
							CAST(RecipeID AS VARCHAR(10)),
							CAST(MAX((CAST(Substring(bcRecipeVersionDef.VersionNumber,Len(bcRecipeVersionDef.VersionNumber)-1, 2) AS INT)+1)) AS VARCHAR(10)) 
							)
						  )
						FROM bcRecipeVersionDef
						GROUP BY RecipeID
						HAVING  RecipeID = :RecipeID


BEGIN TRY
    BEGIN TRANSACTION
		--CreateLikeRecipeVer (((Recipe decompiler 347)))
		INSERT INTO bcRecipeVersionDef
		(RecipeID, VersionNumber, ValidFrom, Expire, Created, FactoryObjectID, FactoryObjectPriority, Notes, Status, Modified, CreatedBy, LastSavedBy)
		SELECT     :RecipeID, @NewRecipeVersion, ValidFrom, Expire, GETDATE(), :FactoryObjectID, FactoryObjectPriority, Notes, 1, GETDATE(), :CreatedBy, :CreatedBy
		FROM         bcRecipeVersionDef AS bcRecipeVersionDef_1
		WHERE ID = :RecipeVerID
		SET @NewRecipeVerID = SCOPE_IDENTITY()

		-- COPY ALL PHASES OF OLD :RecipeVerID TO NEW @NewRecipeVerid
		-- on insert to table of phases there is trigger that creates records in phase parameter table 
		-- according to machine function id
		--DECLARE @ReturnValue INT
		INSERT INTO bcRecipeVerPhase(RecipeVerID, Stage, MachineFunctionID, Notes)
		SELECT @NewRecipeVerID, Stage, MachineFunctionID, Notes
		FROM bcRecipeVerPhase AS bcRecipeVerPhase_1
		WHERE RecipeVerID = :RecipeVerID;
		
		-- COPY MACHINEFUNCTION PHASE PARAMETERS FROM OLD RECIPEVERID TO NEW @NewRecipeVerID
		WITH SourceTable AS (
			SELECT bcRecipeVerPhase_1.RecipeVerID , bcPhaseParamValue_1.MachineFunctionParamID , 
					bcRecipeVerPhase_1.MachineFunctionID ,bcRecipeVerPhase_1.Stage , bcPhaseParamValue_1.Value, 
					bcPhaseParamValue_1.Value2 , bcPhaseParamValue_1.UoMID , bcPhaseParamValue_1.MaterialID 
			FROM dbo.bcPhaseParamValue AS bcPhaseParamValue_1 INNER JOIN
					dbo.bcRecipeVerPhase AS bcRecipeVerPhase_1 ON bcPhaseParamValue_1.RecipePhaseID = bcRecipeVerPhase_1.ID
			WHERE (bcRecipeVerPhase_1.RecipeVerID = :RecipeVerID)
		),
		DestTable AS (
			SELECT bcRecipeVerPhase_1.RecipeVerID , bcPhaseParamValue_1.MachineFunctionParamID , 
					bcRecipeVerPhase_1.MachineFunctionID ,bcRecipeVerPhase_1.Stage , bcPhaseParamValue_1.Value, 
					bcPhaseParamValue_1.Value2 , bcPhaseParamValue_1.UoMID , bcPhaseParamValue_1.MaterialID 
			FROM dbo.bcPhaseParamValue AS bcPhaseParamValue_1 INNER JOIN
					dbo.bcRecipeVerPhase AS bcRecipeVerPhase_1 ON bcPhaseParamValue_1.RecipePhaseID = bcRecipeVerPhase_1.ID
			WHERE (bcRecipeVerPhase_1.RecipeVerID = @NewRecipeVerID)
		)
		MERGE INTO DestTable AS Target
		USING SourceTable AS Source
		ON (Target.MachineFunctionParamID = Source.MachineFunctionParamID AND 
				Target.MachineFunctionID = Source.MachineFunctionID AND
				Target.Stage = Source.Stage)
		WHEN MATCHED THEN 
		    UPDATE SET 
		        Target.Value = Source.Value,
		        Target.Value2 = Source.Value2,
		        Target.MaterialID = Source.MaterialID,
		        Target.UoMID = Source.UoMID;		


		SELECT @num = [FactoryObjectID] FROM [dbo].[bcRecipeVersionDef]	WHERE ID = :RecipeVerID	
		if @num = :FactoryObjectID
			BEGIN
				--CopyHierarchicRecipeVerAlloc
				INSERT INTO bcRecipeParentVer (ParentVerID, ChildVerID)
				SELECT     @NewRecipeVerID, ChildVerID
				FROM         bcRecipeParentVer
				WHERE     (ParentVerID = :RecipeVerID)
			END

		SELECT @NewRecipeVerID AS NewRecipeVerID

    -- Commit the transaction if both queries succeed
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- If an error occurs, rollback the transaction
    ROLLBACK TRANSACTION
    SELECT -1 AS NewRecipeVerID
END CATCH`

=======================================================================================================================================

And this is the error:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 22, in runAction at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:95) at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419) at jdk.internal.reflect.GeneratedMethodAccessor98.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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 12 is out of range.

at org.python.core.Py.JavaError(Py.java:545)

at org.python.core.Py.JavaError(Py.java:536)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)

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._pyx11690.runAction$1(:33)

at org.python.pycode._pyx11690.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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 12 is out of range.

... 34 common frames omitted

Caused by: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The index 12 is out of range.

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)

at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:95)

at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419)

at jdk.internal.reflect.GeneratedMethodAccessor98.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)

... 31 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 12 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.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:1470)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1510)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:1104)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:165)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.runPrepQuery(NamedQueryExecutor.java:484)
2

This is not my area of expertise but I don't think the JDBC driver is able to handle such queries.

You'd have to use a stored procedure in your SQL Server and call it directly from Ignition using the exec SQL keyword.

3 Likes
3

That's because it is a SQL script, not a single SQL statement.

Vanilla JDBC doesn't support SQL scripts, just SQL statements. Some JDBC drivers (like MS) do support SQL scripts, but there are nuances to Ignition's NQ environment that can screw that up too.

For long-term maintainability, don't do this. Use single SQL statements, no comments or declares or any other SQL scripting feature. Where needed for correctness, use an actual stored procedure.

If you feel you must do this in Ignition, get rid of all comments in your SQL script. That will avoid the NQ environment's biggest problem.

3 Likes
4

Thanks