System db update the data into the latest record

Ignition
1

Hi guys,
I’m trying to implement a value changed script to update the end_time to the row that already had start_time logged. At the moment, I’m able to insert them into 2 separate rows. Please see attached pics and code!

start_time= system.date.format(system.date.now(), "yyyy-MM-dd HH:mm:ss")
end_time= system.date.format(system.date.now(), "yyyy-MM-dd HH:mm:ss")

if previousValue.value == False and initialChange == False :
 	system.db.runPrepUpdate("INSERT INTO downtime_data.downtime_debarker (start_time) VALUES (?)",[start_time],"downtime_data")
if currentValue.value == False and initialChange == False :
 	system.db.runPrepUpdate("INSERT INTO downtime_data.downtime_debarker (end_time) VALUES (?)",[end_time],"downtime_data",)

image
image701×521 36.8 KB

2

I’m not sure I understand the problem but I think there are two issues:

  1. You might be looking for a Python None rather than False. (They’re not the same.) Try
    if previousValue.value is None and initialChange is None:

  2. If you’re trying to update end_time then use the SQL UPDATE command rather than insert. The raw SQL syntax is,

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The Ignition script syntax would be like,

query = ('UPDATE downtime_data.downtime_debarker ' 
		+ 'SET end_time= ? '
		+ 'WHERE start_time = ? ')
args = [end_time, start_time] 
system.db.runPrepUpdate(query, args)

What is `“downtime_data”? You have it quoted so that’s just going to be a string “downtime_data” rather than the contents of the down_time variable.

3
  1. It is a boolean tag. So, I’m just trying to make valued change script from this boolean tag, and skip the quality of the tag which is initialChange.
  2. With a bit changes, I’ve made it successfully based on Transistor’s help:
start_time= system.date.format(system.date.now(), "yyyy-MM-dd HH:mm:ss")
	end_time= system.date.format(system.date.now(), "yyyy-MM-dd HH:mm:ss")
	
	query = ('UPDATE downtime_data.downtime_debarker ' 
			+ 'SET end_time= ? '
			+ 'ORDER BY start_time DESC ' +'LIMIT 1' )
	args = [end_time] 
	
	
	if previousValue.value == False and initialChange == False :
			 system.db.runPrepUpdate("INSERT INTO downtime_data.downtime_debarker (start_time) VALUES (?)",[start_time],"downtime_data")
	if currentValue.value == False and initialChange == False :
			system.db.runPrepUpdate(query, args,"downtime_data")

Basically, it will update the end_time to the latest record of start_time whenever the boolean tags changes its value.

Thanks for your help Transistor!!!

4

“downtime_data” is my SQL database name. IDK why it only works with this syntax inside. Maybe I have various databases.

5

Tip: use the </> code formatting button to preserve indentation and avail of syntax highlighting. It’s especially important for Python code. Can you fix your answer (pencil icon) to make it readable? Thanks!

6

Oops! Learn new things every day. Cheers Transistor!