![]() The only thing we need to configure in the SQLite node is the database name: If you look at the console you should see the confirmation when the node has finished installing. This is because it usually needs to do a compile which takes time so you need to start the install and wait 5-10 minutes for it to install. Note: When installing the SQLite node on raspberry Pi it appears to have installed but hasn’t. However the code shown here and used in the videos should work with any SQL database. In contrast to other SQL databases it isn’t run as a service and so is much easier to setup and use. SQLite is a simple lightweight database and ideal for using on devices like the Raspberry PI. ![]() There are node-red nodes available for a variety of databases in the video I use the SQlite node and a SQLite database. The interesting and most important point when constructing the command is that text values need to be in quotes, and because we are constructing a string we need to escape them. The SQlite node expects the command in the topic field Msg="(sensor,temperature,humidity) values(" ",\'" sensor "\'," temperature "," humidity ")" The following code snippet shows how we do this: var data="sensor1,temperature,20,humidity,71" ![]() Pass SQL command to the database connector.Extract data elements from incoming data.In our node-red flow we will usually need to construct this command in JavaScript using a function node. Notice the text data is enclosed in quotes. In our simple example we have: Insert Into sensor_data(Sensor,Temperature,Humidity)("sensor1",20,71) Where columns are out column names and values are the values we are assigning to these columns. The command to do this has the form: Insert Into Tablename (columns)(values) To add data to a table we need to use SQL commands. Note: Column names are not case sensitive. When we add our data to the table the table would look like this: Sensor We also need to assign a data type to the values, in our case sensor would be TEXT and temperature and humidity would be Integers ( INT) In our example we would need three columns called: To do that we need to assign names to the columns. To create a row we need to add elements to the columns. Now a table consists of rows and columns. So lets create a database called test.db and a table called sensor_data. ![]() To store this data in a data base we first need a database and a table. So what we have is a device called sensor1 and two key/value pairs: Let’s assume our incoming data payload looked like this: The easiest way to see this is by doing an example. JSON encoded data is also a text string and so can also be stored directly in the database, but more commonly you will want to extract parts of the data to use as database keys (column names). Incoming text data can be stored directly in the database. IOT data from sensors and other devices usually comes as either a text string usually with key value pairs or more commonly as JSON encoded data.ĭatabases accept a variety of data formats the most common are INT and TEXT. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |