Error Executing system.db.execSProcCall() Gateway Script

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

Not enough information.

We'll need to see the stored procedure code, as you aren't passing a name as a parameter.

Also, what event runs this script?

It runs on badge scan tag change. I just posted the SQL stored procedures in use.

Think this may have answered my own question...I don't think I need the select query after the insert?

Not sure about the final Select, but I noticed that you are querying another server from these stored procedures. Are you sure that nested server connection was healthy at the time? {I would do everything possible to not have that kind of hidden dependency in my operations.}

Consider doing everything in a single stored procedure, that makes a single callout to the remote server, makes all of the skill decisions, and returns all the data that needs to be sent to your PLC.

It is a corporate server that we don't really have access to, our boss had is throw it into our script way back when as a verification method for our employees. It must have been running, as the line still started when they scanned and everything worked as expected, we just got the errors for some reason.

Well, that's where Name comes from, so it is the only source of your nulls.

2 Likes

That's what I am figuring, just not sure how it's returning Null..

Not the point of your question but I think using a stored procedures that runs a singular insert/select statement adds complexity for no benefit. You are splitting up the business logic between your event and the stored procedure, but there is nothing special about that stored procedure you can't do inside of Ignition with a few select/insert statements. If you had these as separate query statements in ignition you would know by know which one was giving you an issue. Getting the real error line number out of the stored procedure can be it's own headache depending on what DB you use (SQL Server...)

I would personally avoid using a stored procedure unless you had to do something that you can't do with a JBDC driver like use a CTE or similar.

1 Like

Reach another server indirectly.

1 Like

I must have missed that. Ignore me. I shouldn't be pre-coffee posting.

If the issue is that Name cannot be null and the above is how you are calculating name, I would inspect this part where you assign the value to name - perhaps it is not working as you expect or there are NULL values in that table itself. Figure out what badge number causes an issue and then go try this out with that badge number, maybe that table really does have a NULL for that badge number.

1 Like

This is a great point and I've thought about changing things. It was our first project we worked on, so we were still newer to Ignition (we were thrown in and learned from doing). Do you think it'd be worth reworking or is it not really worth it at this point?

1 Like

If you could make a direct connection to the database with Ignition and run it with singular statements, I probably would. Otherwise every single time you want to do any atomic action to that database, you'll have to do it in some stored procedure like you have above and if that could be avoided I would avoid it.

Of course that isn't always possible and perhaps IT/DBA doesn't want Igintion to be able to directly talk to that particular DB - you'll have to see what the limitations are.

But if you could make a direct connection to that db with Ignition and run queries directly against it, it certainly would make things easier imo.

2 Likes