Tranform data from one array to another

Using Arduino Project Guidance
1

I have a callback function from SQLite3 library for ESP32. The callback function is shown below:

const char* data = "Data requested";
 static int callback(void *data, int argc, char **argv, char **azColName) {
  Serial.print((char) '[');
  int i;
  for (i = 0; i<(argc); i++) {
    if(i)
    Serial.print((char) ',');
    Serial.printf("%s", argv[i] ? argv[i] : "NULL");
    }
    
  Serial.printf("],\n");
  
  return 0;
  }

That produces result shown below:

[1623287518,24785,1535,15,6,-22,292,49,89,250,76],
[1623287645,24785,1535,15,26,-52,294,51,94,224,48],
[1623287668,24785,1535,15,6,-22,292,49,44,249,81],
[1623287796,24785,1535,15,26,-53,294,46,97,229,36],

Now each row (array) will always have 11 numbers as shown and there could be as many as 400 rows or arrays depending on how much data stored in database.

Now I want to have above result in following format with 11 arrays of unknown numbers:

[1623287518,1623287645,1623287668,24785,1623287796,24785,..........,..........],
[1535,1535,1535,1535,...,....],
[15,15,15,15,..,..],
[6,26,6,26,..,..],
[-22,-52,-22,-53,...,...],
& so on.

I do not know how to do this.

Can anybody help me ?
Thanks.

2

You can code the callback function to store the data in an array in any way you like. If you want an array with 11 rows and 400 columns, instead of 400 rows and 11 columns, you can just do that.

3

I am new to coding stuff.Can you please show me an example callback function please?

Thanks.

4

400 rows x 11 columns of numbers that may be 32-bit longs.
Only need 17600 bytes for the array.

You can add serial RAM on the SPI bus, read it in by row and column and write it out by column and row.

You can add an SD module and store on that.

5

You have one. You included it in your original post.

6

Esp32 has lots of ram. Adding more does not seem necessary.

7

@PaulRB Yes that function prints it certain way and I wanted to reverse it how it prints. I do not know how to do it. Please can you show how to rearrange the original code?

Thanks.

8

Why do you want to print it in another way? I don't want to waste our time making those changes if that is not actually required. You have not yet explained why you want to do this, and I am concerned this could be an "X-Y" problem.

9

You are using the SQLite3 library which to me implies that the callback() function is called as a result of a query on a database. Is that what is happening ?

If so, can you change the query to return the data in a different order ?

10

My purpose to send the result via websockets to websocket client so it can be used by client to draw a graph using javascript. I have javascript part code figured out to draw a graph if data is received with 11 rows and upto 400 columns.

Thanks.

11

@UKHeliBob yes callback is a result of a query from websocket client. But even if I change the query the actual result produced will change (like number of rows and columns) but with this callback function (for loop) as it exists format will be still the same.

So my question is how to change the for loop in this function to re arrange data in such a way that it present data with 11 rows and upto 400 columns?

Thanks.

12

Ok, my question now is what is the Arduino for? Why can't the websocket client, that draws the graph, request the data from the database directly? What value is the Arduino adding to this project? If only to reformat the data, that would be much better done by the client drawing the graph. It will have much greater resources for the task.

13

Can you not change the query to present the data in the required order ?

1 Like
14

How did you achieve that? You said

15

@PaulRB Sorry I am unable to explain to you what my goal is. I did not want to create more confusion by posting whole project here and that's why I started with simple question. But I am posting whole project below if that helps.

ESP32 is doing lot of things and it is at heart of this project as a gateway for a sensor network.

// SQLite reference at https://www.w3schools.com/sql/exercise.asp
// https://zetcode.com/db/sqlite/expressions/
#define PROBEREQUESTS     true     // Receives data from remote ESP8266 devices on network
#define MQTT              true     // Communicate with MQTT clients
#define WEBSOCKETS        true     // Communicate with Websockets clients
#define ASYNCWEBSERVER    true     // Publishes web interface
#define SQLITE3           true     // Store data
#define FIRSTTIME         true    // Warning - This must be false.Make it true if creating new data and table files for first time.

#include <WiFi.h>
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> // Thanks to https://github.com/siara-cc/esp32_arduino_sqlite3_lib
//#include <SPI.h>
#include <FS.h>
#include <LITTLEFS.h>
#include "time.h"   // Arduino built in
String Timestamp, Date, Day, Time, Hour;

#define FORMAT_LITTLEFS_IF_FAILED true
int rc;
sqlite3 *db1;
String insert;
String graphData;
int triggerInsertData = 0;
  
const char* ssid = "";    // Your WiFi SSID
const char* password = ""; // Your WiFi Password
const char* apSSID = "ESP";        // SSID for access point. This must be same on all remote devices.
const char* apPassword = "";       // Password for Access point if required otherwise leave blank.
const int apChannel = 7;           // WiFi Channel number for access point. This must be same on all remote devices.
const int hidden = 0;              // If hidden is 1 probe request event handling does not work ?
int wsport = 8084;                 // Websocket server port

#if PROBEREQUESTS
// Channge following according to your remote sensor types for each device.
int Livingroom[4] = {16,26,36,46}; // temperature = 16, humidity= 26, pressure= 36, light= 46, OpenClose = 56, level = 66, presence = 76, motion = 86, rain = 96 etc.
int Kitchen[4] =    {46,36,26,16};
int Bedroom1[4] =   {46,36,26,16};
int Bedroom2[4] =   {16,26,36,36};
int Bedroom3[4] =   {16,26,36,36};
int Bedroom4[4] =   {16,26,36,36};
int Bathroom1[4] =  {16,26,36,36};
int Bathroom2[4] =  {16,26,36,36};
int Bathroom3[4] =  {16,26,36,36};
int Bathroom4[4] =  {16,26,36,36};
int Laundry[4] =    {16,26,36,36};
int Boiler[4] =     {16,26,36,36};
int Workshop[4] =   {16,26,36,36};
int Garage[4] =     {16,26,36,36};
int Office[4] =     {16,26,36,36};
int Tank[4] =       {16,26,36,36};
int Solar[4] =      {16,26,36,36};

//================= No user confiuration required below this line =================================================


#include <esp_wifi.h>
char str [256];
char s [70];
String deviceData;
String sensorData;
int device;
int voltage;
int rssi;
uint8_t mac[6];
int sensorValues[4];
int sensorTypes[4];
int deviceStatus[4];
#endif

#if MQTT
#include <TinyMqtt.h>   // Thanks to https://github.com/hsaturn/TinyMqtt
#endif

#if WEBSOCKETS
#include <ArduinoWebsockets.h>  // Thanks to https://github.com/gilmaimon/ArduinoWebsockets
using namespace websockets;
WebsocketsServer WSserver;
std::vector<WebsocketsClient> allClients;  // a collection of all connected clients
String sql;
#endif

#if ASYNCWEBSERVER
#include <ESPAsyncWebServer.h>  // Install manually to arduino folder
int index_html_gz_len = 18830;
const uint8_t index_html_gz[] = {
0x1f,0x8b,0x08,0x08,0x0c,0x9e,0xbe,0x60,0x00,0xff,0x69,0x6e,0x64,0x65,0x78,0x2e,0x68,0x74,0x6d,0x6c,0x2e,0x67,0x7a,0x00,0xed,0x5b,0xdb,0x77,0xdb,0x36,0xd2,0x7f,0x96,0xff,0x0a,0x2c,0xdb,0x5d,0xc9,0xb5,0x44,0x8a,0x92,0xed,0x38,0xb6,0xe4,0xb3,0xb9,0x6e,0xb2,0xeb,0x34,0x69,0x9d,0x7e,0x7d,0x48,0x73,0x7a,0x28,0x12,0x92,0x18,0x53,0x84,0x3e,0x12,0xb4,0xad,0x4d,0xfd,0xbf,0xf7,0x37,0x00,0x28,0x52,0x12,0x29,0x2b,0xdb,0xcb,0xe9,0x83,0x75,0xa5,0x80,0xc1,0x60,0x30,0x37,0x0c,0x86,0xa3,0xc1,0x54,0xce,0xa2,0x73,0xb6,0x37,0x98,0x72,0x2f,0xa0,0xef,0x19,0x97,0x1e,0xf3,0xa7,0x5e,0x92,0x72,0x39,0xb4,0x32,0x39,0xee,0x9c,0x58,0xe7,0xa6,0x39,0xf6,0x66,0x7c,0x68,0x5d,0x87,0xfc,0x66,0x2e,0x12,0x69,0x31,0x5f,0xc4,0x92,0xc7,0x00,0xbb,0x09,0x03,0x39,0x1d,0x06,0xfc,0x3a,0xf4,0x79,0x47,0xfd,0x68,0xb3,0x30,0x0e,0x65,0xe8,0x45,0x9d,0xd4,0xf7,0x22,0x3e,0x74,0x2d,0x42,0x2e,0x43,0x19,0xf1,0xf3,0x77,0x89,0x18,0xf1,0x84,0xff,0x7f,0xc6,0x53,0xd9,0x66,0x17,0xa1,0x44,0xe3,0xf3,0xa7,0x6d,0xf6,0x3e,0x8c,0x17,0x6f,0xbe,0x7b,0xff,0xbe,0xcd,0x7e,0xe4,0xa3,0x54,0xf8,0x57,0x5c,0xa6,0x6d,0xf6,0x24,0x5d,0xc4,0xfe,0x0d,0x1a,0x78,0x72,0xcd,0x13,0xf6,0x0f,0x96,0xbd,0x8b,0x84,0x1c,0x38,0x1a,0x17,0x90,0xa6,0x7e,0x12,0xce,0x25,0x93,0x8b,0x39,0x88,0x93,0xfc,0x56,0x3a,0x9f,0xbc,0x6b,0x4f,0xb7,0x5a,0x2c,0x4d,0xfc,0xa1,0x35,0x95,0x72,0x9e,0x9e,0x3a,0x8e,0x1f,0xc4,0x9f,0x52,0xdb,0x8f,0x44,0x16,0x8c,0x23,0x2f,0xe1,0xb6,0x2f,0x66,0x8e,0xf7,0xc9,0xbb,0x75,0xa2,0x70,0x94,0x3a,0x9f,0x40,0x52,0xb2,0x70,0x5c,0xdb,0x75,0xed,0x9e,0xf9,0x65,0xcf,0xc2,0xd8,0xfe,0x94,0x5a,0xe7,0x03,0x47,0xa3,0x3c,0xdf,0xdb,0x1b,0x44,0x61,0x7c,0xc5,0x12,0x1e,0x0d,0xad,0x54,0x2e,0x22,0x9e,0x4e,0x39,0xc7,0x54,0xd3,0x84,0x8f,0x57,0xe6,0xc2,0xb8,0x80,0x47,0xe1,0x75,0x62,0xc7,0x5c,0x3a,0xf1,0x7c,0xe6,0x64,0x73,0xd0,0xfe,0x4f,0xd7,0x3e,0xb6,0x1f,0x39,0x41,0x98,0x4a,0x47,0x2d,0x46,0xcd,0xe1,0xa7,0x98,0x04,0xb8,0x15,0x46,0xac,0xeb,0x2b,0xe2,0xae,0x17,0xc6,0x58,0xf3,0x67,0xa6,0x78,0x7a,0xca,0xdc,0x6e,0xf7,0xef,0x67,0x6c,0xca,0xc3,0xc9,0x54,0x9e,0xb2,0xc3,0xc7,0xd7,0xd3,0x33,0x36,0xf2,0xfc,0xab,0x49,0x22,0xb2,0x38,0xe8,0xf8,0x22,0x12,0xc9,0x29,0xfb,0xaa,0xdf,0xef,0x9f,0x31,0xa0,0x9f,0x47,0xde,0xe2,0x94,0x8d,0x23,0x7e,0x7b,0xc6,0xbc,0x28,0x9c,0xc4,0x9d,0x50,0xf2,0x59,0x7a,0xca,0x7c,0x48,0x8d,0x27,0x67,0xec,0x53,0x96,0xca,0x70,0xbc,0xc0,0x40,0x25,0xc8,0xa2,0x43,0x80,0xd5,0xe3,0x48,0xdc,0x9c,0xb2,0x69,0x18,0x04,0x3c,0xc6,0x34,0x22,0x09,0x78,0xd2,0x49,0xbc,0x20,0xcc,0x80,0xe1,0xd1,0x1c,0x38,0xa5,0xc8,0xfc,0x69,0xc7,0xf3,0x65,0x28,0xe2,0x53,0x16,0x8b,0x98,0x9f,0xb1,0x3b,0x50,0x4e,0xb3,0xac,0x10,0x4d,0xc0,0x39,0xd5,0xe6,0xe7,0x26,0xd9,0xc9,0x64,0xd4,0xea,0x1d,0x1e,0xb5,0x59,0xaf,0xdf,0x6d,0xb3,0xc7,0x8f,0xf7,0xf3,0x49,0x69,0xcc,0xfc,0x96,0xa5,0x22,0x0a,0x03,0x82,0xf2,0x5a,0x6e,0xff,0xb8,0xcd,0x8a,0x0f,0xfb,0x68,0x09,0xbb,0x24,0xf0,0x88,0x38,0x55,0x45,0x60,0x06,0x45,0xea,0xa4,0x3c,0xe2,0x3e,0x88,0x59,0xa5,0xf9,0x94,0x20,0xaf,0x39,0x48,0xaf,0xa4,0x0e,0xf3,0x1e,0x9f,0x80,0x3c,0x97,0x3e,0x7a,0xa0,0xd1,0xb5,0xbb,0x5d,0xcc,0xbc,0x1c,0x3e,0x25,0xb6,0x61,0xb4,0x9f,0x25,0x29,0x8d,0x99,0x8b,0x50,0xb3,0xd3,0xd0,0x66,0x18,0xd2,0x53,0x0c,0xa0,0x51,0x50,0x41,0x0f,0xf0,0x73,0x91,0xc2,0x56,0x88,0xc4,0x71,0x78,0xcb,0x83,0x33,0x96,0x68,0x56,0xf5,0xfb,0x76,0xbf,0xaf,0x96,0x31,0xa7,0x05,0x91,0xb4,0x69,0x54,0x2a,0x31,0x6c,0xa6,0x84,0xa6,0xf4,0xe3,0xf3,0x9a,0x46,0xcc,0xbd,0x20,0x08,0xe3,0xc9,0x69,0xf7,0x8c,0xcd,0xbc,0x64,0x12,0xc6,0xc5,0x55,0x67,0x14,0xc1,0xae,0x3a,0xa9,0xf4,0x12,0xb9,0xd1,0xca,0xe3,0xa0,0xd4,0x16,0xc6,0xd0,0x72,0xbe,0x01,0x6a,0x9a,0x15,0x6c,0x25,0x35,0x2c,0x9c,0x4d,0x3e,0x2f,0xb5,0x4f,0x61,0xa6,0xc1,0xb7,0x66,0xf5,0x5a,0x85,0xa1,0xf0,0x1d,0x43,0xb4,0xd1,0xe2,0x15,0xe9,0x1d,0x12,0x83,0xcc,0x84,0xb4,0xf8,0x13,0x88,0x1f,0x73,0xc1,0x04,0x8d,0x71,0xe0,0xd2,0x38,0xac,0xbd,0xc1,0x48,0x04,0x0b,0x78,0xa8,0x2f,0x73,0x03,0x3b,0x9b,0x66,0x18,0x8e,0x79,0xa5,0x0f,0x98,0xf6,0x7e,0x27,0x47,0x06,0x44,0x40,0x37,0x16,0xc9,0x8c,0x69,0x4d,0x1d,0x5a,0x70,0x04,0x0c,0xcb,0xbd,0x7c,0x71,0xf1,0xe2,0xd9,0x7b,0xe6,0x47,0x5e,0x9a,0x0e,0x2d,0xf8,0xaa,0x11,0x18,0x6c,0x31,0x58,0x81,0xfa,0x35,0xf3,0xe2,0xc0,0x52,0x4e,0x99,0x0d,0x99,0xe5,0xa9,0x41,0x8c,0x0d,0xc4,0x9c,0x90,0x80,0x56,0x73,0xb1,0xd2,0xaa,0x15,0x9f,0x8d,0x13,0x31,0x63,0x12,0x64,0xff,0x2c,0x47,0x11,0xbb,0x99,0x62,0x15,0xd5,0xf0,0xcf,0xc3,0x49,0x28,0xbd,0x88,0xfd,0x98,0x40,0xc3,0xab,0x41,0x9e,0xc4,0x5e,0x24,0x26,0xdb,0x20,0x72,0x24,0xdf,0x43,0x64,0x5b,0x71,0xd4,0x03,0x7c,0xcb,0xc5,0x1c,0xb6,0x11,0x55,0xf7,0x5e,0x72,0xc9,0x9e,0xbc,0x63,0xcf,0xa6,0x5e,0x1c,0x6f,0x83,0xb9,0x8c,0x38,0x9f,0x43,0x3c,0xb3,0x1a,0x42,0x09,0xe6,0x8d,0x08,0x4a,0xbd,0x03,0x47,0x0b,0x81,0x44,0xb4,0x4d,0x1c,0x7a,0xef,0x2b,0xa4,0x31,0xda,0x49,0x1a,0x17,0xe1,0x35,0x0c,0x35,0x11,0x62,0x56,0xdd,0xff,0x9f,0x50,0xfa,0x53,0x1e,0x57,0x77,0x3e,0xe5,0x01,0x8d,0x74,0xb7,0xf6,0xf6,0x6a,0x7a,0x3d,0x39,0xdd,0x36,0xd8,0x74,0xd7,0x8c,0xbe,0xf0,0xe0,0x18,0x93,0x45,0x75,0xe7,0xdb,0xf1,0x18,0x9c,0x58,0xed,0xdb,0x99,0x8d,0x46,0xab,0xdd,0x82,0x91,0xbe,0xc5,0x56,0xf1,0x77,0xbb,0xd5,0xf3,0x76,0x6b,0xd6,0xd2,0xad,0x59,0x44,0xb7,0x5f,0xd3,0x7e,0x58,0xd3,0x7e,0x54,0xd3,0x7e,0x5c,0xdd,0xee,0xd6,0xcc,0xeb,0xd6,0xcc,0xeb,0xd6,0xcc,0xeb,0xd6,0xcc,0xeb,0xd6,0xcc,0xdb,0xab,0x69,0xef,0xd7,0xb4,0x1f,0xd6,0xb4,0x1f,0xd5,0xb4,0x1f,0xd7,0xb4,0x3f,0xaa,0x69,0x3f,0xa9,0x69,0x7f,0x5c,0xc7,0xb7,0x5a,0x86,0xd6,0xad,0xd8,0xad,0x5b,0x32,0x82,0x83,0x3a,0x5e,0xd7,0x75,0xd4,0xad,0xda,0xad,0x5b,0xb6,0x5b,0xb7,0x6e,0xb7,0x6e,0xe1,0x6e,0xdd,0xca,0x7b,0x75,0x2b,0xef,0xd5,0xca,0xba,0x6e,0xe5,0xbd,0xba,0x95,0xf7,0xea,0x56,0xde,0x5b,0x5f,0xf9,0x97,0xda,0x6c,0xaf,0xb0,0xd9,0x60,0xcd,0xf9,0x3d,0x98,0xec,0x83,0xc9,0x3e,0x98,0xec,0x1f,0x6b,0xb2,0x5f,0x68,0xad,0xfd,0xc2,0x5a,0xf9,0x83,0xb5,0x3e,0x58,0xeb,0x83,0xb5,0xfe,0x95,0xad,0xf5,0xb0,0xb0,0xd6,0xf1,0x43,0x3c,0xfc,0x60,0xae,0x0f,0xe6,0xfa,0x57,0x35,0xd7,0x28,0x9b,0xc5,0x85,0xb1,0x4e,0xd6,0xb6,0xd6,0x0b,0xe1,0x7b,0x74,0x51,0x93,0x26,0xf1,0x6a,0x13,0x28,0x5e,0xcd,0x61,0xbb,0x3e,0x95,0xf1,0x4a,0x64,0xc9,0x97,0x2f,0x40,0xcc,0x79,0xe2,0x49,0x91,0x14,0x4b,0x98,0xae,0x2d,0x61,0x58,0x3d,0x5d,0x4d,0x82,0x63,0x50,0xd3,0x5c,0x87,0xa5,0xa6,0x7d,0x50,0x42,0x5f,0x2c,0x65,0x30,0x4a,0x54,0xc2,0xdc,0xa4,0xe4,0xae,0xbd,0x84,0x79,0x52,0x7a,0xfe,0xf4,0xc5,0x35,0xd2,0xd8,0x20,0x7e,0x9c,0xc5,0x2a,0xa1,0xd6,0x8a,0x91,0xd3,0x69,0x33,0x4e,0xcd,0x6d,0x16,0x21,0xb9,0xc7,0x91,0xa8,0x6c,0x53,0x2a,0xf8,0x99,0x37,0x97,0x59,0xc2,0xf7,0xd9,0x67,0x4c,0xe8,0x38,0xec,0x0d,0x97,0x53,0x11,0x30,0x64,0xe3,0xd8,0xcb,0x97,0x6d,0xf6,0x96,0xf8,0xd1,0x46,0x62,0x09,0xf9,0x32,0x60,0xb8,0xf4,0xc6,0x5e,0x12,0x02,0x32,0x1c,0xb3,0x16,0xf2,0xdb,0x71,0x20,0x6e,0x6c,0x64,0x5b,0xd5,0x84,0x17,0x06,0x2f,0xd3,0xc8,0x18,0x92,0xcb,0x01,0xdf,0xe8,0x6d,0x6d,0xa1,0x82,0xfd,0xf2,0x0b,0x1b,0x7b,0x51,0xca,0xf7,0xcf,0x80,0xe0,0x4e,0x53,0xf4,0xfa,0x05,0x9b,0x7a,0x29,0x0b,0x65,0xca,0xc4,0x4d,0xcc,0x70,0x23,0x06,0x04,0xa2,0x8b,0x03,0x6e,0x65,0xa2,0x62,0xe9,0xad,0xa6,0x88,0x9b,0x07,0x6b,0x13,0x19,0x9c,0x77,0x67,0x7b,0x7b,0xc0,0xfa,0x36,0xf6,0x39,0x93,0x53,0x6e,0x16,0xc1,0x22,0xe1,0x05,0x29,0xc3,0x56,0xa3,0x1a,0x9f,0xbf,0x7d,0xc3,0xc2,0x14,0x77,0x38,0xbc,0x60,0xd1,0x36,0x4c,0x55,0x1d,0x0a,0x27,0xf2,0xd1,0xea,0xc7,0x0c,0xf9,0x5e,0xe0,0x2c,0x4f,0xac,0xb1,0xb5,0x99,0x45,0xf8,0xac,0x76,0x21,0x01,0xcd,0x13,0x12,0x91,0xce,0x40,0xfe,0x6c,0x76,0x36,0x48,0x29,0x10,0x7e,0x36,0xc3,0x60,0x7b,0xc2,0xe5,0x8b,0x88,0xd3,0xe5,0xd3,0xc5,0xeb,0xa0,0xb5,0x4c,0x71,0x82,0xf2,0x95,0xb1,0xaf,0xd0,0x18,0x81,0xcf,0x25,0x01,0xe7,0x2c,0x67,0x4c,0xab,0x89,0xbb,0x0d,0xaf,0xc9,0xd5,0xed,0xb7,0xf7,0x1a,0x39,0x7c,0x6f,0x07,0x3a,0x5c,0x22,0xa4,0x3c,0x49,0x7f,0x87,0x41,0xbd,0xf5,0x41,0x87,0x3b,0x0c,0xea,0xaf,0x0f,0x3a,0xda,0x61,0xd0,0xe1,0xfa,0xa0,0xe3,0xed,0x83,0x94,0xa7,0x5a,0x1b,0xf2,0x68,0xdb,0x90,0xa5,0x6f,0xd8,0x3f,0x6b,0x30,0x3d,0x8c,0x34,0xe9,0x72,0x0a,0xf5,0x21,0x49,0xe2,0x4e,0x91,0x56,0x29,0x6f,0x3e,0x4f,0xc4,0x3c,0x09,0xe1,0xce,0x8c,0xc4,0x9a,0xa9,0x86,0x27,0xb3,0x91,0xd3,0x30,0xb5,0xaf,0xbd,0x28,0x83,0x7b,0x81,0x7f,0xc9,0x13,0xbb,0x16,0x29,0xff,0x5a,0x5f,0x39,0xb3,0x6c,0x2d,0x45,0x6c,0x28,0xce,0x25,0x6d,0xab,0x7b,0x02,0xb6,0xb9,0xe1,0x40,0x2e,0xcb,0xca,0x17,0x95,0x0b,0xf7,0x7e,0x90,0xfe,0xfd,0x20,0x87,0xf7,0x83,0x1c,0xdd,0x0f,0x72,0xbc,0x09,0x42,0xf7,0xa0,0xd6,0xc1,0x1e,0xdd,0x07,0x66,0xb8,0x70,0xa7,0xdd,0x40,0x05,0x5f,0x57,0x53,0xe2,0x8a,0xbb,0xaa,0xad,0x48,0x81,0x17,0x6d,0x94,0xf2,0xd6,0xbf,0xca,0x99,0x7a,0xdd,0x52,0xca,0xcc,0xaf,0x82,0xac,0x49,0xe5,0x4f,0x93,0x4b,0x15,0xbb,0x2a,0x64,0x53,0x05,0x56,0x21,0x9f,0x2a,0xb0,0xdf,0x4d,0x46,0x8c,0x1c,0x4c,0xa5,0xd6,0x6f,0x32,0x11,0x26,0xa5,0x80,0x77,0xe2,0xdf,0x6e,0x2c,0xdc,0x4d,0xbb,0x77,0x67,0xe2,0xee,0x7c,0xdc,0x9d,0x95,0x3b,0x70,0xb3,0x01,0xbe,0xdc,0xa9,0x4f,0xf5,0x51,0xc1,0xcd,0xfa,0x9b,0x5a,0xbf,0x2f,0x6b,0x6b,0x48,0xbf,0x57,0x49,0xff,0x6c,0x16,0xef,0xc6,0x5e,0x62,0x2d,0xb9,0x90,0xdc,0x93,0x50,0x78,0xa0,0x76,0x5a,0x38,0xf5,0x1f,0xe0,0x54,0xc8,0x95,0x8b,0x18,0xa5,0x20,0xf1,0x04,0x3e,0x1d,0x0e,0x5e,0xc4,0x57,0x7c,0x31,0x4f,0x78,0x9a,0xea,0x38,0x20,0xa5,0x40,0x20,0xe0,0x92,0x78,0x0f,0x6e,0xc7,0x6a,0x84,0x1e,0xaf,0x65,0x23,0xc6,0xeb,0xbb,0x3d,0x45,0x32,0x1a,0x23,0x85,0x30,0xe5,0xc8,0x61,0x15,0x10,0x11,0x84,0x06,0x43,0x0c,0xb1,0xb2,0xe9,0xab,0x0d,0x6b,0xeb,0xb8,0x9c,0xc6,0x8a,0x91,0x77,0x14,0x49,0x7c,0xdd,0xca,0x77,0xb8,0x7d,0x5b,0x05,0x38,0xad,0x22,0x8a,0x20,0x5f,0xf6,0x75,0xcb,0xa2,0x7b,0xb1,0xd6,0xbe,0x9d,0x66,0xa3,0x59,0x28,0xd7,0xba,0xf3,0x40,0x24,0x96,0xcf,0xee,0x8d,0x60,0x9a,0xa0,0x23,0x8b,0x64,0x73,0x5f,0xeb,0xaa,0x96,0xca,0x4d,0x6a,0x63,0x74,0x00,0xc2,0x97,0x28,0x50,0x43,0xa0,0xbc,0xa7,0xe3,0xa0,0x54,0x26,0x91,0x2d,0xeb,0x52,0xcd,0x2c,0xb9,0x8a,0x7d,0x54,0x07,0x7c,0x70,0xab,0x79,0x89,0x71,0x74,0x63,0x9f,0x35,0xd9,0x41,0x99,0x04,0x1d,0xda,0xed,0xb6,0x40,0xb3,0x87,0x7e,0xdd,0x6a,0x52,0xf1,0x0b,0x85,0xfe,0x20,0x0f,0x5d,0x4d,0xcd,0xef,0x66,0x39,0x66,0x03,0xf4,0x1e,0x99,0x1b,0x80,0x51,0x8d,0xa2,0xa6,0xd2,0x6b,0x41,0xbc,0xf5,0x47,0xed,0x31,0xfb,0x20,0xf0,0x33,0x71,0x83,0xd8,0x3c,0xf7,0x16,0x14,0x48,0x82,0xc5,0x55,0x24,0x1c,0xb0,0xa6,0x43,0x9c,0xa0,0x3e,0x1d,0xd3,0x55,0x76,0xe5,0xe1,0x5b,0xde,0x79,0xa6,0x34,0x1d,0x3c,0xd8,0xba,0xb6,0x35,0x0f,0xad,0xd8,0x56,0xc8,0xff,0x0f,0x21,0x0c,0xcd,0x96,0x63,0xad,0x76,0xf6,0x56,0xa8,0x26,0x6b,0xdd,0x46,0xf4,0x36,0x47,0xf8,0x85,0x2b,0xb0,0xf0,0xcc,0x29,0xa1,0x50,0xb1,0xd4,0x45,0x8d,0x79,0x30,0xb8,0xd6,0xbc,0xba,0xda,0x25,0xcd,0x8d,0xbd,0x06,0xf5,0x96,0xcd,0xa1,0x65,0x08,0x58,0x02,0x29,0xf5,0xa5,0x12,0x8f,0x65,0x85,0x05,0x1d,0xef,0x06,0x61,0x3c,0xcf,0xca,0x55,0x1d,0x74,0x50,0x1d,0x5a,0x1a,0x13,0xca,0x3a,0xc2,0xff,0xaa,0x53,0x6a,0xef,0x44,0x9f,0x58,0x51,0x90,0x05,0x1b,0xb1,0xb4,0xff,0x41,0x1d,0x05,0x73,0xd6,0x50,0x68,0x9b,0x5e,0x02,0x90,0x45,0x31,0x63,0x45,0xaa,0xfa,0xca,0x21,0xdb,0xa7,0x0b,0x9a,0x4b,0x95,0xe7,0x24,0xe2,0x06,0xc7,0x64,0x64,0xe0,0xc0,0x07,0x5c,0xf4,0x8f,0x34,0x05,0xc6,0x91,0x96,0xa6,0x4a,0xe7,0x3c,0x8a,0x70,0xbb,0xde,0xbf,0x1a,0x5a,0xea,0x14,0x67,0x31,0x75,0x82,0x12,0x71,0xb4,0x00,0xed,0x49,0x86,0x06,0xe5,0x84,0xd1,0x8d,0x62,0x99,0x0e,0xd1,0x7e,0xea,0x1e,0xa3,0xd4,0x45,0x55,0xd4,0x2c,0xeb,0xa3,0xf8,0xec,0x8c,0x2a,0xee,0x9c,0x9c,0x02,0xa2,0xe6,0x6f,0x9d,0xce,0x60,0x7e,0x6e,0x56,0x42,0xd3,0x93,0xf5,0xbf,0x81,0x97,0xf3,0xe0,0x22,0x57,0xb8,0x43,0x58,0x41,0x6e,0x77,0x49,0x58,0xf7,0xd8,0xc1,0x8b,0x30,0x8e,0x32,0x29,0x45,0x4c,0x3e,0x3d,0x0a,0x89,0x48,0x62,0xd5,0x7b,0x0c,0x6a,0xed,0x5b,0xa8,0x7c,0x28,0x18,0xc1,0x70,0xf8,0xbd,0xfc,0xee,0x62,0xe0,0xe8,0x01,0x38,0x7b,0xcf,0x31,0x3a,0x8b,0x0c,0xe7,0x7d,0x8e,0x1a,0xa9,0x20,0x9f,0xfc,0xdc,0x0c,0x4a,0x51,0xd0,0x62,0xda,0xe8,0xe0,0xa8,0x81,0xb4,0x26,0x9a,0x82,0x17,0x1c,0x0b,0x38,0x14,0x6f,0xc4,0x51,0x57,0x46,0x77,0x17,0x33,0xd4,0x1d,0xb2,0x4e,0xa7,0x38,0xc2,0xeb,0x75,0x34,0xd7,0x6a,0x77,0x9a,0x00,0x20,0x9d,0xb7,0x50,0x4b,0x73,0xa9,0x6a,0x69,0x20,0x80,0xd8,0x9c,0x5b,0x69,0x7b,0xc7,0x21,0x84,0x14,0xfa,0x26,0x85,0x22,0xc4,0xfc,0x86,0x6a,0x6e,0x34,0x1c,0xc6,0xdc,0x50,0xb9,0x8f,0xfb,0xb8,0x67,0xa3,0x72,0xcb,0xee,0xda,0xfd,0xd3,0x93,0xee,0x09,0x1d,0x94,0x60,0xfc,0xdb,0xc7,0x90,0xf2,0x9b,0xf3,0x3d,0x2a,0x98,0x54,0xd2,0xc6,0x9e,0x8a,0x54,0xaa,0xb4,0x08,0xac,0xa3,0x84,0x08,0xdb,0x5d,0x87,0x1e,0xe4,0x91,0x71,0x7c,0x06,0xf5,0x2c,0xf0,0x50,0x2d,0xa9,0x1a,0xf7,0x72,0x6f,0xaa,0x7a,0x35,0xb7,0x41,0x73,0xbe,0x87,0x18,0x8e,0x6d,0xdb,0x43,0x4a,0x60,0xc5,0x46,0x52,0xb7,0x21,0x18,0x40,0xd8,0x52,0x79,0x9b,0x29,0x5a,0xef,0xe0,0xff,0xd1,0x23,0x62,0xd8,0x6f,0x5c,0x3a,0x40,0xb3,0xd2,0x09,0x9a,0x39,0xdf,0x94,0xc2,0x87,0xed,0xab,0x2b,0x01,0x9a,0x09,0xc1,0xc0,0x57,0xb0,0x03,0xc1,0x50,0xe9,0x64,0x58,0xaa,0xe5,0x8f,0x3c,0xc2,0x18,0xc5,0x70,0xea,0x8c,0xa7,0xf1,0xbc,0x6e,0x92,0x6e,0xa8,0x15,0xb0,0x7e,0x8f,0xb9,0xc7,0x6c,0x14,0x4a,0xbc,0x63,0x2f,0x59,0x40,0xc4,0x92,0x4f,0x78,0x92,0xda,0x9a,0xc9,0x75,0x04,0x19,0xe8,0x6a,0x92,0x50,0x93,0x96,0xc2,0x1b,0x86,0x23,0x11,0x7b,0xbe,0x1f,0x5e,0x52,0xa5,0x16,0xe5,0x3d,0xb4,0xcc,0x7f,0xc0,0x14,0xee,0xf1,0x93,0x24,0x41,0x38,0xd4,0xea,0xf7,0x96,0x47,0x61,0x7a,0x6c,0x8e,0xf9,0xd0,0xfd,0x88,0x71,0x9d,0x9e,0x7b,0x0f,0x98,0x4b,0x60,0x6e,0x7f,0x05,0x0a,0xf6,0xd4,0x22,0x89,0x87,0xe8,0xea,0x9d,0xe1,0x6b,0x80,0xf5,0xd2,0xf7,0xc1,0xc1,0x7e,0x15,0x8e,0x90,0x70,0x54,0xb5,0xb3,0x0e,0x03,0xfe,0x83,0xba,0xbe,0xde,0x47,0xe3,0x4c,0xd7,0x24,0xb2,0x01,0x5e,0xac,0xf5,0x1b,0x47,0x07,0x81,0xcb,0x06,0x52,0x1c,0x11,0xcf,0x96,0x7a,0x59,0x68,0x08,0xbf,0x96,0x5a,0x49,0x14,0xa8,0x0a,0xc3,0x61,0xaf,0x62,0x4c,0x1d,0x36,0x09,0x80,0xf6,0xa0,0x21,0x6b,0xa2,0x20,0x10,0x12,0x6d,0xd2,0xce,0x9e,0xf7,0x40,0x9a,0xa8,0x23,0xc4,0xc4,0x88,0x08,0x2f,0x55,0x37,0x30,0xa9,0x38,0xf3,0xfd,0xcb,0xce,0x09,0x31,0x68,0x86,0x98,0x0e,0xfe,0x42,0x8f,0x55,0xe2,0x5c,0x3d,0xe9,0x2e,0xa5,0x6e,0x5c,0xcb,0x86,0x26,0x16,0xea,0xa1,0x2c,0xe3,0xfb,0x2a,0x0f,0x44,0x56,0xb2,0x24,0xb6,0x90,0x90,0x09,0xbb,0xa0,0xb9,0xff,0x12,0x70,0x42,0x84,0xb8,0x3c,0x4a,0x15,0x75,0x2e,0xb5,0xf9,0x54,0xed,0x8b,0x15,0x58,0xcc,0x86,0x60,0x8e,0x25,0x07,0x43,0x1d,0x23,0xeb,0xe5,0xc3,0x61,0xfc,0x14,0xab,0xa3,0xcc,0x12,0x9c,0xf4,0x61,0xb9,0xc7,0x6c,0x31,0x7f,0x23,0x89,0xb4,0xb9,0x42,0xb0,0xc2,0x0a,0x5e,0x7f,0x30,0x69,0x3e,0x35,0x0d,0xa2,0x3d,0x50,0xe0,0xf3,0x96,0xf3,0xc4,0x99,0xb4,0x59,0xb3,0xb9,0x7f,0xd0,0x7c,0xda,0x84,0x4a,0x34,0x1a,0xa8,0x79,0x84,0x2d,0x08,0x1c,0x00,0x88,0x3b,0xff,0xbe,0x7c,0xfb,0xad,0x3d,0xa7,0x12,0xef,0x96,0x5a,0x04,0x2c,0x8c,0x12,0x39,0x9c,0xb3,0xbc,0x42,0x12,0xd2,0xf2,0xaf,0xf2,0x22,0x60,0x55,0x25,0xad,0x8a,0x1c,0xa1,0x07,0xa9,0x73,0x74,0x78,0xe4,0xba,0x47,0xfd,0x13,0x42,0x09,0x08,0x6c,0x64,0x4a,0x68,0xa8,0x0c,0x95,0xa2,0xe3,0xc5,0x1d,0x8f,0x2c,0xaa,0x23,0xc6,0xfa,0x22,0x45,0x7b,0xa7,0xf0,0xe6,0xc4,0x82,0x46,0xc3,0x90,0xff,0x32,0x8f,0x34,0x11,0x28,0x23,0xff,0x19,0xb3,0x0f,0x24,0xa1,0x42,0x6b,0x10,0x2c,0x7d,0x6c,0xee,0xab,0x38,0xa2,0xd1,0x28,0x2f,0xc0,0xb0,0xbe,0xd1,0x50,0x5c,0xa4,0x42,0x70,0x0a,0x02,0x5e,0xc4,0xd7,0x61,0x42,0xba,0x8b,0x14,0xa5,0x1a,0x6f,0xa4,0x88,0x71,0x09,0x2c,0x5e,0xde,0x88,0xe4,0x4a,0x09,0x41,0xfb,0x05,0x1c,0x8c,0xc8,0xfd,0x2f,0xdd,0x1e,0xd3,0x98,0x4e,0xf5,0x57,0xbb,0x68,0x36,0x65,0xbb,0xfd,0xe3,0x6e,0xa9,0x31,0xdf,0xae,0x8f,0x0f,0x57,0x5a,0x85,0xb8,0x42,0xf1,0x31,0xe1,0xd4,0x47,0x4e,0x15,0x80,0x9f,0xb2,0x0f,0x0a,0xa4,0xc1,0x32,0x36,0x3c,0x5f,0xce,0xd8,0x60,0x11,0xbc,0x63,0x04,0x37,0xa7,0x4e,0x11,0x99,0x8d,0x92,0x3b,0x69,0xab,0xaa,0xf3,0x4b,0x15,0xc8,0x89,0xa4,0x65,0xd9,0x59,0x47,0x43,0x14,0xb9,0xba,0x22,0xd5,0xc2,0xcc,0xe8,0xcd,0x2c,0x33,0x12,0x7c,0x70,0xa0,0xb7,0x12,0xcc,0xc8,0x70,0x12,0x02,0x7f,0x8a,0x79,0xf5,0x83,0xdb,0x38,0x26,0xd1,0x98,0xe7,0x7c,0xec,0x21,0x98,0x02,0x9b,0xcb,0xbe,0x93,0x50,0x2b,0xd7,0x1d,0xf2,0xf4,0x45,0x04,0xf2,0xb8,0x0d,0x5d,0x85,0x76,0x52,0xf9,0x7c,0x0a,0x5d,0x50,0xa4,0xe9,0xfe,0x32,0x69,0x65,0xe2,0x56,0x90,0xbc,0x0e,0x6e,0x81,0x45,0xb9,0x5b,0xcc,0x2c,0xa4,0x20,0xef,0x61,0xa7,0x08,0x45,0x50,0x86,0xef,0x45,0x51,0xcb,0x2c,0xc5,0x9f,0x86,0x51,0xf0,0x2d,0x4e,0x0b,0xe9,0xbe,0x8d,0x0d,0x98,0xdf,0xbe,0x1d,0x63,0x13,0xd3,0x64,0xec,0x32,0x8f,0xe2,0xa4,0xbe,0xfc,0xb0,0x9c,0xf9,0x63,0xed,0xc0,0xb2,0x5a,0x69,0x70,0x3b,0xf2,0x10,0xa3,0x94,0xa6,0xa2,0xd0,0xd4,0x5c,0xa9,0xef,0x8f,0x24,0xf0,0x3b,0xfa,0x50,0xff,0x6d,0x30,0xf2,0x66,0xec,0x36,0xbf,0x20,0x4d,0x9a,0x91,0x22,0x21,0xe6,0xd3,0xca,0x41,0x03,0xf5,0x08,0x35,0x45,0xae,0x10,0x9f,0x55,0x5b,0x1e,0x96,0x63,0x15,0x34,0x33,0x3c,0x4c,0x7e,0x9f,0xc8,0xca,0x55,0x0b,0x26,0x26,0xae,0x80,0xd1,0x9a,0x67,0xc9,0x3c,0xc2,0xa9,0xd8,0xb4,0x8f,0xc3,0x88,0xe0,0x55,0x71,0x7a,0xb7,0xdd,0x6d,0xf7,0x8e,0x8e,0xda,0x5d,0xbb,0xb7,0x6f,0x00,0x6a,0xd0,0xff,0xdf,0x26,0x5e,0xd4,0xd6,0x5f,0xff,0x56,0xb4,0x97,0x15,0x68,0x13,0x7d,0x88,0xff,0x4d,0x78,0xdf,0x6f,0xe2,0x1d,0xc1,0xc7,0xfe,0x46,0xac,0xaf,0x36,0xb1,0x26,0x38,0x70,0x57,0x21,0x55,0x08,0xe9,0x79,0x2f,0xd2,0x77,0x9b,0x48,0x27,0x09,0xe7,0x85,0x20,0xff,0x37,0x5a,0x2f,0x36,0xd1,0x2e,0x28,0xc8,0xba,0xf9,0x22,0x72,0xa1,0xb7,0x94,0xe0,0xd9,0x23,0x5b,0x81,0x23,0x52,0x41,0x90,0xaa,0x41,0x6f,0x91,0x2b,0x6c,0x2b,0x9f,0x89,0xcf,0x7c,0x27,0xa2,0xba,0x7a,0xe5,0x64,0x1b,0x08,0x1b,0x61,0x34,0xb4,0xe3,0x57,0x6d,0xe8,0x4f,0x23,0x31,0x32,0xdb,0x79,0x29,0x1a,0x2b,0xc5,0xc5,0xf9,0x96,0x5d,0x8e,0xd5,0x90,0x07,0xc2,0x5f,0x03,0x46,0xf9,0x1d,0xa7,0x98,0xec,0x90,0x36,0x13,0xdc,0xe7,0xa2,0xd0,0x4f,0x30,0xb5,0x79,0xb0,0x51,0x36,0x1e,0x63,0xf7,0x35,0x91,0x1d,0x79,0xfb,0xd9,0xe2,0x25,0x8e,0x8d,0xb2,0xdf,0xd3,0x81,0x1b,0x56,0x91,0x45,0xd1,0x99,0xe9,0x52,0x6d,0x4f,0xf5,0x98,0xd5,0x9e,0x97,0x61,0xc4,0x29,0xbf,0xa0,0x3b,0xb0,0xf0,0x52,0x03,0x79,0xbe,0x32,0x04,0xe2,0x1f,0x73,0x34,0x2e,0x07,0x3f,0x94,0x4f,0x59,0x73,0xa1,0x6c,0x63,0x4a,0xbd,0x19,0x1b,0x47,0xa9,0x0f,0xa9,0x65,0xcf,0xa3,0x87,0xac,0x2f,0x80,0xa8,0x29,0x37,0xb5,0x56,0xb0,0xea,0xcd,0x79,0x30,0x64,0xb8,0xa1,0xab,0x79,0x87,0xf8,0x39,0xc6,0x11,0x8a,0x8e,0xf5,0xf4,0x37,0x2b,0x7d,0xcf,0x45,0x8d,0x84,0x34,0x10,0x48,0x53,0x14,0x8d,0x7d,0x1b,0x1e,0x04,0xa1,0x94,0xfa,0x0f,0x0a,0x58,0x31,0xc3,0x3f,0x15,0xd2,0x9c,0x85,0xc5,0xb1,0x43,0x85,0x3c,0x14,0xd1,0xac,0xcc,0x69,0x8f,0x16,0x92,0x5f,0xf0,0x78,0x22,0xa7,0x2a,0x15,0x40,0x3f,0x71,0xfb,0x71,0xbc,0x22,0xc2,0xfa,0x18,0xe9,0xa7,0xf8,0x03,0xbf,0xf5,0x66,0x70,0x54,0xf8,0xe7,0xce,0x47,0x6c,0xb9,0x2b,0x11,0x30,0xfe,0x4b,0x42,0x11,0xf0,0x2a,0x1f,0xec,0x48,0xcd,0x86,0x2e,0x8a,0x89,0x0b,0x0a,0x11,0x47,0xe9,0x4c,0xc4,0x1a,0xdb,0x10,0x23,0xd3,0x29,0xc8,0x04,0x6e,0x4b,0xf8,0x95,0x0d,0xa2,0x08,0x68,0xd5,0x96,0x45,0x72,0x26,0x56,0xaa,0x03,0x08,0x5e,0x2a,0x2f,0x52,0x3a,0xa6,0x40,0x03,0x63,0x6c,0xba,0x24,0x6e,0xec,0x43,0x29,0xe2,0x69,0xf0,0x96,0x98,0x9e,0x86,0x6a,0x29,0x96,0xda,0xb5,0x90,0x05,0x00,0x3f,0x28,0x29,0x80,0x15,0xeb,0xad,0x50,0x09,0xa0,0x34,0x1a,0xc7,0x66,0x52,0x63,0x5f,0x64,0x11,0x0e,0x28,0x3a,0x91,0x9a,0x9f,0x81,0x31,0x2d,0x0c,0x77,0x8f,0x02,0xee,0x15,0x95,0xa3,0x38,0xe1,0x49,0x5a,0x56,0xa6,0x22,0xac,0xc6,0x9a,0xd4,0x7d,0x5b,0x58,0x62,0x43,0x87,0xe7,0x7a,0x3d,0x6b,0xc7,0x37,0xba,0x1b,0x97,0x07,0xb2,0xc5,0x79,0xb6,0x44,0x17,0xd6,0xac,0x06,0x06,0xfa,0x4c,0x45,0x31,0xbf,0xb9,0x7f,0x54,0xa8,0xf5,0x06,0x02,0xdc,0x14,0x06,0x97,0x62,0x68,0x49,0x9a,0xcd,0xe9,0x6f,0x7f,0x88,0xa9,0x47,0x0b,0xb6,0x20,0xc6,0x18,0x36,0x10,0xba,0x62,0xb3,0xa4,0x24,0x4a,0x91,0xc3,0x71,0xcc,0xbf,0x73,0x1c,0xf5,0x3f,0xc3,0x5f,0x01,0xad,0x81,0x6b,0x40,0x6e,0x38,0x00,0x00
};
AsyncWebServer webserver(80);
#endif


#if WEBSOCKETS
void onMessage(WebsocketsClient& client, WebsocketsMessage message) {
  Serial.print("Message Received from javascript websocket client: ");
  Serial.println(message.data()); 
  sql = message.data().c_str();
  Serial.println("=============== Filtered data by SQL statement received from client=====");
  Serial.println();
  //Serial.println(sql.c_str());
  #if SQLITE3
  selectData();
  #endif
  Serial.println();
  Serial.println("=========================================================================");
}

void pollAllClients() {    // This method goes thrugh every client and polls for new messages and events

  for(auto& client : allClients) {
    client.poll();
  }
}
#endif

#if MQTT
std::string sentTopic = "data";
std::string receivedTopic = "command";
MqttBroker broker(1883);
MqttClient myClient(&broker);

void receivedMessage(const MqttClient* source, const Topic& topic, const char* payload, size_t length)
{
  Serial << endl << "Received message on topic " << receivedTopic.c_str() << " with payload " << payload << endl;
    if (receivedTopic == "command")
  {
    mac[0] = atoi(&payload[0]);
    mac[1] = atoi(&payload[3]);
    mac[2] = atoi(&payload[6]);
    mac[3] = atoi(&payload[9]);
    mac[4] = atoi(&payload[12]);
    mac[5] = atoi(&payload[15]);
   }
 }
#endif

const char* ntpServer = "pool.ntp.org";
unsigned long epoch; 

unsigned long getTime() {
  time_t now;
  struct tm timeinfo;
  if (!getLocalTime(&timeinfo)) {
    //Serial.println("Failed to obtain time");
    return(0);
  }
  time(&now);
  return now;
}

#if SQLITE3
 
 const char* data = "Data requested";
 static int callback(void *data, int argc, char **argv, char **azColName) {
  Serial.print((char) '[');
  int i;
  for (i = 0; i<(argc); i++) {
    if(i)
    Serial.print((char) ',');
    Serial.printf("%s", argv[i] ? argv[i] : "NULL");
    }
    
  Serial.printf("],\n");
  
  return 0;
  }

int db_open(const char *filename, sqlite3 **db) {
   rc = sqlite3_open(filename, db);
   if (rc) {
       Serial.printf("Can't open database: %s\n", sqlite3_errmsg(*db));
       return rc;
   } else {
       Serial.printf("Opened database successfully\n");
   }
   return rc;
}

char *zErrMsg = 0;
int db_exec(sqlite3 *db, const char *sql) {
   Serial.println(sql);
   long start = micros();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   Serial.print(F("Time taken in milliseconds: "));
   Serial.println((micros()-start)/1000);
   return rc;
}
#endif

void setup() {

  Serial.begin(115200);
  
  delay(500);
  Serial << "Starting client and AP......." << endl;

  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {   Serial << '-'; delay(500); }

  Serial << "Connected to " << ssid << " IP address: " << WiFi.localIP() << endl;

  WiFi.softAP(apSSID, apPassword, apChannel, hidden);
  esp_wifi_set_event_mask(WIFI_EVENT_MASK_NONE); // This line is must to activate probe request received event handler.
  Serial << "The AP mac address is " << WiFi.softAPmacAddress().c_str() << endl;
  Serial << "Access point started with SSID " << apSSID << " and webserver started at IP address: " << WiFi.localIP() << endl;
  Serial.print("Websocket Server at Port: ");
  Serial.println(wsport);
  Serial << "Connected to " << ssid << " IP address: " << WiFi.localIP() << endl;
  
#if ASYNCWEBSERVER
    webserver.on("/", HTTP_GET, [](AsyncWebServerRequest * request) 
    {
    AsyncWebServerResponse *response = request->beginResponse_P(200, "text/html", index_html_gz, sizeof(index_html_gz));
    response->addHeader("Content-Encoding", "gzip");
    request->send(response);
    });
    
    webserver.begin();
#endif
    
#if WEBSOCKETS
    WSserver.listen(wsport);
#endif

#if MQTT
    broker.begin();

  // ============= Client Subscribe ================
    myClient.setCallback(receivedMessage);
    myClient.subscribe(receivedTopic);
    myClient.subscribe(sentTopic);
#endif

#if PROBEREQUESTS
    WiFi.onEvent(probeRequest, SYSTEM_EVENT_AP_PROBEREQRECVED);
    Serial << "Waiting for probe requests ... " << endl;
#endif
  
  configTime(0, 0, ntpServer);
  epoch = getTime();
  Serial.print("Epoch Time: ");
  Serial.println(epoch);
  
  delay(1000);

#if SQLITE3     
 
   if (!LITTLEFS.begin(FORMAT_LITTLEFS_IF_FAILED)) {
       Serial.println("Failed to mount file system");
       return;
   }

   // list LITTLEFS contents
   File root = LITTLEFS.open("/");
   if (!root) {
       Serial.println("- failed to open directory");
       return;
   }
   if (!root.isDirectory()) {
       Serial.println(" - not a directory");
       return;
   }
   File file = root.openNextFile();
   while (file) {
       if (file.isDirectory()) {
           Serial.print("  DIR : ");
           Serial.println(file.name());
       } else {
           Serial.print("  FILE: ");
           Serial.print(file.name());
           Serial.print("\tSIZE: ");
           Serial.println(file.size());
       }
       file = root.openNextFile();
   }
#if FIRSTTIME  
   // remove existing file so we start clean for this test.
   LITTLEFS.remove("/test1.db");
   LITTLEFS.remove("/test1.db-journal");


   // Create the db file before trying to open it.
   if (!LITTLEFS.exists("/test1.db")){
      File file = LITTLEFS.open("/test1.db", FILE_WRITE);   //  /littlefs is automatically added to the front 
      file.close();
   }

   // We also need to create the journal file.
   if (!LITTLEFS.exists("/test1.db-journal")){
      File file = LITTLEFS.open("/test1.db-journal", FILE_WRITE);   //  /littlefs is automatically added to the front 
      file.close();
   }
#endif

   sqlite3_initialize();

   if (db_open("/littlefs/test1.db", &db1))
       return;
#if FIRSTTIME
   rc = db_exec(db1, "CREATE TABLE test1 (id INTEGER, Date, Time, Hour, Location, Voltage, RSSI, Temperature, Humidity, Pressure, Light);");
   if (rc != SQLITE_OK) {
       sqlite3_close(db1);
       return;
       }
       sqlite3_close(db1);       
#endif  
#endif   
}

void loop() {

#if WEBSOCKETS
 while (WSserver.available()) {
    
    if(WSserver.poll()) {  // if there is a client that wants to connect
      
      Serial.println("Accepting a new client!");
      WebsocketsClient client = WSserver.accept(); // Accept the connection
      client.onMessage(onMessage);                 // Register callback
      
      allClients.push_back(client); // Store it for later use
     }
      
      pollAllClients();      // Check for updates in all clients
      
      #if SQLITE3
       if (triggerInsertData == 1) { // Insert data if new data received
       insertData(); 
       triggerInsertData = 0; 
       } 
      #endif
      
      #if MQTT
       broker.loop();  // Don't forget to add loop for every broker and clients
       myClient.loop();
      #endif
   }  
#endif  
} // End of loop

#if SQLITE3
void insertData()
{
   epoch = getTime();
   
   if (!LITTLEFS.begin(FORMAT_LITTLEFS_IF_FAILED)) {
       Serial.println("Failed to mount file system");
       return;
   }

   //Create the db file if it does not exist before trying to open it.
   if (!LITTLEFS.exists("/test1.db")){
      File file = LITTLEFS.open("/test1.db", FILE_WRITE);   //  /littlefs is automatically added to the front 
      file.close();
   }

   // We also need to create the journal file if it does not exist.
   if (!LITTLEFS.exists("/test1.db-journal")){
      File file = LITTLEFS.open("/test1.db-journal", FILE_WRITE);   //  /littlefs is automatically added to the front 
      file.close();
   }

   sqlite3_initialize();

   if (db_open("/littlefs/test1.db", &db1))
       return;
   
   insert = "insert into test1 values (";
  
               insert += epoch;
               insert += ", ";
               insert += 060321;
               insert += ", ";
               insert += 1535;
               insert += ", ";
               insert += 15;
               insert += ", ";
               insert += device;
               insert += ", ";
               insert += rssi;
               insert += ", ";
               insert += voltage;
               insert += ", ";
               insert += sensorValues[0];
               insert += ", ";
               insert += sensorValues[1];
               insert += ", ";
               insert += sensorValues[2];
               insert += ", ";
               insert += sensorValues[3];
               insert += ")";
               Serial.println(insert);
              
   
   rc = db_exec(db1, insert.c_str());
   if (rc != SQLITE_OK) {
       sqlite3_close(db1);
       return;
   }
   
   
   sqlite3_close(db1);
   
   // list LITTLEFS contents
   File root = LITTLEFS.open("/");
   if (!root) {
       Serial.println("- failed to open directory");
       return;
   }
   if (!root.isDirectory()) {
       Serial.println(" - not a directory");
       return;
   }
   File file = root.openNextFile();
   while (file) {
       if (file.isDirectory()) {
           Serial.print("  DIR : ");
           Serial.println(file.name());
       } else {
           Serial.print("  FILE: ");
           Serial.print(file.name());
           Serial.print("\tSIZE: ");
           Serial.println(file.size());
       }
       file = root.openNextFile();
   }
 }
 
void selectData() {

  if (db_open("/littlefs/test1.db", &db1))
   return;
       
  rc = db_exec(db1, sql.c_str());
   if (rc != SQLITE_OK) {
       sqlite3_close(db1);
       return;
   }
   
   sqlite3_close(db1);
   
   // list LITTLEFS contents
   File root = LITTLEFS.open("/");
   if (!root) {
       Serial.println("- failed to open directory");
       return;
   }
   if (!root.isDirectory()) {
       Serial.println(" - not a directory");
       return;
   }
   File file = root.openNextFile();
   while (file) {
       if (file.isDirectory()) {
           Serial.print("  DIR : ");
           Serial.println(file.name());
       } else {
           Serial.print("  FILE: ");
           Serial.print(file.name());
           Serial.print("\tSIZE: ");
           Serial.println(file.size());
       }
       file = root.openNextFile();
   }
 }
#endif

#if PROBEREQUESTS

void probeRequest(WiFiEvent_t event, WiFiEventInfo_t info) 
{
    Serial.print("Probe Received :  ");
    for (int i = 0; i < 6; i++) {
    Serial.printf("%02X", info.ap_probereqrecved.mac[i]);
    if (i < 5)Serial.print(":");
    }
    Serial.println();

      if (info.ap_probereqrecved.mac[0] == 6 || info.ap_probereqrecved.mac[0] == 16 || info.ap_probereqrecved.mac[0] == 26 || info.ap_probereqrecved.mac[0] == 36 || info.ap_probereqrecved.mac[0] == 46 || info.ap_probereqrecved.mac[0] == 56 || info.ap_probereqrecved.mac[0] == 66 || info.ap_probereqrecved.mac[0] == 76 || info.ap_probereqrecved.mac[0] == 86 || info.ap_probereqrecved.mac[0] == 96 || info.ap_probereqrecved.mac[0] == 106 || info.ap_probereqrecved.mac[0] == 116 || info.ap_probereqrecved.mac[0] == 126 || info.ap_probereqrecved.mac[0] == 136 || info.ap_probereqrecved.mac[0] == 146 || info.ap_probereqrecved.mac[0] == 156 || info.ap_probereqrecved.mac[0] == 166 || info.ap_probereqrecved.mac[0] == 176 || info.ap_probereqrecved.mac[0] == 186 || info.ap_probereqrecved.mac[0] == 196 || info.ap_probereqrecved.mac[0] == 206 || info.ap_probereqrecved.mac[0] == 216 || info.ap_probereqrecved.mac[0] == 226 || info.ap_probereqrecved.mac[0] == 236 || info.ap_probereqrecved.mac[0] == 246) // only accept data from certain devices.
       {
       
      //sendCommand();

      if (info.ap_probereqrecved.mac[1] == 06) 
      { // only accept data from device with voltage as a sensor type at byte 1.
               if (device == 06) { for (int i = 0; i < 4; i++) sensorTypes[i] = Livingroom[i];} 
               if (device == 16) { for (int i = 0; i < 4; i++) sensorTypes[i] = Kitchen[i];}
               if (device == 26) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bedroom1[i];} 
               if (device == 36) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bedroom2[i];}
               if (device == 46) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bedroom3[i];} 
               if (device == 56) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bedroom4[i];}
               if (device == 66) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bathroom1[i];} 
               if (device == 76) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bathroom2[i];}
               if (device == 86) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bathroom3[i];} 
               if (device == 96) { for (int i = 0; i < 4; i++) sensorTypes[i] = Bathroom4[i];}
               if (device == 106) { for (int i = 0; i < 4; i++) sensorTypes[i] = Laundry[i];} 
               if (device == 116) { for (int i = 0; i < 4; i++) sensorTypes[i] = Boiler[i];}
               if (device == 126) { for (int i = 0; i < 4; i++) sensorTypes[i] = Workshop[i];} 
               if (device == 136) { for (int i = 0; i < 4; i++) sensorTypes[i] = Garage[i];}
               if (device == 146) { for (int i = 0; i < 4; i++) sensorTypes[i] = Office[i];}
               if (device == 156) { for (int i = 0; i < 4; i++) sensorTypes[i] = Tank[i];} 
               if (device == 166) { for (int i = 0; i < 4; i++) sensorTypes[i] = Solar[i];}
               deviceStatus[0] = info.ap_probereqrecved.mac[2];
               deviceStatus[1] = info.ap_probereqrecved.mac[3];
               deviceStatus[2] = info.ap_probereqrecved.mac[4];
               deviceStatus[3] = info.ap_probereqrecved.mac[5];
      } else {

      device = info.ap_probereqrecved.mac[0];
      rssi = info.ap_probereqrecved.rssi;         
      voltage = info.ap_probereqrecved.mac[1];
      voltage = voltage * 2;
                  
      sensorValues[0] = info.ap_probereqrecved.mac[2];
      sensorValues[1] = info.ap_probereqrecved.mac[3];
      sensorValues[2] = info.ap_probereqrecved.mac[4];
      sensorValues[3] = info.ap_probereqrecved.mac[5];
              
      }

      if (voltage > 2.50 && voltage < 3.60) 
      {
      
      sprintf (str, "{");
      sprintf (s, "\"%s\":\"%i\"", "Location", device);    strcat (str, s);
      sprintf (s, ",\"%s\":\"%i\"", "Voltage", voltage);    strcat (str, s);
      sprintf (s, ",\"%i\":\"%i\"", sensorTypes[0], sensorValues[0]); strcat (str, s);
      sprintf (s, ",\"%i\":\"%i\"", sensorTypes[1], sensorValues[1]); strcat (str, s);
      sprintf (s, ",\"%i\":\"%i\"", sensorTypes[2], sensorValues[2]); strcat (str, s);
      sprintf (s, ",\"%i\":\"%i\"", sensorTypes[3], sensorValues[3]); strcat (str, s);
      sprintf (s, "}"); strcat (str, s);
              
      Serial.println();
      Serial.println("Following ## Sensor Values ## receiced from remote device  & published via MQTT: ");
      Serial.println(str);
      Serial.println();
      #if MQTT
      myClient.publish("sensor", str);
      #endif
      if (voltage < 2.50) {      // if voltage of battery gets to low, print the warning below.
         //myClient.publish("Warning/Battery Low", location);
       }
     }

   if (info.ap_probereqrecved.mac[3] == apChannel) {
     
     sprintf (str, "{");
     sprintf (s, "\"%s\":\"%i\"", "Location", device);    strcat (str, s);
     sprintf (s, ",\"%s\":\"%i\"", "RSSI", info.ap_probereqrecved.rssi); strcat (str, s);
     sprintf (s, ",\"%s\":\"%i\"", "MODE", deviceStatus[0]); strcat (str, s);
     sprintf (s, ",\"%s\":\"%i\"", "CHANNEL", deviceStatus[1]); strcat (str, s);
     sprintf (s, ",\"%s\":\"%i\"", "IP", deviceStatus[2]); strcat (str, s);
     sprintf (s, ",\"%s\":\"%i\"", "Sleeptime", deviceStatus[3]); strcat (str, s);
     sprintf (s, "}"); strcat (str, s);
                           
     Serial.println();
     Serial.println("Following ## Device Status ## receiced from remote device & published via MQTT: ");
     Serial.println(str);
     Serial.println();
     #if MQTT
     myClient.publish("device", str);
     #endif
     triggerInsertData = 1;   
    }
   }
  }
#endif

Thanks.

16

Ok, I just wanted to challenge you on the architecture of the project, to be sure there wasn't an easier and better way to handle the data to avoid the reformatting you are proposing.

To do what you described, you will need to use a 2-dimensional array in your Arduino code. As the results arrive from the database, you would write them into the array, row-by-row. Once all the results have been received, to send the data onwards, reformatted, you would read back the data from the array, but this time column-by-column.

Given the complexity of the Arduino code you posted above, and your familiarity with JavaScript, I expect you to make some kind of an attempt to write the necessary code. If you cannot make this work, post the attempt here and I and other forum members will be pleased to help you.

17

@PaulRB Thank you for all your help. Keep helping others.

18

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.