SQL integration not accepting SQL statement

Configuration
1

I want to use the SQL - Home Assistant integration.

When adding it in the UI I use the following:

  • name:
    Einträge Multi-Sensoren mit Temperatur -100.0 Grad
  • database URL:
    sqlite:////home/user/.homeassistant/config/home-assistant_v2.db
    (shouldn’t be needed according to docs cause I use the default one - anyway, when leaving it blank the form will complain about it)
  • column:
    Anzahl
  • statement:
SELECT COUNT (*)
FROM 'states'
WHERE entity_id LIKE 'sensor.multi_%_temperature' AND state='-100.0'
ORDER BY entity_id ASC, last_updated DESC;

The problem is: the form doesn’t accept the SQL statement, it always returns “SQL statement invalid” and resets all fields:
grafik

The SQL statement is working just fine in the SQLite Web. I really tried every constellation (used state as column, put the SQL statement in one line, tried " / ', …) - NOTHING is working.
→ What’s wrong with it?
→ Certain syntax needed for the SQL integration?

2

Don’t know if that solves your problem, but it should be

SELECT COUNT (*) as Anzahl

if you want to use column ‘Anzahl’.

3

Thanks for the hint - but unfortunately it changes nothing. That form still refuses to accept that (any?!?) SQL statement.

Gosh what annoying sh*it is this UI integration. I don’t get it. Has anyone ever successfully integrated SQL via the UI? So annoying.

This one is working:
SELECT COUNT (*) AS Anzahl FROM states;

So the problem is with the rest… somewhere…

SELECT COUNT (*) AS Anzahl FROM states WHERE entity_id LIKE sensor.multi_%_temperature AND state=-100.0;

Some say the UI has issues with quotes (see https://github.com/home-assistant/home-assistant.io/issues/23066) - but it’s unclear what it wants instead.

4

Next problem i see is that you order by colums that are not returned by you query.
Try:

SELECT COUNT(*) as Anzahl, entity_id, last_updated
FROM states
WHERE entity_id LIKE 'sensor.multi_%_temperature' AND state='-100.0'
ORDER BY entity_id ASC, last_updated DESC;

BTW, i also removed the space in count (*) and the quotes around 'states' to test your query without errors in my sql client.
I use mariadb, but most of the syntax is mostly the same.

5

I don’t use that initial SQL statement as the ordering is useless when counting.

It must be a bug in the UI because:

  1. Create a new SQL integration with SQL statement
    SELECT COUNT (*) AS Anzahl FROM states;
    → That works just fine, integration is being created :white_check_mark:
  2. Change the configuration of the existing SQL integration by changing the SQL statement to
    SELECT COUNT (*) AS Anzahl FROM states WHERE "entity_id" LIKE "sensor.multi_%_temperature" AND state="-100.0";
    → Now this is also working! :white_check_mark: Which it doesn’t when being used at the integration form.

So to summarize:

  1. The exact same SQL statement is not accepted when being used initially.
  2. The exact same SQL statement is accepted when changing an existing SQL integration configuration.
  3. I can not confirm the “rumors” about double quotes, single quotes, no quotes - as my double quotes example finally worked (on the 2nd attempt).

I’d tend to call this either an UI integration or documentation disaster - or there’s another issue specific to my setup, which I don’t think so.

6

I have similar issue here: Even the simplest SQL statement is not accepted at all. here is my example. What Im I doing wrong?

HA_SQL
HA_SQL515×796 29.2 KB

7

I meanwhile created a few other sensor. It always behaves the same (weird). Unfortunately I don’t remember the exact „trick“ which worked and works for me.

According to your screenshot I‘d say at least the database URL is missing. What error/message is shown?

That’s the last one I created. UI shows the configure/edit mode once it has been created. So I think database was always needed:

Bild
Bild1125×2248 166 KB

8

There we go: although the UI says: ‘Leave DB Url empty for standard HA database’, It appears I do have to fill it in completely (including credentials for my SQL connection). Thanks!

1 Like