I have this code that is failing at the SQL query and does not execute the except code. Basically I want to write to some tags if this script fails. Sometimes the end date is null and so this SQL INSERT will fail, as in this example.
try:
logged_on = now
ret = system.db.runPrepUpdate('INSERT INTO datSeqLogs (seq_id, started_on, stopped_on, completed, logged_datetime) VALUES (?,?,?,?,?)', [seq_id, startDate, stopDate, completed, logged_on], SQL_CONNECTION_NAME)
seq_logs_id = system.db.runPrepQuery('SELECT id FROM datSeqLogs WHERE seq_id = ? AND logged_datetime = ? AND started_on = ? AND stopped_on = ? AND completed = ?', [seq_id, logged_on, startDate, stopDate, completed], SQL_CONNECTION_NAME)[0]['"d"] #ignore the extra ' here, used to fix forum colour formatting
except Exception, e:
error = '<HTML>Failed to write sequence log into datSeqLogs: INSERT INTO datSeqLogs (seq_id, started_on, stopped_on, completed, logged_datetime) VALUES (%s, %s, %s, %s, %s)<P>' % (seq_id, startDate, stopDate, completed, now)
error += 'or failed to run: SELECT id FROM datSeqLogs WHERE seq_id = %s AND logged_datetime = %s AND started_on = %s AND stopped_on = %s AND completed = %s' % (seq_id, logged_on, startDate, stopDate, completed)
system.tag.write(sequenceTagPath + '/Log/Common/Log Failed', 1)
system.tag.write(sequenceTagPath + '/Log/Common/Log Script Running', 0)
system.tag.write(sequenceTagPath + '/Log/Common/Last Log Error', error)
LOGGER.error(e)
LOGGER.error("Sequence logging failed for sequence %s" % sequenceTagPath)
return
Error:
Error executing script.
Traceback (most recent call last):
File "<tagevent:valueChanged>", line 4, in valueChanged
File "<module:shared.sequences.logging>", line 123, in logSeqData
File "<module:shared.sequences.logging>", line 123, in logSeqData
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.GeneratedMethodAccessor132.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO datSeqLogs (seq_id, started_on, stopped_on, completed, logged_datetime) VALUES (?,?,?,?,?), [0300, Tue Nov 19 07:07:22 ACDT 2019, , 1, Tue Dec 17 18:39:59 ACDT 2019], SQLServer, , false, false)