Script - SQL query error

Hi All

The following code works perfectly in the script console. However, when this code is called in a tag event script, it generates an error (shown below).

The table contains columns for Batch, StartTime and StopTime. The script is intended to prevent over writing of a record. i.e. check if the batch number exists, if not, insert (with additional code).

What is the reason for the different behaviour? 1394 does exist in the Batch column. and 27 is the line starting with "exists=" Is there a better way to achieve the desired functionality? I have played with COALESCE and ISNULL without any luck.

Can anyone help please?

command = "SELECT COUNT(Batch) FROM hamjet_report.dbo.STS_test WHERE Batch = 1394"
exists=system.db.runScalarPrepQuery(command)

Error executing script.
Traceback (most recent call last):
File "tagevent:valueChanged", line 27, in valueChanged
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:315)
at jdk.internal.reflect.GeneratedMethodAccessor93.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.runScalarPrepQuery(SELECT COUNT(Batch) FROM hamjet_report.dbo.STS_test WHERE Batch = 1394, , [null], )

You are using the “Prep” version of the scalar query and not providing substitution parameters. Place a question mark where 1394 is, then put that batch number in the list of parameters.

2 Likes

Many thanks! My code is now working. Do you know why my incorrect code worked in the script console?

No idea.

OK. Cheers.