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)