How to Update/Modify an existing row in a db Table, using a component's actionPerformed Script?

Ignition
, ,
1

https://docs.inductiveautomation.com/display/DOC79/system.db.runPrepUpdate

I use the following script within a components actionPerformed script area and it works great for Inserting/adding a new row into an existing db table.

system.db.runPrepUpdate(INSERT INTO table(t_stamp, name.....)

Now I would like to use a actionperformed script to Update an existing row within an existing table.
Where do I start? Help.

2 
#Variables Defined
queryValue = '0'
keyValue1 = event.source.parent.ndx

#Update Query
system.db.runUpdateQuery("UPDATE flavor SET draft = %s WHERE ndx = '%f'" % (queryValue, keyValue1), 'quality')
1 Like
3

You really should use prepared statements to pass parameters. Like so:

system.db.runPrepUpdate("UPDATE flavor SET draft = ? WHERE ndx = ?", [queryValue, keyValue1], 'quality')

Even better would be to convert it to a named query.

1 Like
4

Iā€™m learning this as I go, What would a named query look like?
Your solution is a prepared statement, what terms would you use to describe my solution?

5

Start here. Then use system.db.runNamedQuery().

Well, I can't use the appropriate words on a family-friendly website. /-:
Generically, it is a query produced from manual string substitution. Anything in your substitutions that could be interpreted by your DB as a delimiter will break it. And if those substitutions are supplied by a user, it is a security hazard known as SQL injection.

1 Like
6

I appreciate your help, Thank you.