From c source to cloud with ESP8266

There are many steps from c source code to cloud data storage. Let’s start…

“To get the end, you must go back to the start” – Grandmother Yelsh

In this contribution I will describe how I started using Espressif ESP8266 with Eclipse and some other details how to store data in “usual” web server. I will not talk about the hardware too much. To follow the tutorial it is enough to have any ESP8266 module connected to serial port on PC either via USB/UART virtual cvom port or directly (rare these days). Two most common possibilities are either module with the USB/UART integrated on-board or separated USB/UART + ESP8266-xx module (usually on breadboard with some additional periphery):

Two most common types of dev.boards for ESP8266

0. There is an “Unofficial Development Kit for Espressif ESP8266” prepared by Mikhail Grigorev

The kit includes the following components and tools:
– Unofficial GCC compiler for SoC Xtensa LX106.
– Official Espressif ESP8266 SDK
Documentation for SoC ESP8266.
– Examples in the firmware source code in C language.
– Additional tools for working with boards based on the ESP8266.

Downloads are available from Mikhail’s web page.

1. First install all tools and software exactly as written by Mikhail

Instructions for installing and configuring the Unofficial Development Kit for Espressif ESP8266:
1. Download the Unofficial Development Kit for Espressif ESP8266 (148Mb) and install.
2. Download and install the Java Runtime x86 or x64 (jre-8uXXX-windows-xxx.exe)
3. Download and install Eclipse Neon x86 or Eclipse Neon x86_64 for the development of C ++. Extract the archive to the root of drive C.
4. Download and install MinGW. Run mingw-get-setup.exe, the installation process to select without GUI, ie uncheck “… also install support for the graphical user interface”.
5. Download the Mikhail scripts to automate the installation MinGW packages.
6. Run the install-mingw-package.bat file from step 5. It will download from Mikhail’s website packages for MinGW (~90 Mb) and install the basic modules for MinGW. Download pre-loaded packages for MinGW files ensures that they will be installed, sometimes the server where MinGW packages are is no longer available and the required packages may not be installed.
7. Start the Eclipse from the directory c:\eclipse\eclipse.exe
8. In Eclipse, select File -> Import -> General -> Existing Project into Workspace, in the line Select root directory, select the directory C:\Espressif\examples and import work projects.

 

After installation, the Eclipse IDE should look something like this:

Eclipse Neon after installation and import ESP8266 examples

 

2. Make simple example project (Sending “Hello world” to UART – from scratch)

Now we can write our first “hello world” example. Here are step-by-step instructions (PDF file, suitable for printing):

Click here for PDF file.

3. Prepare the server

The simple example is running. Let’s make some useful project now. I will explain how to prepare the server side followed by ESP8266 source code to send some data to it.

As a researcher I have free access to some infrastructure for testing and non-profit operations. All my work here is non-profit and presented mostly for educational purposes. I created virtual machine with debian distribution of linux (only console). It’s very similar to raspberry pi with raspbian.

There are many excellent resources how to install the apache, MySQL and PHP to your favorite hardware and it’s no need to repeat all those instructions. Here are some links:

http://howtolamp.com/

https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-16-04

https://www.cloudways.com/blog/install-lamp-on-debian/

https://www.howtoforge.com/tutorial/install-apache-with-php-and-mysql-on-ubuntu-16-04-lamp/

Just type “install LAMP”  in google search.

After this process there should be running apache with PHP and MySQL and some database management interface, most common is phpMyAdmin.

Anyway here’s step-by-step tutorial I followed:

Before running through the steps of this tutorial, make sure that 
all of your repositories are up to date:

apt-get update

With that completed, go ahead and start installing the LAMP server.
Step One—Install Apache

Apache is a free open source software which runs over 50% of the world’s 
web servers.
To install apache, open terminal and type in these commands:

apt-get install apache2

That’s it. To check if Apache is installed on your VPS, direct your 
browser to your server’s IP address (eg. http://12.34.56.789). 
The page should display the words “It works!" like this.
How to Find your Server’s IP address

You can run the following command to reveal your VPS's IP address.

ifconfig eth0 | grep inet | awk '{ print $2 }'

Step Two—Install MySQL

MySQL is a widely-deployed database management system used for 
organizing and retrieving data.

To install MySQL, open terminal and type in these commands:

apt-get install mysql-server

During the installation, MySQL will ask you to set a root password. 
If you miss the chance to set the password 
while the program is installing, it is very easy to set the 
password later from within the MySQL shell.
Finish up by running the MySQL set up script:

mysql_secure_installation

The prompt will ask you for your current root password.
Type it in.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Then the prompt will ask you if you want to change the root password. 
Go ahead and choose N and move on to the next steps.

It’s easiest just to say Yes to all the options. At the end, MySQL 
will reload and implement the new changes.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

Once you're done with that you can finish up by installing PHP 
on your virtual server.
Step Three—Install PHP

PHP is an open source web scripting language that is 
widely use to build dynamic webpages.
To install PHP, open terminal and type in this command. 
Note: If you are on a version earlier than Debian 7, 
include php5-suhosin as well.

apt-get install php5 php-pear php5-mysql

After you answer yes to the prompt twice, PHP will install itself.
Finish up by restarting apache:

service apache2 restart

Congratulations! You now have LAMP stack on your droplet!

Step Four—RESULTS: See PHP on your Server

Although LAMP is installed, we can still take a look 
and see the components online by creating a quick 
php info page

To set this up, first create a new file:

nano /var/www/info.php

Add in the following line:

<?php
  phpinfo();
?>

Then Save and Exit.

 

3.1. Create database

Now login into the phpMyAdmin and create new database “iot” with two tables: “events” and “nodes”. The creation script for both tables is:

--
-- Database: `iot`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `nodes`
--
DROP TABLE IF EXISTS `nodes`;
CREATE TABLE `nodes` (
`ID` BIGINT(20) UNSIGNED NOT NULL,
`NodeID` text NOT NULL COMMENT 'Identificator of the data source',
`Description` text NOT NULL COMMENT 'Description of the data source ',
`ApiKey` text NOT NULL COMMENT 'Unique API key for this source'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Node list';
 
--
-- Add demo node into table `nodes`
-- API Key is 32 symbols long
--
INSERT INTO `nodes` (`ID`, `NodeID`, `Description`, `ApiKey`) VALUES
(1, 'DemoNode1', 'This is demo node', '12345678901234567890123456789012');
 
--
-- Indexes for table `nodes`
--
ALTER TABLE `nodes`
ADD UNIQUE KEY `ID` (`ID`),
ADD UNIQUE KEY `ID_2` (`ID`);
 
--
-- AUTO_INCREMENT for table `nodes`
--
ALTER TABLE `nodes`
MODIFY `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;COMMIT;
 
 
 
-- ---- Events  --------:::
--
-- Table structure for table `events`
--
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`Timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Node` INT(11) NOT NULL,
`Address` SMALLINT(6) NOT NULL,
`Device` text NOT NULL,
`Parameter` text NOT NULL,
`Value` DOUBLE NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `events`
--
 
INSERT INTO `events` (`id`, `Timestamp`, `Node`, `Address`, `Device`, `Parameter`, `Value`) VALUES
(1, '2011-01-01 00:00:01', 1, 1, 'MyDevice1', 'T', 24.25);
 
--
-- Indexes for table `events`
--
ALTER TABLE `events`
ADD UNIQUE KEY `id` (`id`);
 
--
-- AUTO_INCREMENT for dumped tables
--
 
--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
MODIFY `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;COMMIT;

 

3.2. Prepare php script for adding data to the tables

I configured my http root folder in LAMP as /var/www/html . From console type

nano /var/www/http/info.php

and type in the editor the following php code:

<?php
  phpinfo();
?>

When called, the output will show information about your php installation. It is important that MySQL sections are shown:

In phpMyAdmin add new user with username iot and some good password. Grant privileges to tables “nodes” and “events” from localhost to prevent remote access to the database.

 

Create file config.php:

<?php
// config.php
  $mysql_host     = 'localhost';
  $mysql_username = 'iot';
  $mysql_password = 'verystrongpassword';
  $mysql_dbname   = 'iot';
  $mysql_events   = 'events';
  $mysql_nodes    = 'nodes';
?>

and another php script file event.php:

<html>
<body>
<pre>
Test
<?php
// Configuration -------------------
require_once('./config.php');
require_once('./functions.php');
// Get URL data
$apikey = filter_input( INPUT_GET, 'apikey', FILTER_SANITIZE_URL ); // Get the URL thingy
$address= filter_input( INPUT_GET, 'address', FILTER_SANITIZE_URL ); // Get the URL thingy
$device= filter_input( INPUT_GET, 'device', FILTER_SANITIZE_URL ); // Get the URL thingy
$parameter= filter_input( INPUT_GET, 'parameter', FILTER_SANITIZE_URL ); // Get the URL thingy
$value= filter_input( INPUT_GET, 'value', FILTER_SANITIZE_URL ); // Get the URL thingy
// Create connection
$conn = new mysqli($mysql_host,$mysql_username,$mysql_password,$mysql_dbname);
// Check connection
if ($conn->connect_error)
{
    die("Connection failed: " . $conn->connect_error);
}
$sql = 'SELECT * FROM nodes WHERE ApiKey="'.$apikey.'"';
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
    // output data
    $row = mysqli_fetch_assoc($result);
    $node_ident = $row["ID"];  // numeric unique identifier
    $node_id = $row["NodeID"];
    $node_desc = $row["Description"];
    echo "Call from ID ". $row["ID"] . ", " .$row["NodeID"]. ", ".$row["Description"]."\n\r";
    $sql = sprintf("INSERT INTO `".$mysql_dbname."`.`".$mysql_events."` (`id` ,`Timestamp` ,`Node` ,`Address` ,`Device` ,`Parameter` ,`Value`)".
                   "VALUES (NULL ,CURRENT_TIMESTAMP , '%d', '%s', '%s', '%s', '%s');",$node_ident,$address,$device,$parameter,$value);
    $result = $conn->query($sql);
    echo "insert SQL=".$sql;
} else
{
    echo "0 results";
}
$conn->close();
?>
</pre>
</body>
</html>

 

3.3. Check posting data

The principle is simple: when url is called with proper parameters, the url encoded data is stored in the database. Sample url for testing connection with the tables nodes with above example node is:

http://myserver.ip/event.php?apikey=12345678901234567890123456789012&address=1&device=test&parameter=someparameter&value=123.456

 

The server will return:

Test

Call from ID 1, DemoNode1, This is demo node

insert SQL=INSERT INTO `iot`.`events` (`id` ,`Timestamp` ,`Node` ,`Address` ,`Device` ,`Parameter` ,`Value`)VALUES (NULL ,CURRENT_TIMESTAMP , '1', '1', 'test', 'someparameter', '123.456');

and the table “events” will have one new line of data:

New row of data in the table “events”

 

Next step is to prepare new project in eclipse for ESP8266 to call the url above.

4. Call the URL from ESP8266

The URL which we have to “call” is

http://my.server.ip/event.php?apikey=ApiKeyFromTheTableNodes&address=1&device=test&parameter=someparameter&value=123.456

The easiest way is to open the http_client demo application from the folder C:\Espressif\examples\ESP8266\esp8266-nonos-sample-code\02Network\HTTP_Client_Demo

Then change the line in the file user_main.c in the callback function static void ICACHE_FLASH_ATTR test_timer_cb() to suit your server address and device key from the database:

http_get(“http://my.server.ip/event.php?apikey=ApiKeyFromTheTableNodes&address=1&device=test&parameter=someparameter&value=123.456”, “”, http_callback_example);

Just in case, here is the whole project (zip file).

Now doubleclick make and flash. After few seconds, your server should start recording new “readouts” from the fictive sensor:

Recorded events from the remote ESP8266.

5. Visualize the data

One final step is to visualize the recorded data. Easiest way to do this is by using highcharts. First we need to prepare data. Two php files are known from above, here is the script to query the database using URL parameters and output this in JSON for highcharts.

prepare_data.php

When we have JSON prepared it’s time to draw some chart with this script in file chartdata.php.

The URL for the chart is:
http://my.server.ip/chartdata.php?nodeid=1&address=1&device=test&parameter=someparameter&limit=10

this will draw last 10 readouts in a chart:

Data from ESP8266 drawn in chart

6 Comments

  1. Edvard says:

    Pozdravljeni!
    Zelo zanimiva tema! Zgornje poskušam izvesti na Qnap NAS serverju.
    SQL deluje, uspelo mi je tudi dodati tabele in uporabnika.
    Za test kar na root direktoriju web serverja dodam info.php in ob klicu url-ja dobim informacije o verziji,…
    dodam še datoteki config.php in event.php.
    Ob klicu testnega URLja za vnos vrednosti v browserju dobim samo izpis

    Test

    Ne javi pa nobene druge napake.
    Kaj počnem narobe?
    Z iot uporabnikom se v phpMyAdmin lahko prijavim na server in pregledujem podatke v tabelah nodes in events.

    • Mare says:

      Saj kaj več kot “Test” ne bo izpisalo. Tisti PHP lahko kličeš tudi iz browserja. Poglej v tabelo, ali ti je vpisalo kakšen podatek?

  2. Edvard says:

    Poizkušam iz browserja in izpis v browserju je samo Test.
    Pričakoval bi tole kot piše zgoraj:
    “The server will return:

    Test

    Call from ID 1, DemoNode1, This is demo node

    insert SQL=INSERT INTO `iot`.`events` (`id` ,`Timestamp` ,`Node` ,`Address` ,`Device` ,`Parameter` ,`Value`)VALUES (NULL ,CURRENT_TIMESTAMP , ‘1’, ‘1’, ‘test’, ‘someparameter’, ‘123.45”

    Torej se php del niti ne izvede?
    Če ne more vzpostaviti povezave z bazo bi moral javiti napako(glede na napisano v skripti)?

  3. Edvard says:

    Je uspelo!

    Skopiral sem vsebino config.php v event.php in zakomentiral klic “require_once(‘./functions.php’);”
    V tem primeru gre vpis v bazo lepo skozi. Tudi izpis v browserju je tak kot zgoraj.

    Prosim še za datoteko “prepare_data.php” – link zgoraj ne deluje.

  4. Mare says:

    Functions.php ne vsebuje nič takega, kar bi bilo potrebno. Jaz sem imel le funkcijo hexdump(): https://pastebin.com/m9ectAjK

    prepare_data.php pa je tukaj: https://pastebin.com/wFg1exSm Upam, da bo v pomoč. To potem postrežem highcharts-om.

    Hvala za preizkus in povratne informacije.

  5. Edvard says:

    No počasi bom prišel do dna… no mogoče ne ravno do dna 🙂

    v config.php sem imel napako v sintaksi.

    Skripta prepare_data.php se v SQL stavku sklicuje na tabelo “dogodki”, ki se če sledite navodilom zgoraj imenuje “events”.
    V NodeID imenu sem imel podčrtaj (ESP_Dnevna), ki ga skripta očitno izloči. Po popravku NodeIDja v bazi na takega brez podčrtaja HighCharts prikažejo grafe!!! 🙂
    Odlično!
    Hvala za pomoč!
    Lep večer!