I have a step sequence in a Database Table that I would like to edit through a table in the Ignition Designer. I have the table setup to look at the data from the database, but I am trying to add a button that will add a new row in a specific section of the table. In MySQL i had to break it down into two queries that are listed below.
The database table looks like this…
| ID | model_num | step_id|
| 1 | ABC | 1 |
| 2 | ABC | 2 |
| 3 | ABC | 3 |
| 4 | XYZ | 1 |
| 5 | XYZ | 2 |
| 6 | XYZ | 3 |
And the MySQL query that works in MySQL workbench is as follows…where the value 2 and “ABC” are variables that the user will select.
UPDATE test_table SET step_id = step_id + 1 WHERE step_id >= 2 AND model_num = "ABC" ORDER BY step_id DESC;
INSERT INTO test_table (model_num, step_id) VALUES ("ABC", 2);
I have tried to run two separate “system.db.runPrepUpdate” calls but have had no success.
newRowModel = “ABC”
newRowValue = 2
#Define the Update Query
update_query = “UPDATE test_table SET step_id = step_id + 1 WHERE step_id >= ? AND model_num = ? ORDER BY step_id DESC”
update_args = [newRowValue, newRowModel]
#Define the Insert Query
insert_query = “INSERT INTO test_table (model_num, step_id) VALUES (?, ?)”
insert_args = [newRowModel,newRowValue]
#Run the Querys
#refresh the table data
Any suggestions/advice would be appreciated, below is the current python code that I am working with.