Gelöst - dbRep sqlCmd

JF Mennedy · 17 September 2020, 15:16:42

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.

Code Auswählen

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

ch.eick · 17 September 2020, 16:39:00

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

DS_Starter · 17 September 2020, 20:14:17

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:

Code Auswählen


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:

Code Auswählen


....
"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:

Code Auswählen


"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

JF Mennedy · 18 September 2020, 07:59:45

Hallo und Danke für die schnelle Hilfe

Die Syntax scheint jetzt durchzugehen, aber ich habe noch ein Problem mit den Zugriffsrechten:

Code Auswählen

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

DS_Starter · 18 September 2020, 08:24:20

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

JF Mennedy · 18 September 2020, 12:23:45

Prima 😊 hab auch noch dieses Problemchen gelöst und es funktioniert wirklich super 👌👌👌 ganz grosses Dankeschön für die Unterstützung 👏👏

JF Mennedy · 18 September 2020, 12:57:54

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

Code Auswählen

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:

Code Auswählen

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

JF Mennedy · 18 September 2020, 13:04:54

OK Ich habs gefunden...

Aus dem AT müssen die Semikolons noch escaped werden

Code Auswählen

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:

DS_Starter · 18 September 2020, 13:11:48

Jetzt sind es aber schon eine ganze Menge Semikolons.

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

ch.eick · 18 September 2020, 16:24:42

@JF Mennedy: Kannst du bitte auch die notwendigen Datenbankeinstellungen für die Berechtigung gleich hier mit rein stellen?

JF Mennedy · 18 September 2020, 18:04:49

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

JF Mennedy · 21 September 2020, 07:55:43

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:

Code Auswählen

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

DS_Starter · 21 September 2020, 08:15:33

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

JF Mennedy · 21 September 2020, 08:24:55

Hier das list von at device:

Code Auswählen

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:

Code Auswählen

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;

DS_Starter · 21 September 2020, 08:35:17

So funktioniert es in der Kommandozeile:

Code Auswählen


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