INSERT String Tags to db records
Aloha Mates!
I'm struggling with being able to INSERT db records from tag event script. You all are my ChatGPT backup.
We have a Fairbanks FB2560 Truck Scale.
This device is connected to Ignition over Ethernet via standard TCP Driver.
The FB scale has always printed hardcopy tickets. These hardcopy tickets are manually entered into Airtable.
(I'd like to see us use our MSSQL db and get rid of Airtable subscription.)
The INBOUND half of the ticket is printed when the harvest truck arrives.
About 10 minutes later, the OUTBOUND half of the ticket is printed when the truck has unloaded and is ready to leave.
Tickets contain various data pertaining to Crew #, LoopID, Truck #, Ticket #, Date-Timestamp, etc.
I've configured the scale to send the same data as ticket info, out its COM4 port.
I've created the following string tags:
"[default]Scale1_" receives FB scale string (anywhere from 115 chars to 155 chars). This tag has a Value_Changed event script.
"[default]Scale1_MemIN" has a Value_Changed event script that parses INBOUND data to individual INBOUND related tags.
"[default]Scale1_MemOUT" has a Value_Changed event script that parses OUTBOUND data to individual OUTBOUND related tags.
"[default]Scale1_trig_Crew" crew# is parsed out of "[default]Scale1_MemIN" and written here, as a string
"[default]Scale1_trig_LoopID" loopid# is parsed out of "[default]Scale1_MemIN" and written here, as a string
The "[default]Scale1_" script simply sends INBOUND data to [default]Scale1_MemIN, and OUTBOUND data to [default]Scale1_MemOUT. This works.
"[default]Scale1_MemIN" and "[default]Scale1_MemOUT" parse to individual tags. This works.
I'm only presenting (2) of the (15) tags that parse to.
PROBLEM:
I want to write the individually parsed tags to fields in our MSSQL db.
(Enabling History doesn't work because some fields don't change value for some time, so there's no corresponding db entry for that piece at a given timestamp.)
(Clearing field first works but then I have extra blank entries in db.)
I don't have SQL Bridge module.
I've been unsuccessful with correct syntax to force a db INSERT at the end of MemIN/MemOUT scripts.
Value_Changed script in "[default]Scale1_MemIN"
#Parse INBOUND data from Fairbanks 2560 scale
import re
sourceValue = system.tag.read("[default]Scale1_MemIN").value
#When data does arrive, make sure there is enough before parsing.
#Occasionally, we get a timeout between Ignition and Fairbanks Scale device.
#This results in only partial population of the INBOUND data, less than 5 characters.
if len(sourceValue) > 100:
str = 'CREW NO.\s+(\d+)'
result = re.findall(str,sourceValue)
crew_value = result
system.tag.writeBlocking("[default]Scale1_trig_Crew", result)
str = 'LOOP ID\s+(\d+)'
result = re.findall(str,sourceValue)
loop_id_value = result
system.tag.writeBlocking("[default]Scale1_trig_LoopID", result)
# Get the current epoch timestamp (since t_stamp uses epoch time)
epoch_time = system.date.toMillis(system.date.now())
# Define the SQL query to insert data into sqlt_data_1_2024_11 for Crew
query_crew = """
INSERT INTO sqlt_data_1_2024_11 (tagid, stringvalue, t_stamp)
VALUES (?, ?, ?)
"""
# Execute the query to insert Crew (tagid 692)
system.db.runPrepUpdate(query_crew, [692, crew_value, epoch_time], "SCADA")
## Define the SQL query to insert data into sqlt_data_1_2024_11 for LoopID
query_loop_id = """
INSERT INTO sqlt_data_1_2024_11 (tagid, stringvalue, t_stamp)
VALUES (?, ?, ?)
"""
# Execute the query to insert LoopID (tagid 693)
system.db.runPrepUpdate(query_loop_id, [693, loop_id_value, epoch_time], "SCADA")
