I have an issue regarding data from a named query that appears to be caching, even though caching is not set.
I have an SFC that runs a loop, the loop has a 2-second delay, then if it’s “ok to do data verification” it calls a script which does the data verification. Once that is done, it returns to the delay and loops again
The data verification script does a named query and compares the result to the data in PLC tags.
This all works fine, But if we pause the loop while we update the data in the database and the PLC and then start the loop again, the data that the script is bringing back from the database is the old value. I have tried lengthening the timer, and I can see through SSMS that the data in the database has changed but the named query appears to have the old data.
The ‘Cache Query Results’ in the named query is not enabled
Script (data verification)
sourceFunction= usrFunc.functionName()
strBatch = str(system.tag.readBlocking(['Batch/Data/selectedBatchID'])[0].value)
intRecipe = system.tag.readBlocking(['Batch/Data/Recipe/RecipeID'])[0].value
currStep = system.tag.readBlocking(['Batch/Sequence/Main/iState'])[0].value
skipValidation = system.tag.readBlocking(['Batch/Sequence/Main/skipParamValid'])[0].value
lineName = system.tag.readBlocking(['Batch/Sequence/lineName'])[0].value
#Query active parameters listed on DB
namedQuery = "SFC/get_ActiveParams"
dsActiveParams=system.dataset.toPyDataSet(system.db.runNamedQuery(namedQuery,{'line':lineName}))
*#Iterate DS to get mismatch between DB and OPC*
result = \[\]
**for** index, row **in** enumerate(dsActiveParams):
*#Read OPC path and get formatted data*
tries = 0
tagRead = \[None\]
**while** (tries <=3) **and** (tagRead\[0\] == None):
tagRead = utils.opcReadValue(row\['TagUrlRead'\],0,opcServerName)
tries += 1
*#Compare erading against SP*
matchEval = (str((row\['SetPoint'\])) == str(tagRead\[0\])) & ('Good' == str(tagRead\[1\].quality))
**if** matchEval != True:
evaluation = \['Tag: '+str(row\['Tag'\]),'DBValue: '+str(row\['SetPoint'\]),'FromPLC: '+str(tagRead\[0\]),'OPCQuality: '+str(tagRead\[1\].quality)\]
result += \[evaluation\]
*#Function will return False or empty List if No Difference found*
**if** (len(result) != 0) **and** (skipValidation == 0):
**if** listMismatch == False:
**return** True
**else**:
**return** result
**else**:
**if** listMismatch == False:
**return** False
**else**:
**return** \[\]
Named Query (SFC/get_ActiveParams)
SELECT
[Tag],
IIF([DataType] LIKE 'String%', [StringSetPoint], CAST([NumericSetPoint] AS NVARCHAR(30))) AS [SetPoint],
IIF([DataType] LIKE 'String%', '', CAST([NumericLowLimit] AS NVARCHAR(30))) AS [LowLimit],
IIF([DataType] LIKE 'String%', '', CAST([NumericHighLimit] AS NVARCHAR(30))) AS [HighLimit],
[TagUrlWrite],
[TagUrlRead],
[DataType]
FROM
[BatchData].[dbo].[Tag_Parameter_Active_Data]
WHERE
LineName LIKE :line;