Working on saving data logs into MySQL v8.0.42.
Hi @vladmen,
I would recommend that you create a seperate instance of the table data using a view and convert the timestamp, which comes in unix format, and blob which comes as binary to the preferred type. Here is some SQL code that would do this:
CREATE OR REPLACE VIEW hostname_log000_view AS
SELECT
*,
FROM_UNIXTIME(`time@timestamp`) AS datetime_converted
FROM database.hostname_log000_data;
SELECT datetime_converted, data_format_0, CONVERT(data_format_1 USING utf8) AS readable_text
FROM hostname_log000_view;
Where hostname_log000_view is the table, the time@timestamp is the column that holds the Unix time data sourced from the data sampling output, and data_format_1 is the blob data.
Within this view the rendered table looks like this:
However, with this view the output is as shown:
The settings of my data sampling object in this example are as follows:
Brandon,
thank you -- that's exactly what I needed.
The only other question is if it’s possible to automatically name data_format_XX with Descriptions from Data Sampling.
I understand that when creating view I can rename them, but when adding items to sampling it takes extra steps to update SQL statements.
I have not tried this, but I suppose it should be possible to log a separate STRING column that contains the desired column names and modify the query above to read the STRINGs from that column (or those column(s)) and use them when renaming the columns in the view.
When I have time later, I will try to test this and share some sample code.
To my knowledge, a CMT-X HMI will send raw data to a SQL server, which means the HMI will send “data_format_XX” as each header name. There is no way to automatically name “data_format_XX” with wanted Descriptions from Data Sampling object.
Hi @vladmen,
Thank you for your patience. In this case, I would agree with @TimWusa that this may not be possible as it is part of the method the HMI uses to create the table data. The only way to do this would be to execute a custom SQL query from the HMI that performs the operations noted above and copies that data into a new table for reference. A query formatted like this may work:
Note: The command creates and re-creates a copy of the data sampling table called ‘datalog’. The copy assigns your desired column names using the “AS” key word and reformats the data as described above.
This query can be added to the SQL query object using the method shown within our “SQL Query advanced” tutorial shown here: Link
Thank you.
I do not think I will go this route, since it will need to be manually updated every time Data Logging is changing on HMI. I will do it as suggested initially in the View.
