MySQL Command

[Yes]

I think the version is causing the problem.
Responding services is a feature that has been introduced in Home Assistant 2023.7.0

2023.7: Responding services

I will update the readme on github.

Can you try and update your container to the newest HA version?

[Sergio_ITAP]

Gonna check if i can, i’ll let you know

[Sergio_ITAP]

Hi, sorry for late reply.

I could finally make it work and it works awesome, but i’m having some trouble cause i wanna get on evalue from the result and it’s not working.
My plan is to get one number from de database and using an “if” sentence, do something if that´s the number i want, but i think i can´t get the number from ‘response’.
I’m using this automation and it gets the number right but then the “if” sentence doesn’t work properly, maybe because the ‘value_template’ isn’t right.

I show you the MQTT results below.

Maybe changing the ‘value_template’ sentence or extracting just the number from ‘response’ into another variable it would work

[Yes]

Hi,

Good to hear that the query command works at your side.
Did you update your container to a recent HA version?

Regarding your automation, i also think the template is incorrect.
Maybe you can try one thing, if that does not work, i will try to replicate your query and automation to debug the problem.

Since result is a collection, in order to be able to return a recordset (multiple records), depending on the query, you’ll have to specify the item number.

in your case:

prueba = {‘result’: [{‘idAP’: 379}]}

you can see the square brackets around the value, because it is a collection.

So you can try this template:

value_template: “{{ state_attr(‘sensor.response’, ‘result[0]’, ‘idAP’) == 379 }}”

[Yes]

Or you could try something like:

value_template: “{{ sensor.response.result[0].idAP == 379 }}”

[Sergio_ITAP]

After almost an hout and a half trying any possibility this finally worked!

value_template: "{{ response.result[0].idAP == 379 }}"

Thanks for your time and help!!!

[Yes]

Y.w.c. and glad to hear you’ve got it working now.
Thanks for trying the service and for your feedback

[actran70 (AndyT)]

@Yes Thank you for creating this custom service. Installed and working on my side with Home Assistant 2023.12.1 against Mariadb.

[Yes]

You’re welcome.

Glad to hear it works for you too.
And many thanks for your time to respond.

[actran70 (AndyT)]

I was wondering if I can seek your advice. I am able to do a test mysql_query in dev tools.

But wondering how to call your service and display the mysql response in lovelace UI? (2nd screenshot shows entity display with desired mysql response as secondary info.)

Happy to use a custom UI component if that is required.
Thank you in advance for your help. Happy holidays!

[Yes]

If you pick a card that supports templating, you should be able to show the result by using:

{{ response.result.ts }}

to show the item.

I’m using the custom:button-card for my dashboards.

[actran70 (AndyT)]

@Yes Can you give an example of how I can call your service in lovelace UI without having to click a button (or do anything)?

[Yes]

I’m not sure, but afaik, you’ll always have to respond to a trigger in order to call any HA service.

You could create a Template Helper and use that in Lovelace.
But you’ll still have to call the service now and then to update the Template Helper’s underneath value.
The trigger to call the service could be a timer or other event such as an entity’s state that is changing.

I’m not sure if this approach makes sense for you.

Maybe if you could explain your use case in more detail, hopefully someone else might come up with an other approach.

[actran70 (AndyT)]

My use case: As you can see from my lovelace screenshot above, I display the last_change timestamp for each of my entities. However the last_change timestamp does not always reflect the last change time if I reboot HA, last change timestamp would then be the reboot timestamp. (This is a well-known gripe for users Persist last_changed between HA restarts)

The idea is to do a mysql select like shown in my first screenshot to retrieve the correct last_change value for each entity and display with each entity in lovelace.

I hope that makes sense.

[Yes]

Ah, i see what you’re trying to achieve.

As said, you’ll have to call the MySQL service from a trigger. In your case the trigger will be HomeAssistant starting up.
So you need to follow these steps:

1. First create a Helper entity of type Date and time and give it a name, for example Door1TimeStampHelper

2. Next create an Automation that populates the Helper entity when HA starts:

alias: refresh_last_change_timestamp
description: ""
trigger:
  - platform: homeassistant
    event: start
  - platform: homeassistant
    event: start
condition: []
action:
  - variables:
      response: null
  - service: mysql_query.query
    data:
      query: select last_updated_ts from states where entity_id = 'binary_sensor.front_door'
    response_variable: response
  - service: input_datetime.set_datetime
    target:
      entity_id: input_datetime.Door1TimeStampHelper
    data:
      value: "{{ response.result[0].last_updated_ts }}"
mode: single

3. Finally you should now be able to use Door1TimeStampHelper entity in Lovelace

I haven’t tested above literally, so you may have to adjust the entity names and the SQL query. But that should be no problem, since I saw you already had an advanced query in your screenshot

[Yes]

One more thing:

You will actually also have to update the Helper entity at the moment the garagedoor is being opened or closed.
For that you can follow one of below scenarios:

1. Add an extra trigger to the automation above, to also fire when the state of the garagadoor changes.
2. Create a separate automation to set the Helper’s entity value to the state of the garagedoor when that changes

[actran70 (AndyT)]

@Yes Hmmm, I was trying to avoid having to create more entities like input_datetime.Door1TimeStampHelper to store timestamp values that are already in DB.

Is there no way to display the results of a mysql select for each entity in lovelace? (where the user does not have to tap/click anything)

[Yes]

I do understand wat you’re looking for, but unfortunately i have no clue right now how to achieve this.

[Yes]

I was browsing through the list of available official HA integrations when
I came across the SQL integration. Could this be what you’re looking for?

I haven’t tried it myself (yet), but it seems it lets one create sensors based on SQL Queries. Not just for MySQL databases but for other DB engines as well.
With sensors you should be able to display the retrieved values in Lovelace without a user having to press a button etc.
You will still need a query per entity I guess.

SQL

Forum thread that discusses how to handle query updates.

(Re-)enable scan_interval on the SQL integration

I can confirm that the workaround with manually updating the sensors with homeassistant.update_entity after disabling the polling on the SQL integration did in fact work. Around half of my integration does not have a disable polling switch, but, as you said, they were push integrations. My bad (and, I didn’t even know about that switch anyway, before you told me!). I sometimes do see a use case for manually updating something that is “push”. Some of my power monitoring plugs are updating their …

[actran70 (AndyT)]

Yes, I thought about the above SQL sensor but adding more entities simply to store a timestamp that is already in DB seems to be inefficient.