db.runPrepUpdate issue

So I am terrible with queries and was trying my go at it, but it’s not working. I know I am missing something relatively simple. I am using the runPrepUpdate with in a tag change script to capture certain data from tags at that particular instance and throw them into a table. My log is giving me an error but it is hard for me to follow. Here is my script that I am using to attempt this.
Thanks for any assistance.

query = "INSERT INTO 'Filler3_Data' (dmx, pre_weight, time_stamp, position, dose_weight, post_weight, fault_code) VALUES (?, ?, ?, ?, ?, ?, ?)"
dmx = system.tag.read("[.]DMX_Cell").value
pre_weight = system.tag.read("[.]../../../5310/P5310_Mes/Weight/5310_Mes_w").value
time_stamp = (system.tag.read("[.]../../../5320/P5320_EOC_FILLING/Year").value,system.tag.read("[.]../../../5320/P5320_EOC_FILLING/Month").value,system.tag.read("[.]../../../5320/P5320_EOC_FILLING/Day").value,system.tag.read("[.]../../../5320/P5320_EOC_FILLING/Hour").value,system.tag.read("[.]../../../5320/P5320_EOC_FILLING/Minute").value,system.tag.read("[.]../../../5320/P5320_EOC_FILLING/Second").value)
position = system.tag.read("[.]P5320_pos").value
dose_weight = system.tag.read("[.]../../../5340/P5340_Mes/Weight/Weight_dose").value
post_weight = system.tag.read("[.]../../../5340/P5340_Mes/Weight/5340_Mes_w").value
fault_code = system.tag.read("[.]Op_Fault_Number").value
dbName = 'Prismatic_Data'
values = (dmx, pre_weight, time_stamp, position, dose_weight, post_weight, fault_code)
	
if not initialChange:	
	system.db.runPrepUpdate(query, values, dbName, '',True, True)

Use square brackets

values =  [dmx, pre_weight, time_stamp, position, dose_weight, post_weight, fault_code]

system.db.runPrepUpdate(query, values, database = dbName)

I am able to see the values but still an error. Here is the error:

([default]SAFT/JAX/Workshop_2/Filling3/INPUTS/CELL_INFO/Status/DMX_Cell, valueChanged) Error executing tag event script: Traceback (most recent call last): File "<tagevent:valueChanged>", line 14, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at jdk.internal.reflect.GeneratedMethodAccessor541.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 'Filler3_Data' (dmx, pre_weight, time_stamp, position, dose_weight, post_weight, fault_code) VALUES (?, ?, ?, ?, ?, ?, ?), [ALX19338M00046, 678.8099975585938, (2019, 12, 5, 14, 15, 52), A2P61, 134.07000732421875, 812.8800048828125, 0], Prismatic_Data, , false, false)
system.db.runPrepUpdate(query, values, dbName, '',True, True)

Im no expert in these but does having no space before the True effect it ? or maybe the single quotation marks around your database table effects it…

"INSERT INTO 'Filler3_Data' 

Use back ticks around ‘Filler3_Data’

`Filler3_Data`

Or even don’t quote the column name at all, since there’s no spaces in it.

1 Like

Sorry, for the late response. Meetings all morning. I tried a couple of the ideas you all have provided and still having an issue. Here is part of the log that I believe I need to look at but not sure:

Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 14, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at jdk.internal.reflect.GeneratedMethodAccessor541.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 `Filler3_Data` (dmx, pre_weight, time_stamp, position, dose_weight, post_weight, fault_code) VALUES (?, ?, ?, ?, ?, ?, ?), [ALX19339M00072, 682.219970703125, (0, 0, 0, 0, 0, 0), A3P12, 0.22003173828125, 682.4400024414062, 51203], Prismatic_Data, , false, false)

... 29 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO `Filler3_Data` (dmx, pre_weight, time_stamp, position, dose_weight, post_weight, fault_code) VALUES (?, ?, ?, ?, ?, ?, ?), [ALX19339M00072, 682.219970703125, (0, 0, 0, 0, 0, 0), A3P12, 0.22003173828125, 682.4400024414062, 51203], Prismatic_Data, , false, false)

... 28 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

Hi Kathy,

I am not sure what you mean. Can you elaborate? Thanks.

The table name should not need quotes because there are no spaces in it.

INSERT INTO Filler3_Data

1 Like

It may not like your TimeStamp format either. If you are using the current date and time, you can snag it from the Gateway tag "CurrentDateTime". There are also functions for creating a custom timestamp value as well.

The time_stamp is something that happens earlier in the process and is capture. There are actually 4 time_stamps during the entire process, but I am only wanting that particular one.

This usually means your MSSQL JDBC driver is out of date. Try updating it.

As for quoting: single quotes are only allowed in SQL for constant values, never for structure (table/column/schema names). When quoting for structure, the SQL standard is to use double quotation marks. SQL Server and MySQL (and MariaDB) violate the standard by default, requiring square brackets and reverse-single-quotes, respectively. In default mode, MySQL allows double quotes interchangeably with single quotes for values, another violation of the SQL standard.

Both SQL Server and MySQL can be configured to follow the standard by specifying “ANSI Quotes” in their configuration.

I don’t believe that is the case since I have no issues with other DB’s on the instance of SQL

Break down the update query. Try updating using only 1 value, say dmx. If that works then build the rest in until it faults.

Even if the other databases are working, that doesn’t rule out the driver. It all depends on what you are trying to do or convert in the query.

Thanks for everyone’s help. I had a couple of issues. One was the way I was displaying the time stamp. The other was an actual database issue. Even though I set up the types of data, there was one that I selected incorrectly and did not notice(integer vs float). Once I fixed that issue, everything fell into place. Thanks a lot.

2 Likes