Connect ESP8266 to the cloud directly. This idea came to me after
connecting ESP to a home server running Linux and a MySQL database.
Having accidentally seen the video with the AZURE capabilities,
I decided to try the cloud on my project. I first deployed in the Ubuntu Server
cloud with my Java program and the SQL Server database
But after I moved to SQL Server Directly in the cloud and created
the IoT database function, the virtual server was deleted and now
I'm using only the bundle ESP8266 + Internet + Azure + SQL Server as DB
So the actual AZURE function code:
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System;
namespace SensorFunction
{
public static class insert
{
[FunctionName("insert")]
public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)
{
log.Info("Inserting operation");
Int32 count = 0;
Int32 sp_code = 0;
bool errorstatus = false;
String errormess = "";
string json = req.GetQueryNameValuePairs().FirstOrDefault(q => string.Compare(q.Key, "json", true) == 0).Value;
dynamic data = await req.Content.ReadAsAsync<object>();
json = json ?? data?.json;
if (json != null)
{
var dt = JsonConvert.DeserializeObject<Sensordata>(json);
SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"Data Source=myazure.database.windows.net;" +
"Initial Catalog=DatabaseIOT;" +
"User id=user;" +
"Password=passwd;";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "USE DatabaseIOT; SET DATEFORMAT YMD; DECLARE @mess AS INT EXECUTE sp_DataINSERT " + dt.sensor_id + ", " + dt.sensor_value + ", @mess output; SELECT @mess AS RET";
cmd.Connection = conn;
sp_code = (Int32)cmd.ExecuteScalar();
cmd.CommandText = "USE DatabaseIOT; SELECT COUNT(id) FROM SnsData";
count = (Int32)cmd.ExecuteScalar();
conn.Close();
errorstatus = false;
}
catch (SqlException ex)
{
log.Info(ex.Message);
errorstatus = true;
errormess = ex.Message;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
if (sp_code == 1 && errorstatus == true)
{
return json == null
? req.CreateResponse(HttpStatusCode.BadRequest, "{\"STATUS\":\"@STERR#\",\"CNT\":-1,\"SPRETURN\":-1,\"MESSAGE\":\"BAD REQUEST!\"}")
: req.CreateResponse(HttpStatusCode.OK, "{\"STATUS\":\"@STERR#\",\"CNT\":" + count.ToString() + ",\"SPRETURN\":" + sp_code.ToString() + ",\"MESSAGE\":\"" + errormess + "\"}");
}
if (sp_code == 0 && errorstatus == false)
{
return json == null
? req.CreateResponse(HttpStatusCode.BadRequest, "{\"STATUS\":\"@STERR#\",\"CNT\":0,\"SPRETURN\":2,\"MESSAGE\":\"BAD REQUEST!\"}")
: req.CreateResponse(HttpStatusCode.OK, "{\"STATUS\":\"@STOK#\",\"CNT\":" + count.ToString() + ",\"SPRETURN\":" + sp_code.ToString() + ",\"MESSAGE\":\"SPRETURN 1-ERROR, 0-SUCCESS}");
}
else
{
return json == null
? req.CreateResponse(HttpStatusCode.BadRequest, "{\"STATUS\":\"@STERR#\",\"CNT\":0,\"SPRETURN\":2,\"MESSAGE\":\"BAD REQUEST!\"}")
: req.CreateResponse(HttpStatusCode.OK, "{\"STATUS\":\"@STERR#\",\"CNT\":0,\"SPRETURN\":2,\"MESSAGE\":\"" + errormess + "\"}");
}
}
}
public class Sensordata
{
public int sensor_id { get; set; }
public int sensor_value { get; set; }
}
}
And function is working
The function takes as input parameter JSON - deserilizes it and connected to the database
Incomming parametr for function - JSON. For ex. after call http:
Here is example of my stored procedure for data save:
CREATE PROCEDURE [dbo].[sp_DataINSERT]
-- Add the parameters for the stored procedure here
@sensorID int,
@sensorValue int,
@res int out
AS
BEGIN
SET NOCOUNT ON;
SET @res = 0
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [dbo].[SnsData]
([sensor_id]
,[sensor_value]
,[sensor_date])
VALUES (@sensorID, @sensorValue, (SELECT DATEADD(HOUR, 1, (SELECT GETDATE()))))
SET @res = 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
SET @res=1
END CATCH
END
ESP8266 was programmed in Arduino IDE with code:
#include <SimpleTimer.h>
#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>
#define BlueLed 2
#define GreenLed 0
#define RedLed 4
const char* ssid = "SSID";
const char* password = "password";
SimpleTimer timer;
const char* host = "myfunction
.azurewebsites.net";
const int httpsPort = 443;
WiFiClientSecure client;
const char* fingerprint = "3a b0 b1 c2 7f 74 6f d9 0c 34 f0 d6 a9 60 cf 73 a4 22 9d e8";
void setup()
{
Serial.begin(115200);
pinMode(RedLed, OUTPUT);
pinMode(GreenLed, OUTPUT);
pinMode(BlueLed, OUTPUT);
digitalWrite(RedLed, 1);
digitalWrite(GreenLed, 0);
digitalWrite(BlueLed, 0);
delay ( 250 );
WIFI_Connect();
timer.setInterval(15000L, SendData);
SendData();
}
void WIFI_Connect()
{
WiFi.disconnect();
Serial.println("Connection...");
WiFi.mode(WIFI_STA);
WiFi.begin(ssid, password);
for (int i = 0; i < 25; i++)
{
if ( WiFi.status() != WL_CONNECTED )
{
digitalWrite(GreenLed, 0);
digitalWrite(BlueLed, 0);
digitalWrite(RedLed, 1);
delay ( 250 );
digitalWrite(2, 0);
Serial.print ( "." );
delay ( 250 );
}
}
if (WiFi.status() == WL_CONNECTED)
{
digitalWrite(RedLed, 0);
digitalWrite(GreenLed, 1);
}
}
void SendData()
{
if (WiFi.status() == WL_CONNECTED)
{
digitalWrite(GreenLed, 0);
digitalWrite(BlueLed, 1);
Serial.print("connecting to ");
Serial.println(host);
if (!client.connect(host, httpsPort))
{
digitalWrite(BlueLed, 0);
Serial.println("connection failed");
return;
}
if (client.verify(fingerprint, host)) {
Serial.println("certificate matches");
} else {
Serial.println("certificate doesn't match");
}
int sensor_id = random(0, 15);
int sensor_value = random(10, 45);
String url = "/api/insert?json={\"sensor_id\":" + String(sensor_id) + ",\"sensor_value\":" + String(sensor_value) + "}";
Serial.print("requesting URL: ");
Serial.println(url);
client.print(String("GET ") + url + " HTTP/1.1\r\n" +
"Host: " + host + "\r\n" +
"User-Agent: BuildFailureDetectorESP8266\r\n" +
"Connection: close\r\n\r\n");
Serial.println("request sent");
while (client.connected()) {
String line = client.readStringUntil('\n');
if (line == "\r") {
Serial.println("headers received");
break;
}
}
String line = client.readStringUntil('\n');
Serial.println("reply was:");
Serial.println("==========");
Serial.println(line);
Serial.println("==========");
Serial.println("closing connection");
digitalWrite(BlueLed, 0);
digitalWrite(GreenLed, 1);
}
}
void loop()
{
timer.run();
if (WiFi.status() != WL_CONNECTED)
{
digitalWrite(RedLed, 1);
digitalWrite(GreenLed, 0);
WIFI_Connect();
}
else
{
digitalWrite(GreenLed, 1);
}
Embedded world 