Hallo,
folgender SQL Befehl wird aus dem Terminal ausgeführt und erstellt mir eine CSV Datei:
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/opt/fhem/export/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';
");
PREPARE statement FROM @CMD;
EXECUTE statement;
Wenn ich allerdings in dbRep den Befehl über sqlCmd aufrufe, wird es nicht ausgeführt und dbRep zeigt error an.
DBD::mysql::db do 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 '"' FIELDS ENCLOSED BY '\"' TERMINATED BY '' at line 1 at ./FHEM/93_DbRep.pm line 6225.
Muss ich hier irgendwas beachten bezgl escapen von Zeichen?
Gruss Jan
Hallo Jan,
ich könnte mir vorstellen, dass die Maskierung mit '\"' nicht ausreichend ist.
Ein Versuch wäre \\ also doppelt maskieren.
TERMINATED BY '' <<< hier wurde auch das ; geschluckt. In FHEM muss oft auch ;; geschrieben werden oder eventuell auch ein \; , damit FHEM es nicht schluckt.
Gruß
Christian
Hallo Jan & Christian,
es gibt doch immer wieder etwas Neues. ;)
Dieses Konstrukt mit einem PREPARE statement hatte ich noch nicht in DbRep abgebildet.
In meinem contrib liegt eine erweiterte Version zum Test bereit.
Außerdem muß man ";" die nicht zur Trennung von SQL Befehlen verwendet werden, durch ";;" maskieren.
Folgendes Beispiel zeigt die zu verwendende Syntax:
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/volume1/ApplicationBackup/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhemtest`.`history`
WHERE `DEVICE`='SMA_Energymeter' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;
");
PREPARE statement FROM @CMD;
EXECUTE statement;
Ich habe es bei mir erfolgreich getestet. Im Ergebnis werden solche Datensätze geschrieben:
....
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Bezug_WirkP_Zaehler_Diff: 0.0097";"Bezug_WirkP_Zaehler_Diff";"0.0097";""
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Bezug_WirkP_Kosten_Diff: 0.0026";"Bezug_WirkP_Kosten_Diff";"0.0026";""
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Einspeisung_WirkP_Zaehler_Diff: 0";"Einspeisung_WirkP_Zaehler_Diff";"0";""
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Einspeisung_WirkP_Verguet_Diff: 0.0000";"Einspeisung_WirkP_Verguet_Diff";"0.0000";""
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"-579.1";"state";"-579.1";"W"
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Saldo_Wirkleistung: -579.1";"Saldo_Wirkleistung";"-579.1";"W"
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Bezug_Wirkleistung: 579.1";"Bezug_Wirkleistung";"579.1";"W"
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Bezug_Wirkleistung_Zaehler: 3635.3867";"Bezug_Wirkleistung_Zaehler";"3635.3867";"kWh"
"2017-12-01 22:48:55";"SMA_Energymeter";"SMAEM";"Einspeisung_Wirkleistung_Zaehler: 5710.9842";"Einspeisung_Wirkleistung_Zaehler";"5710.9842";"
kWh"
....
Zum Download in der FHEMWEB Kommandozeile inklusive der Ausführungszeichen angeben und danach FHEM restarten:
"wget -qO ./FHEM/93_DbRep.pm https://svn.fhem.de/fhem/trunk/fhem/contrib/DS_Starter/93_DbRep.pm"
Edit: Ich denke das wäre wieder ein schönes Script zur Ergänzung des Wiki.
Grüße,
Heiko
Hallo und Danke für die schnelle Hilfe :-)
Die Syntax scheint jetzt durchzugehen, aber ich habe noch ein Problem mit den Zugriffsrechten:
2020.09.18 07:49:33 4: DbRep DBLoggingRep - -------- New selection ---------
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Command: sqlCmd SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d'); SET @FOLDER = '/opt/fhem/export/'; SET @PREFIX = 'export'; SET @EXT = '.csv'; SET @CMD = CONCAT(" SELECT * FROM `fhem`.`history` WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY) INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';;' ESCAPED BY '\"'"," LINES TERMINATED BY '\r\n';; "); PREPARE statement FROM @CMD; EXECUTE statement;
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Timestamp begin human readable: not set
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Timestamp end human readable: not set
2020.09.18 07:49:33 4: DbRep DBLoggingRep - database user for operation: fhemuser
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Set SQL session variable: SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Set SQL session variable: SET @FOLDER = '/opt/fhem/export/';
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Set SQL session variable: SET @PREFIX = 'export';
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Set SQL session variable: SET @EXT = '.csv';
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Set SQL session variable: SET @CMD = CONCAT(" SELECT * FROM `fhem`.`history` WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY) INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'"," LINES TERMINATED BY '\r\n'; ");
2020.09.18 07:49:33 4: DbRep DBLoggingRep - Exec PREPARE statement: PREPARE statement FROM @CMD;
2020.09.18 07:49:33 2: DbRep DBLoggingRep - ERROR - DBD::mysql::db do failed: Access denied for user 'fhemuser'@'%' (using password: YES) at ./FHEM/93_DbRep.pm line 6238.
Gruss Jan
Moin Jan,
ja gut, das ist jetzt ein Sache der Userberechtigung in der DB.
Du müsstest mal schauen, ob dein verwendeter User von dem verwendeten Rechner aus auf die DB zugreifen darf und ob er die benötigten Rechte hat.
Zugriff scheint mir prinzipiell gegeben zu sein weil die SET Kommandos funktionieren. Vielleicht fehlt nur ein nötiges Recht.
Grüße,
Heiko
Prima 😊 hab auch noch dieses Problemchen gelöst und es funktioniert wirklich super 👌👌👌 ganz grosses Dankeschön für die Unterstützung 👏👏
Jetzt hab ich doch noch eine Frage...
Wie bekomme ich das Konstrukt denn aus einem AT-Device geladen? Aus dem AT Device stoppt die Abfrage nach dem 1. Befehl...
Internals:
CFGFN ./log/fhem.save
COMMAND set DBLoggingRep sqlCmd
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/opt/fhem/export/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;
");
PREPARE statement FROM @CMD;
EXECUTE statement;)
DEF 2020-09-19T01:00:00 set DBLoggingRep sqlCmd
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/opt/fhem/export/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;
");
PREPARE statement FROM @CMD;
EXECUTE statement;)
FUUID 5f648c81-f33f-e2c3-3598-4ec1c1d317e841a0
NAME Export_Ernster_Eglise_Elec
NR 17
PERIODIC no
RELATIVE no
STATE Next: 2020-09-19 01:00:00
TIMESPEC 2020-09-19T01:00:00
TRIGGERTIME 1600470000
TRIGGERTIME_FMT 2020-09-19 01:00:00
TYPE at
VOLATILE 1
Helper:
DBLOG:
state:
DBLogging:
TIME 1600426570.33737
VALUE execNow
READINGS:
2020-09-18 12:56:04 state Next: 2020-09-19 01:00:00
Attributes:
Internals:
DATABASE fhem
DEF DBLogging
FUUID 5f647ee1-f33f-e2c3-70f5-3ecb1c74e3bd467d
FVERSION 93_DbRep.pm:v8.40.8-s22733/2020-09-04
LASTCMD sqlCmd SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d')
MODEL Client
NAME DBLoggingRep
NOTIFYDEV global,DBLoggingRep
NR 15
NTFY_ORDER 50-DBLoggingRep
ROLE Client
STATE done
TYPE DbRep
UTF8 0
HELPER:
DBLOGDEVICE DBLogging
GRANTS ALL PRIVILEGES
IDRETRIES 3
MINTS 2020-09-18 11:24:39
PACKAGE main
SQLHIST
VERSION 8.40.8
DBREPCOL:
COLSET 1
DEVICE 64
EVENT 512
READING 64
TYPE 64
UNIT 32
VALUE 128
OLDREADINGS:
READINGS:
2020-09-18 12:59:33 SqlResultRow_1 0E0
2020-09-18 12:59:33 sqlCmd SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d')
2020-09-18 12:59:33 sqlResultNumRows 0
2020-09-18 12:59:33 state done
Attributes:
DbLogExclude .*
alias DBLoggingRep
allowDeletion 1
event-on-change-reading state
verbose 0
Gruss Jan
OK Ich habs gefunden...
Aus dem AT müssen die Semikolons noch escaped werden :-)
Internals:
CFGFN ./log/fhem.save
COMMAND set DBLoggingRep sqlCmd
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');;
SET @FOLDER = '/opt/fhem/export/';;
SET @PREFIX = 'export';;
SET @EXT = '.csv';;
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;;;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;;;
");;
PREPARE statement FROM @CMD;;
EXECUTE statement;
DEF 2020-09-19T01:00:00 set DBLoggingRep sqlCmd
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');;
SET @FOLDER = '/opt/fhem/export/';;
SET @PREFIX = 'export';;
SET @EXT = '.csv';;
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;;;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;;;
");;
PREPARE statement FROM @CMD;;
EXECUTE statement;
FUUID 5f648c81-f33f-e2c3-3598-4ec1c1d317e841a0
NAME Export_Ernster_Eglise_Elec
NR 17
PERIODIC no
RELATIVE no
STATE Next: 2020-09-19 01:00:00
TIMESPEC 2020-09-19T01:00:00
TRIGGERTIME 1600470000
TRIGGERTIME_FMT 2020-09-19 01:00:00
TYPE at
VOLATILE 1
Helper:
DBLOG:
state:
DBLogging:
TIME 1600427003.61177
VALUE execNow
READINGS:
2020-09-18 13:02:55 state Next: 2020-09-19 01:00:00
Attributes:
Jetzt sind es aber schon eine ganze Menge Semikolons. ;D
Ich habe den Code nochmal etwas umgeschrieben um eventuelle Seiteneffekte zum bisherigen Verhalten zu vermeiden.
Kannst du das Modul bitte nochmal aus dem contrib laden und testen ?
Danke und LG,
Heiko
@JF Mennedy: Kannst du bitte auch die notwendigen Datenbankeinstellungen für die Berechtigung gleich hier mit rein stellen?
Bin erst Montag wieder da. Dann kann ich das testen... Ich habe fhemuser all privileges zugeteilt und das Export Verzeichnis gehört fhem:dialout mit Berechtigung 777... Da auf dem System nur der Export von Daten, die über httpmod reinkommen, ausgeführt wird, mache ich mir erstmal keine Gedanken über zuviele rootrechte.. Der Inhalt der csv Datei wird im Nachgang noch zerhackstückelt und in eine weitere Datei geschrieben, die dann in einem speziellen Format per Mail verschickt werden soll...
Schönes Wochenende :-)
Gruss Jan
Guten Morgen,
mit der neuen Version lässt sich der Befehl nicht mehr aus einem AT Device ausführen:
Hier der Auszug aus dem LOG:
2020.09.21 07:54:16 3: export_Ernster_Eglise_Elec: Please define @FOLDER first
Please define @PREFIX first
Please define @EXT first
Please define @CMD first
Unknown command PREPARE, try help.
Unknown command EXECUTE, try help.
Gruss Jan
Guten Morgen,
sieht mir eher nach einer fehlerhaften DEF des at-Devices aus. Zum Beispiel werden PREPARE und EXECUTE als FHEM-Befehle interpretiert.
Zeig uns mal bitte dein AT.
Grüße,
Heiko
Hier das list von at device:
Internals:
COMMAND set DBLoggingRep sqlCmd
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/opt/fhem/export/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;");
PREPARE statement FROM @CMD;
EXECUTE statement;
DEF *01:00:00 set DBLoggingRep sqlCmd
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/opt/fhem/export/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhem`.`history`
WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;");
PREPARE statement FROM @CMD;
EXECUTE statement;
FUUID 5f683e35-f33f-e2c3-6ef5-d43394b7c002ba52
NAME export_Ernster_Eglise_Elec
NR 18
PERIODIC yes
RELATIVE no
REP -1
STATE Next: 01:00:00
TIMESPEC 01:00:00
TRIGGERTIME 1600729200
TRIGGERTIME_FMT 2020-09-22 01:00:00
TYPE at
Helper:
DBLOG:
state:
DBLogging:
TIME 1600667656.14957
VALUE execNow
READINGS:
2020-09-21 07:54:09 state Next: 01:00:00
Attributes:
group Ernster_Eglise
room EIPortal
Das passiert aber auch, wenn ich den Befehl aus der Kommandozeile ausführe:
set DBLoggingRep sqlCmd SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d'); SET @FOLDER = '/opt/fhem/export/'; SET @PREFIX = 'export'; SET @EXT = '.csv'; SET @CMD = CONCAT(" SELECT * FROM `fhem`.`history` WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY) INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';;' ESCAPED BY '\"'"," LINES TERMINATED BY '\r\n';;"); PREPARE statement FROM @CMD; EXECUTE statement;
So funktioniert es in der Kommandozeile:
set Rep.Temp sqlCmd SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');; SET @FOLDER = '/opt/fhem/export/';; SET @PREFIX = 'export';; SET @EXT = '.csv';; SET @CMD = CONCAT(" SELECT * FROM `fhem`.`history` WHERE `DEVICE`='Ernster_Eglise_Elec' AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY) INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';;;;' ESCAPED BY '\"'"," LINES TERMINATED BY '\r\n';;;;");; PREPARE statement FROM @CMD;; EXECUTE statement;;"
Ich muß jetzt aber erstmal los ...
LG,
Heiko
Ah ok,
ich dachte in der neuen Version musste ich die ganzen Semikolons wieder rausholen...
So geht es bei mir dann auch...
Gruss Jan
Sehr schön. :)
Die Vervielfältigung der Semikolons kann ich leider nicht beeinflussen. Das ist FHEM systemisch bedingt.
Dann checke ich die Version heute Abend ein und ist morgen früh im Regelupdate enthalten.
Grüße,
Heiko
Kleiner Nachtrag,
ich habe die benötigten Rechte gecheckt... der User braucht das FILE Recht.
Außerdem habe ich das Statement in einem Wiki (https://wiki.fhem.de/wiki/DbRep_-_Reporting_und_Management_von_DbLog-Datenbankinhalten#Device_.2F_Reading_Daten_in_eine_CSV-Datei_exportieren_.28MySQL.29) Artikel niedergeschrieben.
Was mir aufgefallen ist ...der Teil des Statements
AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
zieht bei mir nicht. D.h. werden alle Daten des Device/Readings exportiert und nicht nur des letzten Tages.Ich hatte aber bisher nicht die Muße mich damit zu beschäftigen.
Vllt. seht ihr den Grund.
Grüße,Heiko
Ich habe auch noch den selben Effekt, dass alle Werte abgeholt werden. Die syntax ist aber eigentlich in Ordnung... Versuche das grade in die myUtils auszulagern... Habe hier aber noch Probleme das zusammenzusetzen...
Ich habe die Befehle jetzt mal direkt im SQL Editor von phpMyAdmin ausführen lassen.
Das Ergebnis ist das gleiche, es wird auch nicht nur der letzte Tag exportiert, sondern alle Tage.
An der Syntax stimmt also etwas noch nicht, bin allerdings auch der Meinung dass sie so richtig sein müsste.
Irgendwas übersehen wir.
Moin,
hatte Tomaten auf den Augen ... TIMESTAMP hat natürlich gefehlt. So ist es richtig:
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d');
SET @FOLDER = '/volume1/ApplicationBackup/';
SET @PREFIX = 'export';
SET @EXT = '.csv';
SET @CMD = CONCAT(" SELECT *
FROM `fhemtest`.`history`
WHERE `DEVICE`='SMA_Energymeter' AND TIMESTAMP > DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"'
FIELDS ENCLOSED BY '\"'
TERMINATED BY ';;'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';;
");
PREPARE statement FROM @CMD;
EXECUTE statement;