Issue using transactions with named queries?

Trying to run some named queries in a Form class. I am getting a datasource error and I have no idea why.

Heres the portion of my code that is running

class Form(object):
	Abstract class to be implemented by script modules in forms package.
		NamedQueries: for subclasses should be under the forms folder of named queries, 
			with a sub folder matching the name of the class.  For now, lets just leave it strictly like this to enforce some organization...
		idx: int, used for updating only, or will be assigned during after creation
		newData: dictionary, used for insert statements or update statements
		oldData: dictionary, can be provided for update statements in the event comparisons are needed
		defaultValues: dictionary, used to fill in blank columns of newData for inserts
		logger: logger, from system.util.getLogger
		tx: str, transaction id for running of queries
	namedQueryFolder = ''
	idx = None
	newData = None
	oldData = None
	defaultValues = {}
	logger = None
	# Transaction information
	tx = None
	isolationLevel = system.db.SERIALIZABLE
	timeout = 5000
	def __init__(self):
	def _insert(self):
		createNamedQuery = self.namedQueryFolder + '/create'
		print 'createNamedQuery:' + createNamedQuery
		print 'newData: ' + str(self.newData)
		print 'tx: ' + str(self.tx)
		self.idx = system.db.runNamedQuery(createNamedQuery, self.newData, tx=self.tx, getKey=1)

	def create(self, newData):"running create")
		self.newData = newData
		self.tx = system.db.beginNamedQueryTransaction(self.isolationLevel, self.timeout)
		self.updateUIAfterInsert()"Created new record with idx %i"%(self.idx))

The last line self.idx = system.db.runNamedQuery(createNamedQuery, self.newData, tx=self.tx, getKey=1) in def _insert(self) is throwing the following error and I have no idea why -

newData: {'defaultProjectManager': -1, 'description': u'abcde', 'active': True, 'customerPath': u'abcde', 'creditHold': False, 'viewable': True, 'unpaidInvoiceTotal': 0.0, 'paidOnUnpaidInvoice': 0.0, 'name': u'abcde', 'creditLimit': 0.0, 'activeInvoiceFlags': 0, 'companyTypeId': 1, 'discountId': 5}
tx: b4fcf72b-20ce-4028-9fb8-0001894a5479
13:58:54.142 [AWT-EventQueue-0] ERROR com.inductiveautomation.ignition.client.util.gui.ErrorUtil - <HTML>Error executing script for event:&nbsp;<code><b>actionPerformed</b></code><BR>on component:&nbsp;<code><b>cmdOK</b></code>.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):
  File "<event:actionPerformed>", line 31, in <module>
  File "<module:classes.Form>", line 94, in create
  File "<module:classes.Form>", line 74, in _insert
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(
	at com.inductiveautomation.factorypmi.application.db.namedqueries.ClientNamedQueryManager.execute(
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLException: Datasource "8" does not exist in this Gateway.

If I change the problematic line to self.idx = system.db.runNamedQuery(createNamedQuery, self.newData, getKey=1) and get rid of the lines starting/closing the transaction, it works fine, so it must be related to my tx=self.tx but I don’t know what I am doing wrong here. Any thoughts?

Another odd thing to me is that with runNamedQuery you don’t get to pass in the datasource into the funciton per the docs system.db.runNamedQuery - Ignition User Manual 8.0 - Ignition Documentation. My named query definitions all have the default database connection set and as mentioned work fine when I do it without the attempting to do it in a transaction.

The only place I can see an 8 even potentially coming from is system.db.SERIALIZABLE which is 8, but I don’t know how that that would happen.

I recall a bug in transactions for Named Queries. Pretty sure it was fixed some time in v8.1.

1 Like

Ah ok that makes sense. I’m on 8.0.17. Guess it’s time to upgrade.

Yup. And discussion here:

1 Like

Hmm. I’m not seeing this in the changelogs…

@kcollins1 Hey, a thread you were involved in last year regarding transactions for named queries was mentioned. Do you know if this something that has been fixed and if so do you know what version?

3829 hasn’t been picked up yet. Paul has escalated it, but if you want it even higher in the list contact support and have them attach a support ticket to it.

1 Like

Ok thank you. It is not urgent, but I am in the midst of refactoring a project to all namedQueries and at the same time started putting things into transaction groups to have some atomicity to the database operations. Project still works as is so everyone is happy but I would definitely love this functionality for any fresh future projects.