Arduino MySQL Connector Issues with Select only

Question

first question i am doing so hope this is the right place.

I have an issue with an Arduino Uno and an Arduino ESP8266WiFi Shiald by WangTongze using this tutorial (https://www.instructables.com/id/ESP8266-ESP-12E-UART-Wireless-WIFI-Shield-TTL-Conv/) I have been able to connect to the my Wifi and host a small webserver.

Then with the Examples of the MySql Connector Library I have been able to do a simple Insert to a MySQL Database on a NAS Server. However, I want to be able to select a value from my DB and print it on the screen for some reason the Cursor always returns a null. I have been able to select from my PC using the same user. and created the user francis@% so that the host isn't an issue. Please note that this same code with a few tweaks worked to insert a record to the same table. Is there anything I am missing? Below is the code:

//#include <WiFi.h> // Use this for WiFi instead of Ethernet.h
#include <WiFiEsp.h> //use for ESP8266
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
// Emulate Serial1 on pins 2/3 if not present
#ifndef HAVE_HWSERIAL1
#include "SoftwareSerial.h"
SoftwareSerial Serial1(2, 3); // RX, TX
#endif
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,x,x); // IP of the MySQL server here
char user[] = "francis";
char password[] = "xxxxxx";
// WiFi card example
char ssid[] = "PintoLoco"; // your network SSID (name)
char pass[] = "xxxxxxxx"; // your network password
//WiFiClient client; // Use this for WiFi instead of EthernetClient
WiFiEspClient client; //Use this for ESP8266
MySQL_Connection conn((Client *)&client);
MySQL_Cursor cur = MySQL_Cursor(&conn);
char query[] = "SELECT Value FROM Peceras.Parameters ";
void setup() {
 Serial.println("Starting....");
 Serial.begin(115200);
 // initialize ESP module
 WiFi.init(&Serial);
 Serial.println("Connecting to WiFi");
 // Begin WiFi section
 int status = WiFi.begin(ssid, pass);
 while(status != WL_CONNECTED){
 status = WiFi.begin(ssid, pass);
 }
 // print out info about the connection:
 Serial.println("Connected to network");
 IPAddress ip = WiFi.localIP();
 Serial.print("My IP address is: ");
 Serial.println(ip);
 // End WiFi section
 Serial.println("Connecting...");
 if (conn.connect(server_addr,3306,user,password)) {
 delay(1000);
 }
 else
 Serial.println("Connection failed.");
}
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
void loop() {
 row_values *row = NULL;
 long head_count = NULL;
column_names *cols = cur_mem->get_columns();
cur_mem->execute(query);
 // Execute the query
 do {
 row = cur_mem->get_next_row();
 if (row != NULL) {
 for (int f = 0; f < cols->num_fields; f++) {
 head_count = atoi(row->values[f]);
 Serial.print("Ingestelde Temperatuur: ");
 Serial.println(head_count);
 if (f < cols->num_fields-1) {
 Serial.println(',');
 }
 }
 Serial.println();
 }
 } while (row != NULL);
 // Deleting the cursor also frees up memory used
 delete cur_mem;
 delay(2000);
}

• Peter Paul Kiefer: I do not know much about MySQL databases and absolute nothing about the Client you use on your Arduino. So my hints are only wild guesses ;-): You did not end the SQL statement with a semicolon. Normally that's not a problem because the client lib does it for you, but this client could be an exception. You define a cursor cur you never use. Perhaps the client lib can only handle one active cursor. You select only one field "Value" but iterate over a list of fields. This is not a problem if the field "Value" exists. Perhaps you could try: "SELECT * FROM Peceras.Parameters;"
• Francis Manuel Astley Richmond: Thanks Peter, for your response, I have tried your seggestions and I get the same output sadly. The database is just one table with 3 columns and Value is one. I am unsure and thinking it might be to slow to obtain the data when doing the command? out of ideas
• Peter Paul Kiefer: Sorry I did not realize that you used the Serial interface for the normal Serial to USB comunication and also for the connection to the WIFI shield. That will not work. The program defines a Serial1 that you can use to connect to the Shield, but you have to connect the TX and RX pins of the shield with the pins D2 and D3 of the Arduino with wires. (It's been described in the instructables you linked to) I added an answer, For me it's easier to write code than to write text. ;-) I also did not realize that you close the cursor in the for loop again and again, but you opened it just once.

Answer 1

I have no access to a compiler, so there might be errors.

#include <WiFiEsp.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#ifndef HAVE_HWSERIAL1
#include "SoftwareSerial.h"
SoftwareSerial Serial1(2, 3); // RX, TX
#endif
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,x,x);
char user[] = "francis";
char password[] = "xxxxxx";
char ssid[] = "PintoLoco";
char pass[] = "xxxxxxxx";
WiFiEspClient client;
MySQL_Connection conn( (Client *) &client );
MySQL_Cursor *cur_mem = null;
// PPK: some database systems are case sensitive
// so Peceras.Parameters is not the same as peceras.parameters
// check if the tables and fields are named correctly.
char query[] = "SELECT Value FROM Peceras.Parameters";
void setup() 
{
 Serial.begin( 115200 );
 Serial1.begin( 115200 ); 
 Serial.println( "Connecting to WiFi" );
 WiFi.init( &Serial1 );
 int status = WiFi.begin( ssid, pass );
 while( status != WL_CONNECTED )
 {
 status = WiFi.begin(ssid, pass);
 }
 Serial.println( "Connected to network" );
 IPAddress ip = WiFi.localIP();
 Serial.print( "My IP address is: " );
 Serial.println( ip );
 Serial.println( "Connecting DB ..." );
 if ( conn.connect( server_addr, 3306, user, password ) ) 
 {
 Serial.println( "DB connected." );
 cur_mem = new MySQL_Cursor( &conn );
 row_values *row = NULL;
 long head_count = NULL;
 column_names *cols = cur_mem->get_columns();
 cur_mem->execute(query);
 do 
 {
 row = cur_mem->get_next_row();
 if ( row != NULL ) 
 {
 for ( int f = 0; f < cols->num_fields; f++ ) 
 {
 head_count = atoi( row->values[f] );
 Serial.print( "Ingestelde Temperatuur: ");
 Serial.println( head_count );
 if ( f < cols->num_fields - 1 ) 
 {
 Serial.println(',');
 }
 }
 Serial.println();
 }
 } while (row != NULL);
 // Deleting the cursor also frees up memory used
 // PPK: I'm not sure if this is a good idea, I don't know
 delete cur_mem;
 }
 else
 {
 Serial.println( "DB Connection failed." );
 }
}
void loop() 
{
 // PPK: I would recommend to do the select in the setup section
 // It looks like you want to run it just once
}

Edit

Please note, that this code only works if you connect the software serial pins D2, D3 to the respective RX and TX pins on the Shield. You have to bend the pins (P0-P3) of the shield to disconnect them from the Arduino. Then you have to connect D2 and D3 to P0 and P1. This is described in step 1 of the instructables you linked to in the question. See the picture at the end of Step 1. https://www.instructables.com/id/ESP8266-ESP-12E-UART-Wireless-WIFI-Shield-TTL-Conv/

• Francis Manuel Astley Richmond: Thanks Peter for the response, i tried this code, and now it doesn't start the Esp Module, I get a timeout: [WiFiEsp] >>> TIMEOUT >>> [WiFiEsp] Cannot initialize ESP module