%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).
?” 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.