ESP32 connect to mysql database

[tullaMann]

Hallo,

i'm new her. I can`t find a solution for my problem. I use a esp8266 with the esp8266wifi.h and the mysql_connection.h and mysql_coursor.h in my scripts.
And now i want make the same with the esp32, but donsn`t work. I can`t include the wifi.h and the two classes from mysql_ .
He set a exception

Arduino: 1.8.5 (Windows 10), Board: "ESP32 Dev Module, QIO, 80MHz, 4MB (32Mb), 921600, None"

Arduino\libraries\MySQL_Connector_Arduino-master\src\old\MySQL_Connection.cpp: In member function 'boolean MySQL_Connection::connect(IPAddress, int, char*, char*)':

\Arduino\libraries\MySQL_Connector_Arduino-master\src\old\MySQL_Connection.cpp:74:25: error: 'check_ok_packet' was not declared in this scope

if (check_ok_packet() != 0) {

^

the sketch for this:

Code: Select all

// LOAD CLASSES
#include "time.h"
#include <TimeAlarms.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <WiFi.h>


//LOAD CONFIGRATION
#include "default.h"
#include "setup.h"
#include "db.h"

WiFiServer  server(80);


void setup(){
  //DEFINE IO's
  
  pinMode(ACTIV_LED, OUTPUT);
  pinMode(ACTION_LINE1, INPUT);
  pinMode(ACTION_LINE2, INPUT);
  pinMode(ACTION_LINE3, INPUT);
  pinMode(ACTION_LINE4, INPUT);
  
  pinMode(ACTIVELINE1, OUTPUT);
  pinMode(ACTIVELINE2, OUTPUT);
  pinMode(ACTIVELINE3, OUTPUT);
  pinMode(ACTIVELINE4, OUTPUT);
  pinMode(ALARM_ACTIV, OUTPUT);


  //ACTIVATE SERIAL MONITORING
  Serial.begin(115200);
  delay(10);
  Serial.println("setup - ok");
  Serial.println("connect to the network... ");

  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  delay(2000);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
   Serial.println(" connection established !");

   Serial.println("\n read timestamp from ntpServer ...");
   configTime(gmtOffset_sec, daylightOffset_sec, ntpServer);
   printLocalTime();

   getConfiguration();
}



void loop(){
  
}

Anyone can help me?

[ikram.benabd]

did you find a solution ???

[tullaMann]

No, sorry!

I hav a another version with php an json.
I read the data from database per php und print it as json argument in the html.
The esp32 read this file and get the data for declare the variable..

if you are interest it, i write the code her. but`s not really the result for this conent.

[phscar_skuldtec]

I found a solution that worked in my case.

I use the following code

Code: Select all

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <WiFi.h>

const char* ssid = "your wifi name";
const char* password = "your wifi password";

char user[] = "root"; // MySQL user login username
char passwordSQL[] = ""; // MySQL user login password

char INSERT_SQL[] = "INSERT INTO dbi.PARAMETRO(CANTEIRO, TIPO, HORA_INICIO, HORA_FIM, UMIDADE_INICIO, UMIDADE_FIM) VALUES ('CENTRAL ESP32', 'UMIDADE', '11:00:00', '12:30:00', '50', '70');";

IPAddress server_addr(your MySQL ip like (8, 8, 8, 8); // IP of the MySQL server here


WiFiServer  server(80);
void setup() {
  //Código de configuração aqui
  Serial.begin(115200);

  connectToNetwork();
  mySqlLoop();
}

void loop() {
}
void connectToNetwork() {
  WiFi.begin(ssid, password);
 
  while (WiFi.status() != WL_CONNECTED) {
    delay(1000);
    Serial.println("Establishing connection to WiFi..");
  }
 
  Serial.println("Connected to network");
 
}
void mySqlLoop(){
  WiFiClient client;
  MySQL_Connection conn((Client *)&client);
  if (conn.connect(server_addr, 3306, user, passwordSQL)) {
    Serial.println("Database connected.");
  }
  else{
    Serial.println("Connection failed.");
    Serial.println("Recording data.");
  }
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(INSERT_SQL);
  // Note: since there are no results, we do not need to read any data
  // Deleting the cursor also frees up memory used
  delete cur_mem;
  Serial.println("closing connection\n");
  //client.stop();
}

Work with MariaDB in xampp, and use direct connection without php neither json, just esp32 with arduino IDE

References: https://github.com/ChuckBell/MySQL_Conn ... issues/114
https://techtutorialsx.com/2017/06/29/e ... with-wifi/

[arge06]

Hi, with your code insert works well, did you manage to use select query with wifi ? Thanks in advance.

[asifuz]

So my code didn't working?
It don't even connect to the database? Why???
this is the code:

Code: Select all

#include <WiFi.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

const int soilMoisturePin = 34; // Replace with your actual pin
int soilMoistureValue;

const char *ssid = "JANINA";
const char *password = "sayma01720";
const char *host = "localhost";
const char *user = "fafafa";
const char *password_mysql = "Asif@0172";
const char *database = "dibl_projects";

IPAddress server_addr(44,206,65,171); // Replace with your MySQL server IP address

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

void setup() {
    Serial.begin(115200);
    delay(1000);
    
    // Connect to Wi-Fi
    WiFi.begin(ssid, password);
    while (WiFi.status() != WL_CONNECTED) {
        delay(1000);
        Serial.println("Connecting to WiFi...");
    }
    Serial.println("Connected to WiFi");

    // Connect to MySQL database
    Serial.println("Connecting to MySQL Server...");
    if (conn.connect(server_addr, 3306, const_cast<char*>(user), const_cast<char*>(password_mysql), const_cast<char*>(database))) {
        Serial.println("MySQL Connected!");
    } else {
        Serial.println("Connection failed.");
        return;
    }

    pinMode(soilMoisturePin, INPUT);
}

void loop() {
    soilMoistureValue = analogRead(soilMoisturePin);
    int moisturePercentage = map(soilMoistureValue, 0, 4095, 0, 100);

    if (isnan(moisturePercentage)) {
        Serial.println("Failed to read moisture data!");
        return;
    }

    Serial.println("Moisture Percentage: " + String(moisturePercentage));

    updateDatabase(moisturePercentage);

    delay(2000); // Adjust delay as needed
}

void updateDatabase(int moisturePercentage) {
    String query = "UPDATE phec_livedata SET ph_value='" + String(moisturePercentage) + "' WHERE user_id=3";
    
    Serial.println("Query: " + query);

    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    cur_mem->execute(query.c_str());
    delete cur_mem;
}

[grellclaus]

Hi asifuz

So my code didn't working?
It don't even connect to the database? Why???

Did you find a soulution? I'm having the same problem.

if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}

The terminal just keep saying "...trying..."

Claus

[Aguilaaar]

Hi, has anyone found a solution to this problem? or can you point me to a different library for MySQL with google cloud...
I'm also getting "trying..." and I don't know what else to do.