Is it possible to insert multiple records at once with system.db.runPrepUpdate?

Ignition
1

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?

2

You need commas between each value set.

1 Like
3

Hmmmm. So I had a comma between between my value sets in the system.db.runUpdateQuery and it worked.

The

print query, argslst

In my first version showed commas, but of course that was just what Python put in there to interface with the human.

“INSERT INTO tblNumbers (field, tenx) VALUES (?,?) [[6, 60], [7, 70], [8, 80], [9, 90], [10, 100], [11, 110]]”

My attempt to include commas in my second attempt resulted in this code:

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)
	argslst.append(",")
argslst = argslst[:-1]
query = qryflds + "(?,?) "
print query, argslst
system.db.runPrepUpdate(query + "(?,?) ", argslst, 'BoilerMSSQL')

and this output

“INSERT INTO tblNumbers (field, tenx) VALUES (?,?) [[6, 60], ‘,’, [7, 70], ‘,’, [8, 80], ‘,’, [9, 90], ‘,’, [10, 100], ‘,’, [11, 110]]”

from the

print query, argslst

And the insert failed again.

I’m not sure how else to get commas between the value sets when system.db.runPrepUpdate needs a Python list of arguments.

4

When doing things like this, I find that the join() Python method is extremely helpful. You call it directly on a delimeter string, supplying a sequence of arguments:

argslst = [[1, 2], [3, 4], [5, 6], [7, 8]]
valueStmt = ', '.join(["(?, ?)"] * len(argslst))

returns:
(?, ?), (?, ?), (?, ?), (?, ?); each element of the sequence provided to join is separated by the input string. It’s not the most readable, at first glance, but the output should be exactly what you need:
system.db.runPrepUpdate(query % valueStmt, argslst, 'BoilerMSSQL')

5 Likes
5

Thanks Paul.

My understanding of join() is that it just produces a string.

I still haven’t been able to get it to work. I corrected a couple of typos in your suggestion, but still no luck.

When I entered this code:

query = "INSERT INTO tblNumbers (field, tenx) VALUES " argslst = [[1, 2], [3, 4], [5, 6], [7, 8]] valueStmt = ', '.join(["(?, ?")] * len(argslst)) print query, valueStmt, argslst system.db.runPrepUpdate(query % valueStmt, argslst, 'BoilerMSSQL')

I got

  File "<buffer>", line 3
    valueStmt = ', '.join(["(?, ?")] * len(argslst))
                                 ^
SyntaxError: no viable alternative at input ')'

I corrected that with this:

query = "INSERT INTO tblNumbers (field, tenx) VALUES " argslst = [[1, 2], [3, 4], [5, 6], [7, 8]] valueStmt = ', '.join(["(?, ?)"] * len(argslst)) print query, valueStmt, argslst system.db.runPrepUpdate(query % valueStmt, argslst, 'BoilerMSSQL')

When I ran that I got:

Traceback (most recent call last):
  File "<buffer>", line 5, in <module>
TypeError: not all arguments converted during string formatting

I corrected that with this:

query = “INSERT INTO tblNumbers (field, tenx) VALUES "
argslst = [[1, 2], [3, 4], [5, 6], [7, 8]]
valueStmt = ', '.join([”(?, ?)"] * len(argslst))
print query, valueStmt, argslst
system.db.runPrepUpdate(query + valueStmt, argslst, ‘BoilerMSSQL’)

The print statement gave me

INSERT INTO tblNumbers (field, tenx) VALUES  (?, ?), (?, ?), (?, ?), (?, ?) [[1, 2], [3, 4], [5, 6], [7, 8]]

And I got an explosion of error text, the salient part of which was

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.

At this point at least the syntax errors are gone and I’m getting SQL stuff.

And…

I just finally got it to work.

Because all of the ? end up as text in the query, I shouldn’t be supplying a list of lists, it should just be a list.

The crude result that I finally got to work was this.

query = "INSERT INTO tblNumbers (field, tenx) VALUES " argslst = [1, 2, 3, 4, 5, 6, 7, 8] valueStmt = ', '.join(["(?, ?)"] * (len(argslst)/2)) print query, valueStmt, argslst system.db.runPrepUpdate(query + valueStmt, argslst, 'BoilerMSSQL')

The result of that print statement was:

INSERT INTO tblNumbers (field, tenx) VALUES  (?, ?), (?, ?), (?, ?), (?, ?) [1, 2, 3, 4, 5, 6, 7, 8]

I’ll automate this a bit and clean it up. Thank you so much for your help.
8 Likes