[APP][PRO] MySQL-Client

Apps
, ,
61

I checked the …list.js script, which gave me:

Executes the SQL query with ID [[id]]: [[query]].
{
  "uri": "homey:device:bc2f14db-77ce-4a43-ad93-5f9d48fa3766",
  "id": "post_query",
  "args": [
    {
      "name": "query",
      "type": "text",
      "placeholder": "INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);",
      "title": "SQL query"
    },
    {
      "name": "id",
      "type": "text",
      "placeholder": "Query ID",
      "title": "ID"
    }
  ]
}

Then I had multiple attempts to try to get it working, but without success. Here is my piece of code:

let result = await Homey.flow.runFlowCardAction({
  uri: 'homey:device:bc2f14db-77ce-4a43-ad93-5f9d48fa3766',
  id: 'post_query',
  args: {
      name:'Select * from Homey.Energieverbruiken;'
    }
});

This always results in:

{
  returnTokens: null,
  usedTokens: {},
  error: {
    message: `SQL database "Homey": Error in SQL query: Cannot read properties of undefined (reading 'replace')`
  },
  elapsedTime: 20.185842007398605
}

Any suggestions?

62

Use “query” instead of “name” as argument id.

63

Life is so easy sometimes!
That did the trick, thanks!

1 Like
64

Hi,
When I try to create/connect a sql device I get the following error: “Connection not possible: connect ECONNREFUSED”…
I can connect from a regular javascript outside Homey.
Can anyone help - what am I doing wrong? :slight_smile:

65

Are you running that “regular javascript” on the same machine that’s also running MySQL? It looks like it might not be configured to accept TCP connections.

66

Many thanks for the quick response…
You are absolutely right, I run the javascript from the same machine that also runs MySQL - so I think you have a point.
Wonder how can I fix that?

67

It’s a setting in the MySQL configuration, see for example this page: How To Allow Remote Access to MySQL | DigitalOcean

1 Like
68

I have to admit this is difficult for me, but I am also a beginner… How do you others run your setup - do you have e.g. mySQL running on a NAS server?
I look forward to hearing how your setup is - if you want to share your knowledge :slight_smile:

69

Hi,
I use the default MariaDB server of my Qnap NAS.
You have to add a new database and a user with access rights. Then you can add tables and define the structure/columns.
And the SQL service port must be accessible on the Nas IP.
That should be all to access the DB from Homey.

70

@soap: you mean connecting a MS Accessapp to a mySQL database? That’s perfectly possible.

@RonnyW just discovered your app, it’s promising. I just want to export my insights for a specific set of devices to a place where I can create a proper report. I’m using Homey Insights so far but the fact you have to set it up every time you use it (choose the devices) is such a bummer.

Tried the MS SQL app for homey, but hosting a 24/7 MS database requeres Azure or a dedicated server. MySQL is running on my NAS, so perfect so far

This is working:
image
Is there a way to dynamically select the devicename/capability instead of typing it in?

I see a script above that cycles all devices, but I just want a specific set of devices/capabilities in a generic table. Maybe I could adjust that script (no scripting experience yet)
image

Also read about Influx-db in this topic to do a generic export, but my NAS does not support dockers

71

Hi @zakraket,
that’s not possible. The app can only access its own devices and global tags because it has not implemented HomeyAPI that gives full control over Homey. That’s not the target of the app.

But why do you want to record sensor data every minute?
I would suggest to use the “has changed” trigger for every sensor you want to record and then store this value with the timestamp to your database.
That’s preventing performance issues reading all sendors at once and you will get all changes in time even if a sensor changed twice in a minute. And it prents duplicated entries if the sensor doesn’t change the value in a minute.

It will need to setup a flow with all devices, but will have much more control. So you can log all temperatures in one table and all energy reletated data in another.

72

I just need 10, max 15 values from <10 devices, so I can do it in one advanced flow… I could even do it in one SQL, include al 10-15 devicevalues in one INSERT VALUES statement
Might use “has changed” but I was mainly curious if maybe there was an easier way to include devicename etc

73

No, not in the way you need.
You can use a trigger (time, “has changed”). Then you can use the trigger tags or all other global tags.

I think the “has changed” is the best way. And you can use the tag of the trigger card. If you need a timestamp in your dataset, please take a look at the examples in the first posts.

74

Hi all, First of all many thanks for creating this app. It works flawlessly for me with rather complex (for me) queries. But of course I have a question:

Is it possible to select a value from my database and present it in Homey as a (in my case) temperature device?

Something like:

SELECTtemp FROM weather ORDER BY timeDESC LIMIT 1;

So I have the latest value of temp from my weather table and want to have that as a device in Homey.

Hope you can help,

Best regards,

Ben

1 Like
75

You can select the field using your SQL. I make an example with my energy DB:

In AdvancedFLow it’s much easier because you can connect cards together. In StandradFlow you have to use the SELECT card and react on the result using the trigger card.

Use the card with the “export tag” in AdvancedFlow:
grafik

My table:
grafik

I read the latest grid_injected value:

grafik
grafik450×555 36.5 KB

Selecting none field you can directly use the tag in the next card to store this value in a virtual device.

You can also select a whole table line. You get the line as JSON array with one line:

grafik
grafik455×522 41.4 KB

You can parse this JSON with a logic card to extract the field value:

grafik
grafik845×318 38 KB

In this case, the first line is read (using [0]) and then the field “grid_injected” is read (using .grid_injected).

…and many thanks for your donation :slight_smile: :beers:

1 Like
76

Hi Ronny,

Thank you for the great answer en your more than welcome for the donation :wink:

Stil one question, Now I have the “Result”. Can I create a device in Homey which constantly shows the last result of this query?

Best regards,

Ben

77

Yes, but that’s more a question about virtual devices than SQL :wink:

You can use VD app:

Add a device with your needed capabilities (like measure_temperature).

grafik
grafik427×778 24.9 KB

grafik
grafik423×201 7.45 KB

Then use the “set virtual sensor” flow card:

grafik
grafik639×546 35 KB

1 Like
78

Hi Ronny,

Thanks a lot, that will work for me.
Sorry for asking beyond the scope of this thread,
Your explanation did the job, all is running well :wink:

Regards,

Ben

1 Like
79

Hi, I get errors from my web host that this app sends data without closing the the connection. Please advise?

80

Hi,

My web host suspended my site where I have PHP and MySQL for logging Homey stuff. They say that my IP (Homey) is sending a lot of database updates without closing the connection properly. This is affecting their server and they have now (temporarily) suspended my site.

I’m running Homey Pro 2019 and the app has not been updated last 2 years so I cannot for my life understand why this is happening now.

Please advise!

Edit: Added: They said that this has also happened in Nov and Dec but somehow it didn’t trigger the suspension. I had (have) bad internet from time to time. This results in “Error in SQL query: connect EHOSTUNREACH” if Homey tries to write to MySQL and Internet is down.

In order to combat that, in that time period, I put a lot (a loooot) of if error → delay 5 seconds → try again.
Could it be that (really weird), the app somehow have opened a connection anyhow and then it mayebe tries 5 times more (more opened connections?) and finally is able to write?

But I feel that if MySQL is temporarily down/unreachable, no connection could be opened so maybe I’m just fishing in the dark here. Unfortunatelly I cannot find more information or contact info for MySQL App for Homey | Homey and Ronny Winkler himself regarding this.