Hi Again
Now that I got solved my Recipe question no.1 
, I can move on to question no.2.
How do I Edit database data in Ignition, I have read the relevant Ignition help pages, watched a lot of Ignition videos and surfed a lot on the internet without finding answers to my questions, so now I have to try here
See screenshot below.
I created a table and retrieved SQL data using Named Queries.
I have created a PopUp window, which opens with a double click on the data set I want to edit, and from here I do not know what to do.
How do I get the right data into the Popup window?
What is the correct method when saving edited data? script or?
A bit more reading i the help pages for table componet.
https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table
Component Events
onRowDoubleClick
event.value
Object Path: event.value
Type: PlainObject
Description: The rows value as a JSON object.
I think thats want I need to get data from the table to my PopUp window? or?
But how to do that, I got no idea at the moment, can somebody lead me the right way?
The way you do this will vary by what database youâre using. We typically use MSSQL so I call stored procedures when Iâm manipulating data.
Youâre looking at the onRowDoubleClick event. What you want to do is add a script to that event that looks up all the data you want to load on your form and writes it to the form. When you create a script for an onRowDoubleClick event it will put comments in the script to tell you what values you have at your disposal. Youâre probably going to want to parse through the value dictionary and populate your fields with that data.
The user then can change the values in the fields and press save. The save button would call another named query that updates the data for the record that has the ID you are editing. You donât want to display the ID in an editable field because this could cause the user to update the wrong recipe. On a side note, MAKE SURE YOUR UPDATE STATEMENT HAS A WHERE CLAUSE. If you donât, it will update all of your recipes to the values entered on the form. I donât know your level of SQL experience but I donât want to see you blow away all your recipes. Itâs a good idea to back up all your recipes before testing an update statement.
You must return a resultset from any query that is used in a named query so I just put âSelect 0â after my query. The point of the query is to update the record; not to return stuff back to Ignition. You can return error codes in the select statement if it makes sense in your application. If youâre using MSSQL Server you will want to âSET NOCOUNT ONâ before the query in your stored procedure to keep it from returning a resultset that tells you how many records are in the normal resultset which confuses most people.
The last thing you want to do in your save button is to refresh the binding on your table control in the form that launched the popup. For this you want to write a message handler on the parent form that does a refresh binding on your table. Then your save button simply sends a message in the session scope to the parent form to refresh the binding.
That might be a lot to take in but hopefully it points you in the direction of the right things to learn how to do.
2 Likes
Many thanks for your long and detailed answer ;-), it is appreciated
Thanks for pointing me the right direction
I have been using MSSQL for the last 15-20 years so I have reasonable control over this part. However, in this project I use MySQL (MariaDB) but I do not think it will present the big challenges.
Itâs more the data flow in Ignition I have trouble understanding.
When transferring data from script to PopUp window, should this be done with Memory tags or page parameter (PARAMS) or in a completely different way?
When âevent.valueâ is called in the script how is that done?
x = event.value.PwrID
x = tablename.event.value.PwrID
x =?
Status / Monitoring is really bad in Ignition, so I often find it really hard to âfindâ the right syntax.
You should be setting the query type in the named query to Update, and your update should return the number of rows changed - this shouldn't be hidden, as if you're expecting the update to affect 1 row and it affects 10k, you have a major problem that would otherwise be unknown (until of course the site goes down)
This should be done as View params. Memory tags should only be used for things that need to live across different clients (and projects for that matter).
This confuses me. Ignition has the best status and monitoring that i've seen in all of the ~10 SCADA applications that i've used, and it's not by a whisker, it's by circumferences-of-worlds' difference.
Unless of course you're talking about the functionality of a script debugger, in which case, Ignition doesn't have that... 
From the doc string: "value (dict): The row's value as a JSON object."
So you could use either:
event.value.city
event.value['city']
I think because the object class is actually a "com.inductiveautomation.ignition.common.script.adapters.PyJsonObjectAdapter" which implements a map (? @PGriffith) you can use either dot notation or traditional dict['key'] notation
Why not use,
Table.props.selection.data
I imagine using the event object provided by the event handler would guarantee that you get the right row, whereas I donât know if you can guarantee that the selection.data would be updated before the double click event handler is called. I mean, it probably would since the first click should change the selection, but in general, this would be my reasoning to always use whatâs provided by the event handler rather than the same properties from outside
Ok ................. but I'm so stupid I can not see how the tag syntax should be in the script, is there are somewhere in the help file I can see that?
The value attribute of the event object is a dictionary, so I think the script you are looking for is:
SQL_Edit_pwr.view.params.PwrID = event.value['PwrID']
Tried that
SQL_Edit_pwr.view.params.PwrID = event.value[âPwrIDâ]
and also
SQL_Edit_pwr.view.params.PwrID = 11
None of them give a value in my PopUp field 
The Log in the Gataway, tells me that âSQL_Edit_pwrâ is not a legal global name, so âSQL_Edit_pwr.view.params.PwrIDâ is not the correct syntax for the PopUp view parameters.
So now I only need to figure out the correct syntax 
SQL_Edit_pwr isnât defined anywhere in your script. Iâm not sure what this is supposed to be.
So you want to bring up a popup to edit the row data?
Then donât you want to open the popup on the double click action?
Ah, I see what youâre doing, you have two actions, one to open the popup and one youâre trying to use to pass the values - you have this part wrong. You need to open the popup and pass the params at the same time, via the open popup command. Delete your script action and use the Popup action to pass your params.
Fyi, you can only pass params to a popup via the open popup action or script function (system.perspective.openPopup) at the time you open the popup. You can change custom props in a popup via a few different methods: message handlers, using custom session props, or using tags. But you cannot reference a popup via script to make direct changes to its props. If you could, you wouldnât simply be able to use the name of the popup as the reference, you would need to call a function to get the reference to it (as you can in Vision)
You mean like this
system.perspective.openPopup("myPopupId",
'PopUp/SQL_Edit_pwr',
params = {
'PwrID':event.value.PwrID,
'Fct':event.value.Fct},
title = 'DD',
showCloseIcon = True,
resizable = True)
AND it works, Thank you 
Next step, save to DB after edit
80-90% off the way with saving Edit data.
First I made a Update Query with Parameters, pretty easy to do.
Second a script on the Save button, needed to read help and google around for 1 hour to find out how to do it !!!
Question, script line 5 & 6
How do I get information, that the Query performed correctly?
I only want to close the PopUp window if everything was ok
Question, script line 8 & 9
Want to update the Table view with the new data.
This don´t work, can somebody help?
runPrepUpdate will return the number of rows affected, so you should check that this is 1.
You could also use a try/except block to check for any other errors. Note there are two main types of exceptions to handle, python and Java (i may not have the lingo quite right here), but to capture any errors you would use:
import java.lang.Exception as JLException
import traceback # this will get you access to the context of the error as well as the error itself
try:
stuff...
except Exception, e:
tb = traceback.format_exc() # get the context and the error
do something
except JLException, e:
tb = traceback.format_exc() # get the context and the error
do something
Assuming the named query is correct, this would be working and would be returning a dataset with the results of the query to use within the script. But this probably isn't what you want to do. This is a similar issue in fundamental understanding as when you were trying to write to the popup's params directly in script without having a reference to the popup itself. Simply running a named query is not going to update anywhere else that is using the same named query. You need to refresh the specific component and the property that is bound to the query itself. For that, there is the refreshBinding method available on all components:
For example:
table = self.getSibling("Table") # get a reference to the table (this will change depending on your project) Use the property picker to select your table
table.refreshBinding("props.data")
LOL I didnât notice the update thing. I call spâs for mine so it only matters because of the silly select at the end of it. Thanks for pointing it out.
If I read the help file correctly, RefreshingBindings can only be used inside a "page hierarchy" with Siblings and childs, and not across the hole project (session?)
So I ended up using Message Handler instead. (your answer lead me the right way)
Code on table
Code on Save button
With "system.db.runPrepUpdate" I need to move SQL query from my Named Querie to the place where I call system.db.runPrepUpdate (if I read help correctly). But with system.db.runPrepUpdate it is also possible to INSERT (AddNew) data and DELETE data i the SQL table, nice to know.
I think "try/except block" is pure Python or Java, and at the moment I know far too little about these to get me started on this
So for now i will keep what i have and finish my project, a later upgrade could easily be to implement the system.db.runPrepUpdate function