Ok, having a brain fart here…
How do I call an update query with multiple variables??
sn = some tag
cav = some number
id = some tag
system.db.runPrepUpdate(‘UPDATE QC_Sample_Measurements SET SnapID = sn WHERE cavityID = cav AND SampleID = id’)
I know when I see the answer it will be a “doh” moment…
Specify placeholder replacements (?) as a second argument to runPrepUpdate:
sn = system.tag.read("sometag").value
cav = 12345
id = system.tag.read("someothertag").value
system.db.runPrepUpdate('UPDATE QC_Sample_Measurements SET SnapID = ? WHERE cavityID = ? AND SampleID = ?', [sn, cav, id])
Yep… Baging my head on the desk now that I see. Thanks!! For some reason I was stuck on ‘%s’ method of replacing the variable instead of ‘?’.
“%s” is the default python string replacement, and does just that, pull the arguments into the string. You need to use it with the % operator, like
myValue = 5
system.db.runUpdateQuery("UPDATE Table SET Value = %s' % 5)
But this can hurt performance, as the SQL engine gets that query with a different value every time, so it will either calculate a new execution plan, or it will have a harder time figuring out what the variables are.
It can even be dangerous, as the Python string replacement doesn’t escape characters, so it’s vulnerable to (accidental or deliberate) SQL injections (https://en.wikipedia.org/wiki/SQL_injection).
The “?” replacement on the other hand is used by the JDBC driver (the Java driver Ignition uses to communicate with the SQL engine). It passes the variables to the SQL engine separately, so the SQL engine escapes it correctly (making sure no SQL injection can happen), and the SQL engine also gets a slightly easier time figuring out the difference between variables and constants of that query.
Note that the “?” replacement only works for variables, and not f.e. for table or column names. If for any reason you need to dynamically set table or column names, you should still use the “%” operator, or some other Python string tricks. But the need to do this usually means there’s some bad DB design going on.
So.. the last part of your response applies to me sadly 
I have a a UDT that grabs all the robot data (about 7 different tags per robot) from the line I wrong a scrip to put the robot cycle time to a table as it changes ( new cycle time comes in) well my table is already at 300k rows and that's one line, I have to add 3 more and originally my thought process was well lets keep the tables separate so I don't dump them all in one table but then when i wanted to create the tags for the second line I realized that I hard coded the table name in my UDT so If I made the new tags they would work but they will all go into the other table so I do I have to either have 4 different UDTs and just change the table name in the UDT or make the table name dynamic so I stick with one UDT.
Does
system.db.runPrepUpdate('INSERT INTO ? (Val1,Val2) Values(?,?)', [table, val1, val2])
work or do I have to do it a different way?
You could do something like
TableVar = event.source.parent.TableVar
val1 = event.source.parent.val1
val2 = event.source.parent.val2
uQuery = ‘INSERT INTO ‘+TableVar +’ (TableFieldName1,TableFieldName2) Values(?,?)’
ret = system.db.runPrepUpdate(uQuery, [val1, val2])
Yes, you have to do it a different way. The purpose of '?' parameters is to prevent SQL injection and improve the efficiency of moving data from the program to the DB. In order to prevent SQL Injection, the structure of the "prepared" query must be fixed, and only the data supplied by '?' substitution. Table names, column names, and expressions are all "structure". In your example, imagine if the table name were a string supplied by a user, and the user supplied this as the table name:
users (name, pwd, privilege) Values ('eviluser', 'evilpwd', 'administrator');
Oops!
Now, you can still shoot yourself in the foot with Maynard's idea, but it highlights the problem with creating SQL statements from scratch as strings. You should only ever do so if the strings for any structure part are built into your code (a dropdown for the table name, perhaps).
Agreed Phil, but this is a closed system and he is asking about hard coding a function to write from a tag event script.
The proper design for his situation is to have a column with a DeviceID and then just pass that as a parameter for the insert. This way he has a single table instead of a table for each device.
If the user had any way of inputting data I’d be 1000% with you @pturmel. I’m putting the query in myself and unless the evil side of me shows up and decides to do a silly injection I think we are safe lol.
All the data is coming directly from PLC and either a BOOL or Float as far as what goes in and the line name and robot name are set in stone by me. Any other day I’d say yeah lets do it the efficient way but today I really just want to get it working 
Thanks for your suggestion @MMaynard I will try it
This is until you (or your colleague) finds out it's easier to make these dynamic for future installations, and creates some user input field for it. Then someone creates a robot named "Mr.K001's robot", and all hell breaks loose.
Assuming that things are "set in stone" are dangerous assumptions to make.
Well I decided to just keep the table name hard coded and call it the day… you guys happy?!!
just kidding, I know this is the right way of doing things. Thanks for all the suggestions 