Updating a Mysql Using Script Function

Ignition
,
1

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,

2

Put MFGTLW00017 in quotes. It's being perceived as an undefined variable.

queryValue = "MFGTLW00017"

3 Likes
3

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

4 
queryValue = "MFGTLW00017"
value = "1"
system.db.runPrepUpdate("UPDATE Pallet_Tracking SET Status = ? WHERE Pallet_ID = ?", [value, queryValue])
4 Likes
5

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
image1176×535 35.4 KB

This is how i expect to end my script.

image
image1016×356 51.2 KB

image
image1002×322 29 KB

6

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
image1030×536 83.3 KB

7

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.)

3 Likes
8

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

9

Simple example of a "Prep" query:

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.

2 Likes