hardlog commented 5 years ago

Hello,

This is a great Tutorial and a great library. Thank you! Is it possible to transmit "Custom Parameters" via the Autoconnect menu (/ _ac)? I would like to be able to enter the address and topic of the MQTT Broker in the Autoconnect menu to use it later in my Sketch.

Thank you Flo

Hieromon commented 5 years ago

There are many requests for the custom data entry function in part of AutoConnect 's menu transition like as your recommendation. It will be similar to the user own code as the exit routines or callback functions. I have been thinking from time to time and have some ideas.

Add custom parameters to the Configure new AP screen
Add user's own screen created by PageBuilder library to menu transition

I have not decided which one to implement. Both have advantages and disadvantages. Its enhancement will be the main title of the next update.

cruizg commented 5 years ago

You stole the question, I was about to write the same, the idea is that with your library we can send in addition to the user and password, additional parameters and saved in the ESP8266 / ESP32 for example MQTT values or values sent from a mobile app that wants send additional to user and password parameters that register the device and then use it in the sckets.

Hieromon commented 5 years ago

That's right. I have already started designing it. It is expected to be released within a few weeks. That release can prepare the add-on page adding input fields or buttons optionally and invoke them from the AutoConnect menu. You will be able to retrieve the inputted values in add-on page from sketches and can respond by the customized page. However, saving/loading of user parameters will be further scheduled.

ageurtse commented 5 years ago

Need this to for my project.

Hieromon commented 5 years ago

Now, I am staging a new version that supports this improvement on the enhance/AutoConnectAux branch, but I found a potential bug related to PROGMEM. Also, it does not work well with LwIP v2 of ESP8266 core 2.4.2. The behavior of WiFi connection with AP_STA mode seems changed. Currently, I'm giving priority to solving it. The next version release you want will be delayed.

Naboo2604 commented 5 years ago

Hi, thank you for this very cool library. i build IoT weather sensors for family and friends and use your lib for self-configuration. it would be great if you implement custom fields, so i can use it for location data and custom name in the autoconfig process. thanks again and merry christmas, markus

ageurtse commented 5 years ago

When looking at the mqtt examples in https://github.com/Hieromon/AutoConnect/tree/enhance/AutoConnectAux there are some custom fields.

Keep in mind that this branche is the development branche, so there could be some isues.

JoeyUson commented 5 years ago

Any idea how to include in the parameters the Server IP, USER and PASSWORD for use later in connection to mySQL database resides in other server or Raspi

Hieromon commented 5 years ago

@JoeyUson What you want to do is similar to the example mqttRSSI in the library. However, the following two conditions will be added to it.

To available the MySQL settings page, ESP8266/ESP32 has already established a connection to AP with STA mode. In other words, the menu of MySQL parameters settings does not appear while processing in Captive portal.
The response of the MySQL parameters setting page will return the result of the connection to MySQL.

Is it right? If so, you can control menu display dynamically using the menu function and the sketch as like this:

#include <ESP8266WiFi.h>
#include <ESP8266WebServer.h>
#include <AutoConnect.h>
#include <MySQL_Connection.h>

const static char* mysqlSettingPage PROGMEM = R"(
{
  "title": "MySQL Setting",
  "uri": "/mysql_setting",
  "menu": false,
  "element": [
    {
      "name": "header",
      "type": "ACText",
      "value": "<h2>MySQL broker settings</h2>",
      "style": "text-align:center;color:#2f4f4f;padding:10px;"
    },
    {
      "name": "server",
      "type": "ACInput",
      "value": "",
      "label": "Server",
      "placeholder": "MySQL server IP address"
    },
    {
      "name": "user",
      "type": "ACInput",
      "label": "user"
    },
    {
      "name": "password",
      "type": "ACInput",
      "label": "password"
    },
    {
      "name": "connect",
      "type": "ACSubmit",
      "value": "Connect",
      "uri": "/mysql_conn"
    },
    {
      "name": "discard",
      "type": "ACSubmit",
      "value": "Discard",
      "uri": "/ac"
    }
  ]
}
)";

const static char* mysqlConnectPage PROGMEM = R"(
{
  "title": "MySQL Setting",
  "uri": "/mysql_conn",
  "menu": false,
  "element": [
    {
      "name": "caption",
      "type": "ACText",
      "value": "<h4>MySQL connection</h4>",
      "style": "text-align:center;color:#2f4f4f;padding:10px;"
    },
    {
      "name": "Result",
      "type": "ACText"
    },
    {
      "name": "OK",
      "type": "ACSubmit",
      "value": "OK",
      "uri": "/"
    }
  ]
}
)";

ESP8266WebServer  webServer;
AutoConnect     portal(webServer);
AutoConnectAux  mysqlSetting;
AutoConnectAux  mysqlConnect;

WiFiClient client;
MySQL_Connection conn((Client *)&client);

void handleRoot() {
  String  content =
    "<html>"
    "<head>"
    "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">"
    "</head>"
    "<body>"
    "Your sketch application there."
    "<p style=\"padding-top:5px;text-align:center\">" AUTOCONNECT_LINK(COG_24) "</p>"
    "</body>"
    "</html>";
  webServer.send(200, "text/html", content);
}

String onConnect(AutoConnectAux& aux, PageArgument& args) {
  // here, obtain parameters to connect MySQL
  IPAddress server;
  server.fromString(mysqlSetting.getElement<AutoConnectText>("server").value);
  String user = mysqlSetting.getElement<AutoConnectText>("user").value;
  String password = mysqlSetting.getElement<AutoConnectText>("password").value;

  unsigned long tm = millis();
  while (conn.connect(server, 3306, user.c_str(), password.c_str()) != true) {
    if (millis() - tm > 10000)
      break;
    delay(200);
    Serial.print('.');
  }
  AutoConnectText& result = aux.getElement<AutoConnectText>("Result");
  if (conn.connected())
    result.value = "Connected";
  else
    result.value = "Timeout";
  return String();
}

void setup() {
  delay(1000);
  Serial.begin(115200);
  Serial.println();
  mysqlSetting.load(mysqlSettingPage);
  mysqlSetting.menu(false);
  mysqlConnect.load(mysqlConnectPage);
  mysqlConnect.menu(false);
  mysqlConnect.on(onConnect);
  webServer.on("/",handleRoot);
  portal.begin();
}

void loop() {
  // MySQL setting menu control
  bool mysqlMenu = WiFi.status() == WL_CONNECTED;
  mysqlSetting.menu(mysqlMenu);

  if (conn.connected()) {
    // here, your actual sketch with using MySQL
  }

  portal.handleClient();
}

The above code has not been tested.

JoeyUson commented 5 years ago

@JoeyUson What you want to do is similar to the example mqttRSSI in the library. However, the following two conditions will be added to it.

To available the MySQL settings page, ESP8266/ESP32 has already established a connection to AP with STA mode. In other words, the menu of MySQL parameters settings does not appear while processing in Captive portal.
The response of the MySQL parameters setting page will return the result of the connection to MySQL.

Is it right? If so, you can control menu display dynamically using the menu function and the sketch as like this:

#include <ESP8266WiFi.h>
#include <ESP8266WebServer.h>
#include <AutoConnect.h>
#include <MySQL_Connection.h>

const static char* mysqlSettingPage PROGMEM = R"(
{
  "title": "MySQL Setting",
  "uri": "/mysql_setting",
  "menu": false,
  "element": [
    {
      "name": "header",
      "type": "ACText",
      "value": "<h2>MySQL broker settings</h2>",
      "style": "text-align:center;color:#2f4f4f;padding:10px;"
    },
    {
      "name": "server",
      "type": "ACInput",
      "value": "",
      "label": "Server",
      "placeholder": "MySQL server IP address"
    },
    {
      "name": "user",
      "type": "ACInput",
      "label": "user"
    },
    {
      "name": "password",
      "type": "ACInput",
      "label": "password"
    },
    {
      "name": "connect",
      "type": "ACSubmit",
      "value": "Connect",
      "uri": "/mysql_conn"
    },
    {
      "name": "discard",
      "type": "ACSubmit",
      "value": "Discard",
      "uri": "/ac"
    }
  ]
}
)";

const static char* mysqlConnectPage PROGMEM = R"(
{
  "title": "MySQL Setting",
  "uri": "/mysql_conn",
  "menu": false,
  "element": [
    {
      "name": "caption",
      "type": "ACText",
      "value": "<h4>MySQL connection</h4>",
      "style": "text-align:center;color:#2f4f4f;padding:10px;"
    },
    {
      "name": "Result",
      "type": "ACText"
    },
    {
      "name": "OK",
      "type": "ACSubmit",
      "value": "OK",
      "uri": "/"
    }
  ]
}
)";

ESP8266WebServer  webServer;
AutoConnect     portal(webServer);
AutoConnectAux  mysqlSetting;
AutoConnectAux  mysqlConnect;

WiFiClient client;
MySQL_Connection conn((Client *)&client);

void handleRoot() {
  String  content =
    "<html>"
    "<head>"
    "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">"
    "</head>"
    "<body>"
    "Your sketch application there."
    "<p style=\"padding-top:5px;text-align:center\">" AUTOCONNECT_LINK(COG_24) "</p>"
    "</body>"
    "</html>";
  webServer.send(200, "text/html", content);
}

String onConnect(AutoConnectAux& aux, PageArgument& args) {
  // here, obtain parameters to connect MySQL
  IPAddress server;
  server.fromString(mysqlSetting.getElement<AutoConnectText>("server").value);
  String user = mysqlSetting.getElement<AutoConnectText>("user").value;
  String password = mysqlSetting.getElement<AutoConnectText>("password").value;

  unsigned long tm = millis();
  while (conn.connect(server, 3306, user.c_str(), password.c_str()) != true) {
    if (millis() - tm > 10000)
      break;
    delay(200);
    Serial.print('.');
  }
  AutoConnectText& result = aux.getElement<AutoConnectText>("Result");
  if (conn.connected())
    result.value = "Connected";
  else
    result.value = "Timeout";
  return String();
}

void setup() {
  delay(1000);
  Serial.begin(115200);
  Serial.println();
  mysqlSetting.load(mysqlSettingPage);
  mysqlSetting.menu(false);
  mysqlConnect.load(mysqlConnectPage);
  mysqlConnect.menu(false);
  mysqlConnect.on(onConnect);
  webServer.on("/",handleRoot);
  portal.begin();
}

void loop() {
  // MySQL setting menu control
  bool mysqlMenu = WiFi.status() == WL_CONNECTED;
  mysqlSetting.menu(mysqlMenu);

  if (conn.connected()) {
    // here, your actual sketch with using MySQL
  }

  portal.handleClient();
}

The above code has not been tested.

Just new in using AutoConnect with MySQL, however I add "portal.join({ mysqlSetting });" just after mysqlConnect.on(onConnect); and the setting menu appear in the menu selection but after filling the form it give 403 error code

Hieromon commented 5 years ago

At first, can you determine the state of LAN transparency with a sniffer tool such as Wireshark? 403 response has nothing to do with AutoConnect.

Hieromon commented 5 years ago

@JoeyUson I reviewed the code I presented. Your point is partially correct. The above code does not have a custom web page joined to the portal. The code to add is below.

portal.join({ mysqlSetting, mysqlConnect });

However, the cause of the 403 response is unknown.

JoeyUson commented 5 years ago

Thanks a lot for the reply. I checked the sketch for MySQLconnect and use the supplied sketch (simplified) and I'm having problem with connecting to database and so nothing related to AutoConnect.

Have a good day.

On Thu, Apr 4, 2019 at 3:00 AM Hieromon Ikasamo notifications@github.com wrote:

@JoeyUson https://github.com/JoeyUson I reviewed the code I presented. Your point is partially correct. The above code does not have a custom web page joined to the portal. The code to add is below.

portal.join({ mysqlSetting, mysqlConnect });

However, the cause of the 403 response is unknown.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Hieromon/AutoConnect/issues/17#issuecomment-479700333, or mute the thread https://github.com/notifications/unsubscribe-auth/ApfpsZHTgEW9HXfGhrJpY5v8E6vvx_o-ks5vdUCfgaJpZM4XvvBJ .

JoeyUson commented 5 years ago

I already solve MySQL database and it is only the user grant access. Used MySQL sample database sketch and able to insert data at regular interval from ESP8266.

These 3 give type mismatch error in Serial Monitor server.fromString(mysqlSetting.getElement("server").value); String user = mysqlSetting.getElement("user").value; String password = mysqlSetting.getElement("password").value;

Hieromon commented 5 years ago

@JoeyUson Sorry, I took a mistake. The correct code is:

  server.fromString(mysqlSetting.getElement<AutoConnectInput>("server").value);
  String user = mysqlSetting.getElement<AutoConnectInput>("user").value;
  String password = mysqlSetting.getElement<AutoConnectInput>("password").value;

JoeyUson commented 5 years ago

//Here the complete code for the test I did for AutoConnect and MySQL connection after the changes // It is working now , Thanks a lot for the assistance `#if defined(ARDUINO_ARCH_ESP8266)

include

#include <ESP8266WebServer.h>

elif defined(ARDUINO_ARCH_ESP32)

#include <WiFi.h>
#include <WebServer.h>

endif

include

const static char* mysqlSettingPage PROGMEM = R"( { "title": "MySQL Setting", "uri": "/mysql_setting", "menu": false, "element": [ { "name": "header", "type": "ACText", "value": "

MySQL broker settings

", "style": "text-align:center;color:#2f4f4f;padding:10px;" }, { "name": "server", "type": "ACInput", "value": "", "label": "Server", "placeholder": "MySQL server IP address" }, { "name": "user", "type": "ACInput", "label": "user" }, { "name": "password", "type": "ACInput", "label": "password" }, { "name": "connect", "type": "ACSubmit", "value": "Connect", "uri": "/mysql_conn" }, { "name": "discard", "type": "ACSubmit", "value": "Discard", "uri": "/_ac" } ] } )";

const static char* mysqlConnectPage PROGMEM = R"( { "title": "MySQL Setting", "uri": "/mysql_conn", "menu": false, "element": [ { "name": "caption", "type": "ACText", "value": "

MySQL connection

", "style": "text-align:center;color:#2f4f4f;padding:10px;" }, { "name": "Result", "type": "ACText" }, { "name": "OK", "type": "ACSubmit", "value": "OK", "uri": "/" } ] } )";

if defined(ARDUINO_ARCH_ESP8266)

ESP8266WebServer    webServer;

elif defined(ARDUINO_ARCH_ESP32)

WebServer           webServer;

endif

AutoConnect portal(webServer); AutoConnectAux mysqlSetting; AutoConnectAux mysqlConnect;

WiFiClient client;

// database related MySQL_Connection conn((Client )&client); MySQL_Cursor cursor; // Sample query const char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";

void handleRoot() { // this is the content of the root page to be display as default String content = "" "" "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">" "" "" "Your sketch application there." "<p style=\"padding-top:5px;text-align:center\">" AUTOCONNECT_LINK(COG_24) "

" "" ""; webServer.send(200, "text/html", content); }

String onConnect(AutoConnectAux& aux, PageArgument& args) { // here, obtain parameters to connect MySQL IPAddress server;

server.fromString(mysqlSetting.getElement<AutoConnectInput>("server").value);
String user = mysqlSetting.getElement<AutoConnectInput>("user").value;
String password = mysqlSetting.getElement<AutoConnectInput>("password").value;

unsigned long tm = millis();

while (conn.connect(server, 3306, user.c_str(), password.c_str()) != true) {
    if (millis() - tm > 500)
        break;
    delay(200);
    Serial.print('.');
}

AutoConnectText& result = aux.getElement<AutoConnectText>("Result");
if (conn.connected()) {
    result.value = "Connected";
    // create MySQL cursor object if connected
    cursor = new MySQL_Cursor(&conn);
}
else
    result.value = "Timeout";
return String();

}

void setup() { delay(1000); Serial.begin(115200); Serial.println(); mysqlSetting.load(mysqlSettingPage); mysqlSetting.menu(false); // hide the myssqlSetting from menu initially mysqlConnect.load(mysqlConnectPage); mysqlConnect.menu(false); // should always be hidden and call by mysqlsetting only mysqlConnect.on(onConnect); // to be executed for database connection

portal.join({ mysqlSetting,mysqlConnect});  // added in the standard menu items
webServer.on("/", handleRoot);
portal.begin();

}

void loop() { // MySQL setting menu control bool mysqlMenu = WiFi.status() == WL_CONNECTED; mysqlSetting.menu(mysqlMenu); // will display the mysqlSetting once connected to WiFi

static unsigned long tm = millis();

if (conn.connected())                   // if still connected to database
    if (millis() - tm > 5000) {
        cursor->execute(INSERT_SQL);        // Insert Hello Message
        tm = millis();
    }

portal.handleClient();

}`

With the above sketch I noticed that if you reset the board the WiFi connection is restore but for MySQL connection you need to feed in again the server,user and password for database access in order to reconnect. Is it possible to save the 3 data and during reboot will be use to reconnect to database and of course if unsuccessful will give timeout ?

Hieromon commented 5 years ago

You can save the inputted parameter as server IP, user, and password for MySQL connection, to SPIFFS on the ESP module. The AutoConnectAux::saveElement function outputs the specified elements to the stream along with the elements value. For example, to save the server IP address, user, password after conn.connect is successful, you can use the following code:

#include <FS.h>
#define PARAM_FILE      "/param.json"

// In onConnect function

  if (conn.connected()) {
    saveParams(PARAM_FILE);    // Insert this line
    result.value = "Connected";
    // create MySQL cursor object if connected
    cursor = new MySQL_Cursor(&conn);
  }

// Add new function to save parameter as the below:
void saveParams(const char* paramFile) {
  SPIFFS.begin();
  File param = SPIFFS.open(paramFile, "w");
  mysqlSetting.saveElement(param, { "server", "user", "password" });
  param.close();
  SPIFFS.end();
}

And, load this parameter file in advance after resetting.

void setup() {
  delay(1000);
  Serial.begin(115200);
  Serial.println();
  mysqlSetting.load(mysqlSettingPage);

  loadParams(PARAM_FILE);       // Load the parameters in advance

  mysqlSetting.menu(false); // hide the myssqlSetting from menu initially
  mysqlConnect.load(mysqlConnectPage);
...
}

// Add new function to load the stored parameter as the below:
void loadParams(const char* paramFile) {
  SPIFFS.begin();
  File param = SPIFFS.open(paramFile, "r");
  if (param) {
    bool rc = mysqlSetting.loadElement(param);    // Load the elements with parameters
    if (rc)
      Serial.println(String(paramFile) + " loaded");
    else
      Serial.println(String(paramFile) + " failed to load");
    param.close();
  }
  SPIFFS.end();
}

But this is still not enough. It's smart that the menu is available when you time out with parameters loaded after reset as you said. Therefore we can take the further measures.

Make server IP, user, password to global variables. It is accessible at any time.
The loaded parameters are stored in their global variables and retrieved as needed.
The MySQL connection is executed by another function. This function takes parameters from the above global variables.
In the loop function, we do the sketch application. Activate the menu if the MySQL connection lost.

Here, the final code looks like this

#if defined(ARDUINO_ARCH_ESP8266)
#include <ESP8266WiFi.h>
#include <ESP8266WebServer.h>
#elif defined(ARDUINO_ARCH_ESP32)
#include <WiFi.h>
#include <WebServer.h>
#include <SPIFFS.h>
#endif

#include <FS.h>
#include <AutoConnect.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

const static char* mysqlSettingPage PROGMEM = R"(
{
  "title": "MySQL Setting",
  "uri": "/mysql_setting",
  "menu": false,
  "element": [
    {
      "name": "header",
      "type": "ACText",
      "value": "MySQL broker settings",
      "style": "text-align:center;color:#2f4f4f;padding:10px;"
    },
    {
      "name": "server",
      "type": "ACInput",
      "value": "",
      "label": "Server",
      "placeholder": "MySQL server IP address"
    },
    {
      "name": "user",
      "type": "ACInput",
      "label": "user"
    },
    {
      "name": "password",
      "type": "ACInput",
      "label": "password"
    },
    {
      "name": "connect",
      "type": "ACSubmit",
      "value": "Connect",
      "uri": "/mysql_conn"
    },
    {
      "name": "discard",
      "type": "ACSubmit",
      "value": "Discard",
      "uri": "/_ac"
    }
  ]
}
)";
const static char* mysqlConnectPage PROGMEM = R"(
{
  "title": "MySQL Setting",
  "uri": "/mysql_conn",
  "menu": false,
  "element": [
    {
      "name": "caption",
      "type": "ACText",
      "value": "MySQL connection",
      "style": "text-align:center;color:#2f4f4f;padding:10px;"
    },
    {
      "name": "Result",
      "type": "ACText"
    },
    {
      "name": "OK",
      "type": "ACSubmit",
      "value": "OK",
      "uri": "/"
    }
  ]
}
)";
#if defined(ARDUINO_ARCH_ESP8266)
ESP8266WebServer webServer;
#elif defined(ARDUINO_ARCH_ESP32)
WebServer webServer;
#endif

AutoConnect portal(webServer);
AutoConnectAux  mysqlSetting;
AutoConnectAux  mysqlConnect;
WiFiClient client;

// database related
MySQL_Connection conn((Client )&client);
MySQL_Cursor cursor;
IPAddress server;
String user;
String password;
bool invokeConnect = false;

// Parameter file
#define PARAM_FILE  "/param.json"

// Sample query
const char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";

void handleRoot() {
  String  content =
    "<html>"
    "<head>"
    "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">"
    "</head>"
    "<body>"
    "Your sketch application there."
    "<p style=\"padding-top:5px;text-align:center\">" AUTOCONNECT_LINK(COG_24) "</p>"
    "</body>"
    "</html>";
  webServer.send(200, "text/html", content);
}

// Get parameters from AutoConnectElement
void getParams() {
  // here, obtain parameters to connect MySQL
  server.fromString(mysqlSetting.getElement<AutoConnectInput>("server").value);
  user = mysqlSetting.getElement<AutoConnectInput>("user").value;
  user.trim();
  password = mysqlSetting.getElement<AutoConnectInput>("password").value;
  password.trim();
  invokeConnect = true;
}

// Load parameter
void loadParams(const char* paramFile) {
  SPIFFS.begin();
  File param = SPIFFS.open(paramFile, "r");
  if (param) {
    // Load the elements with parameters
    bool rc = mysqlSetting.loadElement(param);
    if (rc) {
      // here, obtain parameters to connect MySQL
      getParams();
      Serial.println(String(paramFile) + " loaded");
    }
    else
      Serial.println(String(paramFile) + " failed to load");
    param.close();
  }
  SPIFFS.end();
}

// Save parameter
void saveParams(const char* paramFile) {
  SPIFFS.begin();
  File param = SPIFFS.open(paramFile, "w");
  mysqlSetting.saveElement(param, { "server", "user", "password" });
  param.close();
  SPIFFS.end();
}

// Custom Web page onConnect handler
String onConnect(AutoConnectAux& aux, PageArgument& args) {
  getParams();
  connectMySQL(3000);
  AutoConnectText& result = aux.getElement<AutoConnectText>("Result");
  if (conn.connected()) {
    saveParams(PARAM_FILE);
    result.value = "Connected";
    // create MySQL cursor object if connected
    cursor = new MySQL_Cursor(&conn);
  }
  else
    result.value = "Timeout";
  return String();
}

// Makes MySQL connection
bool connectMySQL(unsigned long timeout) {
  if (invokeConnect)
    return false;

  unsigned long tm = millis();
  Serial.print("MySQL connection start ");
  Serial.print(server + " ");
  Serial.print(user + " ");
  Serial.println(password);

  bool rc;
  while ((rc = conn.connect(server, 3306, user.c_str(), password.c_str())) != true) {
    if (millis() - tm > timeout) {
      // Once it times out, it does not try to connect
      // with the same parameters.
      invokeConnect = false;
      Serial.println("timeout");
      break;
    }
    delay(300);
    Serial.print('.');
  }
  return rc;
}

void setup() {
  delay(1000);
  Serial.begin(115200);
  Serial.println();
  mysqlSetting.load(mysqlSettingPage);
  loadParams(PARAM_FILE);     // Load the parameters in advance
  mysqlSetting.menu(false);   // hide the myssqlSetting from menu initially
  mysqlConnect.load(mysqlConnectPage);
  mysqlConnect.menu(false);   // should always be hidden and call by mysqlsetting only
  mysqlConnect.on(onConnect);   // to be executed for database connection

  portal.join({ mysqlSetting,mysqlConnect});    // added in the standard menu items
  webServer.on("/", handleRoot);
  portal.begin();
}

void loop() {
  // MySQL setting menu control
  bool mysqlMenu = WiFi.status() == WL_CONNECTED;
  // will display the mysqlSetting once connected to WiFi
  mysqlSetting.menu(mysqlMenu);

  static unsigned long tm = millis();
  if (conn.connected()) {                   // if still connected to database
    if (millis() - tm > 5000) {
      cursor->execute(INSERT_SQL);  // Insert Hello Message
      tm = millis();
    }
  }
  else if (mysqlMenu) {
    // WiFi connection has been established, but MySQL connection lost
    // Try to reconnect
    connectMySQL(3000);
  }

  portal.handleClient();
}

JoeyUson commented 5 years ago

I remove the delay(300) inside the loop of conn.connect.. to reduce the delay in connecting the WiFi. It seems the mySQLconnect is racing with WiFI connection and now it takes longer time to connect the WiFi. It works and the save server,user and password are retain even restart of esp8266.

Latest sketch `// AutoConnect with MySQL database connection that work for ESP8266 and ESP32

if defined(ARDUINO_ARCH_ESP8266)

#include <ESP8266WiFi.h>
#include <ESP8266WebServer.h>   
#include <ESP8266mDNS.h>                        // for using esp.local
#include <FS.h>

elif defined(ARDUINO_ARCH_ESP32)

#include <WiFi.h>
#include <WebServer.h>
#include <MDNS.h>
#include <SPIFFS.h>

endif

                                // in order to save the MySQL connection parameter to JSON

include

//**** Access Point default password ****

define AP_PASSWORD "12345678" // default password for Access Point

define DEVICE_TITLE "WiFi_Camera" // Header title to appear in webpage

const static char* mysqlSettingPage PROGMEM = R"( { "title": "MySQL Setting", "uri": "/mysql_setting", "menu": false, "element": [ { "name": "header", "type": "ACText", "value": "

MySQL broker settings

", "style": "text-align:center;color:#2f4f4f;padding:10px;" }, { "name": "mysqlserver", "type": "ACInput", "value": "", "label": "MySQLServer", "placeholder": "MySQL server IP address" }, { "name": "user", "type": "ACInput", "label": "User", "placeholder": "MySQL user"
}, { "name": "password", "type": "ACInput", "label": "Password", "placeholder": "MySQL password" }, { "name": "connect", "type": "ACSubmit", "value": "Connect", "uri": "/mysql_conn" }, { "name": "discard", "type": "ACSubmit", "value": "Discard", "uri": "/_ac" } ] } )";

const static char* mysqlConnectPage PROGMEM = R"( { "title": "MySQL Setting", "uri": "/mysql_conn", "menu": false, "element": [ { "name": "caption", "type": "ACText", "value": "

MySQL connection

", "style": "text-align:center;color:#2f4f4f;padding:10px;" }, { "name": "Result", "type": "ACText" }, { "name": "OK", "type": "ACSubmit", "value": "OK", "uri": "/" } ] } )";

// Select the correct Webserver base in processor

if defined(ARDUINO_ARCH_ESP8266)

ESP8266WebServer    webServer;

elif defined(ARDUINO_ARCH_ESP32)

WebServer           webServer;

endif

// AutoConnect declaration for menu handling with Aux page AutoConnect portal(webServer); AutoConnectConfig config; // for customization of various parameters for Autoconnect AutoConnectAux mysqlSetting; AutoConnectAux mysqlConnect;

WiFiClient client;

// database related connection declaration MySQL_Connection conn((Client *)&client); IPAddress mysqlserver; String user; String password;

// Sample query const char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";

bool invokeConnect = false; // Parameter file

define PARAM_FILE "/param.json"

/* Set Customized parameters for AutoConnect

Can be use to uniquely identified each chip
Can have a known IP for Access point / void setAP() { // define a unique SSID based on the chip ID as alternative /

if defined(ARDUINO_ARCH_ESP8266)
```
config.apid = "ESP-" + String(ESP.getChipId(), HEX);
```
elif defined(ARDUINO_ARCH_ESP32)
```
config.apid = "ESP-" + String((uint32_t)(ESP.getEfuseMac() >> 32), HEX);
```
endif

*/

// define unique access point base on device title without space config.apid = DEVICE_TITLE; config.psk = AP_PASSWORD;

// Access point Definition config.apip = IPAddress(192, 168, 240, 1); // Sets SoftAP IP address config.gateway = IPAddress(192, 168, 240, 1); // Sets WLAN router IP address same since no actual gateway config.netmask = IPAddress(255, 255, 255, 0); // Sets WLAN scope

/ If needed to have static IP address config.staip = IPAddress(192, 168, 10, 10); // Sets static IP config.staGateway = IPAddress(192, 168, 10, 1); // Sets WiFi router address config.staNetmask = IPAddress(255, 255, 255, 0); // Sets WLAN scope / config.title = DEVICE_TITLE; // Customize the menu title

portal.config(config);                               // Update the configuration

}

void handleRoot() { // this is the content of the root page to be display as default String content = "" "" "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">" "" "" "Your sketch application there." "<p style=\"padding-top:5px;text-align:center\">" AUTOCONNECT_LINK(COG_24) "

" "" ""; webServer.send(200, "text/html", content); }

// Get parameters from AutoConnectElement void getParams() { // here, obtain parameters to connect MySQL mysqlserver.fromString(mysqlSetting.getElement("mysqlserver").value); user = mysqlSetting.getElement("user").value; user.trim(); password = mysqlSetting.getElement("password").value; password.trim(); invokeConnect = true; }

// Load parameter of MySQL from JSON file bool loadParams(const char* paramFile) { bool loadedParameter = false; SPIFFS.begin(); File param = SPIFFS.open(paramFile, "r"); if (param) { // Load the elements with parameters bool rc = mysqlSetting.loadElement(param); if (rc) { // here, obtain parameters to connect MySQL // getParams(); load only parameters to Element so no need Serial.println(String(paramFile) + " loaded"); loadedParameter = true; } else Serial.println(String(paramFile) + " failed to load"); param.close(); //since able to open then close now here } SPIFFS.end(); return loadedParameter; }

// Save parameter to /param.json file void saveParams(const char* paramFile) { SPIFFS.begin(); File param = SPIFFS.open(paramFile, "w"); mysqlSetting.saveElement(param, { "mysqlserver", "user", "password" }); param.close(); SPIFFS.end(); }

// Makes MySQL connection bool connectMySQL(unsigned long timeout) {

bool rc = false;        // if connected to MySQl database

if (WiFi.status() == WL_CONNECTED) { // if connected only to WiFi will start the MySQL connecting process
    getParams(); // get the parameters from the elements of AutoConnect ,if loaded then has the value from JSON file
    Serial.println("***MySQL connection start***");
    Serial.print("MySQLServer:"); Serial.println(mysqlserver);
    Serial.println("User:" + user + " ");
    Serial.println("Password:" + password);

    unsigned long tm = millis();

    while ((rc = conn.connect(mysqlserver, 3306, user.c_str(), password.c_str())) != true) {
        if (millis() - tm > timeout) {
            // Once it times out, it does not try to connect
            // with the same parameters.
            invokeConnect = false;
            Serial.println("timeout");
            break;
        }

// delay(100); // Serial.print('.'); }

}
return rc;  // will return true if connected during the while loop else false

}

// new version // Custom Web page onConnect handler String onConnect(AutoConnectAux& aux, PageArgument& args) { connectMySQL(3000); // get MySQL credential for connection and try to connect with delay AutoConnectText& result = aux.getElement("Result"); if (conn.connected()) { // success connection to database saveParams(PARAM_FILE); // write JSON file result.value = "Connected"; } else result.value = "Timeout"; return String(); }

void setup() { delay(1000); Serial.begin(115200); Serial.println(); setAP(); // set the WiFi parameters for AutoConnect

mysqlSetting.load(mysqlSettingPage);// define the setting page
mysqlSetting.menu(false);           // hide the myssqlSetting from menu initially
loadParams(PARAM_FILE);             // Load the parameters in advance even not connected to WiFi
mysqlConnect.load(mysqlConnectPage);// define the connectpage
mysqlConnect.menu(false);           // should always be hidden and call by mysqlsetting only
mysqlConnect.on(onConnect);         // to be executed for database connection

portal.join({ mysqlSetting,mysqlConnect});  // added in the standard menu items
webServer.on("/", handleRoot);
// portal.begin();
if (portal.begin())
    if (MDNS.begin(DEVICE_TITLE)) {     // Using the device title as device.local
        MDNS.addService("http", "tcp", 80);
    }

}

void loop() { // MySQL setting menu control bool mysqlMenu = WiFi.status() == WL_CONNECTED; mysqlSetting.menu(mysqlMenu); // will display the mysqlSetting once connected to WiFi

static unsigned long tm = millis();

if (conn.connected()) {                                     // if still connected to database
    // normal process to execute while connected to MySQL
    if (millis() - tm > 5000) {
        MySQL_Cursor* cur_mem = new MySQL_Cursor(&conn);    // create MySQL cursor object if connected
        cur_mem->execute(INSERT_SQL);                       // Insert Hello Message to dbase
        delete cur_mem;                                     // Deleting the cursor also frees up memory used   

        tm = millis();                                      // get new timer as start up
    }
}
else 
    connectMySQL(3000);                 // Try to reconnect

portal.handleClient();                  // handle the interaction to WiFi

}`

Hieromon commented 5 years ago

@JoeyUson I can not evaluate it because your adjustment depends on the MySQL library but I think you understand AutoConnect's parameter loading and saving mechanism. May I close this topic?

JoeyUson commented 5 years ago

Yeh you can, its more in fine tuning..

Hieromon / AutoConnect

Custom parameters ? #17

include

elif defined(ARDUINO_ARCH_ESP32)

endif

include

include

include

MySQL broker settings

MySQL connection

if defined(ARDUINO_ARCH_ESP8266)

elif defined(ARDUINO_ARCH_ESP32)

endif

if defined(ARDUINO_ARCH_ESP8266)

elif defined(ARDUINO_ARCH_ESP32)

endif

include

include

include

define AP_PASSWORD "12345678" // default password for Access Point

define DEVICE_TITLE "WiFi_Camera" // Header title to appear in webpage

MySQL broker settings

MySQL connection

if defined(ARDUINO_ARCH_ESP8266)

elif defined(ARDUINO_ARCH_ESP32)

endif

define PARAM_FILE "/param.json"

if defined(ARDUINO_ARCH_ESP8266)

elif defined(ARDUINO_ARCH_ESP32)

endif