I have created an SQLTag in my project, the table (FlowData) which holds the data I want to display is updated by a WPF application that reads the data daily from an XML file and adds a new record in the FlowData table for each entry in the XML file.
I have followed the steps outlined in document in this link viewtopic.php?f=25&t=5054.
I have dragged my SQLTag onto an easy chart my SQLTag properties are set as shown below.
When I navigate to my chart it only displays a continuous straight line regardless of how I change the settings in my SQLTag.
The SQL query can only return a single value. Your query is returning DTG and Reading. I bet the reading is the value you want to store in history. Make sure the query only returns a single value:
SELECT Reading FROM FlowData WHERE ReadingType = ‘Cumulative Total’ AND Name = “Flow1” ORDER BY DTG
Thank you Travis, Reading is the value I want to display.
I will try that on Monday.
Travis I changed my query to only bring back the Reading value but my graph still displays a straight line. Should the scan class that I created in the sqlt_sc table be an option in the dropdown list (Historical Scanclass) in the History Tab. Maybe I am missing something basic I am new to Ignition. Also as all the data will be coming from an outside source should the Write value back to OPC item be un-checked.
Yes, you should have the scan classed defined for the tag but I don’t think that is the issue. Are you seeing the tag’s value change at all?
I got it to work by changing my query as below
select Reading from FlowData
where ReadingType = ‘Cumulative Total’
and Name = ‘Flow1_Cumulative’ and DTG between DATEADD(SECOND,-5, GETDATE()) AND DATEADD(SECOND,+5,Getdate())
order by DTG
I have have changed my query since as follows
DECLARE @curDT DATETIME = GETDATE();
DECLARE @interval int = -400;
Declare @dtWithInterval DateTime = DATEADD(ss, @interval, GETDATE()); --Cur DateTime with interval added.
WITH cte AS
(
SELECT DTG, Reading, ABS(DATEDIFF(SS, @curDT, DTG)) diff,
ROW_NUMBER() OVER (PARTITION BY Reading ORDER BY ABS(DATEDIFF(SS, @curDT, DTG))) AS SEQUENCE
FROM FlowData
Where DTG >= @dtWithInterval
)
SELECT Top 1 Reading
FROM cte
WHERE SEQUENCE = 1
ORDER BY DTG DESC
;
Now I get the following error “Cannot run this function when in read-only mode”.
How do I give Ignition write access.
That is weird. Where are you running that query? That error message is coming from the designer when you are in read-only mode. You can set the designer to read-write from the menu but I thought the query was on the tag?
Travis the query is in the tag, I am testing is using a local database I got around the error by changing my query as below:
select Top 1 Reading from FlowData
Where ReadingType = ‘Cumulative Total’
and Name = ‘Flow1’
and DTG between DATEADD(SECOND, -400, GETDATE())
AND DATEADD(SECOND,+0,GETDATE())
ORDER BY DTG DESC
This works fine for records that are updated in real time, but the records that I want to display will always be a day old that is yesterdays records will only be available to my program today.
When I use the same procedure to add yesterdays records to SQL, my graph does not display them.
What is the solution to this problem.
Well the problem is that you are not in control of the timestamp for SQLTags history. When we see the value change we will record the value with the current timestamp. You will have to use a transaction group to log the data since you can be in control of the timestamp. Take a look at this post to see the differences between SQLTags Historian and Transaction groups:
Travis I created a transaction group and read in my data again having first deleted the old records. My graph displays as a straight line. Is there any step by step guide for doing this, or better again a example project that I could download and see exactly how it is done.
I think you need to call our tech support to see what is going on. It is hard to troubleshoot over the forum.