MySQL Command

[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.

[royf007 (royf)]

First of all, this is a fantastic integration. I’m using it to pull energy usage statistics for a given date range.

I wrote a complex query in my automation using the SQL WITH clause against MariaDB. It always returns an empty response.

The query runs fine directly on the database. Below is an example of this type of query (this simple example shows the issue). The “response” is always empty though.

description: >-
  Test SQL With Clause
mode: single
trigger:
  - platform: state
    entity_id:
      - input_button.run_energy_cost_report_button
condition: []
action:
  - service: mysql_query.query
    data:
      query: |
        WITH ABC AS (SELECT 'Hello' FROM DUAL)
        SELECT * FROM ABC
    response_variable: response
  - service: input_text.set_value
    data:
      value: |
        {{ response }}
    target:
      entity_id: input_text.sql_result

I’m not sure if this is a bug? I’ve since re-written my query to use a sub-select, instead of the WITH clause.

On another note… I don’t think the next issue is an integration bug, but maybe the way Home Assistant works. For testing, I’m trying to set the same input text helper, called input_text.sql_result, (configured for a max of 3000 characters) with the result of a query, and the set_value service is not storing the result in the input text helper.

I’m not sure if Home Assistant can’t handle the length, newlines, special characters, etc. Below is the SQL response JSON I’m trying to set, which is returned from my query:

{'result': [{'date': datetime.date(2024, 5, 29), 'datetime_local':
datetime.datetime(2024, 5, 29, 22, 0, 10, 354330), 'latest_created_ts':
1717038010.3387063, 'statistics_id': 17, 'stat_id_name':
'sensor.4244_consumed_energy_kwh_cost', 'total_sum': 0.41204799999999797,
'mean': None, 'min': None, 'max': None, 'last_reset': None,
'last_reset_ts': 1717032229.294907, 'state': 0.263755999999998, 'sum':
0.41204799999999797}, {'date': datetime.date(2024, 5, 30),
'datetime_local': datetime.datetime(2024, 5, 30, 0, 0, 10, 346312),
'latest_created_ts': 1717041610.3463125, 'statistics_id': 1197,
'stat_id_name': 'sensor.consumed_energy_kwh_cost', 'total_sum':
1.421792, 'mean': None, 'min': None, 'max': None, 'last_reset': None,
'last_reset_ts': 1717032229.294907, 'state': 1.2735, 'sum': 1.421792}]}

I would think that any text can be stored as long as it doesn’t exceed the size limit?

[Yes]

Hi all,

This thread originally belongs to the MySQL Command and although not on purpose, meanwhile it feels like i’ve hijacked it.

I decided to create a separate topic where the MySQL Query component can be discussed.
Please continue over there for MySQL Query related questions or requests.

Thanks for your kindness, tolerating the usage of this thread qrioniclabs

[royf007 (royf)]

Thank you @qrioniclabs as well for letting us converse on this thread.