Good morning! I am having problems with my gateway script giving off random errors every now and then under the following error code:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 14, 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 jdk.internal.reflect.GeneratedMethodAccessor42.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:547)
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 jdk.internal.reflect.GeneratedMethodAccessor42.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:552)
at org.python.core.PyObject.__call__(PyObject.java:461)
at org.python.core.PyObject.__call__(PyObject.java:465)
at org.python.pycode._pyx13588.f$0(:42)
at org.python.pycode._pyx13588.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:1687)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:803)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:819)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:751)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:800)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:242)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:194)
at com.inductiveautomation.ignition.common.util.SerialExecutionQueue$PollAndExecute.run(SerialExecutionQueue.java:102)
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 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()
... 27 common frames omitted
Caused by: java.lang.Exception: Error executing system.db.execSProcCall()
... 26 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'Name', table 'Lodestar.dbo.Badge_Read_Historical'; column does not allow nulls. INSERT fails.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1673)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:620)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:540)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3912)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:518)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.execute(SRConnectionWrapper.java:961)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:326)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._call(GatewayDBUtilities.java:382)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:528)
... 24 common frames omitted
It is odd that I only get this error every few days. Today, I got this error on my log 10 times and I don't want it to end up causing downtime as this is a program we use on the floor. What this script does is take a badge scan, verify they're in the system, if they are then it will return the skill level associated to the job where they scanned to the PLC and start the line, if they aren't it will add them to the system and keep the line down. Does anyone know what may be causing the "Cannot insert the value NULL into column 'Name'? The value should never be null as we match against a database the operators badge ID and Name tied to it. This gateway script runs on tag change. I'll include the gateway script below.
#Variables
badge = system.tag.readBlocking(["[Badge]Line 7/CTQ/Flash Station 2/Waterfill2"])[0].value
line = 7
area = 'CTQ'
job = 'Flash Station 2'
if badge > 0:
#Stored procedure to insert into historical table
s32proc = system.db.createSProcCall("[insertintohistory]", "Lodestar")
s32proc.registerInParam('BadgeNum', system.db.INTEGER, badge)
s32proc.registerInParam("Area", system.db.NVARCHAR, area)
s32proc.registerInParam('Line', system.db.INTEGER, line)
s32proc.registerInParam('Job', system.db.NVARCHAR, job)
system.db.execSProcCall(s32proc)
#Checks to see if operator exists in line & workcenter
deproc = system.db.createSProcCall("[IsOpInArea]", "Lodestar")
deproc.registerInParam('BadgeNum', system.db.INTEGER, badge)
deproc.registerInParam("Area", system.db.NVARCHAR, area)
deproc.registerInParam('Line', system.db.INTEGER, line)
deproc.registerInParam('Job', system.db.NVARCHAR, job)
system.db.execSProcCall(deproc)
DOEIA = deproc.getResultSet()
doesOperatorExistInArea = DOEIA.getValueAt(0, 0)
#If operator exists in the workcenter, retrieve skill value and set skill level tag, then reset badge ID tag
if doesOperatorExistInArea == True:
mproc = system.db.createSProcCall("PullSkillLevel", "Lodestar")
mproc.registerInParam('BadgeNum', system.db.INTEGER, badge)
mproc.registerInParam("Area", system.db.NVARCHAR, area)
mproc.registerInParam('Line', system.db.INTEGER, line)
mproc.registerInParam('Job', system.db.NVARCHAR, job)
#Set retrieved value = skill variable
system.db.execSProcCall(mproc)
num = mproc.getResultSet()
skill = num.getValueAt(0, 0)
system.tag.writeBlocking(["[Badge]Line 7/CTQ/Flash Station 2/SkillLevel"], skill)
#If operator doesn't exist, set skill level to 0
else:
system.tag.writeBlocking(["[Badge]Line 7/CTQ/Flash Station 2/SkillLevel"], 0)
numjobs = system.db.runNamedQuery("numjobs", {"lineval" :line, "areaval" :area})
numcount = numjobs.getRowCount()
#Insert operator into every workstation with skill level 0
for i in range(numcount):
nameOfjob = numjobs.getValueAt(i, 0)
sproc = system.db.createSProcCall("[SplutchInserthistory1]", "Lodestar")
sproc.registerInParam('BadgeNum', system.db.INTEGER, badge)
sproc.registerInParam('area', system.db.NVARCHAR, area)
sproc.registerInParam('line', system.db.INTEGER, line)
sproc.registerInParam('Job', system.db.NVARCHAR, nameOfjob)
sproc.registerInParam('Tlevel', system.db.INTEGER, 0)
system.db.execSProcCall(sproc)
EDIT: To make this even more confusing, the operators scanned at 6:37 so it shouldn't have even run at 6:44 when I acquired these 10 errors..
Included below are the SQL stored procedures.
ALTER PROCEDURE [dbo].[insertintohistory]
-- PARAMETERS FOR STORED PROCEDURE
-- THESE PARAMS ARE PASSED THROUGH IN THE GATEWAY EVENTS SCRIPT
@BadgeNum INTEGER,
@Area nvarchar(50),
@Line integer,
@Job NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
declare @name as nvarchar(50)
declare @badgeid as nvarchar(50)
declare @Date as date
declare @Time as nvarchar(10)
declare @Time1 as time
set @Date = GETDATE()
set @Time1 = (select convert(varchar, getdate(), 108))
set @Time = (SELECT CONVERT(VARCHAR(10), CAST(@Time1 AS TIME), 0))
set @name = (SELECT DISTINCT bn.[Name] as 'Name' FROM [10.4.98.204].[ACVSCore].[Access].[Personnel] bn INNER JOIN [10.4.98.204].[ACVSCore].[Access].[Credential] cn ON bn.Name = cn.Name
WHERE cn.[Active] = 1 AND (bn.Text16 = 'Clyde Manufacturing' or bn.Text1 like 'Adecco %') and cn.[CardNumber] = @BadgeNum)
set @badgeid = (SELECT DISTINCT bn.[Text13] as 'Clock Number' FROM [10.4.98.204].[ACVSCore].[Access].[Personnel] bn INNER JOIN [10.4.98.204].[ACVSCore].[Access].[Credential] cn ON bn.Name = cn.Name
WHERE cn.[Active] = 1 AND (bn.Text16 = 'Clyde Manufacturing' or bn.Text1 like 'Adecco %') and cn.[CardNumber] = @BadgeNum)
INSERT INTO [dbo].[Badge_Read_Historical] (name, ClockNumber, CardNumber, Line, Area, Job, [Date], [Time])
Values (@name, @badgeid, @BadgeNum, @Line, @Area, @Job, @Date, @Time)
END
ALTER PROCEDURE [dbo].[IsOpInArea]
--Parameters
@BadgeNum INTEGER,
@Area nvarchar(50),
@Line integer,
@Job nvarchar(50)
AS
BEGIN
--Variable
DECLARE @doesexist BIT
--If operator exists in line and area, return 1
IF(SELECT count(name) FROM Badge_Read_Training_Level WHERE Line = @Line and Area = @Area and BadgeID = @BadgeNum) >= 1
BEGIN
SET @doesexist = 1
SELECT @doesexist
RETURN
END
--If operator does not exist in line and area, return 0
ELSE IF(SELECT count(name) FROM Badge_Read_Training_Level WHERE Line = @Line and Area = @Area and BadgeID = @BadgeNum) = 0
BEGIN
SET @doesexist = 0
SELECT @doesexist
RETURN
END
END
ALTER PROCEDURE [dbo].[PullSkillLevel]
@BadgeNum INTEGER,
@Area nvarchar(50),
@Line integer,
@Job nvarchar(50)
AS
BEGIN
select TrainingLevel
from Badge_Read_Training_Level
where Line = @Line and Area = @Area and Job = @Job and BadgeID = @BadgeNum
end
ALTER PROCEDURE [dbo].[SplutchInserthistory1]
-- PARAMETERS FOR STORED PROCEDURE
-- THESE PARAMS ARE PASSED THROUGH IN THE GATEWAY EVENTS SCRIPT
@BadgeNum int,
@area nvarchar(50),
@line int,
@Job nvarchar(50),
@Tlevel int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- THE @NAME IS A VAR WHOS VALUE IS = TO A QUERY RETURN VALUE
-- LOOK INTO THE SET @NAME FOR EXPLENATION
declare @name as nvarchar(50)
declare @ClockID as int
-- @RECORDCOUNT CHECKS THE NUMBER OF OCCURENCES A BADGE EXISTS IN A TABLE
-- @ NAME IS BASSICALLY THE SUBQUERY, BUT WE SELECT ONLY THE NAME, THIS CAN BE USED TO PULL
-- CLOCK ID AND BADGE ID TOO
set @name = (SELECT bn.[Name] as 'Name'
FROM [10.4.98.204].[ACVSCore].[Access].[Personnel] bn INNER JOIN [10.4.98.204].[ACVSCore].[Access].[Credential] cn ON bn.Name = cn.Name
WHERE cn.[Active] = 1 AND (bn.Text16 = 'Clyde Manufacturing' or bn.Text1 like 'Adecco %') and cn.[CardNumber] = @BadgeNum
group by bn.[Name])
set @ClockID =(SELECT bn.[Text13] as 'Clock Number'
FROM [10.4.98.204].[ACVSCore].[Access].[Personnel] bn INNER JOIN [10.4.98.204].[ACVSCore].[Access].[Credential] cn ON bn.Name = cn.Name
WHERE cn.[Active] = 1 AND (bn.Text16 = 'Clyde Manufacturing' or bn.Text1 like 'Adecco %') and cn.[CardNumber] = @BadgeNum
group by bn.[Text13])
-- CHECKS TO SEE IF BADGE EXISTS IN AREA TESTER, IF NOT IT'LL INSERT IT ALONG WITH
-- OTHER VALUES.
INSERT INTO [dbo].[Badge_Read_Training_Level](name, ClockID, BadgeID, line,area, Job, TrainingLevel) Values(@name, @ClockID, @BadgeNum, @line, @area, @Job, @Tlevel)
SELECT bn.[Name] as 'Name', cn.[CardNumber] as 'Card Number', bn.[Text13] as 'Clock Number', brtl.Line, brtl.area, brtl.job, brtl.TrainingLevel
FROM [10.4.98.204].[ACVSCore].[Access].[Personnel] bn INNER JOIN [10.4.98.204].[ACVSCore].[Access].[Credential] cn ON bn.Name = cn.Name
INNER JOIN Badge_Read_Training_Level brtl on brtl.badgeID = cn.CardNumber
WHERE cn.[Active] = 1 AND (bn.Text16 = 'Clyde Manufacturing' or bn.Text1 like 'Adecco %') and cn.[CardNumber] = @BadgeNum
order by brtl.line, brtl.area, Name
END