Update Database Table and Insert new Values

Ignition
1

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.

[code]#Define Values
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
system.db.runPrepUpdate(update_query, update_args)
system.db.runPrepUpdate(insert_query, insert_args)

#refresh the table data
system.db.refresh(event.source.parent.getComponent(‘Table’), “data”)[/code]

Any suggestions/advice would be appreciated, below is the current python code that I am working with.

2

Hello,

What happens when you run your Python script? Do you get any error messages?

Best,

3

No error messages come up, but the button stays pressed in (orange/tan border stays on).

Thanks,

4

Okay, does the entire GUI freeze? Where is the script, in an actionPerformed event script?

Can you add a “print ‘test’” statement at the end of your script and when you run the script check the output console to see that “test” got printed? This is to ensure that your script ran and there were no errors.

5

I am able to click other buttons after the insert button fails. I am using the "KeyPressed" event to trigger the script. I opened the script console and saw an error in the line where I refresh the table data. Below is the script I used with some print lines and below that is the output from the script.

#Define Values newRowModel = "ABC" newRowValue = 2 print 'test1' #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] print'test2' #Define the Insert Query insert_query = "INSERT INTO test_table (model_num, step_id) VALUES (?, ?)" insert_args = [newRowModel,newRowValue] print'test3' #Run the Querys system.db.runPrepUpdate(update_query, update_args) system.db.runPrepUpdate(insert_query, insert_args) print'test4' #refresh the table data system.db.refresh(event.source.parent.getComponent('Table'), "data")

test1
test2
test3
2
1
test4
Traceback (most recent call last);
File "", line 18, in
NameError: name 'event' is not defined

Just realized my own error... changed the trigger to be "MouseClicked" and everything works great.

Thanks for your assistance,