Begin Transaction, problem with tx number java.util.map

[mdemaris]

Hey all,

I am running a script from a button event, that sends a message to the root container, which runs a named query, and inserts a few values into the DB.
I am trying to test the system.db.beginNamedQueryTransaction because I am trying to run multiple update queries back-to-back in a new project, but for some reason I only get a few updates (out of 27) to run successfully.
In this test, this is the query:

INSERT INTO [dbo].[Table_3] ([F1], [F2], [F3])
VALUES (:p_1, :p_2, :p_3 )

This is the message handler: (`test01')

def onMessageReceived(self, payload):
	# implement your handler here
	query = 'Coater/Test01'
	p1 = self.getChild("NumericEntryField").props.value
	p2 = self.getChild("NumericEntryField_0").props.value
	p3 = self.getChild("TextField").props.text
	params = {'p_1':p1, 'p_2':p2, 'p_3':p3}
	proj = 'Sandbox_Sample'
	db = 'IGNSVR_Dev'
	tx = ''
	
	tx = system.db.beginNamedQueryTransaction(proj, db, 2)
	system.db.runNamedQuery(query, params, tx)
	system.db.commitTransaction(tx)

(I removed the closeTransaction to see if anything would change...)

This is the button event script:

def runAction(self, event):
	msg = 'test01'
	payload = {}
	system.perspective.sendMessage(msg, payload)

This is the gist of the error:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 23, in onMessageReceived at ... 
java.lang.ClassCastException: java.lang.ClassCastException: 
Cannot coerce value '90da8353-ce17-42a0-852e-65506aacc7d2' into type: interface java.util.Map

I've read a few other threads and the docs and it appears the args for Perspective scope are correct.
Any ideas?

[ryan.white]

@mdemaris

type: interface java.util.Map

Looks like in some cases the tx is being passed to the params argument of the runNamedQuery function.

system.db.runNamedQuery(query, params, tx)

Needs project name at the front. If this is in perspective it runs in gatetway-esc scope, despite what the manual says.

I made a helper function to automatically add project name at the front based on the executed scope. I use this across the board in perspective projects, handlers and otherwise. Some of my use cases match yours exactly (message -> handler -> transaction), though all my handlers are 1 line calls to a script library function.

def runContextualNamedQuery(path, params=None, tx=None, getKey=False):
	""" """
	if shared.util.getExecutionScope() in ("P", "G"):
		return system.db.runNamedQuery(system.util.getProjectName(), path, params, tx, getKey)
	else:
		return system.db.runNamedQuery(path, params, tx, getKey)

from com.inductiveautomation.ignition.common.model import ApplicationScope

def getExecutionScope():
	""" """
	scope = ApplicationScope.getGlobalScope()
	if ApplicationScope.isClient(scope):
		return "C"

	if ApplicationScope.isDesigner(scope):
		return "D"

	if ApplicationScope.isGateway(scope):
		if "perspective" in dir(system):
			return "P"

		return "G"

	return "UNK"

[mdemaris]

@ryan.white

shared.util.getExecutionScope()

shared.util is a Project Library package?

This works well! Thank you Ryan!

[ryan.white]

@mdemaris

Project Library package?

Correct, its in the project at the top of our inheritance stack. We've also put stuff like pturmel's later.py, xml.py and queueUtil.py in there.

Edit, looks like this issue has been around a while? Found this:

[IGN-3829] Error executing system.db.commitTransaction

I had a similar problem when I was trying to set up an all or nothing write to our DB. What fixed it for me was the first query in the transaction (after getting the transaction ID) needed to have the project argument filled. Subsequent named query calls do not need that parameter filled. This worked: txID = system.db.beginNamedQueryTransaction(project="project1", database="database1") try: system.db.runNamedQuery(project="project1", path="query1", params=params1, tx=txID) #do some stu…

Also, if you are interested, a transaction decorator that automatically catches errors:

Script Benchmarking with Decorators Example

I made something similar to what you are aiming for but its also made for managing database transactions and errors in a standard way by creating a Response object that then I inspect after. import functools import timeit import traceback from forms2.structs import Response import java.lang.Throwable class handleTransaction(object): def __init__(self, logger): self.logger = logger def __call__(self, inner_func): """ We do 'tx' not in kwargs a few times here. If a fucntion has tx=None…

[paul-griffith]

@ryan.white

if "perspective" in dir(system):

This will always be true (in gateway scope, if you have the Perspective module installed) - this won't catch scenarios where you're not in a Perspective launched context but elsewhere on the gateway (e.g. a tag event script). See @nminchin's similar looking code here:

Get current scope in scripting

There are a number of topics about this, with this being the only topic that works to distinguish between gateway and perspective thanks @PGriffith and @hunterdg I've incorporated this into @Kyle_Chase 's script from this topic: from com.inductiveautomation.ignition.common.model import ApplicationScope try: from com.inductiveautomation.ignition.gateway import IgnitionGateway isGateway = True except ImportError: isGateway = False def getScope(): scope = ApplicationScope.getG…

I'll also mention for posterity here that in 8.3 we introduced new named query scripting functions to address this fundamental inconsistency.

[ryan.white]

@paul-griffith

This will always be true

Huh, guess I've been lucky enough with my usage I haven't smacked into this. Will update this immediately.

Any insight as to why the named query call is expecting the project name, despite being called in a perspective context? This appears to only happen after starting a transaction in a perspective scope.

[mdemaris]

I don't know if this makes any difference, but I tried IsolationLevel 8 and things weren't working very well, with 27 update queries in queue, but with a level 2, everything seems to work fine so far. I also changed the helper function as Paul pointed out in the other thread.

EDIT:
And, I'm running them like this:

		try:
			runContextualNamedQuery(query, params, txNumber1)
		except Throwable as t:
			system.db.rollbackTransaction(txNumber1)
		else:
			system.db.commitTransaction(txNumber1)
		finally:
			system.db.closeTransaction(txNumber1)
		
		sleep(0.5)

I don't know if the sleep is necessary, but I seem to recall an issue with running queries without a slight pause. This might not make a difference when using transactions, though. Have yet to test it out.

[paul-griffith]

@ryan.white

This appears to only happen after starting a transaction in a perspective scope.

Not sure. I would've thought that providing a transaction entered some different block of code early on, but the check to load the contextual project name happens right at the top of the runNamedQuery block.