ChuckBell / MySQL_Connector_Arduino

Database connector library for using MySQL with your Arduino projects.
332 stars 133 forks source link

Select operation with esp8266 #190

Open JoeyUson opened 2 years ago

JoeyUson commented 2 years ago

I used the provided demo and just few alteration and run. below are my code

#include <Arduino.h>

#include <ESP8266WiFi.h>                        // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(192,168,68,104);        // IP of the MySQL *server* here, local server
char user[] = "admin";                        // MySQL user login username
char password[] = "admin";                    // MySQL user login password

char query[] = "SELECT message FROM arduino.arduino WHERE counter < 10";

// WiFi card example
char ssid[] = "amigos";                         // your SSID
char pass[] = "c3l3br1t7";                      // your SSID Password

WiFiClient client;                              // Use this for WiFi instead of EthernetClient
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

void setup()
{
  Serial.begin(9600);
  while (!Serial);                              // wait for serial port to connect. Needed for Leonardo only

                                                // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
                                                // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.print("Connecting to SQL...  ");
  if (conn.connect(server_addr, 3306, user, password)) {
    Serial.println("OK.");
    cursor = new MySQL_Cursor(&conn);
    cursor->execute(query);
    Serial.println("Connected to dbs");
  }
  else
    Serial.println("FAILED.");
}

void loop() {
  delay(2000);

  Serial.println("\nRunning SELECT and printing results\n");
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);                          // Initiate the query class instance
  cur_mem->execute(query, false);                                            // Execute the query with the PROGMEM option = true
  delay(500);
  cur_mem->show_results();                                                  // Show the results
  delay(500);
  delete cur_mem;                                                           // Deleting the cursor also frees up memory used
}

and here the output in my serial monitor

......
Connected to network
My IP address is: 192.168.68.103
Connecting to SQL...  ...trying...
Connected to server version 5.5.5-10.4.11-MariaDB
OK.
Connected to dbs

Running SELECT and printing results

Bad mojo. EOF found reading column header.

Running SELECT and printing results

Bad mojo. EOF found reading column header.

Running SELECT and printing results

message
Godbless you
Godbless you
Godbless you
Godbless you
Godbless you
Godbless you
Godbless you
Godbless you
8 rows in result.
Error: 1 = .

Its printing the correct output but why the error are keep on coming.

Also if using PROGMEM with changes below

char PROGMEM query[] = "SELECT message FROM arduino.arduino WHERE counter < 10"; cur_mem->execute(query, true);

0‚~?–4“Ò¶£ÿOzI{’‡8VúÁ
Connecting to amigos...........
Connected to network
My IP address is: 192.168.68.103
Connecting to SQL...  ...trying...
Connected to server version 5.5.5-10.4.11-MariaDB
OK.

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (3):
epc1=0x4000bf64 epc2=0x00000000 epc3=0x00000000 excvaddr=0x4023ba39 depc=0x00000000

>>>stack>>>

ctx: cont
sp: 3ffffdc0 end: 3fffffc0 offset: 0190
3fffff50:  4020314c 3ffefb74 3ffefb74 402023b5  
3fffff60:  3ffee5cc 40201119 00000020 401008c8  
3fffff70:  6744a8c0 00ffffff 00000118 3ffee798  
3fffff80:  3ffee5cc 3ffefb74 3ffee70c 40201130  
3fffff90:  402070f4 6844a8c0 feefeffe feefeffe  
3fffffa0:  3fffdad0 00000000 3ffee784 402040f8  
3fffffb0:  feefeffe feefeffe 3ffe85f8 40100c25  
<<<stack<<<

Hoping for anybody's reply

Laurent6012 commented 2 months ago

Hi, Hope you had found the problem since 2022. But if not, and that maybe will help others, i encountered the same issue on an ESP8266, that was due because i put 2 single cotes around the table name to test. To be honest between Oracle, and the real developer of MySQL it's a bit the circus sometime... And the worst that it be even more complicated with each update. I think maybe it's that, but not sure because it was with another library dedicated to MariaDB. With that, before i had have the error code 204 with 2 single quotes around the database name, hem. And of course i never found any error code under 1000 for MySQL or MariaDB on the Web, too funny, or stupid who knows ? Anyways, this other library ever didn't work to connect :/

Exception is maybe due to the ESP, not from library, the problem with the ESP8266 it's that the µC must have enough time to manage the WiFi, otherwise the watchdog reset the card.

Have fun, welcome to Filipinos ;)