# Updating a Mysql Using Script Function **URL:** \ **Category:** Ignition\ **Tags:** ignition81, scripting\ **Created:** [December 8, 2022, 11:31pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410 "2022-12-08T23:31:08Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Jair\_Manquero](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jair\_Manquero](https://forum.inductiveautomation.com/u/Jair_Manquero)\ **Post date:** [December 8, 2022, 11:31pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/1 "2022-12-08T23:31:08Z")
Hi guys , Im trying to build a push button that when its pressed it updates a query that i have on my server. I have the following script below. queryValue = MFGTLW00017 system.db.runUpdateQuery("UPDATE Pallet\_Tracking SET Status = 1 WHERE Pallet\_ID =‘%s’ " % queryValue ) every time and execute this button i get the following error. Traceback (most recent call last): File "event:actionPerformed", line 2, in NameError: name 'MFGTLW00017' is not defined Ignition v8.1.16 (b2022040511) Java: Azul Systems, Inc. 11.0.14.1 any help would be appreciate it. Thank you, ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [December 9, 2022, 12:38am UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/2 "2022-12-09T00:38:36Z")
> [@Jair\_Manquero](#): > > NameError: name 'MFGTLW00017' is not defined Put MFGTLW00017 in quotes. It's being perceived as an undefined variable. `queryValue = "MFGTLW00017"` ---
**Author:** ![Jair\_Manquero](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jair\_Manquero](https://forum.inductiveautomation.com/u/Jair_Manquero)\ **Post date:** [December 9, 2022, 6:49pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/3 "2022-12-09T18:49:32Z")
Hi justin, Thank for that. That is something i tried before, but that still didnt work on my end. If define my variable this way queryValue = "MFGTLW00017" i get the following. error : Traceback (most recent call last): File "event:actionPerformed", line 3, in java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate( UPDATE Pallet\_Tracking SET Status = 1 WHERE Pallet\_ID =‘MFGTLW00017’ , [null], , , false, false) ``` caused by Exception: Error executing system.db.runPrepUpdate( UPDATE Pallet_Tracking SET Status = 1 WHERE Pallet_ID =‘MFGTLW00017’ , [null], , , false, false) caused by GatewayException: SQL error for " UPDATE Pallet_Tracking SET Status = 1 WHERE Pallet_ID =‘MFGTLW00017’ ": Unknown column '‘MFGTLW00017’' in 'where clause' caused by SQLSyntaxErrorException: Unknown column '‘MFGTLW00017’' in 'where clause' ``` Ignition v8.1.16 (b2022040511) Java: Azul Systems, Inc. 11.0.14.1 this is my current SQL TABLE, ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d9dd27219e7030eaab2871a3687d0cb7fee348c.png) ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [December 9, 2022, 7:00pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/4 "2022-12-09T19:00:43Z")
```auto queryValue = "MFGTLW00017" value = "1" system.db.runPrepUpdate("UPDATE Pallet_Tracking SET Status = ? WHERE Pallet_ID = ?", [value, queryValue]) ``` ---
**Author:** ![Jair\_Manquero](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jair\_Manquero](https://forum.inductiveautomation.com/u/Jair_Manquero)\ **Post date:** [December 10, 2022, 2:35am UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/5 "2022-12-10T02:35:55Z")
Thank you so much Craig. I have run into other issue and maybe hopefully with your expertise you can help me out. I'm able to update into the database now. In order for me to update the correct "item" i must find the item that i need. This is what i have so far. I have a queryvalue value that sets my 0 and 1 (rightnow is set to 0). after that i have variable select\_item. This number is set base on query tag that looks into the pallet\_id when its scanned. Everytime i start the program or return to the program it give me a fault or red indicating tag because there is no scan there for the query tag cant execute. I'm trying to build a basic select query such as pallet = "MFGTLW00017" query = system.db.runQuery("Select \* From Pallet\_Tracking where Pallet\_ID = %s " % (pallet),'Cypress\_SCADA') value = query system.tag.writeBlocking(['[default]SCADA\_TAGS/Pallet\_Tracking/remove\_pallet'], [value]) but i keep getting this error still ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/9/79be76295ca84bd61347bb5b1c8d4ce1130c833b.png) This is how i expect to end my script. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/b/3bcf552d518558a65a9abeaeb204290606eafa14.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/f/7f0442afa172473f82b8cb84fbed4092a68c3ef1.png) ---
**Author:** ![Jair\_Manquero](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jair\_Manquero](https://forum.inductiveautomation.com/u/Jair_Manquero)\ **Post date:** [December 10, 2022, 6:29am UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/6 "2022-12-10T06:29:04Z")
dont worry about this craig. i manage to figure it out. Thanks for your help though. FYI just incase some one ever comes across this post. pallet\_id = system.tag.readBlocking("[default]SCADA\_TAGS/Pallet\_Tracking/pallet\_id")[0].value query = "SELECT \* FROM Cypress\_SCADA.Pallet\_Tracking WHERE Pallet\_ID = '%s' " % pallet\_id query\_results = system.db.runPrepQuery(query) pyData = system.dataset.toPyDataSet(query\_results) item\_id = pyData[0][0] system.tag.writeBlocking(['[default]SCADA\_TAGS/Pallet\_Tracking/New Tag'], [item\_id]) #transaction variables base on users input from Main Window. introducing\_pallet = system.tag.readBlocking("[default]SCADA\_TAGS/Pallet\_Tracking/introduce\_pallet")[0].value removing\_pallet = system.tag.readBlocking("[default]SCADA\_TAGS/Pallet\_Tracking/remove\_pallet")[0].value # Update Query for Mysql if system.gui.confirm(u'Are you sure?', 'Confirm'): if removing\_pallet == 1: queryValue = '0' #select\_item = system.tag.readBlocking("[default]SCADA\_TAGS/Pallet\_Tracking/item\_id")[0].value select\_item = item\_id system.db.runUpdateQuery("UPDATE Pallet\_Tracking SET Status = %s WHERE Item = %s" % (queryValue,select\_item), 'Cypress\_SCADA') if introducing\_pallet == 1: queryValue = '1' #select\_item = system.tag.readBlocking("[default]SCADA\_TAGS/Pallet\_Tracking/item\_id")[0].value select\_item = item\_id system.db.runUpdateQuery("UPDATE Pallet\_Tracking SET Status = %s WHERE Item = %s" % (queryValue,select\_item), 'Cypress\_SCADA') ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/3/630fd52d5553dc9e9d429f8f3ebef8db10f34983.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 10, 2022, 3:29pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/7 "2022-12-10T15:29:14Z")
That's terribly dangerous, and not fit to use as an example for future visitors to this post. The code needs to use proper parameter substitution with "Prep" queries to eliminate any chance of a SQL injection attack. (Don't use python formatting to insert values into queries.) ---
**Author:** ![Jair\_Manquero](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jair\_Manquero](https://forum.inductiveautomation.com/u/Jair_Manquero)\ **Post date:** [December 12, 2022, 6:53pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/8 "2022-12-12T18:53:24Z")
Hi pturmel, thanks for your feedback. as I'm need to ignition I'm trying to learn the right way. In this case how you archive the above? the right way... thank you ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 12, 2022, 7:32pm UTC](https://forum.inductiveautomation.com/t/updating-a-mysql-using-script-function/67410/9 "2022-12-12T19:32:52Z")
Simple example of a "Prep" query: ```auto query = "SELECT * FROM Cypress_SCADA.Pallet_Tracking WHERE Pallet_ID = ?" query_results = system.db.runPrepQuery(query, [pallet_id]) ``` Notice that the `'%s'` in the bare query is gone. No string formatting is used. String formatting is dangerous--due to the possibility of SQL injection attacks. It is also less performant, as string conversions take more time than binary transfers. With the `?` in the query, JDBC takes the value from the parameter list and transfers it the DB in binary format, avoiding any chance for special characters or punctuation to interfere (change the meaning of the SQL). Use this approach everywhere in your script. Use the functions in `system.db.*` that have "Prep" in their names.