runPrepUpdate vs runUpdateQuery

[Joseph_Loh]

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?

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?

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

[dkhayes117]

I would make this a named query.
Named Queries - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)
system.db.runNamedQuery - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

[PGriffith]

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.

[Joseph_Loh]

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

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)

[PGriffith]

@Joseph_Loh

GatewayException: Cannot run this function when in read-only mode.

Change your designer comm mode.

[Joseph_Loh]

That actually worked, thanks.

[pascal.fragnoud]

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

[mdemaris]

@PGriffith

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?

[PGriffith]

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.

[drewdin]

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

[lrose]

@drewdin

prep update

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

Something like:

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).

[Ryan_Ahlstrom]

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.

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.

[drewdin]

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 (?,?),(?,?),(?,?),(?,?)

[pturmel]

@drewdin

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

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

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

[drewdin]

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

[pturmel]

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

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…

[drewdin]

thanks!

I'm using sql server by the way.