Once a Month Bug - Reporting with SQL

kzenner · June 10, 2020, 4:04pm

I have scripting in my project to generate daily reports based on hour of the day. Every day at 1 am the report generates and most of the time it works like it’s intended to. Except on the first day of every month… I was able to catch the logging error this month which points me to a section of code that executes the query and saves the results. I’ve attached a copy of the error. Any input would be appreciated. I’m at a loss for why this only happens on the first day of every month.

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 2, in File "", line 88, in getFuelData 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 sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values ('1590987600', '0-0-0 0:0:0', '0-0-0 0:0:0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0'), [null], , , false, false)

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

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 sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

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

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

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

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

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

at org.python.pycode._pyx5.getFuelData$1(:126)

at org.python.pycode._pyx5.call_function()

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:120)

at org.python.core.PyFunction.__call__(PyFunction.java:307)

at org.python.pycode._pyx3.f$0(:2)

at org.python.pycode._pyx3.call_function()

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

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

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

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:636)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:603)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:88)

at java.util.TimerThread.mainLoop(Unknown Source)

at java.util.TimerThread.run(Unknown Source)

Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 2, in File "", line 88, in getFuelData 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 sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values ('1590987600', '0-0-0 0:0:0', '0-0-0 0:0:0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0'), [null], , , false, false)

... 25 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values ('1590987600', '0-0-0 0:0:0', '0-0-0 0:0:0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0'), [null], , , false, false)

... 24 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:955)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:180)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:132)

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

... 22 common frames omitted

Here is the method that is called:

def getFuelData():

	###########################################
	# getFuelData gets the data from the PLC
	# and puts it into the database.
	###########################################
	
	# gets a logger
	logger = system.util.getLogger("Reporting")
	
	# gets the data ready tag from the PLC
	data_ready_tag = system.tag.read("NVH/FuelFarm1/DataReady")
	data_ready = data_ready_tag.value
	
	# keeps track of the records inserted
	records_inserted = 0
	
	if data_ready and data_ready_tag.quality.toString() != 'Bad':
	
		# begin looping through and transmitting data
		# in the data buffer
		while data_ready:
			# ordered lists for one-time async read
			column_names = ['timestamp', 'start_time', 'end_time', 'deisel', 'deisel_actual', 'e87', 'e87_actual', 'e93', 'e93_actual', \
							'natural_gas', 'natural_gas_actual', 'propane', 'propane_actual']
			data_types = ['int', 'datetime', 'datetime', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float']
			tags = [
						"NVH/FuelFarm1/RecordReady/timestamp", 
						"NVH/FuelFarm1/RecordReady/sStartTime", 
						"NVH/FuelFarm1/RecordReady/sEndTime", 
						"NVH/FuelFarm1/RecordReady/Deisel", 
						"NVH/FuelFarm1/RecordReady/DeiselActual",
						"NVH/FuelFarm1/RecordReady/E87", 
						"NVH/FuelFarm1/RecordReady/E87Actual", 
						"NVH/FuelFarm1/RecordReady/E93", 
						"NVH/FuelFarm1/RecordReady/E93Actual", 
						"NVH/FuelFarm1/RecordReady/NaturalGas",
						"NVH/FuelFarm1/RecordReady/NaturalGasActual", 
						"NVH/FuelFarm1/RecordReady/Propane", 
						"NVH/FuelFarm1/RecordReady/PropaneActual"
					]
			
			# joins the columns into a long string separated by a commma
			column_string = ", ".join(column_names)
			
			# reads the tags from the plc. returns a list of qualified
			# values in the same order the tags are in
			values = system.tag.readAll(tags)
			
			# this chunk of code iterates through the values,
			# converts them to a string, and joins them in a global
			# string for insert
			val_list = []
			for i in values:
				val_list.append(str(i.value))
			# end for
			values_string = "'" + "', '".join(val_list) + "'"
			
			
			##########################################################
			##           DATABASE INTERACTION SECTION               ##
			##########################################################
			
			# get the index of the timestamp tag
			timestamp_index = tags.index("NVH/FuelFarm1/RecordReady/timestamp")
			
			# Use the timestamp index to find the value in the values array
			# first we need to make sure that the timestamp is not already in the database
			timestamp_id = int(values[timestamp_index].value)
			
			# formulates the select statement for the database query
			select_statement = "SELECT * from nvh_fuelfarm1 WHERE timestamp=%s" % timestamp_id
			
			# executes the query and saves the result
			record_in_database = system.db.runPrepQuery(select_statement)
			
			# if the record is not in the database - we will add it. If the record is in the
			# database we will mark it reported in the PLC so we can clear the buffer
			if not record_in_database:
				# if the record is not in the database, then we insert the record in the db
				insert_statement = "INSERT INTO nvh_fuelfarm1 (%s) values (%s)" % (column_string, values_string)
				
				# this executes the query and saves the result. If it was not successful it returns none. If this
				# raises an exception, none of the code below it will execute.
				logged = system.db.runPrepUpdate(insert_statement)
				
				# write to the logged PLC tag to pop the record off of the queue
				_s = system.tag.write("NVH/FuelFarm1/RecordLogged", 1)
				
				# adds one to the records inserted variable
				records_inserted += 1
				
				# records logging into
				logger.info("logged timestamp=%s in database and cleared in plc" % timestamp_id)
						
			else:
				# if there is already a record in the database
				logger.info("id collision in database; timestamp=%s" % timestamp_id)
				
				# set the record logged tag to pop the record off of the queue
				_s = system.tag.write("NVH/FuelFarm1/RecordLogged", 1)
			# end if
			
			# sleep a couple seconds to make sure that the recordlogged tag
			# was written into the plc
			time.sleep(20)
			
			# after the sleep session, the data ready tag may have changed, so
			# we need to update that.
			data_ready = system.tag.read("NVH/FuelFarm1/DataReady").value
			
		# end while
		
		# outside of the while loop means that there are no more records
		# to be logged
		if records_inserted > 0:
			logger.info("%s records inserted into database" % records_inserted)
		# end if
	else:
		logger.info("project.Reporting.getFuelData no fuel data to be reported.")
	# end if
	
	logger.info("project.Reporting.getFuelData complete.")
# end getFuelData

logged = system.db.runPrepUpdate(insert_statement) is the line of the error

PGriffith · June 10, 2020, 4:12pm

java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values ('1590987600', '0-0-0 0:0:0', '0-0-0 0:0:0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0'), [null], , , false, false)

From your code, you're inserting your values into the SQL statement directly as a string.
That's a big problem for a few reasons:

It's inefficient. SQL Server can't do any optimization, because the query is different every single time (even if the values are the same).
It's (a bit) of a security problem - if bad values ever got into one of those tags, it's a SQL injection risk.
It's also bad for type coercion, which explains the error you're getting here. You don't need to coerce the values into strings; it's much more efficient and less error prone to directly send them into the database as values and let the JDBC driver handle the type coercion.

Use something like this instead:

				# if the record is not in the database, then we insert the record in the db
				insert_statement = "INSERT INTO nvh_fuelfarm1 (%s) values (%s)" % (column_string, ", ".join("?" * len(val_list)))
				
				# this executes the query and saves the result. If it was not successful it returns none. If this
				# raises an exception, none of the code below it will execute.
				logged = system.db.runPrepUpdate(insert_statement, val_list)

The ? in the INSERT statement are value placeholders; so you can directly submit the values to runPrepUpdate as the second argument and they'll be submitted to the JDBC driver directly, no need for type coercion or for the values_string variable in your insert statement.

kzenner · June 10, 2020, 6:24pm

I made the suggested changes and on the next database entry the same error occurred:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 2, in File "", line 88, in getFuelData 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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values (?,?,?,?,?,?,?,?,?,?,?,?,?), [1591808400, 2020-6-0 0:0:0, 0-6-0 0:0:0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0], , , false, false)

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

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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

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

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

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

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

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

at org.python.pycode._pyx28.getFuelData$1(:126)

at org.python.pycode._pyx28.call_function()

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:120)

at org.python.core.PyFunction.__call__(PyFunction.java:307)

at org.python.pycode._pyx29.f$0(:2)

at org.python.pycode._pyx29.call_function()

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

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

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

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:636)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:603)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:88)

at java.util.TimerThread.mainLoop(Unknown Source)

at java.util.TimerThread.run(Unknown Source)

Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 2, in File "", line 88, in getFuelData 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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values (?,?,?,?,?,?,?,?,?,?,?,?,?), [1591808400, 2020-6-0 0:0:0, 0-6-0 0:0:0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0], , , false, false)

... 26 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO nvh_fuelfarm1 (timestamp, start_time, end_time, deisel, deisel_actual, e87, e87_actual, e93, e93_actual, natural_gas, natural_gas_actual, propane, propane_actual) values (?,?,?,?,?,?,?,?,?,?,?,?,?), [1591808400, 2020-6-0 0:0:0, 0-6-0 0:0:0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0], , , false, false)

... 25 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:955)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:180)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:132)

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

... 23 common frames omitted

This error used to happen once a month, not every instance. This seems like a promising step forward but the bug is still there.

Here is the code with your suggested changes:

 def getFuelData():

	###########################################
	# getFuelData gets the data from the PLC
	# and puts it into the database.
	###########################################
	
	# gets a logger
	logger = system.util.getLogger("Reporting")
	
	# gets the data ready tag from the PLC
	data_ready_tag = system.tag.read("NVH/FuelFarm1/DataReady")
	data_ready = data_ready_tag.value
	
	# keeps track of the records inserted
	records_inserted = 0
	
	if data_ready and data_ready_tag.quality.toString() != 'Bad':
	
		# begin looping through and transmitting data
		# in the data buffer
		while data_ready:
			# ordered lists for one-time async read
			column_names = ['timestamp', 'start_time', 'end_time', 'deisel', 'deisel_actual', 'e87', 'e87_actual', 'e93', 'e93_actual', \
							'natural_gas', 'natural_gas_actual', 'propane', 'propane_actual']
			data_types = ['int', 'datetime', 'datetime', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float']
			tags = [
						"NVH/FuelFarm1/RecordReady/timestamp", 
						"NVH/FuelFarm1/RecordReady/sStartTime", 
						"NVH/FuelFarm1/RecordReady/sEndTime", 
						"NVH/FuelFarm1/RecordReady/Deisel", 
						"NVH/FuelFarm1/RecordReady/DeiselActual",
						"NVH/FuelFarm1/RecordReady/E87", 
						"NVH/FuelFarm1/RecordReady/E87Actual", 
						"NVH/FuelFarm1/RecordReady/E93", 
						"NVH/FuelFarm1/RecordReady/E93Actual", 
						"NVH/FuelFarm1/RecordReady/NaturalGas",
						"NVH/FuelFarm1/RecordReady/NaturalGasActual", 
						"NVH/FuelFarm1/RecordReady/Propane", 
						"NVH/FuelFarm1/RecordReady/PropaneActual"
					]
			
			# joins the columns into a long string separated by a commma
			column_string = ", ".join(column_names)
			
			# reads the tags from the plc. returns a list of qualified
			# values in the same order the tags are in
			values = system.tag.readAll(tags)
			
			# this chunk of code iterates through the values,
			# converts them to a string, and joins them in a global
			# string for insert
			val_list = []
			for i in values:
				val_list.append(str(i.value))
			# end for
			values_string = "'" + "', '".join(val_list) + "'"
			
			
			##########################################################
			##           DATABASE INTERACTION SECTION               ##
			##########################################################
			
			# get the index of the timestamp tag
			timestamp_index = tags.index("NVH/FuelFarm1/RecordReady/timestamp")
			
			# Use the timestamp index to find the value in the values array
			# first we need to make sure that the timestamp is not already in the database
			timestamp_id = int(values[timestamp_index].value)
			
			# formulates the select statement for the database query
			select_statement = "SELECT * from nvh_fuelfarm1 WHERE timestamp=%s" % timestamp_id
			
			# executes the query and saves the result
			record_in_database = system.db.runPrepQuery(select_statement)
			
			# if the record is not in the database - we will add it. If the record is in the
			# database we will mark it reported in the PLC so we can clear the buffer
			if not record_in_database:
				# if the record is not in the database, then we insert the record in the db
				insert_statement = "INSERT INTO nvh_fuelfarm1 (%s) values (%s)" % (column_string, ",".join("?" * len(val_list)))
				
				# this executes the query and saves the result. If it was not successful it returns none. If this
				# raises an exception, none of the code below it will execute.
				logged = system.db.runPrepUpdate(insert_statement, val_list)
				
				# write to the logged PLC tag to pop the record off of the queue
				_s = system.tag.write("NVH/FuelFarm1/RecordLogged", 1)
				
				# adds one to the records inserted variable
				records_inserted += 1
				
				# records logging into
				logger.info("logged timestamp=%s in database and cleared in plc" % timestamp_id)
						
			else:
				# if there is already a record in the database
				logger.info("id collision in database; timestamp=%s" % timestamp_id)
				
				# set the record logged tag to pop the record off of the queue
				_s = system.tag.write("NVH/FuelFarm1/RecordLogged", 1)
			# end if
			
			# sleep a couple seconds to make sure that the recordlogged tag
			# was written into the plc
			time.sleep(20)
			
			# after the sleep session, the data ready tag may have changed, so
			# we need to update that.
			data_ready = system.tag.read("NVH/FuelFarm1/DataReady").value
			
		# end while
		
		# outside of the while loop means that there are no more records
		# to be logged
		if records_inserted > 0:
			logger.info("%s records inserted into database" % records_inserted)
		# end if
	else:
		logger.info("project.Reporting.getFuelData no fuel data to be reported.")
	# end if
	
	logger.info("project.Reporting.getFuelData complete.")
# end getFuelData

PGriffith · June 10, 2020, 6:38pm

			val_list = []
			for i in values:
				val_list.append(str(i.value))
			# end for
			values_string = "'" + "', '".join(val_list) + "'"

Drop this whole section - it’s still changing the types from whatever they are on the tags into strings. Use values directly in the runPrepUpdate call, instead of val_list.

kzenner · June 10, 2020, 7:28pm

Thank you for your input, that worked. I appreciate the fast responses!