10x ESP8266 + database server app

Projects General Guidance
1

Hello there :slight_smile:
I am preparing wireless system based on 10 ESP8266 running Arduino IDE which will be reading RFID tags and send tag numbers into the database server app in the local network via Wi-Fi.

  1. ESP 8266-12
    Like I said before, I have ten ESP8266-12, and TowiTek RFID reader which use one GPIO pin of the ESP8266-12. When the RFID tag is in the range of the TowiTek reader ESP8266-12 stores tag ID once a second. If any tag is in the range of the TowiTek reader the ESP stores nothing in my ID variable. Every ESP is connected to the one wireless network made by my home router. Each ESP has own number.

  2. Database Server App
    It runs 24/7 and has to send requests to every ESP once a minute sequentially in loop or simultaneously (if possible?) to read the state of the reader. The reader should send answer with the stored ID variable via wifi of course. Db server stores record in database with the ESP number, tag number and date.
    Application will be installed on the touchscreen windows/android device connected to the same network as the all ESP's. Data from the base will be prepared by the application for my own purposes.

What I've already done.

  • I am able to store the RFID tag with my ESP8266-12 using Arduino IDE,
  • I am able to connect my wifi network

What I'm asking about

  • The DB server app asking every ESP for its state once a minute and writing record in database when getting answer. Isn't better each other ESP8266 sending data to the DB server once a minute? In the second case are there any possibilities of data collisions?
  • Which system will be better for my database application Windows or Android? SQL? MySQL?
  • How should I send the data from ESP to my SQL server? With AT commands? Could you link here some libraries? Examples?

I think that now I should begin with searching for the SQL database app or write it myself. But I need your guide to know which way I should go.

2

There is no reason to wait 1 minute to talk to the server, you can send a new ID to server app as soon as the ESP reads it.

Open a TCP socket to the server from each ESP and write a line every time a new ID is read.

Regarding server's system I would recommend a raspberry PI or clone. Its reliability and low power demand will be perfect for the 24/7 operation. You can power it from a 12V battery if uptime is critical.
As for databases and SQL you have lots of free options, mysql is one of them. Others I recall are sqlite and postgress. And you can choose from lots of languages, java, C, python, tcl, ruby and others ... whichever you know or want to learn. It depends more upon what you want to do with the data after you store it in the database.

Or use Windows if you find it more comfortable. Android isn't really designed to run server applications. not that you can't but it's more complicated than using a PI or similar linux based board.

3

wireless system based on 10 ESP8266 running Arduino IDE

That's not going to happen. The ESP8266 may run sketches created using the Arduino IDE, but they can't run the IDE.

It runs 24/7 and has to send requests to every ESP once a minute sequentially in loop or simultaneously (if possible?)

Far less network traffic if each ESP sends data when it sees a change in tag.

Isn't better each other ESP8266 sending data to the DB server once a minute?

That's better, but best is sending data only when there is a change.

In the second case are there any possibilities of data collisions?

Yes, but the server should be able to handle 10 clients.

Which system will be better for my database application Windows or Android?

The one that you can get a database application for, and that you are familiar with.

SQL? MySQL?

Both are good. MySQL has always been free, regardless of how you use it. SQL (from Microsoft) is now free for non-money-making uses. If you plan to make money, plan to pay Microsoft (even if you don't actually make any money).

How should I send the data from ESP to my SQL server?

You don't. You send GET requests to the server which define a script to be run and some data to pass to the script. The script deals with the database.

With AT commands? Could you link here some libraries? Examples?

For someone with no experience with the ESP devices, I think you may be in over your head.

I think that now I should begin with searching for the SQL database app or write it myself.

I would think that, if you were capable of writing a relational database application that understood SQL commands, you wouldn't need to be asking these questions. I think you need to plan to use one that other people have already written.

4

Thanks for reply @blimpyway

blimpyway:
Open a TCP socket to the server from each ESP and write a line every time a new ID is read.

Do I have to close connection every time after the data has been sent?

blimpyway:
Regarding server's system I would recommend a raspberry PI or clone. Its reliability and low power demand will be perfect for the 24/7 operation. You can power it from a 12V battery if uptime is critical.
As for databases and SQL you have lots of free options, mysql is one of them. Others I recall are sqlite and postgress. And you can choose from lots of languages, java, C, python, tcl, ruby and others ... whichever you know or want to learn. It depends more upon what you want to do with the data after you store it in the database.

Or use Windows if you find it more comfortable. Android isn't really designed to run server applications. not that you can't but it's more complicated than using a PI or similar linux based board.

I think that Windows will be the most comfortable for me. So tell me am I thinking right... I should start from running MySQL database service, make a new database and finally start to write an application? I think that C# will be fine for me.

Should I send the data from ESP directly to the database? Sending it through the server socket then receive it and put in the database made by my application won't be easier?

PaulS:
I would think that, if you were capable of writing a relational database application that understood SQL commands, you wouldn't need to be asking these questions. I think you need to plan to use one that other people have already written.

I agree with you, but I have so much time to learn :slight_smile: Do you know any projects that I can take an example from?

5

Do you know any projects that I can take an example from?

The entire source code for MySQL is available.

6

If you can use Arduino IDE to write arduino compiled C program

  1. To an arduino UNO or equivalent that links via serial to an ESP for networking. In this case arduino UNO "tells" ESP how/where to connect via AT commands.

  2. To an ESP board directly, by using network library directly. In this case there are no AT commands,

The two modes above are mutually exclusive, your description suggests you want to use 2., but you have to learn more.

How the data gets into database. Unless you can find SQL drivers for ESP to write directly to the database (and I doubt there are any), you need write a server app that acts as middle man - it receives data from the 10 ESPs and uses appropriate SQL library, or SQL queries to write that data into the database.

That small app you'll have to write yourself, it isn't difficult but you have to learn how to use networking and SQL libraries for the database you choose.

BTW "SQL" is an acronym for "Structured Query Language" and most databases use it. When you refer to Microsoft's SQL server, you have to be more specific.

7

If it all in your own local network, that is the ESP8266 devices and the database server which collects information from them, then you are a bit freer with your design because you don't have to worry about configuring your router for port forwarding etc.

However, I'd recommend anyway that instead of the server polling the individual ESP8266 devices, that the ESP8266 devices regularly send data direct to the server.

It could work like this:
An ESP8266 device regularly issues a formatted URL which contains the server address and the parameters you want to send.
This page is sent by an HTTP Post or Get request.
On the server, the PHP page referenced by the URL is executed. It parses the data and writes it to a table in a mySQL database.

There are also other technologies for achieving the same thing.

8

I would suggest windows VB.net app what listens for traffic on many udp ports and writes data to an sql database

each esp sends its data down a unique port number