Hallo zusammen,
ich habe mir ein DBrep mit einem sqlcmd Statment angelegt.
Internals:
DATABASE fhem
DEF logdb
FUUID 60363f9c-f33f-d79e-0d59-4da7480aeed4f0dc
FVERSION 93_DbRep.pm:v8.52.10-s27746/2023-07-10
LASTCMD sqlCmd SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;
MODEL Client
NAME myrepgas
NOTIFYDEV global,myrepgas
NR 483
NTFY_ORDER 50-myrepgas
ROLE Client
STATE done
TYPE DbRep
UTF8 1
eventCount 76
HELPER:
DBLOGDEVICE logdb
GRANTS UPDATE,SELECT,USAGE,DELETE,INSERT
IDRETRIES 2
MINTS 2020-02-29 00:00:02
PACKAGE main
VERSION 8.52.10
CV:
aggregation no
aggsec 1
destr 2023-10-26
dsstr 2023-09-01
epoch_seconds_end 1698357599
mestr 10
msstr 09
testr 23:59:59
tsstr 00:00:00
wdadd 259200
yestr 2023
ysstr 2023
DBREPCOL:
COLSET 1
DEVICE 64
EVENT 512
READING 64
TYPE 64
UNIT 32
VALUE 128
OLDREADINGS:
READINGS:
2023-10-26 09:26:12 SqlResult {"01":"MAX(TIMESTAMP)|MAX(VALUE)|MIN(TIMESTAMP)|MIN(VALUE)|DIFF|READING","02":"2023-10-13 23:55:10|20714.56|2023-10-13 00:00:47|20714.56|0|total","03":"2023-10-14 23:59:35|20714.56|2023-10-14 00:00:11|20714.56|0|total","04":"2023-10-15 23:59:01|20714.56|2023-10-15 00:04:36|20714.56|0|total","05":"2023-10-16 23:58:28|20714.56|2023-10-16 00:04:02|20714.56|0|total","06":"2023-10-17 23:57:56|20715.92|2023-10-17 00:03:29|20714.56|1.36|total","07":"2023-10-18 23:57:23|20718.345|2023-10-18 00:02:56|20715.92|2.43|total","08":"2023-10-19 23:56:49|20719.785|2023-10-19 00:02:24|20718.345|1.44|total","09":"2023-10-20 23:56:15|20721.54|2023-10-20 00:01:50|20719.785|1.76|total","10":"2023-10-21 23:55:41|20722.99|2023-10-21 00:01:16|20721.54|1.45|total","11":"2023-10-22 23:55:06|20724.76|2023-10-22 00:00:42|20722.99|1.77|total","12":"2023-10-23 23:59:33|20726.685|2023-10-23 00:00:07|20724.76|1.93|total","13":"2023-10-24 23:58:58|20728.175|2023-10-24 00:04:34|20726.685|1.49|total","14":"2023-10-25 23:58:24|20730|2023-10-25 00:03:59|20728.175|1.83|total","15":"2023-10-26 09:25:08|20730.53|2023-10-26 00:03:25|20730|0.53|total"}
2023-10-26 09:26:12 sqlCmd SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;
2023-10-26 09:26:12 sqlResultNumRows 14
2023-10-26 09:26:12 state done
hmccu:
Attributes:
DbLogExclude .*
aggregation day
allowDeletion 1
device CUL_EM_9
diffAccept 10000
limit 1000000
reading total
room DB,Unsorted
sqlCmdHistoryLength 5
sqlCmdVars SET @i:=1, @begin_time=CURDATE()- INTERVAL 13 DAY, @device='CUL_EM_9', @reading='total'
sqlResultFormat json
timestamp_begin previous_month_begin
timestamp_end current_day_end
userExitFn resfromjson
verbose 3
Dies funktioniert auch so wie ich es wollte.
Nun wollte ich dieses DBrep über ein Notify aufrufen.
Internals:
CFGFN
DEF heizeinl {
my @i;
my @begin_time;
my @device;
my @reading;
fhem "SET @i:=1, @begin_time=CURDATE()- INTERVAL 13 DAY, @device='CUL_EM_9', @reading='total'; set myrepgas sqlCmd SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;" ;
}
FUUID 6538d028-f33f-d79e-f435-eac4a131acf4e2ac
NAME Dayheiz
NOTIFYDEV heizeinl
NR 600269
NTFY_ORDER 50-Dayheiz
REGEXP heizeinl
STATE 2023-10-25 17:50:59
TRIGGERTIME 1698249059.45438
TYPE notify
eventCount 9
Helper:
DBLOG:
state:
logdb:
TIME 1698249022.83122
VALUE active
READINGS:
2023-10-25 17:50:22 state active
2023-10-25 17:50:59 triggeredByDev heizeinl
2023-10-25 17:50:59 triggeredByEvent einlesen
hmccu:
Attributes:
room ebus
Hierbei kommt es dann zu folgendem Fehler:
DBD::mysql::st execute failed: 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 'AND READING = AND TIMESTAMP >= and TIMESTAMP <= '2023-10-26 23:59:59' GROUP BY D' at line 1 at ./FHEM/93_DbRep.pm line 11676.
2023-10-26 15:10
Ich habe schon mehrere Versuche gemacht, mit SET Variablen und ohne (setzen sqlCmdVars) ohne Erfolg.
Was mach ich falsch?
Gruß Ulrich
Die Variablen @i, @begin_time etc. sind hier SQL-Variablen, kein Perl.
Du wirst sie im Statement unterbringen und vermutlich die @ escapen müssen.
Versuche es mal so:
DEF heizeinl {
fhem "set myrepgas sqlCmd SET \@i:=1, \@begin_time=CURDATE()- INTERVAL 13 DAY, \@device='CUL_EM_9', \@reading='total'; SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = \@device AND READING = \@reading AND TIMESTAMP >= \@begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;" ;
}
Leider funktioniert die Lösung so nicht:
errortext DBD::mysql::st execute failed: Query was empty at ./FHEM/93_DbRep.pm line 11676. 2023-10-26 18:15:31
sqlCmd SET @i:=1, @begin_time=CURDATE()- INTERVAL 13 DAY, @device='CUL_EM_9', @reading='total';
Ich habe aber eine Lösung gefunden.
Wenn ich die Variablen unter sqlcmdVars eintrage gehts.
Ulrich
Ja, so gehts auch.
Muß aber auch in dem Aufruf funktionieren. Kannst ja noch ein bisschen probieren, z.B. so:
DEF heizeinl {
fhem qw(set myrepgas sqlCmd set \@i:=1; set \@begin_time=CURDATE()- INTERVAL 13 DAY; set \@device="CUL_EM_9"; set \@reading="total"; SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = \@device AND READING = \@reading AND TIMESTAMP >= \@begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;)
}
Der Aufruf läuft fehlerfrei durch bringt aber nur
SqlResult {"1":"MAX(TIMESTAMP)|MAX(VALUE)|MIN(TIMESTAMP)|MIN(VALUE)|DIFF|READING"}
Wenn ich die sqlCmdVar setze dann funktioniert es.
Ulrich
Es hat mir keine Ruhe gelassen und habe das Statement bei mir mit einem at Device probiert.
Es funktioniert einwandfrei mit:
+*01:00:00 set Rep.CPU sqlCmd set @i:=1, @begin_time=CURDATE()-INTERVAL 13 DAY, @device="SMA_Energymeter", @reading="Einspeisung_Wirkleistung_Zaehler";; SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;
Man kann ja ganz einfach das "set" als FHEM Kommando ausführen und braucht das Konstrukt "fhem ("")" nicht.
Außerdem muß man ";" als ";;" escapen.
Bei deinem notify sollte es so klappen:
heizeinl set myrepgas sqlCmd set @i:=1, @begin_time=CURDATE()- INTERVAL 13 DAY, set @device="CUL_EM_9", @reading="total";; SELECT MAX(TIMESTAMP), MAX(VALUE), MIN(TIMESTAMP), MIN(VALUE), round((MAX(VALUE)-MIN(VALUE)), 2) AS Diff, READING FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= §timestamp_end§ GROUP BY DATE(`timestamp`) LIMIT 40;
Ja so scheint es zu gehen.
Einen Fehler habe ich noch entdeckt
heizeinl set myrepgas sqlCmd set @i:=1, @begin_time=CURDATE()- INTERVAL 13 DAY, set @device="CUL_EM_9", @reading="total";;
set muß entfernt werden
vielen Dank so komm ich weiter
Gruß
Ulrich
Ja, das hatte ich noch vergessen zu entfernen.
Na dann viel Erfolg.