[SOLVED] Run SQL Query from Rule does not work

Setup, Configuration and Use Scripts & Rules
1
  • Platform information:
    • Hardware: Raspi Pi 3
    • OS: Openhabian (Raspbian)
    • openHAB version: 2.4.0-1
    • MariaDB mysql-Server

Hi community,

this is my first post, so sorry if I miss relevant information.
I would call me still being a beginner with OH, but have quite something already running.

My issue: I want to update a database table via rule and time cron. The called “test switch” to execute the rule will be changed to a gasmeter-sensor, connected to a GPIO as input.
So far I got that stuff running but receiving an (SQL-) error message in the log when I execute the rule (“write to database”). When I issue the SQL-command (mysql -h localhost -u openhabian -pxxxxxxxxxx -e “INSERT INTO PTest (value) VALUES (0.4);” -D openhab_db) via SSH-console, it works. So I don’t really know what to change. Any help appreciated. Many thanks in advance.

Please see in the following my setup:

Item configuration:

Switch GasSwitchtemp "Gasswitchtemp" // to emulate gpio-pin "Gasmeter"
Switch GasSwitchtempDB "GasswitchtempDB" // to emulate Time Cron
Contact Gasmeter "Gasmeter" (Monitoring) { gpio="pin:18" }
Number GasmeterDaily "GasmeterDaily" (Monitoring, PersistenceOnChange)

Rule configuration:

var mysql_host = "localhost"
var mysql_user = "openhabian"
var mysql_pw	= "xxxxxxxxxxx"
var mysql_db	= "openhab_db"

rule "monitor gasmeterdaily" 
    when 
    	Item GasSwitchtemp changed to ON // Item Gasmeter changed to CLOSED in future
    then
    		var updatevalue = (GasmeterDaily.state as Number) + 0.1
    		GasmeterDaily.sendCommand(updatevalue)
    end

rule "write to database"
    when
    	Item GasSwitchtempDB changed to ON //Time cron "0 59 23 1/1 * ? *" in future
    then
    		var nowvalue = (GasmeterDaily.state as Number)
    		var nowvaluestring = nowvalue.toString()
    		var String sql_query = "mysql -h " + mysql_host + " -u " + mysql_user + " -p" + mysql_pw + " -e \"INSERT INTO PTest (value) VALUES (" + nowvaluestring + ");\" -D " + mysql_db
    		var String mysql = executeCommandLine(sql_query, 5000)
    		if(mysql!="") {
    			logInfo("SQL","SQL query to be executed: " + sql_query)
        	        logInfo("SQL","Result SQL query: " + mysql)
    		GasmeterDaily.sendCommand(0)
    		}
    end

Log:

2019-02-10 20:01:36.545 [INFO ] [g.eclipse.smarthome.model.script.SQL] - SQL query to be executed: mysql -h localhost -u openhabian -pxxxxxxxxxx -e "INSERT INTO PTest (value) VALUES (0.4);" -D openhab_db
2019-02-10 20:01:36.550 [INFO ] [g.eclipse.smarthome.model.script.SQL] - Result SQL query: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"INSERT INTO PTest (value) VALUES (0.4);"' at line 1

As mentioned, issuing the “INSERT INTO”-query via SSH-console works fine.
Any help appreciated!

Cheers,
Matthias.

2

Two ideas:

Is the mysql comman in the path of the user that executes openhab, you might try using the whole path of the command.

It might also be necessary to replace the spaces in the command by@.

3

What I don’t understand is why don’t you use the persistence services?

4

Hi guys,

thanks for your replies.
The path might be ok as I reach mysql, but as seen in the log, it states that the query is wrong (but which works from the console). I tried using @'s instead of spaces as well but did not work (but I will give it another try tomorrow).

I use persistence in many cases already, but in this specific case I did not find a proper solution to use persistence: my gasmeter will just change the contact (via GPIO-Input) with every 0.1qm. My approach is to sum up a whole day and store only this one value in the table (just what I need for my purpose). But maybe there’s another approach???

Many thanks and best greetings
Matthias.

5

Create two additional items:

Number GasmeterSum
Number GasmeterSumPerDay

Everytime the gasmeter changes, you add the value to GasmeterSum and with a cron triggered rule you write this value to GasMeterSumPerDay (which is the item you persist in your DB) and at the same time, reset GasmeterSum

So OpenHab sums the values to an item and this gets written once per day into another item which is persisted

1 Like
6

You could create an additional item for the daily value, which is persisted. With the strategy „on every change“ or update this value would be stored on a postUpdate().

That said I would store the value more often, as the values might be interesting later. My gas meter impulses are counted by a KNX counter, which sends a new counter value for every 100l of gas (daily consumption between 0,6qm in the summer and 30qm on cold winter days). My water meter sends a new value for every liter.

I store the counter values and calculated hourly, daily, monthly and yearly values in a PosrgreSQL database, using JDBC persistence. If, in several years, this database gets to big I can still purge unneeded data but after 4 1/2 years, with nearly 500 persisted items, the database size is still only 1,2 GB.

7

Yes, there is. Put the value in an item and use the .persist method or a custom cron strategy just for that item.

8

Hi there,

thanks for the support, guys. I made a step forward, but I’m still not where I want to be.

Yes, using just another Item (for the daily sum) and persist this with a cron strategy was a good idea. I changed it to that option now and it looks promising. thanks @vzorglub, @juelicher and @Sascha_Billian.
(background: I did a complete different approach in the past and had a weird query in GRAFANA, this now makes it much much easier, but sometimes you basically don’t use the straight path because of something being already set up)

Unfortunately I still have the open topic with the SQL query being executed via a rule. The “@” or “@@” instead of spaces did not help. In different variations I tried using @'s and received from mysql help page to java error to the known error (please see above) every kind of error. Using spaces where closest to all tries.

My items are looking now like this:

Items:

Contact Gasmeter "Gasmeter" (Monitoring) { gpio="pin:18" }
Number GasmeterDaily "GasmeterDaily" (Monitoring, PersistenceOnChange)
Number GasmeterDailySum "GasmeterDailySum" (Monitoring, PersistenceOnChange)

I like to persist the “temporary” information of “GasmeterDaily” summing up over the day as well (in case of power loss or maintenance I loose the information), but would like to purge the information after “GasmeterDailySum” is written via cron at the end of the day. Information then is not needed at “GasmeterDaily” anymore and I want to keep the database clean. Is that possible or do I still need a SQL query in the rule?

Long story short: I still would like to run a SQL query fired from a rule. Now not as an INPUT anymore, but as a DELETE.

Many thanks for further support and ideas.
Cheers,
Matthias.

9

If I understand your use case correctly, you could use the mapdb persistence service for the item state of GasmeterDaily that should survive a restart (due to power loss or maintenance), which just stores the latest persisted value and can restore it on startup. No need to delete old entries.

1 Like
10

@noppes123 many thanks for the hint! I did not know that. Sounds exactly what I need.

So a question for my curiosity: anyone any idea why the SQL query does not work? :stuck_out_tongue_winking_eye:

11

I’ve had some struggles with executeCommandLine and the use of spaces or ‘@’ myself. Can you show us what you tried?
I cannot acces my system right now, but will show what I’ve done wit another command tomorrow.

UPDATE: based on a working command in my setup that played havoc using other ways of running it (I’ve tried many combinations of spaces, '@'s, quotes, double quotes, escape codes, etc.), perhaps you could try (perhaps also add the full path to the mysql command):

var String sql_query = "/bin/sh@@-c@@mysql -h " + mysql_host + " -u " + mysql_user + " -p" + mysql_pw + " -e \"INSERT INTO PTest (value) VALUES (" + nowvaluestring + ");\" -D " + mysql_db
var String mysql = executeCommandLine(sql_query, 5000)

The key ‘fix’ was the use of ‘/bin/sh@@-c@@’ to run the command. With '@'s in the actual command string, it wouldn’t run correctly.

12

@noppes123: you’re the man!!! Your update did the trick (to use the full path and exchange spaces with @@'s at the correct places). Changed it to your version and works like a charm (will save it for the future, I assume, I will have use cases for this even if I won’t need it now).

@all: many thanks for your support on this. I changed now the logic for reading and storing the data. Besides of the rules, I received my Gasmeter today, installed it and it works like a charm from the first second. I bought this one: Gasmeter.

Just in case someone is looking for a similar solution. I ended up today with this configuration:

Items:

Contact GasmeterContact "Gasmeter" (Monitoring) { gpio="pin:18" } // Input from Gasmeter
Number GasmeterDaily "GasmeterDaily" (Monitoring, PersistenceOnChange) // Count every click over a day an store in GasmeterDailySum
Number GasmeterDailySum "GasmeterDailySum" (Monitoring, PersistenceOnChange) // To store overall Gas consumption over a day

Rules:

rule "monitor gasmeterdaily" 
when 
	Item GasmeterContact changed to CLOSED
then
		var updatevalue = (GasmeterDaily.state as Number) + 0.01
		GasmeterDaily.sendCommand(updatevalue)
end

rule "write to database"
when
	Time cron "0 50 23 1/1 * ? *"
then
		var update = (GasmeterDaily.state as Number)
		GasmeterDailySum.sendCommand(update)
		GasmeterDaily.sendCommand(0)
end

Sidenote: the meter does not count not every 0.1qm, but every 0.01qm.
In Grafana I draw my graph with the SQL statement:

select 
    UNIX_TIMESTAMP(time) as time_sec,
    (value) as Value,
    'PowerTest' as metric
from PTest 
WHERE $__timeFilter(Time)
group by UNIX_TIMESTAMP(time);

and as kind of smoothing the graph with:

SELECT
  UNIX_TIMESTAMP(TIME) as time_sec,
  AVG(CONVERT(VALUE, double)) as value,
  "Geglättet" as metric
FROM PTest
WHERE $__timeFilter(TIME)
GROUP BY UNIX_TIMESTAMP(time) DIV (2*24*60*60);

As you can see, it’s still a test table with test data and just the basics for showing the data. When it runs as expected, I will change the SQL statements within GRAFANA and will add my historic data I took manually from the Gasmeter over the last 2 1/2 years.

Thanks again for your support, guys!

13

Glad it works! :+1:
Can you please add [SOLVED] to the topic title as well?