INSERT INTO SQL Server runPreUpdate Error - object has no attribute

Ignition
, ,
1

This project is running in Ignition Perspective employing SQL Server as the supporting database.
I've been struggling with this obscure error when trying to execute a query in an event script:

def runAction(self, event):
	
	userID = self.session.props.auth.user.id
	tstamp = system.date.now()
	table = self.parent.parent.parent.getChild("Table")
	data = table.props.data
	columns = table.props.columns
	txID = system.db.beginTransaction(database="SQLEXPRESS", timeout = 5000)
	
	try:
		for row in data:
			Id = row["Id"]["value"]
			removeMe = row["removeMe"]
			updateMe = row["updateMe"]

# 	insert
			if Id is None:
				
				system.perspective.print("Attempt Insert")
				payload = {"status":"Attempt Insert"}
				system.perspective.sendMessage("Status", payload)								
				
				temp = []
				args = []

				for col in columns:
					if col.field == "Id":
						pass
					elif col.field == "ActionUserID":
						args.append(userID)
						temp.append(col.field)
					elif col.field == "ActionTimeStamp":
						args.append(tstamp)
						temp.append(col.field)
					elif col.field == "Segment":
						args.append('MIS')
						temp.append(col.field)					
					elif col.field == "ActionStatus":
						args.append('Active')
						temp.append(col.field)		
					elif col.field == "ActionType":
						args.append('Add')
						temp.append(col.field)
					else:
						args.append(row[col.field]["value"])
						temp.append(col.field)
					
				columnString = ",".join(temp)
				valueString = ",".join(["?" for col in temp])
											
				query = """
					INSERT INTO OEMOTD_Action 
					({}) VALUES ({})
					""".format(columnString, valueString)

				system.perspective.print("query: " + query)
				system.perspective.print("args: " + str(args))
				
				system.db.runPreUpdate(query, args, tx=txID)

	except Exception as e:
		system.db.rollbackTransaction(txID)
		system.perspective.print(message = "rollback tx \n{}".format(e))
		payload = {"status":"Rollback TX - FAIL"}
		system.perspective.sendMessage("Status", payload)		
	
	finally:
		system.db.closeTransaction(txID)
		system.perspective.print(message = "close tx")
#		payload = {"status":"Close TX - SUCCESS"}
		system.perspective.sendMessage("Status", payload)		
		self.parent.parent.parent.getChild("Table").refreshBinding("props.data")

Below are the results of the print statements to the console:

01:30:55.044 [Browser Thread: 31f2935d-32c2-47b0-bde4-8b8e6187b63d] INFO Perspective.Designer.Workspace - Attempt Insert
01:30:55.045 [Browser Thread: 31f2935d-32c2-47b0-bde4-8b8e6187b63d] INFO Perspective.Designer.Workspace - query: 
					INSERT INTO OEMOTD_Action 
					(Site,OTDImpact,PastDue,RootCause,KeyCorrectiveAction,RecoveryTime,Owner,Segment,ActionStatus,ActionUserID,ActionTimeStamp,ActionType) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
					
01:30:55.045 [Browser Thread: 31f2935d-32c2-47b0-bde4-8b8e6187b63d] INFO Perspective.Designer.Workspace - args: [u'Carlsbad', None, None, None, None, None, None, 'MIS', 'Active', u'3', Sun Dec 03 01:30:55 PST 2023, 'Add']
01:30:55.045 [Browser Thread: 31f2935d-32c2-47b0-bde4-8b8e6187b63d] INFO Perspective.Designer.Workspace - rollback tx 
'com.inductiveautomation.ignition.common.script.Imm' object has no attribute 'runPreUpdate'
01:30:55.046 [Browser Thread: 31f2935d-32c2-47b0-bde4-8b8e6187b63d] INFO Perspective.Designer.Workspace - close tx

Thanks for any insight about the cryptic error 'object has no attribute'. Other parts of the code that accomplish UPDATE work just fine.

2

Typo!

1 Like
3

OMG... sighs... staring at this far too long... indeed the proper call should be:

system.db.runPrepUpdate(query, args, tx=txID)

Thank You! Transistor... brilliant

4

I've just noticed - you even posted it in the title! Leave it there!

Don't forget to mark the answer which answers your question as "Solution".

2 Likes
5

One more thing: database operations don't raise python exceptions. You'll need to catch java errors instead.

from java.lang import Throwable

try:
    database stuff...
except Throwable as e:
    message = e.cause
    system.db.rollbackTransaction(tx)
...