Database transaction closed

Ignition
1

Am I using the database transaction correctly? It appears to me that the transaction timeout is not be reset because when I select a small amount of rows it works fine. A large set of rows throws the error.

I am receiving this error

[quote]Traceback (innermost last):

File “event:actionPerformed”, line 12, in ?

java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE [DepManager].[dbo].[tblDepletions] SET Sales=NULL WHERE DepletionID=?, [874779], , eb744ff7-8e08-4df8-bdfc-e131514ef72c, false)

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: Transaction “eb744ff7-8e08-4df8-bdfc-e131514ef72c” is closed.

Caused by: java.sql.SQLException: Transaction “eb744ff7-8e08-4df8-bdfc-e131514ef72c” is closed.

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE [DepManager].[dbo].[tblDepletions] SET Sales=NULL WHERE DepletionID=?, [874779], , eb744ff7-8e08-4df8-bdfc-e131514ef72c, false)

Ignition v7.3.2 (b533)
Java: Sun Microsystems Inc. 1.6.0_29
[/quote]

when i run this code

[code]dateID = event.source.parent.getComponent(‘Date’).intDateID

databaseName = event.source.parent.databaseName

rows = event.source.parent.getComponent(‘Table’).getSelectedRows()

dsPyData = fpmi.dataset.toPyDataSet(event.source.parent.getComponent(‘Table’).data)

txID = system.db.beginTransaction(timeout=5000)

for rowNum in rows:
system.db.runPrepUpdate(“UPDATE “+databaseName+”[tblDepletions] SET Sales=NULL WHERE DepletionID=?”,[row[“DepletionID”]],tx=txID)

system.db.commitTransaction(txID)
system.db.closeTransaction(txID)

fpmi.db.refresh(event.source.parent.getComponent(‘Table’),“data”)
event.source.parent.getComponent(‘Table’).selectedRow = -1[/code]

Thank you,

2

You are using it correctly. I bet your query is just taking a while to complete. I would change the code a bit to make it more efficient:[code]dateID = event.source.parent.getComponent(‘Date’).intDateID

databaseName = event.source.parent.databaseName

rows = event.source.parent.getComponent(‘Table’).getSelectedRows()

dsPyData = fpmi.dataset.toPyDataSet(event.source.parent.getComponent(‘Table’).data)

dIDs = “”
for row in rows:
dIDs = “%s%d,” % (dIDs, row[“DepletionID”])

if dIDs != “”:
dIDs = dIDs[:-1]
system.db.runPrepUpdate(“UPDATE “+databaseName+”[tblDepletions] SET Sales=NULL WHERE DepletionID IN (%s)” % dIDs,[])

fpmi.db.refresh(event.source.parent.getComponent(‘Table’),“data”)
event.source.parent.getComponent(‘Table’).selectedRow = -1[/code]

3

The script is a little more complicated then the simplified one i posted. i increased the transaction timeout to 20 seconds and still have the problem. there is no way that each one of the PrepUpdates is taking more than 20 seconds. i would say the entire script could take more than 20 seconds, but not each individual queries.

i took out the transaction stuff and the script works fine. i just thought if i used the transaction stuff in the case something did go wrong in the middle the changes would rollback.

here is the full code with the transaction functions

dateID = event.source.parent.getComponent('Date').intDateID

databaseName = event.source.parent.databaseName

rows = event.source.parent.getComponent('Table').getSelectedRows()

dsPyData = fpmi.dataset.toPyDataSet(event.source.parent.getComponent('Table').data)

txID = system.db.beginTransaction(timeout=20000)

for rowNum in rows:
	if(dsPyData[rowNum]["Depletions"] == 1 or dsPyData[rowNum]["Depletions"] == 2 or dsPyData[rowNum]["Depletions"] == 3 or dsPyData[rowNum]["Depletions"] == 4):
		#vipCustomerNum = dsPyData[rowNum]["SMDIST"]
		areaID = dsPyData[rowNum]["DistID"]

		if(event.source.parent.getComponent('ctnSalesOrInv').getComponent('rbSalesInv').selected == 1):
			system.db.runPrepUpdate("DELETE "+databaseName+"[tblDepletionsTransferAudit] WHERE DistID=? and DateID=?",[areaID,dateID],tx=txID)
			system.db.runPrepUpdate("DELETE "+databaseName+"[tblDepletions] WHERE AreaID=? and DateID=?",[areaID,dateID],tx=txID)
		elif(event.source.parent.getComponent('ctnSalesOrInv').getComponent('rbSales').selected == 1):
			dsInvResult = fpmi.db.runQuery("SELECT DepletionID,EndingInv,ProdID FROM "+databaseName+"[tblDepletions] WHERE AreaID="+str(areaID)+" and DateID="+str(dateID))
			for row in dsInvResult:
				if(row["EndingInv"] == None):
					system.db.runPrepUpdate("DELETE "+databaseName+"[tblDepletionsTransferAudit] WHERE DistID=? and DateID=? and ProdID=?",[areaID,dateID,row["ProdID"]],tx=txID)
					system.db.runPrepUpdate("DELETE "+databaseName+"[tblDepletions] WHERE DepletionID=?",[row["DepletionID"]],tx=txID)
				else:
					system.db.runPrepUpdate("UPDATE "+databaseName+"[tblDepletions] SET Sales=NULL WHERE DepletionID=?",[row["DepletionID"]],tx=txID)
		elif(event.source.parent.getComponent('ctnSalesOrInv').getComponent('rbInv').selected == 1):
			dsInvResult = fpmi.db.runQuery("SELECT DepletionID,Sales,ProdID FROM "+databaseName+"[tblDepletions] WHERE AreaID="+str(areaID)+" and DateID="+str(dateID))
			for row in dsInvResult:
				if(row["Sales"] == None):
					system.db.runPrepUpdate("DELETE "+databaseName+"[tblDepletionsTransferAudit] WHERE DistID=? and DateID=? and ProdID=?",[areaID,dateID,row["ProdID"]],tx=txID)
					system.db.runPrepUpdate("DELETE "+databaseName+"[tblDepletions] WHERE DepletionID=?",[row["DepletionID"]],tx=txID)
				else:
					system.db.runPrepUpdate("UPDATE "+databaseName+"[tblDepletions] SET EndingInv=NULL WHERE DepletionID=?",[row["DepletionID"]],tx=txID)

			#system.db.runPrepUpdate("UPDATE "+databaseName+"[tblDepletions] SET EndingInv=NULL WHERE AreaID=? and DateID=?",[areaID,dateID])

system.db.commitTransaction(txID)
system.db.closeTransaction(txID)

fpmi.db.refresh(event.source.parent.getComponent('Table'),"data")
event.source.parent.getComponent('Table').selectedRow = -1
4

I see. Any chance you can give our tech support team a call. We probably need to look into that further. Using transactions like that should work just fine.

5

Hey all,

I know this is an old thread, and my search didn’t show any other similar threads, but I was having this same problem(on Ignition 7.7.1) and managed to fix it. So I thought I might share it.

The problem in the full script above, is this query:

dsInvResult = fpmi.db.runQuery(“SELECT DepletionID,EndingInv,ProdID FROM “+databaseName+”[tblDepletions] WHERE AreaID=”+str(areaID)+" and DateID="+str(dateID))

It’s making a query without linking it to the original transaction “txId” hence using a transaction of its own. My guess is that when you do this while a transaction is open it closes the open transaction and starts a new one. So any queries after the one above will indicate the transaction is closed. Just making the full statement: runQuery(query,db,tx) using the same transaction as the whole script should fix it. At least that’s how I managed to fix my problem.

Hope this helps!
/R

1 Like