SQL Query Binding Tag Quality

I have a label in a container that has a custom property bound to an indirect tag. The text property of the label is bound to a SQL Query. The SQL Query references the custom property of the container and has a fallback value assigned.

SELECT name FROM machine WHERE id = {Root Container.MachineId}

The label’s text updates properly when the tag referenced in the custom property is valid. If the tag is invalid (wrong name for example), the text will either stay the same or change to the fallback value. It’s not predictable on what value will be returned from the binding. I also do not get a quality overlay on the label when the tag is invalid. Anything else that is bound (non-SQL) to this custom property shows the quality overlay. I also noticed there isn’t the typical Overlay Opt-Out checkbox for the SQL Query binding.

Does SQL Query binding not recognize tag quality? If not, are there plans to include this in the future and/or is there currently (v7.9) a way to return the fallback value if the tag quality is bad?

Its not a tag quality problem, its a sql query issue. You need to write your query to return an expected result if no result is not found. NULL’s cause issues because they are the absence of values, check for isNull or the length of the result

Here is a more detailed description of what I have configured:

There are two custom properties defined for the Root Container. They are TagPath (String) and MachineId (Int4). TagPath is bound to tag [Client]TagPath and MachineId is bound to an indirect tag {1}/MachineId where {1} references Root Container.TagPath.

So for example, there is a tag folder called MachineA that contains an OPC tag called MachineId. [Client]TagPath would then be set to [default]MachineA. The idea is to be able to switch between machines by selecting a different tag folder containing the same OPC tags but bound to different OPC devices. If I set [Client]TagPath to a particular folder that doesn’t exist or doesn’t contain the tag MachineId then Root Container.dataQuality = 404 which is what I expect. However, I have observed seemingly random results on the MachineId custom property value. Occasionally it will change to -1 but most times it will remain at the previous value when the quality value = 404. If the value remains unchanged, the SQL query returns the result for that particular MachineId which is undesirable. If the value changes to -1, then the SQL query returns nothing so the fallback value is displayed which is what I want.

I’m not sure what mechanism is changing MachineId to -1 when quality = 404 but that is what I would like to have happen ALL the time so the SQL query returns the fallback value. Or at least have the SQL query return the fallback value (if defined) if any referenced tag or property has bad quality.

Since I have one custom property referencing another custom property, could there be some sort of race condition going on when those are evaluated causing this random behavior?

1 Like