Why does the except: statement error

rfolkes · August 30, 2023, 5:41pm

When I run this the 2 print staments do not print anything which is what I would expect since the values are null so the if statement should prevent the try/except from running, but the Query under except will run and cause an error since the tagValues are null (part of the insertArgs)

How do I prevent the try/except from trying to run when values are null? Do I need to put the if statement inside of the try/except blocks?

values = system.tag.readBlocking(tagPaths)
TagValuePLC = values[0].value
TagValueNum = values[1].value
print values
if TagValuePLC is not None and TagValueNum is not None:
        print "PLC=" + str(TagValuePLC)
        print "Num=" + str(TagValueNum)
        print "did you get here?"
        try:
               DBvalues = system.db.runPrepQuery(updateString, updateArgs, DB)
        except:
               system.db.runPrepUpdate(insertString, insertArgs, DB)

code results in

[[null, Bad_NotFound("Path '[PLC2]Input Action/30000/PLC_NUMBER' not found."), Thu Aug 24 08:29:49 CDT 2023 (1692883789152)], [null, Bad_NotFound("Path '[PLC2]Input Action/30000/OUTPUT_NUMBER' not found."), Thu Aug 24 08:29:49 CDT 2023 (1692883789152)]]
Java Traceback:
Traceback (most recent call last):

rfolkes · August 30, 2023, 5:43pm

of note: I did take a lot of lines of code out to narrow it down to where I am seeing the issue.

dkhayes117 · August 30, 2023, 5:47pm

It seems that the tag values are null, not None.

cmallonee · August 30, 2023, 5:47pm

We really would have a much better chance of helping you if you include the rest of the stacktrace.

rfolkes · August 30, 2023, 5:56pm

The issue isn't the source of the error. I know what is causing the error. It is failing due to SQL query from the syste.db.runPrepUpdate line fails since the values in the SQL query are null.

the question is why does the system.db.runPrepUpdate statment even try to run since if it is within the If statement that evaluates to false so like the print statements "did you get here?" it shouldn't run at all.

Kevin.Herron · August 30, 2023, 5:58pm

What makes you think those queries are running?

rfolkes · August 30, 2023, 6:03pm

From my understanding Null = None in python

rfolkes · August 30, 2023, 6:04pm

Because the error is from the query.

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO TGMS_InputDBAction (ActionID, Input_PLC, Input_Number, Output_PLC, Output_Number) Values (?,?,?,?,?), [30000, 2, 0, , ], MSSQL, , false, false)

	... 25 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO TGMS_InputDBAction (ActionID, Input_PLC, Input_Number, Output_PLC, Output_Number) Values (?,?,?,?,?)": Cannot insert the value NULL into column 'Output_PLC', table 'TGMS.dbo.TGMS_InputDBAction'; column does not allow nulls. INSERT fails.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)

dkhayes117 · August 30, 2023, 6:05pm

Yeah, you're right on that.

Kevin.Herron · August 30, 2023, 6:06pm

Put a print statement first thing in the except block.

rfolkes · August 30, 2023, 6:07pm

rfolkes · August 30, 2023, 6:09pm

Kevin.Herron · August 30, 2023, 6:12pm

If you move this code into a library, save, and call it from a fresh Designer console does anything change?

dkhayes117 · August 30, 2023, 6:12pm

Try the script in a different console. I had issues before where I defined a variable while testing, then deleted all the code to test something else, and that variable was still valid and usable in the console.

rfolkes · August 30, 2023, 6:13pm

it was erroring from Project library script.

Put it into a script console and same thing.

dkhayes117 · August 30, 2023, 6:14pm

can you post the entire script and not a screenshot?

rfolkes · August 30, 2023, 6:16pm

New Designer session, new script console same result.

dkhayes117 · August 30, 2023, 6:17pm

I tried this on my machine and it works as expected, it never goes past the if

values = system.tag.readBlocking(['Tag1','Tag2'])
TagValuePLC = values[0].value
TagValueNum = values[1].value
print values
if TagValuePLC is not None and TagValueNum is not None:
        print "did you get here?"
        print "PLC=" + str(TagValuePLC)
        print "Num=" + str(TagValueNum)
        try:
               DBvalues = system.db.runPrepQuery(updateString, updateArgs, DB)
        except:
               system.db.runPrepUpdate(insertString, insertArgs, DB)

post the entire script

rfolkes · August 30, 2023, 6:18pm

#set starting point and DB to connect to
checkcount = 0
updatecount = 0
DB = "MSSQL"

checkpath = "[Design]SQL DB Verification/Input Matrix/CheckCount"
stoptimePath = "[Design]SQL DB Verification/Input Matrix/Stop Time"
starttimePath = "[Design]SQL DB Verification/Input Matrix/Start Time"
inprogressPath = "[Design]SQL DB Verification/Input Matrix/Checking in progress"
updatetagcountPath = "[Design]SQL DB Verification/Input Matrix/Update Tag Count"

starttime = system.date.now()
values = 1, starttime, updatecount
progresspaths = inprogressPath, starttimePath, updatetagcountPath
#system.tag.writeAsync(progresspaths, values)

maxPLCPath = "[Design]System/Max PLC Num"
value = system.tag.readBlocking(maxPLCPath)
maxPLC = value[0].value +1 #range function is not inclusive of end

for PLC in range(2,maxPLC):
	basePath = "[PLC"+str(PLC)+"]PLC Data/Info/"
	maxInputPath = basePath + "PLC_MAXIMUM_INPUT"
	totalActionPath = basePath + "Memory/Input Action Count"
	tagPathsRange = maxInputPath, totalActionPath
	values=system.tag.readBlocking(tagPathsRange)
	maxInput = values[0].value
	totalAction = values[1].value 
	if maxInput is not None:
		lastFixed = maxInput * 10 + 9
		overflowIndex = []
		#iterate through actions
		for index in range(30000,(totalAction+1)):
			checkcount += 1
			#system.tag.writeAsync(checkpath, checkcount)
			UniqueActionID = (PLC*100000+index)
			
            #clear Overflow Input number (will be intered during overflow check at end)
			query = "UPDATE TGMS_InputDBAction set Input_Number=0 WHERE OverflowUniqueActionID = ?"
			args = [UniqueActionID]
			system.db.runPrepUpdate(query, args, DB)
			
			#get Tag Paths
			basePath = "[PLC"+str(PLC)+"]Input Action/"+str(index)
			PLC_NUMBER = basePath+"/PLC_NUMBER"
			OUTPUT_NUMBER = basePath+"/OUTPUT_NUMBER"

			# Grab tag values 
			tagPaths = PLC_NUMBER, OUTPUT_NUMBER
			values = system.tag.readBlocking(tagPaths)
			TagValuePLC = values[0].value
			TagValueNum = values[1].value
			print values
			#continue if tag values valid
			if TagValuePLC is not None and TagValueNum is not None:
				print "PLC=" + str(TagValuePLC)
				print "Num=" + str(TagValueNum)
				print "got here"
				
				#if negative add to overflow index to check at end
				if TagValuePLC <0 and TagValueNum <0 and TagValueNum < TagValuePLC:
					overflowIndex.append[index]
                
                #get input Number based on index value (ignore overflow till later)
                if index <= lastFixed:
                    Input_Number = index/10
                else:
                    Input_Number = 0
                
                updateString = "UPDATE TGMS_InputDBAction SET Output_PLC =  ?, Output_Number = ? OUTPUT DELETED.Output_PLC, DELETED.Output_Number WHERE UniqueActionID = ?"
                updateArgs = [TagValuePLC, TagValueNum, UniqueActionID] 
                insertString = "INSERT INTO TGMS_InputDBAction (ActionID, Input_PLC, Input_Number, Output_PLC, Output_Number) Values (?,?,?,?,?)"
                insertArgs = [index, PLC, Input_Number, TagValuePLC, TagValueNum]
                try:
                    DBvalues = system.db.runPrepQuery(updateString, updateArgs, DB)
                    DBValuePLC = DBvalues.getValueAt(0,0)
                    DBValueNum = DBvalues.getValueAt(0,1)
                    if DBValuePLC != TagValuePLC or DBValueNum != TagValueNum:
                        updatecount += 1
                        updated = 1
                        #set values to str for audit log
                        OldValue = "PLC-"+str(DBValuePLC)+" Num-"+ str(DBValueNum)
                        NewValue = "PLC-"+str(TagValuePLC)+" Num-"+ str(TagValueNum)
                        ColName = "Output PLC/Number Updated"

                except:
                	print "I did get to the except"
                	system.db.runPrepUpdate(insertString, insertArgs, DB)
                	updatecount += 1
                	updated = 1
                    #set values to str for audit log
                	OldValue = "inserted row"
                	NewValue = "PLC-"+str(TagValuePLC)+" Num-"+ str(TagValueNum)
                	ColName = "Output PLC/Number Updated"
                
                if updated == 1:						#add entry to audit log		
                    auditQuery = "INSERT INTO TGMS_AuditInputAction (ModifiedUniqueActionID, OldValue, NewValue, ColumnChanged, TagPath) VALUES (?,?,?,?,?)"
                    auditArgs = UniqueActionID, OldValue, NewValue, ColName, basePath
                    system.db.runPrepUpdate(auditQuery, auditArgs, DB)			
                    updated = 0
                    
		# iterate through overflow index values and set input number on overflow
		for index in overflowIndex:
			UniqueActionID = (PLC*100000+index)
			#get Tag Paths
			basePath = "[PLC"+str(PLC)+"]Input Action/"+str(index)
			PLC_NUMBER = basePath+"/PLC_NUMBER"
			OUTPUT_NUMBER = basePath+"/OUTPUT_NUMBER"

			# Grab tag values for all tags listed in one call
			tagPaths = PLC_NUMBER, OUTPUT_NUMBER
			values = system.tag.readBlocking(tagPaths)
			#Update DB INPUT NUMBER for for overflow if initially Negative values
			#set initial values values to Positive 	
			StartID = values[1].value *-1 
			EndID = values[0].value *-1  #SQL between statement is inclusive of start and end
			#only update if valid entry
			if StartID < EndID and StartID >0 and EndID >0:

				#unique id for start and end inclusive
				UniqueActionStart = PLC *100000+StartID
				UniqueActionEND = PLC *100000+EndID
				
				#update Database
				query = "UPDATE TGMS_InputDBAction set Input_Number=?, OverflowUniqueActionID=? WHERE UniqueActionID BETWEEN ? AND ?"
				args = index, UniqueActionID, UniqueActionStart, UniqueActionEND
				system.db.runPrepUpdate(query, args, DB)
			
stoptime = system.date.now()
values = 0, stoptime, updatecount, checkcount
progresspaths = inprogressPath, stoptimePath, updatetagcountPath, checkpath
#system.tag.writeAsync(progresspaths, values)

lrose · August 30, 2023, 6:23pm

Instead of printing values print TagValuePLC and TagValueNum you might be surprised what you find.