Advice Needed on db Update from Ignition

ahawes · June 28, 2024, 12:52pm

Good morning team,

I have a situation where I need to have a table updated from Ignition Perspective. I am updating just 1 column and either 1 cell or mutiple cells at the same time.

In the picture attached, the cell to be updated would be the Storage Type.

What is the best way to do this. I have used tables previously but i was only updating 1 field in a column at a time.

I was going to use COLUMN_3 to write the new value

I was also going to use this Action Script:

	# Get the selected SKUs from the table
	selected_items = self.getSibling("Table").props.selection.data
					    
	 # Iterate over each selected SKU and update it
	for item in selected_items:
	    sku = item['sku']
	    storage_type = item['storage_type']
					        category = "HMI"
					        source = "ASCADA Client"
					        usr = self.session.props.auth.user.id
					        message = f"{usr} Updated {sku} Storage Type WCS {storage_type}"
					        data9 = usr 
					
					        # Insert or update SKU
					        system.db.runNamedQuery("Tables/update_sku_table", {
					            "sku": sku,
					            "storage_type": storage_type
					        })
					
					        time.sleep(2)
					
					        # Update Syslog
					        system.db.runNamedQuery("Tables/syslog_update", {
					            "category": category,
					            "source": source,
					            "message": message,
					            "data9": data9
					        })
					
					        time.sleep(2)
					
					    # Refresh table data after updating all SKUs
					    self.getSibling("Table").refreshBinding("props.data")
	time.sleep(2)

bkarabinchak.psi · June 28, 2024, 12:59pm

Some general tips with writing db queries here - you do not need to time.sleep(2) to wait for the query to finish or anything like that. If you are worried about one query running and the other one not running, use transactions.

message = f"{usr} Updated {sku} Storage Type WCS {storage_type}" this won't work, there are no f-strings in jython 2.7. If it seems like nothing is happening database wise then its probably because the script is hitting an error on this line.

I don't know exactly what data you get from selection.data but seems a reasonable choice if you only want to affect the selected rows though I can't speak to if your syntax is right or wrong on that.

lrose · June 28, 2024, 2:21pm

Yep and as you are not doing anything with the return from the call to system.db.runNamedQuery() the only thing this is doing is slowing down your performance dramatically. system.db.runNamedQuery() is a blocking function so it will automatically wait for a return before executing the next call anyway.

You can do this however:

message = "{} Updated {} Storage Type WCS {}".format(usr,sku,storage_type)

ahawes · June 28, 2024, 2:33pm

Great tips, thanks so much.

Question, how do I tell the query to go after any ROW that has string in column_3?

I will only need it to update a sku row if there is a letter here.

# Get the selected SKUs from the table
	selected_items = self.getSibling("Table").props.selection.data[0].column_3
	    
	# Iterate over each selected SKU and update it
	for item in selected_items:
		sku = self.getSibling("Table").props.selection.data[0].sku
		storage_type = self.getSibling("Table").props.selection.data[0].column_3
	
	category = "HMI"
	source = "ASCADA Client"
	usr = self.session.props.auth.user.id
	message = str(usr) + " Updated  " + str(sku) + " in WCS " + "to VRT Assignment to " + str(VRT)
	data9 = usr 
	
				# Insert new SKU
	system.db.runNamedQuery("Tables/update_sku_table",{
			"sku": skus,
			"storage_type": storageType
					})

	
	# Refresh table data
	self.getSibling("Table").refreshBinding("props.data")
					
				#Update Syslog		
	system.db.runNamedQuery("Tables/syslog_update",{
				"category": category,
				"source": source,
				"message": message,
				"data9": data9
					})

lrose · June 28, 2024, 2:36pm

Something like:

selected_items = [row.column_3 for row in self.getSibling("Table").props.selection.data if row.column_3 != ""]

csjohnson · June 28, 2024, 4:39pm

Alternatively, you can use an onEditCellCommit event on the table to then update the row.

Mind the if statement so that you only run the named query if the column that was updated is indeed column_2, or whatever your column name is for storage type.

def runAction(self, event):
	sku = self.props.data[event.row].column_1
	
	# Update Storage Type
    # Assuming column name is column_2 for Storage Type
	if event.column == 'column_2':
		storage_type = event.value
	    # Insert or update SKU
        path = "Tables/update_sku_table"
        parameters = {"sku": sku,  "storage_type": storage_type}
        system.db.runNamedQuery(path, parameters)
        self.refreshBinding('props.data')

Named Query

UPDATE [yourTable]
SET [StorageType] = :storage_type
WHERE [SKU] = :sku

ahawes · July 1, 2024, 12:35pm

Morning Team,

I am back at this morning and I am having some issues that I am sure are basic.

First, I am trying a query to onEditCellCommit but i am unsure who to make it work. I need the select query to populate the table but I also need an update query. How do i populate the table and do an update as the same query?

I was working with the table this morning and the table will not allow me to change the storage type. When I edit it, it goes back to the original.

As a note, i am trying to update a mysql table from this ignition table.

Second, when I add another column that I want to pull the data from it does not let me edit that cell. I have everything set properly but i cannot click to edit.

SELECT sku, storage_type
FROM material_master

UPDATE Clemens.material_master
SET storage_type =   :storageType 
WHERE sku IN  ( :skus )

pturmel · July 1, 2024, 12:57pm

You don't, generally.

The onEditCellCommit would do just the UPDATE, then tell your table's props.data binding to refresh.

The props.data binding would have the SELECT (typically in a named query binding).

lrose · July 1, 2024, 1:00pm

In the onEditCellCommit() function, after you call the Update Query, refresh the binding.

self.refreshBinding('props.data')

Are you sure you have everything set properly? Show us the settings.

ahawes · July 1, 2024, 1:18pm

If it would be easier I could use onEditCellCommit but i do not know how to write that.

I see how to add it to the script but not sure if it is correct.

`def runAction(self, event):
	# Get the id of the row we edited.
	id = self.data.getValueAt(rowIndex, 'ID')
	
	# Create our query and arguments. The extension function gives us a colName variable,
	# which we can use in our query. The query will then take two arguments.
	# The value that we are updating and the id of the row we edited.
	query = "UPDATE material_master SET %s = ? WHERE storage_type = ?" % (storage_type)
	args = [newValue, id]
	
	# Run the query with the specified arguments.
	system.db.runPrepUpdate(query, args)
	
	# Re-query the database, so we can ensure it properly updated the table.
	system.db.refresh(self, "data")`

pturmel · July 1, 2024, 1:21pm

This will break immediately. The function signature is runAction(self, event). rowIndex isn't either argument. (Should be event.rowIndex.)

I'm gonna guess you are copying and pasting from Vision examples.

More differences:

A Perspective table doesn't have self.data. It has self.props.data, which might be JSON format instead of a dataset.

Perspective doesn't use system.db.refresh(), it uses self.refreshBinding().

lrose · July 1, 2024, 1:28pm

On top of what Phil has already said, there are some other issues with this script.

'id' is a built-in identifier in python, you should avoid using it as a variable name.
storage_type is not defined anywhere in the script. Did you mean event.columnName?
The system.db.refresh() function is only available in Vision Client Scope, and so will not work in perspective.

ahawes · July 1, 2024, 1:44pm

So what should it be?

I applolgize for so many questions.

lrose · July 1, 2024, 1:55pm

Why? It's a forum, literally a platform for asking questions.

I would expect code something like this:

#assuming that props.data is a dataset
rowId = self.props.data.getValueAt(event.row, "ID")
#if props.data is in JSON format then it would be
#rowId = self.props.data[event.row]["ID"]

#update database with edited data.
query = "UPDATE material_master SET {} = ? WHERE storage_type = ?".format(event.column)
args = [event.value, rowId]
system.db.runPrepUpdate(query,args)

#refresh the binding on props.data to pull in the edited data.
self.refreshBinding('props.data')

ahawes · July 1, 2024, 2:17pm

Thanks for helping Irose!!

I am getting this error.

Unable to run action 'component.onEditCellCommit' on Popups/MHS Converted/Storage Type Pass Through@D/root/Table_0: code could not be compiled.

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 8, in <module>
NameError: name 'event' is not defined

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 8, in <module>
NameError: name 'event' is not defined


Ignition v8.1.33 (b2023101913)
Java: Azul Systems, Inc. 17.0.8

lrose · July 1, 2024, 2:39pm

You're going to have to show the code as you have input it. event is definitely defined in that event script. Something else, must be going on.

ahawes · July 1, 2024, 2:47pm

Here is the new error:

Here is the code i used.

def runAction(self, event):
	#assuming that props.data is a dataset
	rowId = self.props.data.getValueAt(event.row, "ID")
	#if props.data is in JSON format then it would be
	#rowId = self.props.data[event.row]["ID"]
	
	#update database with edited data.
	query = "UPDATE material_master SET {} = ? WHERE storage_type = ?".format(event.column)
	args = [event.value, rowId]
	system.db.runPrepUpdate(query,args)
	
	#refresh the binding on props.data to pull in the edited data.
	self.refreshBinding('props.data')

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 3, in runAction
java.lang.ArrayIndexOutOfBoundsException: java.lang.ArrayIndexOutOfBoundsException: Column 'ID' doesn't exist in this dataset.

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 3, in runAction
java.lang.ArrayIndexOutOfBoundsException: java.lang.ArrayIndexOutOfBoundsException: Column 'ID' doesn't exist in this dataset.

	caused by ArrayIndexOutOfBoundsException: Column 'ID' doesn't exist in this dataset.

Ignition v8.1.33 (b2023101913)
Java: Azul Systems, Inc. 17.0.8

lrose · July 1, 2024, 2:52pm

The error is telling you exactly what the issue is. There is not an "ID" column in the props.data dataset on the table. Either change the way the table is being populated to include the "ID" column or use the correct column name to get the rowId.

ahawes · July 1, 2024, 3:14pm

Ok, i fixed that now i have this.

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 8, in <module>
NameError: name 'event' is not defined

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 8, in <module>
NameError: name 'event' is not defined


Ignition v8.1.33 (b2023101913)
Java: Azul Systems, Inc. 17.0.8

lrose · July 1, 2024, 3:16pm

event is part of the runAction() signature. It must be defined, so, again, there is something else going on. New code?