This was achieved with the following code. This gives a large array of data that needs to be sorted accordingly to the chart format. The input to this function is all the values in the DB-table given from a SQL command msg.topic='SELECT * FROM mydbtable', where asterisk means all columns and rows. This is done in an input form (min and max values in the for-loop). On the dashboard the user can set a range of data they want displayed in the chart. To make a chart of the data from the DB, the data input to the chart needs to follow the format showed in picture 5. Var dateString = year + "-" + (month + 1) + "-" + date + " " + hours + "-" + minutes+ "-" + seconds To give Timestamp the right format this code was used. The Timestamp needed to be sent as a string otherwise Node-Red would read the variable as a calculation and give an error due to the space in my Timestamp. I had some problems with the Timestamp as Node-Red's datatype didn't match, so I had to do it myself. The values need to match the datatypes defined in the MySQL DB. Is a SQL command and it’s self-explanatory.
} “INSERT INTO ”tablename” VALUES(column1,column2)” Msg.topic = 'INSERT INTO mydbtable VALUES ('+ "'" + TimeStamp + "'" + ', ' + InputValue + ')' The data from the Arduino is stored in the SQL DB and is executed every 5 seconds. Running this command in the SQL Query fixed it “ALTER USER IDENTIFIED WITH mysql_native_password BY 'password'” I had a problem where I couldn’t connect to the DB. The MySQL DB nodes needs to be setup accordingly to your MySQL server. Now the variable can be used in other functions later on.
I output the integer as an global variable, in a function, with this command This integer can now be used in a gauge etc. The data I receive from the Arduino is in a string and needs to be changed to an integer, I do this in a change node with the following input. The topic is used in the Arduino either to subscribe or publish data, in this project “room/temperature” is the data I publish from the Arduino. MQTT nodes need to be connected to the host IP (in my project it's localhost), port 1883 (standard). I’ve created a DB called mydb and a table called mydbtable with two columns, Tid and Val, Tid has the datatype DATETIME which has this format “YYYY-MM-DD HH:MM:SS” and Val is an INT(11).
To upload the code, you might need to hold the boot button while its connecting, after uploading press the enable button. The Arduino code is commented further down in the code section. In this section i will primarly focus on the Node-Red code.
0 kommentar(er)
