Transaction Groups - Recipes: Delete a Record

Ignition
1

Followed the help at...
https://docs.inductiveautomation.com/display/DOC81/Recipe+Group

Watched several Inductive university videos and another presentation from a webinar on youtube.

I am happy with my transaction groups thus far for getting and putting recipes into MySQL.
However I'd like the ability to delete a record from DB via from a vision client. But the scripting escapes me. And it does not look like another transaction group will do it.

Googling is not good with signal to noise.
So I asked ChatGPT....

Using a button control within an Igntion vision client, how would one execute a MySQL query to delete a record from a database with teh button is clicked?

Who said...

To execute a MySQL query to delete a record from a database when a button is clicked in Ignition Vision, you can follow these steps:

  1. Create a new button component in the Ignition Vision window by dragging and dropping a Button component from the component palette onto the design canvas.
  2. Double-click the button to open the Button Property Editor.
  3. Under the Actions section, click the "Configure Events" button next to the "MouseClicked" event.
  4. In the Event Script Editor, write the following code:
# Import the system.db module
from system.db import runPrepUpdate

# Define the SQL query to delete the record from the database
sql = "DELETE FROM my_table WHERE id = ?"

# Define the values to substitute into the query (in this case, the ID of the record to delete)
params = [my_record_id]

# Execute the query using the runPrepUpdate function
runPrepUpdate(sql, params, "my_database")

Note: You will need to replace my_table, id, my_record_id, and my_database with the appropriate values for your specific use case.

  1. Save the script and close the Event Script Editor.
  2. Save the changes to the Button component and close the Button Property Editor.

Now, when the button is clicked in the Ignition Vision client, it will execute the specified MySQL query to delete the record from the database.

And so I did all that, (well mostly) and it worked.

Is that the proper thing to do?
Seems like it could have been simpler - but I could not find a 'simple' means.

2

I would turn the prep update into a named query. Once you create the named query, you can use it like so.

system.db.runNamedQuery('Recipes/Delete',{'someParamKey':someValue})

What method do you plan on selecting a recipe to delete? IE select a row in a power table that is populated with all recipes, or a dropdown...

2 Likes
3

My first impression is that it is too simple - authorization, audit trail, etc. would be required in a production system.

It also needs to be tied to which record to delete (where does my_record_id come from - the currently loaded recipe, the row id for the row that the Delete button was rendered on, etc.).

If the record that is deleted is also the record that is loaded in the PLC (OPC tags) then it will need to be deleted from the PLC as well as the database. If the PLC is currently processing the recipe then there would need to be interlocks to prevent the deletion until the batch was completed.

And so on.

1 Like
4

Oy! Transaction groups are not for user interfaces! A transaction group would be appropriate for a PLC triggering a request for the recipe that goes with a part number that just got loaded automatically into the machine.

Use named queries for all database operations from your user interfaces.

1 Like
5

Presently it's a drop down.

Yes.
But I am new to this.
The record to delete comes from a drop down.
The OPC tags that the transaction groups work with are memory tags. There are separate control for sending / receive to/from the actual PLC.

6

OK fine - The gatekeeper is here to yell at the noob, like always.

Sometimes people are coming from very different backgrounds / experience level than you kind sir.

And to this guy, the resources he found online certainly seems like they were pitching transaction groups as the end-all be-all for recipe handling in Ignition. Including on screen controls (pearls being clutched). But hey, I don't have the years of experience with this platform to discern the subtleties.

I've not gotten formal training.

I'm not gonna get formal training.

I've gotta get it working eventually.

I have always wanted to dip my feet into the Ignition pool. Ever since it first came out and I saw it in the trade magazines. Now that I'm doing it - always wrong - . I find the only negative experience in all of it is this community bashing I get when I ask questions.

Still - I will keep asking. Because it's a resource.
Still - I am rather happy that in the span of a day I have installed MySQL, and the SQL Bridge Module, and gotten some recipe testing done.

7

We are all new at something, and most of us have backgrounds/experience/perspectives that might be relevant to others.
The examples and videos at IU are great introductions but often lack context.
You will get it. If you are an a good team with experienced people and oversight and controls then it will be good.
If you are alone and you don't know what you don't know and a valve is left open because the recipe didn't have an interlock on the delete function or the performance lags because a transaction group was used inappropriately (even though the documentation didn't mention it) ... well some of us would like to help prevent that if we can. We don't know the composition of your team or where you are in your project.
Sounds to me like you had a productive day.

1 Like
8

With a dropdown as your selection method, your script should look something like this

recipe = event.source.parent.getComponent('Recipes').selectedStringValue
system.db.runNamedQuery('Recipes/Delete',{'recipeId':recipe})

I recommend you use a two-step acknowledge method for using this script, as in, a button that makes confirm/cancel buttons visible where the confirm button actually runs the script. This would prevent accidental deletions.

9

I'm sorry you took it that way.

It isn't without reason, which you will discover if two clients open that recipe editor at the same time, or you run it in the designer while someone is using it in a client.

10

When you've been here a while you will realise that @pturmel gives phenominal help on the forum. He is not employed by Inductive Automation and he's not a "gatekeeper" on the site. Phil uses, "Oy!", to get one's attention. In that post he explained the problem and the best practice so a word of thanks might have been a better response.

You can train yourself at no cost - other than time - over at https://inductiveuniversity.com/.

11

So lying awake this morning Saturday morning quarterbacking this. This is my, perhaps naïve, perspective:

I essentially have get/put/delete of a test recipe working with (2) transaction groups (get/put) and a script (delete). I had to know zero server query language syntax to pull off the get/put.

For the delete I punted and used ChatGPT. Also, had to know essentially nothing of SQL syntax.

IF (big IF) I understand the named query suggestions, and I could very well decide that it is something I want to educate my self more on, later. But right now, as I understand it, I'd construct queries along the lines of INSERT to <db.table> [values], where values is filled in with a bunch of tag values in script lines above. Give these things names, and then execute them via the names given from GUI controls.

I could have that wrong, not gonna dive into it anytime soon got weekend chores and next week will be onsite for completely different project.

So, I am wondering how that is better than using transaction groups, from strictly an efficiency argument? I mean I just dragged a bunch of tags into the groups, a few clicks later the table was created for me, and a few more click later to set up triggers and I can see what I want to see in the table via MySQL workbench.

To @pturmel 's point about multiple clients, as I explained in other threads this is something I can deal with, the task is to replace a 23 year old HMI - with no notion of a network. I can manage expectations if folks start talking about networking this machine and firing up additional clients. Perhaps myopic.

To @Transistor 's points. No doubt the advise is phenomenal. Phrasing....

Also, I did 100% of the Vision course at inductive University about 2 years ago with another employer. But never got any real hands on time with Ignition projects at that time as I was the PLC guy.

Love the product, love the resources. But still essentially clueless on sql queries and syntax.

1 Like
12

You can self-train on this using just your preferred DB's documentation. The basics for selecting, inserting, updating, and deleting are not rocket science. You start by looking up SELECT, INSERT, UPDATE and DELETE in the MySQL manual (or better, the MariaDB manual).

Not having a handle on at least this much will seriously impede your use of Ignition. (Or any modern SCADA, in my not-so-humble opinion.)

13

I am very much a noob at SQL (and Ignition). I need to check if a recipe exists before writing and offer the option to rename or overwrite. As of now, I can write multiple records with the same name, not acceptable.

14

Most SQL databases offer some sort of Unique Constraint that can be added to the column in the database. If a UC exists and an INSERT or UPDATE tries to add a duplicate then the transaction will fail. You would need to detect the failure and provide feedback to the user ("Pick a different name" or "Overwrite existing record?") but the resulting code is generally cleaner and more robust.

2 Likes