Cannot run multiple Named Queries in 1 tag script

Ignition
1

I cannot run multiple Named Queries in 1 tag script. Only the first named query is being executed. How can I execute all my queries?

def valueChanged(tag, tagPath, previousValue, currentValue, initialChange, missedEvents):

if currentValue.value == 1:
	

	localOrderShopFloorOrderId = system.tag.readBlocking(["[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/MatFlow_MATERIAL_CHECK_MESSAGE/shopFloorOrderId"])[0].value
	bomTargetMaterialNumber = system.tag.readBlocking(["[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/Transaction_DOWNLOAD/targetMaterialNumber"])[0].value
	bomTargetBatchSize = system.tag.readBlocking(["[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/Transaction_DOWNLOAD/targetBatchSize"])[0].value		
	query_select_target_source_item = """
	SELECT [targetSourceItem]
	FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
	WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
	"""
	bomTargetSourceItem = system.db.runPrepQuery(query_select_target_source_item, [bomTargetMaterialNumber, bomTargetBatchSize])

	query_select_material_number = """
	SELECT [materialNumber]
	FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
	WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
	"""
	materialNumber = system.db.runPrepQuery(query_select_material_number, [bomTargetMaterialNumber, bomTargetBatchSize])
	
	query_select_quantity = """
	SELECT [loQuantity]
	FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
	WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
	"""
	quantity = system.db.runPrepQuery(query_select_quantity, [bomTargetMaterialNumber, bomTargetBatchSize])
	life_cycle_params = {"localOrderShopFloorOrderId": localOrderShopFloorOrderId, "lifeCycleName": 'LOCAL_ORDER',"lifeCycleStatus": 'RESERVED',"operatorComment": 'NONE',"createdBy": 'NVML',"archived": 0}
	system.db.runNamedQuery("NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle", life_cycle_params)
	
	# Iterate over each sublist in params
	for i in range(len(materialNumber)):
		material_reservation_params = {"loShopFloorOrderId": localOrderShopFloorOrderId, "bomTargetMaterialNumber": bomTargetMaterialNumber,"bomTargetBatchSize": bomTargetBatchSize, "bomTargetSourceItem": bomTargetSourceItem[i]['targetSourceItem'],"bomMaterialNumber":materialNumber[i]['materialNumber'] , "reservedQuantity": quantity[i]['loQuantity'], "unitOfMesurementId": 'kg ac.ing.', "createdBy": 'NVML', "archived": 0}
		system.db.runNamedQuery("NP/HI/25L/WD/OrderBreakdown/Insert_Material_Reservation", material_reservation_params)
2

Can you post some code so we can see what is going on?

3

I have edited the post now, with the code.

4
  1. This script is inappropriate to be in a tag valueChanged script.
  2. You should not be running multiple named queries in a loop, depending on how often this script is run, you could be hammering your DB. A named query isn't the correct tool for this job.
  3. You should always combine tag reads into a single call, rather than stacking them as you have.

Now, to your question.

Which Named Query call is not running?

5

The last namequery

   	system.db.runNamedQuery("NP/HI/25L/WD/OrderBreakdown/Insert_Material_Reservation", material_reservation_params)

1 - Why is this script inappropriate to be in a tag valueChanged script?
2 - The DB won't be hammered for this application. What should i use instead of namequery?
3 - I agree, I will change that later

I should also add that the code runs perfectly fine in the script console, but not in a tag script.

6

You need to make sure to call the overload of runNamedQuery that accepts a project name as the first parameter: system.db.runNamedQuery | Ignition User Manual

Tags are a global resource and not associated with a project.

4 Likes
7

Because, valueChange scripts run in a limited thread pool of size 3, and a limited event queue of size 5. This means that you can only ever run 3 valueChange scripts across all tags at one time. If those change scripts take long enough executing, and change events start to queue up, you can start to miss change events (on other tags). The general recommendation from the forum anyway, is these types of scripts should not touch a DB (unless you're using the Store and Foreward system), should not call blocking functions (e.g. system.db.runPrepQuery()), and should execute in single digit milliseconds.

You should use a scripted query to do multiple inserts with a single call.

As Kevin, points out, system.db.runNamedQuery() has a different call signature depending on the scope that it is called in. The designer script console will run in "Project Scope", where scripts called from a tag script will run in "Gateway Scope".

As to your issue, my belief is that the first Named Query call returns nothing, so the loop is "skipped".

8

Like this?

def valueChanged(tag, tagPath, previousValue, currentValue, initialChange, missedEvents):

if currentValue.value == 1:
	
	tx_id = system.db.beginNamedQueryTransaction("MyProject", "default")
	localOrderShopFloorOrderId = system.tag.readBlocking(["[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/MatFlow_MATERIAL_CHECK_MESSAGE/shopFloorOrderId"])[0].value
	bomTargetMaterialNumber = system.tag.readBlocking(["[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/Transaction_DOWNLOAD/targetMaterialNumber"])[0].value
	bomTargetBatchSize = system.tag.readBlocking(["[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/Transaction_DOWNLOAD/targetBatchSize"])[0].value		
	query_select_target_source_item = """
	SELECT [targetSourceItem]
	FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
	WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
	"""
	bomTargetSourceItem = system.db.runPrepQuery(query_select_target_source_item, [bomTargetMaterialNumber, bomTargetBatchSize])

	query_select_material_number = """
	SELECT [materialNumber]
	FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
	WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
	"""
	materialNumber = system.db.runPrepQuery(query_select_material_number, [bomTargetMaterialNumber, bomTargetBatchSize])
	
	query_select_quantity = """
	SELECT [loQuantity]
	FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
	WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
	"""
	quantity = system.db.runPrepQuery(query_select_quantity, [bomTargetMaterialNumber, bomTargetBatchSize])
	life_cycle_params = {"localOrderShopFloorOrderId": localOrderShopFloorOrderId, "lifeCycleName": 'LOCAL_ORDER',"lifeCycleStatus": 'RESERVED',"operatorComment": 'NONE',"createdBy": 'NVML',"archived": 0}
	system.db.runNamedQuery("WDE_System","NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle", life_cycle_params)
	
	# Iterate over each sublist in params
	for i in range(len(materialNumber)):
		material_reservation_params = {"loShopFloorOrderId": localOrderShopFloorOrderId, "bomTargetMaterialNumber": bomTargetMaterialNumber,"bomTargetBatchSize": bomTargetBatchSize, "bomTargetSourceItem": bomTargetSourceItem[i]['targetSourceItem'],"bomMaterialNumber":materialNumber[i]['materialNumber'] , "reservedQuantity": quantity[i]['loQuantity'], "unitOfMesurementId": 'kg ac.ing.', "createdBy": 'NVML', "archived": 0}
		system.db.runNamedQuery("WDE_System","NP/HI/25L/WD/OrderBreakdown/Insert_Material_Reservation", material_reservation_params)

It still does not work.

9

It is true that the first query return nothing, but why would that skip the next?

10

If materialNumber is not greater than 0, then this loop doesn't happen.

I'd add some logging using system.util.getLogger to determine what is going on from your return values.

11

Add some logging to your script.

You probably aren't even getting to the runNamedQuery lines because your runPrepQuery calls don't specify a database to use.

1 Like
12

The designer script console is Vision Client scope, which is always associated with a project. Gateway scope may or may not be associated with a project, but there the Named Query functions require a specified project regardless.

This means the script console, by itself, is not appropriate for testing gateway scope scripts. You may find my @system.util.runInGateway decorator helpful.

Consider moving your code to a project library function, and calling that function from a gateway tag change event (in the project) instead of the tag's valueChange event. That will keep this complex and slow script from stalling the tag system.

You can then decorate the project library function to be able to call it from the designer script console for testing.

2 Likes
13

Let's make this a bit simpler:

if currentValue.value == 1:
tx_id = system.db.beginNamedQueryTransaction("MyProject", "default")
tagpaths = [
	"[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/MatFlow_MATERIAL_CHECK_MESSAGE/shopFloorOrderId",
	"[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/Transaction_DOWNLOAD/targetMaterialNumber",
	"[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/Transaction_DOWNLOAD/targetBatchSize"
]
localOrderShopFloorOrderId, bomTargetMaterialNumber, bomTargetBatchSize = [qv.value for qv in system.tag.readBlocking(tagpaths)]

query_select_target_source_item = """
SELECT
	[targetSourceItem],
	[materialNumber],
	[loQuantity]
FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?
"""
data = system.db.runPrepQuery(query_select_target_source_item, [bomTargetMaterialNumber, bomTargetBatchSize])
targetSourceItem = data.getColumnAsList(0)
materialNumber = data.getColumnAsList(1)
quantity = data.getColumnAsList(2)

life_cycle_params = {
	'localOrderShopFloorOrderId': localOrderShopFloorOrderId,
	'lifeCycleName': 'LOCAL_ORDER',
	'lifeCycleStatus': 'RESERVED',
	'operatorComment': 'NONE',
	'createdBy': 'NVML',
	'archived': 0
}
system.db.runNamedQuery("WDE_System","NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle", life_cycle_params)

material_reservation_params = [
	{
		'loShopFloorOrderId': localOrderShopFloorOrderId,
		'bomTargetMaterialNumber': bomTargetMaterialNumber,
		'bomTargetBatchSize': bomTargetBatchSize,
		'bomTargetSourceItem': srcItem,
		'bomMaterialNumber': matNum,
		'reservedQuantity': qty,
		'unitOfMesurementId': 'kg ac.ing.',
		'createdBy': 'NVML',
		'archived': 0
	} for srcItem, matNum, qty in zip(bomTargetSourceItem, materialNumber, quantity)
]
if material_reservation_params:
	q, v = build_insert_string('destination_table', material_reservation_params)
	system.db.runPrepUpdate(q, v, database='destination_db')
  • You don't need 3 queries to pull 3 columns from the same table.
  • Batched tag reads
  • bulk insert instead of inserting row by row in a loop
  • zip() instead of index based iteration - it's more pythonic, though not always easier to read.

here's the query used for the insert:

def build_insert_string(table, data, columns=None):
	"""
	Build the query string and the values list for a multi-insert query.
	Use with `system.db.runPrepUpdate`
	params:
		table (string):						The table to insert into
		data (list of dicts):				A list containing a dict for each row to insert, where the keys are the columns names
		columns_names (list of strings):	The columns to be inserted. If None, they're deduced from the first row's keys.
	return:
		a tuple where:
			- the first element is the query string formatted for a `prepUdate` (with question marks as place holders)
			- the second element is the list of values
	"""
	if not data:
		return None, None
	if columns is None:
		columns = data[0].keys()
	marks = "({})".format(','.join("?" for _ in columns))
	marks = ",".join(marks for _ in data)
	col_names = ','.join(columns)
	q = "insert into {} ({}) values {}".format(table, col_names, marks)
	values = [row[c] for row in data for c in columns]
	return q, values

It returns the query as a string, and the values to pass as parameters. Use with system.db.runPrepUpdate:

q, v = build_insert_string('some_table', data)
system.db.runPrepUpdate(q, v)
3 Likes
14

I didn't even see that. Nice catch!

15

I forgot to address transactions: if you want your queries to be part of a transaction, you’ll need to make them all either named queries or regular queries.
With bulk insert in the mix, I’d go for regular queries.
Also note that when using transactions, it’s usually a good idea to wrap your queries in try/except/finally blocks to handle rollbacks, commits and closing the transaction

1 Like
16

That is not the issue. The code runs fine in a script console and materiNumber is always larger than 0.

17

Even when I do specify a database the code is still not running

18

I tried to move the script into a project library and call the script from the tag's valueChange event, but then none of the queries ran.

19

You'll need to configure the 'gateway scripting project' for this to work.
Or, as suggested before, use a gateway event tag change script instead.

20

How do i configure the 'gateway scripting project' and how should I configure it?