Hi all, I am currently new to Ignition and using it to read data from a database (MySQL PHP MariaDB) for a School Project
Currently, I have managed to come up with a simple Query to get the data as shown
From the Table Perspective:
I was wondering how do I convert the datatime of Log Value into a Date-Time Format using the Expression Transformation or are there easier ways to do this
I have tried using this fromMillis but there is always an error
I am not too sure how to go about this.
I do also have questions regarding the Table, I noticed that I can actually use the Query option to read data from the database automatically using the polling, so that means the table will run the query after every X second, is this how Ignition refreshed data that it read from the database ? Is there a way to automatically run the query when a new data is being added to the database ?
Have you tried doing it in your query? I’m assuming the time you highlighted should be 2021-02-10 06:20:05.7120. If that is right then try changing your query like:
SELECT templog.id,
from_unixtime(templog.LogTime/1000) as LogTime,
templog.temperature,
templog.humidity
FROM templog
ORDER BY templog.id
If you try to do it in Ignition, you would have to do it through a script because your query returns a dataset. You would need to loop through the rows that are returned and convert your value to a time. In this case, I would think it would be easier and more efficient to just do it during the query.
As for your question, you can set up polling to work a number of ways. One is based on a set period of time. You can also have it only do it once when a page loads. As far as having it run the query when new data is added into the database, are you adding it through Ignition or another source. If your doing it through Ignition then yes you can trigger a refresh using a script at the same time that you do the insert or update query. How you do it depends on where your query is done. To do it directly on query in perspective you would use the .refreshBinding() method but you need to keep in mind that will only do it on the instance you run it on. If perspective is running on more than one device, you would need to send a message if you want to do it on every device that has that view open.
Thank you for your reply. I manage to get the 2021-02-10 06:20:05 format by actually adding Columns to the table under the Perspective Property Editor. So far as long as the name of the Column matches the database column name, it will work fine. Like if I set it as Log Time , it will revert back to the epoch time.
I will run the query you recommend to avoid this issue.*
For my question, the data will be added directly to the database via an external source like sensors uploading the data to the Database. So no data is being written to the database through Ignition at all, so I guess I could actually do the refreshing via the query for the perspective to run the query every few seconds to update the table with data.
For running with more than one device, how I do go about sending a message ?
*Edit: I tried running the query you recommend and it returns NULL for LogTime Values. I went to check up on and apparently, the number might have been too big for the from_unix() to handle. a
Probable Solution would to use this instead
I’m testing it on MySQL and it works for me when I type in the number.
Normally in Ignition you can use the column position or column name interchangeably. So if the column name could change for some reason, you should be able to you the position in the dataset. For your original example, id = 0, LogTime = 1, temperature = 2 and humidity = 3. If you show what you were using I could give a more specific example.
If the data is added directly to the database you can’t trigger an update based on data being added so you would need to do it time base. In your original example, you don’t have a where clause so you don’t need any parameters. Will the final query have anything set so the user can filter the results? If not, I would do the query in a tag and then bind your tag to the table your trying to display it in. Then your tag can have a fixed refresh rate. I would also set it up with a leased tag group so your not scanning it as often when the data isn’t being displayed. You can either do a slower rate at this point or set the rate to 0 so it doesn’t scan when it isn’t displayed. The reason I would do it this way is so you aren’t pounding your database with queries for it if multiple users have it open.
