SELECT
Select one value from your database in the THEN part of your flow:
Use the flow trigger in the IF part of your flow to receive the first value of the result.
You can use the Query-ID used in the SELECT above to filter your flow (react only on this kind of query).
Use the value in your flow. Take a look at the local flow tags you can use:
Use the first valus as number or text, write the complete JSON result to the timeline or what ever you need
INSERT / UPDATE
Insert a line into your database. This way you can periodically log capabilities.
Use a trigger to execute the query:
Insert your query and use global tokens or the date variable. This way it’s possible to log a daily sum at a new day using the date of yesterday:
If needed you can print your result in the timeline:
Combining SELECT & INSERT
If you have only a meter capability and want to log the hourly usage, you can first select the last meter value from your database and insert a new line with meter and calculated usage value.
1st: SELECT the last value.
Start the flow every hour. Set a Query ID to be able to react in a trigger only on that selection. Read the newest value from meter column.
2nd: Use this value in a second flow to insert a new line:
Use the Query ID as filter for the flow trigger. Insert a line with date/time, the calculated difference (current meter minus flow token containing the result of your SELECT) and the new meter value.
first i smiled… but then i saw you can only use it for certain tasks, like energy.
I wish there was an universal sql client. so you can configure db in the settings and then use the flows with queries.
If you read again you will see that it’s a universal client.
You can execute all SQL queries you want and use global/local tokens.
For Selects you have only tokens for one field (numeric+text) at the moment because these tokens mut be dwfined in the flow trigger.
For Insert/Update you can use all global tokens you need.
And becaus it’s the default token field, you use MathJS expressions, too.
The examples above should only demonstrate the usage with a common task: logging capabilities to a database.
Sorry, I don’t know what’s your question.
Please wait until the app is approved and try. I’ll post a information when it’s ready.
You will add a database as Homey device (for credentials and flows). Then you define flows (as action for that device) to execute your SQL querys. That’s all. Everything else is up to you.
The Homey Apps Insights2SQL only works with Microsoft databases and MYSQL does not work for me.
With such an app you can save storage space at Homey At least that’s how it’s meant to be for me.
I run HA, which stores energy consumption in MariaDB, for example. But on Homey I have to install additional apps to get this value. It would be easier if I could query the database from HA.
I also hope that this app can perhaps write Homey values to the same database to make them available to the other side.
I have already tried this, but failed.
Hi Ronny,
something is going wrong with me.
I have a connection to the HA database and I also understand that I need two flows to write a value, e.g. to the timeline.
What is not quite clear to me is what exactly the entry in the 1st flow must look like in order to query the value ?
are these table rows? What’s the table name?
To get the last “state”: SELECT state from <TABLE> ORDER BY last_changed DESC LIMIT 1;
Insert the table name and don’t forget the semikolon at the end
You select row “state”, from table ,
then you sort by timestamp (descending),
then you take only 1 row (the first).
The flow trigger will give you the first field of first row (you only select that).
In addition you can use the “Result as text” token to print the complete response as JSON-String (if would select mot columns/rows.
The app is online now 
