# Update Database Table and Insert new Values

**URL:** <https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781>\
**Category:** Ignition\
**Created:** [June 6, 2016, 2:47pm UTC](https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781 "2016-06-06T14:47:33Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![csmeaton](https://avatars.discourse-cdn.com/v4/letter/c/dec6dc/32.png) [@csmeaton](https://forum.inductiveautomation.com/u/csmeaton)\
**Post date:** [June 6, 2016, 2:47pm UTC](https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781/1 "2016-06-06T14:47:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [June 6, 2016, 3:34pm UTC](https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781/2 "2016-06-06T15:34:17Z")

</div>

Hello,

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

Best,

---

<div class="post-metadata">

**Author:** ![csmeaton](https://avatars.discourse-cdn.com/v4/letter/c/dec6dc/32.png) [@csmeaton](https://forum.inductiveautomation.com/u/csmeaton)\
**Post date:** [June 6, 2016, 3:47pm UTC](https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781/3 "2016-06-06T15:47:27Z")

</div>

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

Thanks,

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [June 6, 2016, 3:53pm UTC](https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781/4 "2016-06-06T15:53:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![csmeaton](https://avatars.discourse-cdn.com/v4/letter/c/dec6dc/32.png) [@csmeaton](https://forum.inductiveautomation.com/u/csmeaton)\
**Post date:** [June 7, 2016, 1:05pm UTC](https://forum.inductiveautomation.com/t/update-database-table-and-insert-new-values/11781/5 "2016-06-07T13:05:09Z")

</div>

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,