SQL Data Source - Query

[runnikri]

I'm looking to see if Mango can support importing data from a separate database (eg: sqlite) and record it as separate entries Mango d/b.

I have copied the database (eg: events.db) in MangoES HW and used the SQL Data Source option. My row-based query execution returns the following

I would like to import all of the descriptions as separate entries in the Mango. I would to like to use the timestamp from my database (event.db) as the time of the record when the import to Mango is complete.

Please advise.

[phildunlap]

Hi runnikri,

Mango should be able to do this! What do you mean "import all of the descriptions as separate entries in the Mango"? When we spoke on the phone, I understood all the descriptions as being different point values of the same point, and so that is how my answer is crafted. Did that change?

Because you want all the descriptions in the same point, you'll want to select the IDs as the same string which you can use as the identifier, something like,

SELECT 'description' AS eventId, description FROM events

Now we can use description as our Alphanumeric point's Row Identifier. if we select now, we'll see all the values quickly be set to the point, but when we check the history we'll only see one. This is because without providing timestamp information the data source gave all those point values the same timestamp, and Mango only supports a single value for a point at a time. So, we'll need to select for that timestamp information.

If there is an additional Date column in this table, you can use that. Otherwise, you may want to use functions built into your SQL server for getting a timestamp. For a MySQL server, you could also select a column like

unix_timestamp(current_timestamp-id) as date which would guarantee every row in the result had a different timestamp. However, this would not get the same timestamp for the same event on two different calls, so this could introduce false records at odd times. It would also be a good idea to ORDER BY date ASC so that Mango properly processes values meant for the same point (all of them) without deciding any of them have come from the past.

To prevent false records, I would also return the current max(id) from the table, then use another SQL point that 'Modify table only' to delete all events with IDs below the max read. Were it possible to have a parameter in the SQL data source's select statement this would not be necessary, but we can't prevent selecting records we've already read - so either they must have the same timestamp every read or we must purge them.

To select the max id in the same statement, you can try a statement like...

select 'description' as EventID, description, unix_timestamp(current_timestamp-id) as date from events union select 'maxId' as EventID, max(id) as description, unix_timestamp(current_timestamp) as date from testData order by date asc;

Which is then selected into a point with row identifier maxId, This point has a change detector or point link to the SQL "Modify table only" point that clears the read events from the table.