Add New Rows to Perspective Tables

Ignition
,
1

Hello everyone,

For an application, I am trying to add rows to a perspective table.
I created a query tag dataset and bound the table data to that dataset.

image

I tried 3 different scripts on the button, but none of them worked!

		dataset= system.tag.readBlocking("[defualt]ms/Test/QueryTag.value")[0].value
		dataset = system.dataset.addRow(dataset,[8, 'pass')

	query = "INSERT INTO ref_vac_result (ref_vac_result_id, names) VALUES (?,?)"
	system.db.runPrepUpdate(query, [8, 'pass'])

	table = self.getSibling("Table").props.data
	table = system.dataset.addRow(table,[8,"pass"])
	self.getSibling("Table").refreshBinding("props.data")

I have no idea what I am doing wrong here, so I would appreciate any suggestions you may have.

2

In instance 1, you are creating a new dataset, but I don’t see where you actually write that data back to the table.

In instance 2, you’re (potentially) updating the table, but depending on the configured binding the Table may not be polling for updates. You might want to include a call to refresh your binding or wait for a poll event to occur.

In instance 3, you are once again making a new dataset, but you’re not writing it to the table. The refresh binding call you’re using just tells the Table to refresh its binding and THAT data hasn’t changed; the only change is the local dataset.

Changes:
1.

# The original code shouldn't have worked at all because you are not supplying a list of paths.
tag_path = "[defualt]ms/Test/QueryTag"
dataset = system.tag.readBlocking([tag_path])[0].value
dataset = system.dataset.addRow(dataset,[8, 'pass')
system.tag.writeBlocking([tag_path], [dataset])
# no binding refresh necessary, as the binding will get this new value naturally.

query = "INSERT INTO ref_vac_result (ref_vac_result_id, names) VALUES (?,?)"
system.db.runPrepUpdate(query, [8, 'pass'])
self.getSibling("Table").refreshBinding('props.data')  # assuming the table has props.data bound to a Named Query

# I don't recommend this route if you're using a binding because these 
# changes to the dataset are ephemeral/local and will be lost on page refresh
# or if the underlying data of the binding changes.
table_data = self.getSibling("Table").props.data
dataset = system.dataset.addRow(table_data, [8, "pass"])
self.getSibling("Table").props.data = dataset
1 Like
3

Thanks a million for your reply! I really appreciate your valuable time.

  • In instance 1, you are correct I had to write back the data to the table. I just tried the script you supplied, I do not get any errors, but nothing happens too, so my table and the dataset remain unchanged.

  • In instance 2, I tried the updated script, however, I get this error:

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO ref_vac_result (ref_vac_result_id, names) VALUES (?,?), [8, pass], sandbox_MSSQL, , false, false)

caused by SQLException: Datasource "sandbox_MSSQL" does not exist in this Gateway.
  • I also tried instance 3:

The error I am getting is:
AttributeError: 'com.inductiveautomation.perspective.gateway.script' object has no attribute 'data'

4
  1. Is your table bound to the tag in the script?
  2. I was just using your code. If that query or DB is incorrect, I can’t be held responsible.
  3. Ah, yes - this one was a typo on my part. I’ve amended the code in that snippet.
1 Like
5

Thank you for your reply!

  1. The table data bound to a query dataset tag (using direct tag binding) and I have the setting below for my tag:

    image
    image492Ă—736 42.4 KB

  2. I will check this, but I think the database should be correct as I can write named queries and get responses.

  3. I fixed this earlier and I got this error:
    Error writing to QueryTag: Bad_ReadOnly: Tag value source does not support writing.

I think I have to use
system.db.runUpdateQuery to write to query tag, otherwise writing t a query tag is not allowed

6

  1. I am reasonably sure you can’t “write” to a query tag. The tag always represents the result of the supplied query. If you are using a query tag, you MUST update the underlying DB with the new info. So the code for #1 will have no effect because the underlying dataset used in the binding won’t “accept” the write.

  2. The “sandbox_MSSQL” DB is not supplied in the code we are using, so it looks to be using the default DB configured for the project. I suggest you look there.

  3. Ah, yes, see #1. I did not actually look at the name of the tag and so didn’t realize it was a query tag.

1 Like
7

Thank you very much @cmallonee ! I appreciate your help. I will look at the UpdateQuery and see how I can add that to the code. Thanks a million!!!

1 Like
8

I got this working based on everyone’s feedback and @cmallonee (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 a perspective view with one table component, one text field, 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.
**This section can be skipped if you directly bind the table’s data to a named query.

I wrote the query of the tag as below:

SELECT * 
FROM yourdatabasename
ORDER by ref_vac_result_id DESC

  1. 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.

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

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

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

2 Likes