[APP][PRO] MySQL-Client

That’s right. For insights exports, influx or mqtt with liva data would be a good choice.
But that’s not the use case for this app. It’s a generic client to read/write your MySQL database.

I use it for hourly/daily logging of my energy consumption. There I want fixed times to log. Anf you can insert it in your flows in an easy way - including reading values in conditions.
So it depends on the kind of data and what you would like to achieve.

With test version 0.0.11, flow tokens for Advanced Flows are added for the flow action “Execute SQL query”.
Using these tokens, you can directly use the SQL result in your flow. With the new logic cards you canparse the reault JSON string and extract more than only the first field.

Example:

This flow is:

  • executing the SELECT statement, reading one line from the table
  • printing the result JSON string to the timeline
  • extracting line1, field “gas_meter” from the JSON
  • printing this value to the timeline

grafik

Current test version:

1 Like

A small addition in test version 0.0.12: Exception handling for Advanced FLows.
The error is printed into the timeline as before.
In addition you can directly react on SQL error in the flow now, perhaps send a push message to react in time.

1 Like

Although not a complete newby, I keep struggling to use the THAN card.
After loading the App, configuring a device (connection tested succesfull) the If and AND cards are popping up but the THEN card won’t show:

I already deleted the app and reinstalled it. I did a PTP but no changes.
Funny thing is that IF en AND cards are fine, it is only the THEN card is reluctant.
Any suggestion is highly appreciated

You are right. The card doesn’t appear in standard flows anymore. I just extended it with the result token for advanced flowsnto get the result directly.
I have to ask Athom if that’s the wished behavior. In the meantime you can use the card in an advanced flow - I hope you ordered it.

Aha, it explains why it acts (not) as it does.
I did not ordered Advanced Flows (yet) as standard flows did the every job for me so far.
I understand that additional functionality is only available in the newer versions but it is a pity that new functions apparently break the standard functionality.
Thanks any way for your reply and maybe you can report this as a bug to Athom

I didn’t expect this behaviour. I will check and fix it.
But advanced flow will make creating flows easier. Especially this one. With AdvFlow you can execute the SQL and check the result directly. With standard flow you have to execute the SQL and check the result via a new When card (the trigger). You can see the difference in the screenshots above.

New test version 0.0.15:

Athom decided to hide all flow action with existing result tokens (for use in Advanced Flows) in standard flows. The flow action in this app was implemented to be used in both versions. But now I had to add a second (identical) flow action for standard flows.

So standard flow will now show this action card again.
In AdvancedFlows, two identical action cards are shown.

  • One without result token to use in combination with the corresponding flow trigger (SQL statement was executed)
  • One with result tags for use in AdvancedFlows with directly added further actions.

grafik
1st one with result token, 2nd one without.

In standard flows you will only see one action card:
grafik

@Henkhi : You can install the test version and check if it’s ok now. The stable version will be available after approval by Athom (next week)

@RonnyW : update works as a charm, Thanks

This was very useful. THANKS!!
Now I can retire one of my virtual machines that otherwise did som magic towards my SQLserver.

I also have an old application using MSACCESS (not my choice and I can’t migrate it to SQL).
Is it too much work to create a MSACCESS connector?

Hi,
I haven’t found a possible MSACCESS connector.
For direct access to the database files, you need a access driver installed. So that’s only possible for applications in a windows environment.
For access your DB vie ODBC, you need a NodeJS package and Unix binaried (driver) installed on Homey.
So there is no possible way I can see at the moment.
I haven’t found a way to connect to MSAccess using MySQL. I only found a way to access MySQL database from MSAccess via ODBC. So I think, the only way would be a connection using ODBC. But this swill need a standalone NodeJS package without forther requirements. But I haven’t found such a package.

Is your application directly using a MSAccess DB? Or is MSAccess only needed for the dialog and macros? Perhaps you can migrate the DB to MySQL and connect from the application to the DB via ODBC. So you can use the Access frontend with a MySQL DB as backend.
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-linked-tables.html
Just a thought, but I don’t think such an Access application can easily be splitted.

Thanks.
the application is only using ACCESS database. I would much rather prefer it using MySQL.
I have tried for years to get it connected to MySQL but to no avail. ( the option is there in the application settings, but it wont work.)

I just came up with a dirty solution. replicate entire mdb to mysql - I dont mind if it´s one way replication to mysql, I just want homey to be able to get data from it.
In fact I dont need the entire DB, i only need 3 columns…
I will try this approach and see if I can get it working. :slight_smile:

I think its a DB export, so one way. If you need only historical data, it’s a solution. Perhaps you can automate it with an Access macro or batch file. Depends in your used tool.

I created a new DB on mySQL, on the MSaccess database server I created a script to upload selected colums to the SQL server. The script runs once a minute so I get a little bit of a delay, but I can live with that.

1 Like

Nice App, Ronny! Got it working fairly easy.
A question: is there any way to use this via Homeyscript?

When you run the …list.js script, all your available And & Then flowcards will be shown (takes quite a while before it has finished!)
The …run.js script shows how to use them
Screenshot from 2022-11-17 20-57-56

1 Like

What do you want to do in HomeyScript in addition to flow actions?
I think it’s easier to start a SQL query as a flow action in AdvancedFlow and read the result using the JSON logic cards. Or take the SQL result as token and use this as parameter for HomeyScript to extract JSON attributes in JomeyScript.

Thanks Peter, very insightful, I will try this out!

1 Like

Hi Ronny,
I want to make it dynamic by looping through all my PBTH devices, selecting their energy consumption and costs, and store that in a SQL database. I think the hint from Peter will get me there.
Thanks!

1 Like