I like the way it is possible to insert multiple records into a table using system.db.runUpdateQuery - you just string all of the sets of values together in one string. However, I’ve been bit by SQL injection so I really like using system.db.runPrepUpdate. I haven’t been able to figure out how to insert multiple records at one go though. Here’s the scripting I’ve tried:
qryflds = "INSERT INTO tblNumbers (field, tenx) VALUES " values = "" for x in range(6): values += "(%s,%s)," % (x, 10*x) values = values[:-1] query = qryflds + values print query system.db.runUpdateQuery(query,'BoilerMSSQL') argslst =  args =  for x in range(6,12): args = [x,10*x] argslst.append(args) query = qryflds + "(?,?) " print query, argslst system.db.runPrepUpdate(query + "(?,?) ", argslst, 'BoilerMSSQL')
The first update query works fine - it inserts 6 records into the table. The second query was my best guess at how to do this - system.db.runPrepUpdate requires a list of arguments so I made a list of lists.
Somewhere in the mess of red text I get this,
"Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO tblNumbers (field, tenx) VALUES (?,?) (?,?) “: The conversion from UNKNOWN to UNKNOWN is unsupported.”
Can this be done or do I need to do it one INSERT at a time?