Publish Sensor Data to SQL

Hey everyone. Newb here and have a question regarding a project I'm working on.

The idea is to utilize Arduino/Lora Gateway to retrieve sensor data from Lora Nodes periodically, probably once an hour. Water Tank Level Data to be more specific.

I have SCADA software that I plan to use for the website/display portion of this. The SCADA software can communicate with Databases so my question is, would I be able to publish data from the nodes to a SQL Server? From there, I can use my Display software to retrieve and display the data.

So far with this project I have been able to publish sensor data from the Lora nodes to ThingSpeak Iot Server. I'd like to remove ThingSpeak as the middle man if I could and set up some sort of database of my own to store the data, then use my software to display the data for my users. I don't believe the software I am using works with REST API, or else I'd just extract the data that way. I'm also exploring the idea of MQTT and running my own broker/server but have a lot of research before I get there.

Does anyone have experience with publishing sensor data to their own server and displaying on their own server? I'd love some advise on this project and to give you a quick run down on the project, I have some close friends and family that have oil and they are interested in a cheap solution to monitor their oil tanks. Like super cheap, so I thought I'd play around with Arduino/Lora technology and so far I'm impressed, but I could really use some help in the data storage/publishing portion of this project. Thanks.

Hi @Cranda, I'm just getting into LoRa myself at the moment, so I would be interested to see how you have approached it in more detail.

The sensors I have built so far have used WiFi, so the range is pretty short and the battery life could be better. My sensors update a remote webserver that hosts a MySQL database. The Arduino sends an HTTP GET request, which is picked up by a PHP script running on the webserver. The PHP script inserts the data into the database. Other PHP scripts create web pages to view the data in table or graph format.

I have found out since I constructed these sensors that there is an Arduino library which can directly connect to and insert data into a remote MySQL database, removing the need for the HTTP requests and one of the PHP scripts. I must get around to investigating that further.

All the sketches and scripts are available on my github page.

I use ESP8266 often but I guess as long as your MCU/hardware can form an HTTP POST request, then a simple PHP/SQL back end would suffice.

Amazon Web Services do a free tier "Beanstalk" instance which gives you some SQL databases and a LAMP (PHP) server to host your python/PHP whatever scripts.

I had the same thing set up with an arduino reading temperatures and sending them over wifi via a GET request to a LAMP server I had running, the P in LAMP being perl in my case. The data gets stored in mysql and there are a few graphs served up. This was set up some years ago & the server is a full size PC; these days I'd use a Pi.

Assuming you have an Internet connection of some sort, install the MySQL connector library and you can access it directly (direct connection to the server, get a cursor, start firing SQL commands to the server and read back the response through your cursor).