runQuery Deprecation and Query Stacks

Ignition
1

Hi all.

With 8.3 system.db.runQuery has been deprecated, and the suggested replacement is execQuery (which utilizes named queries instead).

I often use runQuery to execute thousands of queries in one pass, such as when importing values from a csv to merge with a database:

query = 'UPDATE ThisTable SET ThisValue = {} WHERE ID = {}'
queries = []
thisValue = [i for i in range(100000)]
for i in range(100000):
   queries.append(query.format(thisValue[i], i))
outQuery = '\n'.join(queries)
system.db.runQuery(outQuery, 'ThisDB')

A named query cannot do this and execQuery would take forever executing these one by one.

Is there a proposed new solution for efficiently handling very large DB tasks like in the example above?

2

runPrepQuery/runPrepUpdate are the non-deprecated alternatives that allow you to run arbitrary queries defined in scripting, rather than ahead of time.

I'll preempt Phil here and note that you're doing something that's non-standard/non-portable that you've been "getting away with" thus far, and any update to your JDBC driver could break.

runQuery is intended to, as the name implies, run queries, not arbitrary statements. Batching together many independent updates like this is in the realm of undefined behavior.

3 Likes
3

Look into your DB's flavor of MERGE commands.

Additionally, your previous method is extremely vulnerable to SQL injection, you should be using system.db.runPrepUpdate (Which still exists in 8.3) and constructing your query like UPDATE ThisTable SET ThisValue = ? WHERE ID = ?, and passing your values as a list.

4 Likes
4

Hi Paul.

Thank you for the answer. I thought of that (runPrepUpdate) while I was typing this out, but I figured an official answer would leave me more satisfied that this is “the accepted way” and also that it would be a good question/answer to have on the forum for posterity.

I appreciate it!