# runPrepUpdate vs runUpdateQuery

**URL:** <https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197>\
**Category:** Ignition\
**Tags:** database, scripting\
**Created:** [June 19, 2023, 9:04am UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197 "2023-06-19T09:04:00Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joseph\_Loh](https://avatars.discourse-cdn.com/v4/letter/j/ebca7d/32.png) [@Joseph\_Loh](https://forum.inductiveautomation.com/u/Joseph_Loh)\
**Post date:** [June 19, 2023, 9:04am UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/1 "2023-06-19T09:04:00Z")

</div>

I'm writing an insert into query to add a row of data into a table with 5 columns. From reading the doc, I assume I don't need to use prep update as

1. my data is not being typed out by user and doesn't contain additional ''
2. my data does not contain binary or blob values

if I runUpdateQuery, it should look as such right?

```auto
InsertQuery=system.db.runUpdateQuery("INSERT INTO FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES ('%s', '%s','%s', '%s', '%s')" %("5BH1D","4PE42","0","JMLNEW004","19/06/23"))

```

and if I runPrepUpdate, it should look as such right?

```auto
InsertPrep=system.db.runPrepUpdate("INSERT INTO FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?)",["5BH1D","4PE42","0","JMLNEW004","19/06/23"])

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 19, 2023, 12:22pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/2 "2023-06-19T12:22:03Z")

</div>

I would make this a named query.  
[Named Queries - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/Named+Queries)  
[system.db.runNamedQuery - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 19, 2023, 2:42pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/3 "2023-06-19T14:42:45Z")

</div>

You should basically always use runPrepUpdate. It's a straight upgrade over assembling the query manually for a variety of reasons. The two most important:

1. The JDBC driver is in charge of sending your parameters over the wire in a 'native' format, without any lossy conversions to strings. Sending strings works okay for numbers and literal strings, but quickly hits limitations: what about date(time) columns, binary data, JSON, or other exotic types?
2. A prepared statement (from any of the prep\* functions) can be parsed and cached and better optimized on the receiving server. If two identical prep updates come in, the second will likely avoid the SQL parsing work. Manually sent string queries will have to be parsed from scratch each time.

Named queries get all the same benefits as prep updates but are not as flexible, by nature. Both solutions have their place. One thing to never do is enable legacy DB access for Vision clients.

---

<div class="post-metadata">

**Author:** ![Joseph\_Loh](https://avatars.discourse-cdn.com/v4/letter/j/ebca7d/32.png) [@Joseph\_Loh](https://forum.inductiveautomation.com/u/Joseph_Loh)\
**Post date:** [June 21, 2023, 5:27am UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/4 "2023-06-21T05:27:16Z")

</div>

I ran the runPrepUpdate line from above, why do I get this error

```auto
Traceback (most recent call last):
  File "<input>", line 1, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO MDS_FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?), [5BH1D, 4PE42, 0, JMLNEW004, 19/06/23], , , false, false)

	at org.python.core.Py.JavaError(Py.java:547)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:541)

	at org.python.core.PyObject. __call__ (PyObject.java:477)

	at org.python.core.PyObject. __call__ (PyObject.java:481)

	at org.python.pycode._pyx13.f$0(<input>:1)

	at org.python.pycode._pyx13.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:611)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO MDS_FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?), [5BH1D, 4PE42, 0, JMLNEW004, 19/06/23], , , false, false)

	... 26 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Cannot run this function when in read-only mode.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:384)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:283)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:288)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 24 more

Traceback (most recent call last):
  File "<input>", line 1, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO MDS_FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?), [5BH1D, 4PE42, 0, JMLNEW004, 19/06/23], , , false, false)

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 21, 2023, 5:55am UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/5 "2023-06-21T05:55:59Z")

</div>

> [@Joseph\_Loh](#):
>
> ```auto
> GatewayException: Cannot run this function when in read-only mode.
> 
> ```

Change your designer comm mode.

---

<div class="post-metadata">

**Author:** ![Joseph\_Loh](https://avatars.discourse-cdn.com/v4/letter/j/ebca7d/32.png) [@Joseph\_Loh](https://forum.inductiveautomation.com/u/Joseph_Loh)\
**Post date:** [June 21, 2023, 6:12am UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/6 "2023-06-21T06:12:02Z")

</div>

That actually worked, thanks.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [June 21, 2023, 6:20am UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/7 "2023-06-21T06:20:47Z")

</div>

Tip for your next stack trace: Find the lines that start by `Caused by`, they'll tell you what's wrong.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [June 7, 2024, 8:41pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/8 "2024-06-07T20:41:17Z")

</div>

> [@paul-griffith](#):
>
> One thing to never do is enable legacy DB access for Vision clients

Why is this? If I should not allow `runPrepQuery` or `runPrepUpdate` with legacy access, what other option is there and how would it get/update the data?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 7, 2024, 10:12pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/9 "2024-06-07T22:12:00Z")

</div>

Named queries.  
If you truly need dynamic queries, use `system.util.sendRequest` to send the parameters you care about to the gateway, assemble your query (safely!) there, execute it, and return the resulting dataset.

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [June 18, 2024, 3:23pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/10 "2024-06-18T15:23:20Z")

</div>

I wanted to build an insert query with multiple values I.E\> [(val1,val2),(val3,val4),(val5,val6)] etc... I cant use Named queries or prep update (unless i loop the inserts but I prefer one sql statament) so I am only left with runUpdateQuery. is that preferred for my scenario? thanks

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 18, 2024, 4:00pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/11 "2024-06-18T16:00:49Z")

</div>

> [@drewdin](#):
>
> prep update

Why not? You can do multiple inserts with a single prepQuery.

Something like:

```python
query = "INSERT INTO yourTableName column1, column2 VALUES {}"

values = ",".join(["(?,?)"] * len(listToBeInserted) / columnsPerRow)

system.db.runPrepUpdate(query.format(values), listToBeInserted)

```

Just note that you are limited on the number of parameters ("?") you can use in a single prepared statement. For MSSQL, I believe it is 2000. So for this example you would be limited to inserting 1000 rows in a single statement. Anything past that and you would need to batch the inserts. (You should probably batch inserts for anything more than say 50 or so rows so you can avoid deadlock issues).

---

<div class="post-metadata">

**Author:** ![Ryan\_Ahlstrom](https://avatars.discourse-cdn.com/v4/letter/r/ecb155/32.png) [@Ryan\_Ahlstrom](https://forum.inductiveautomation.com/u/Ryan_Ahlstrom)\
**Post date:** [June 18, 2024, 5:44pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/12 "2024-06-18T17:44:45Z")

</div>

This is a helper function that I created for this exact situation. It takes an array of any size and a table name and returns the insert query string.

```python
def insert_array_query_gen(arr, table):
    placeholders = ', '.join(["'%s'"] * len(arr))
    query = "INSERT INTO " + table + " VALUES ({})".format(placeholders) % tuple(arr)
    return query

```

You could modify it for your need. It works well for inserting into wide tables.

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [June 18, 2024, 7:07pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/13 "2024-06-18T19:07:20Z")

</div>

Thanks, I didn't realize you could just add more than one values field and then pass in a list. I looked at the docs and it didn't mention it could be used that way so I didn't try it. I'm going to test it out later. thanks!

params = [[A,B], [C,D], [E,F], [G,H] ]  
INSERT INTO table (col1, col2) VALUES (?,?),(?,?),(?,?),(?,?)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 18, 2024, 7:12pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/14 "2024-06-18T19:12:34Z")

</div>

> [@drewdin](#):
>
> params = [[A,B], [C,D], [E,F], [G,H] ]

No, you have to flatten the list. Like so:

```python
params = [A, B, C, D, E, F, G, H]

```

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [June 18, 2024, 7:15pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/15 "2024-06-18T19:15:33Z")

</div>

Thanks, I have anywhere between 64 to 500 inserts each entry. I am dynamically building the values, I planned on using the runUpdateQuery until i found this post, which method do you prefer? Thanks

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 18, 2024, 7:16pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/16 "2024-06-18T19:16:37Z")

</div>

Absolutely use the `runPrepUpdate`.

If you have a competent DB brand and JDBC driver, you have another, more efficient option:

> [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4):
>
> If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase…

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [June 18, 2024, 7:19pm UTC](https://forum.inductiveautomation.com/t/runprepupdate-vs-runupdatequery/75197/17 "2024-06-18T19:19:43Z")

</div>

thanks!

I'm using sql server by the way.