#include <ESP8266WiFi.h>
#include <WiFiClient.h>
#include "sqlard.h"
#include <ArduinoJson.h>
//ağ bilgilerinizi girin
#ifndef STASSID
#define STASSID "yedek"
#define STAPSK "1234*"
#endif
const char* ssid = STASSID;
const char* password = STAPSK;
int d = 0;
int kk_flag = 0;
char c;
unsigned long asyncDelay = 0; // Maksimum alabileceği rakam : 4,294,967,295
int delayLength = 10000;
uint8_t Ethernet_MacAddr[6] = { 0x74, 0x69, 0x69, 0x2D, 0x30, 0x31 }; /* MAC ADRESİ */
static byte Server_IPAddr[] = { 192, 168, 2, 22 }; // IP-Adres MS-SQL-Server
static byte Static_IPAddr[] = { 192, 168, 2, 195 };
static byte Gateway_IPAddr[] = { 192, 168, 2, 1 };
static byte Subnet_Mask[] = { 255, 255, 255, 0 };
WiFiClient client;
SQLard MSSQL(Server_IPAddr, 1433, &client); // Create the connection with the Server Data Base
void setup() {
// put your setup code here, to run once:
Serial.begin(115200);
while (!Serial);
StartWiFi(ssid, password);
}
void loop() {
recvWithStartEndMarkers();
if (millis() > asyncDelay + delayLength) {
if (asyncDelay > (4294967295 - delayLength)) {
asyncDelay = (4294967295 - asyncDelay) + (delayLength - (4294967295 - asyncDelay));
} else {
asyncDelay += delayLength;
}
Sending_To_database();
}
delay(2000);
}
void Sending_To_database() //CONNECTING WITH MS SQL
{
Serial.println("Login to the server");
if (MSSQL.connect()) // Check if the connection with the Data Base is correct
{
Serial.println("Registration ok");
// The database, the login name, the PW, and a "Sender" name are passed
MSSQL.setCredentials(L"deneme", L"sa", L"1234", L"Mustafa"); // For login to the Data Base
Serial.println("MSSQL-Login");
MSSQL.login(); // Now we are able to send querie
// String tarihs = "09.06.22";
// String saats = "14.42";
// float nem = 70.22;
// float temp = 28.50;
char newName [102];
// sprintf(newName, "INSERT INTO dbo.TestB (Tarih,Saat,Nem,Sicaklik) VALUES('%s','%s','%d.%02d','%d.%02d')", tarihs.c_str(), saats.c_str(), int(nem), int(round(nem * 100)) % 100, int(temp), int(round(temp * 100)) % 100);
sprintf(newName, "SELECT TOP 10 * FROM TestB ORDER BY ID DESC FOR JSON AUTO");
wchar_t fileName[102];
int i;
for (i = 0; i < 102; i++)
{
fileName[i] = newName[i];
}
for (i = 0 ; i < 102 ; i++)
Serial.write(fileName[i]);
Serial.println("");
MSSQL.executeNonQuery(fileName); // Databese'e queryi gönder.
Serial.println("Done");
if (!client) {
Serial.println("disconnecting.");
client.stop();
}
}
else
{
Serial.println("Login error");
}
}
////////////// WiFi, Time and Date Functions /////////////////
int StartWiFi(const char* ssid, const char* password) {
int connAttempts = 0;
Serial.print(F("\r\nBağlanıyor: ")); Serial.println(String(ssid));
WiFi.mode(WIFI_AP_STA);
WiFi.config(Static_IPAddr, Gateway_IPAddr, Subnet_Mask, Gateway_IPAddr);
WiFi.begin(ssid, password);
if (WiFi.waitForConnectResult() != WL_CONNECTED ) {
Serial.print(".");
if (connAttempts > 20) {
Serial.println("\nWi-Fi ağına bağlanılamadı");
return 0;
}
connAttempts++;
}
WiFi.macAddress(Ethernet_MacAddr);
Serial.print("MAC: ");
Serial.print(Ethernet_MacAddr[5], HEX);
Serial.print(":");
Serial.print(Ethernet_MacAddr[4], HEX);
Serial.print(":");
Serial.print(Ethernet_MacAddr[3], HEX);
Serial.print(":");
Serial.print(Ethernet_MacAddr[2], HEX);
Serial.print(":");
Serial.print(Ethernet_MacAddr[1], HEX);
Serial.print(":");
Serial.println(Ethernet_MacAddr[0], HEX);
Serial.println("");
Serial.print(F("WiFi Bağlandı: "));
Serial.println(WiFi.localIP());
return 1;
}
void recvWithStartEndMarkers() { // Databaseden gelen verileri ayrıştırma işlemi yapılır.
String data_from_display = "";
while (client.available() > 0) {
c = client.read();
// Serial.print(c);
if ( c == '[') {
kk_flag = 1;
}
if (kk_flag == 1) {
data_from_display += c;
}
if ( c == ']') {
kk_flag = 0;
}
}
Serial.println(data_from_display);
Serial.println(utf8ascii(data_from_display));
String input = data_from_display;
DynamicJsonDocument doc(1536);
DeserializationError error = deserializeJson(doc, utf8ascii(data_from_display));
if (error) {
Serial.print(F("deserializeJson() failed: "));
Serial.println(error.f_str());
return;
}
for (JsonObject item : doc.as<JsonArray>()) {
int ID1 = item["ID"]; // 29, 28, 27, 26, 25, 24, 23, 22, 21, 20
const char* Tarih1 = item["Tarih"]; // "09.06.22", "09.06.22", "09.06.22", "09.06.22", "09.06.22", ...
const char* Saat1 = item["Saat"]; // "14.42", "14.42", "14.42", "14.42", "14.42", "14.42", "14.42", ...
const char* Nem1 = item["Nem"]; // "70.22", "70.22", "70.22", "70.22", "70.22", "70.22", "70.22", ...
const char* Sicaklik1 = item["Sicaklik"]; // "28.50", "28.50", "28.50", "28.50", "28.50", "28.50", ...
Serial.println("ID: " + String(ID1));
Serial.println("Tarih: " + String(Tarih1));
Serial.println("Saat: " + String(Saat1));
Serial.println("Nem: " + String(Nem1));
Serial.println("Sıcaklık: " + String(Sicaklik1));
}
Serial.println(input.length());
}
// ****** UTF8-Decoder: convert UTF8-string to extended ASCII *******
static byte c1; // Last character buffer
// Convert a single Character from UTF8 to Extended ASCII
// Return "0" if a byte has to be ignored
byte utf8ascii(byte ascii) {
if ( ascii < 128 ) // Standard ASCII-set 0..0x7F handling
{ c1 = 0;
return ( ascii );
}
// get previous input
byte last = c1; // get last char
c1 = ascii; // remember actual character
switch (last) // conversion depending on first UTF8-character
{ case 0xC2: return (ascii); break;
case 0xC3: return (ascii | 0xC0); break;
case 0x82: if (ascii == 0xAC) return (0x80); // special case Euro-symbol
}
return (0); // otherwise: return zero, if character has to be ignored
}
// convert String object from UTF8 String to Extended ASCII
String utf8ascii(String s)
{
String r = "";
char c;
for (int i = 0; i < s.length(); i++)
{
c = utf8ascii(s.charAt(i));
if (c != 0) r += c;
}
return r;
}
// In Place conversion UTF8-string to Extended ASCII (ASCII is shorter!)
void utf8ascii(char* s)
{
int k = 0;
char c;
for (int i = 0; i < strlen(s); i++)
{
c = utf8ascii(s[i]);
if (c != 0)
s[k++] = c;
}
s[k] = 0;
}