Perspective Table Update

[leonardo.godoi]

Seems like i forgot how system.tag.readBlocking works, that’s what i get for relying on system.tag.read for so long .

    # define values:
	Var1='self.parent.parent.getChild("Melt").getChild("TextField").props.text'
	Var2=...
	Var3=...
	Var4=...
	Var5=...
	Var6=...	
	Var7=...
	Var8=...
	Var9=...
	Var10=...
	
	
	#1 define the row values into a list:
	newRow = [Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, "", "", "", "", "", "", "", "", "", "", "", "", ""]
	
	# 2. Get the dataset:
	dataset= system.tag.readBlocking("[default]ms/Test/QueryTag.value")[0].value
	
	#3. Alter the dataset:
	dataset = system.dataset.addRow(dataset,dataset.getRowCount()+1,newRow)
	#The second argument  is to make sure that your new row goes to the bottom
	#You can change that if you want.
	
	#4. Write it back to your tag:
	System.tag.writeAsync("[default]ms/Test/QueryTag.value",dataset)
	
	#5. Refresh Binding on the table
	self.parent.parent.parent.parent.parent.getSibling("Melt").getChild.getchild("Table").props.data

[victordcq]

ah yes my bad, updating the dataset wont really update the database. You can just use the tag tho for datacollection, as these basically are also stored in the internal ignition database xd It depends if you need the data in your own database too.

dataset.getRowCount()+1

Im not sure you need to do +1 btw, dont dataset start counting from 0? so the length should already be the right index^^

also the refresh data isnt right.
it should be like this:
self.parent.parent.parent.parent.parent.getSibling("Melt").getChild.getChild("Table").refreshBinding("props.data")

[pkhoshroo]

When I implemented this, I got an error:

ValueError: Error trying to coerce ‘self.parent.parent.getChild(“Melt”).getChild(“NumericEntryField”).props.value’ to a number.

Then I decided to comment all the VAR and instead write only “” to the new row. Then I got this error:
NameError: global name ‘System’ is not defined

I then used
import system
but it did not change anything.
I do not know what I am doing wrong here but the logic seems to be correct.

[victordcq]

int( self.parent.parent.getChild("Melt").getChild("NumericEntryField").props.value) should turn it into a number

system should be in lowercases

[pkhoshroo]

So you mean the above script only updates the dataset tag and not the database itself?
What would be the best solution here?

Originally, what I wanted to do was to bind each field to one memory tag and then use those tags in a table on another view, then at the same time I wanted to update the database based on the new values entered into the fields, but I thought this would not be the easiest way to do (I am not sure tho).

Now what I am doing is to use a query dataset tag to read from the database and by entering numbers into the fields, the query tag has to be updated, or at least I want the table to be updated.

So this is exactly what should be happened in my application:

[victordcq]

heh yea i suppose its useless yo edit the dataset then

you will have to do a query then i suppose

(you might ahve to add in the 13 other columns too if they arent allowed to be null)

query = "INSERT INTO tableName (col1, col2, col3 ...col10) VALUES (?,?,? ...)"
args = [value1, value2, value3 ... value10]
system.db.runPrepUpdate(query, args)

you can keep the query binding to the dataset tag and the table, you will just have to trigger an update on the table than with the refresh binding.
you co do that in the view itself with a button, or trigger it in a different view you will have to use a message i suppose

if you are leting the operator fill in those vars in a popup you should use a message
https://docs.inductiveautomation.com/display/DOC81/Component+Message+Handlers

[pkhoshroo]

@victordcq

query = "INSERT INTO tableName (col1, col2, col3 ...col10) VALUES (?,?,? ...)"
args = [value1, value2, value3 ... value10]
system.db.runPrepUpdate(query, args)

Thank you very much for your reply!
Where should I add this query? On the button script?

[victordcq]

yes^^

[pkhoshroo]

Since I am getting error I wanted t confirm that I am thinking correctly here:
This is what you are suggesting:

Now the database table is updated with a new row containing the new values, correct?

Is there anyway to update the table that is located on another view automatically? Not using a button.
If there is a way then in this stage the table is updated too.

[victordcq]

the query has to have the “?” after values isntead of vars

query = “INSERT INTO tableName (col1, col2, col3 …col10) VALUES (?,?,? …)”
tables bindings to the tag with the dataset should update automatticaly when browsing to the page, unless its in the page itself where the popup came from then you would need a message to handle the refreshbinding.
i think you can also have it update with a polling rate

[pkhoshroo]

I am going to try this now and see if it is working. Thank you Victor! I do really appreciate it.

[pkhoshroo]

So I simplified the issue here, now I am using this setup:

3 columns table with 3 input text fields.

I bound the table data to a query dataset tag and I wrote the script below for the button:

	query = "INSERT INTO rack_data (rack_data_id, rack_sn, state) VALUES (?,?,?)"
	args = [self.getSibling("TextField").props.text,self.getSibling("TextField_0").props.text, self.getSibling("TextField_1").props.text]
	system.db.runPrepUpdate(query, args)

However, I get this error:
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO rack_data (rack_data_id, rack_sn, state) VALUES (?,?,?), [1, 123, pass], MSSQL, , false, false)

What should be the table name here?

query = “INSERT INTO rack_data (rack_data_id, rack_sn, state) VALUES (?,?,?)”

The table name of the table component I used in ignition? or the table name of the database? I used the latter.

**** Another thing I tried was writing this script on my button (I changed the text fields to numeric fields:

		newRow = [self.getSibling("NumericEntryField").props.value,self.getSibling("NumericEntryField_0").props.value,self.getSibling("NumericEntryField_1").props.value]
		dataset= system.tag.readBlocking("[default]ms/Test/QueryTag.value")[0].value
		dataset = system.dataset.addRow(dataset,newRow)

I have the error of mismatched input" excepting DEDENT

[josborn]

I am fairly certain you need to use named queries in perspective. Honestly, i think its the best way to go as well.

[victordcq]

you dont need to use named queries, but you do need a database with a table named like 'rack_data" connected to the gateway

[pascal.fragnoud]

@pkhoshroo

I bound the table data to a query dataset tag

Don't bind it to a tag, bind it to a query directly. The property binding window in the designer has a query tab where you can select a named query, I can't see any reason not to use this. Tags don't need to be involved here.

After reading this thread, because it doesn't seem to be clear in your mind, I want to point out your table is there only for display. It shouldn't appear anywhere in your database update/insert script, or anywhere else in your form. There are two very distinct steps in the process here:

1. update the database with the new data
2. display the database table in a table.

[pkhoshroo]

I got this working based on everyone’s feedback @victordcq, @pascal.fragnoud @leonardo.godoi @josborn (Thank you!).

So here is what I did for further references (There are two ways to solve this, either with a query tag and binding the tables’ data to the query tag, or directly binding the table’s data to named query):

1. Created this perspective view with one table component, two text fields, and one button

2. Created a query tag with the execution mode to be tag group (the execution mode has to be tag group not event-driven).
   ** Set the database as your Datasource in the tag editor.

I wrote the query of the tag as below:

SELECT * 
FROM yourdatabasename
ORDER by rack_data_id DESC

3. Bound the table data to the query tag.
   You can skip the 2nd section and directly bind the table’s data to a named query instead which is easier. @pascal.fragnoud

4. add a script for the button on the mouse click:

	rack_sn = self.getSibling("TextField").props.text
	state = self.getSibling("TextField_0").props.text
	system.db.runPrepUpdate("INSERT INTO  \
	   yourdatabasename\
	    (rack_sn, state) \
	    VALUES (?,?)", [rack_sn, state])

And now, when you click on the button, a row is being added with the texts you entered into the table, it is also being added to the database as well

[pkhoshroo]

Thank you for your response! Appreciate the information.

[Abhinandan_Rb]

Named queries is working for button scripts too.

params1 = {"values"}
system.db.runNamedQuery(" yourquery", params1)