I work on a project with ESP-CAM that reads QR code holding car plate number, and then compare it with the car plate numbers registered on MySQL database.
I am trying to import the list that may contain up to 10 different car plate numbers (e.g. 458|fgh) and save them into an array, so that I can make a for loop after that to compare it with the one read by camera.
I read different topics in the forum about the processing arrays of strings, but saving the data into the array I declare did not work. And the printing the importing data from the MySQL onto Serial works properly.
here is my code:
#include <WiFi.h> // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
IPAddress server_addr(***,**,**,**); // IP of the MySQL *server* here
char user[] = "ESP32CAM"; // MySQL user login username
char password[] = "1234"; // MySQL user login password
// WiFi card example
char ssid[] = "Barahim"; // your SSID
char pass[] = "********"; // your SSID Password
int flag = 0;
const char QUERY_POP[] = "SELECT Platenumber FROM db_esp32.CarUsers";
char query[128];
#define ARRAYSIZE 10
char *List[]={"000|000","000|000","000|000",
"000|000","000|000","000|000",
"000|000","000|000","000|000","000|000",}; //initial List of car plate numbers
WiFiClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
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.");
else
Serial.println("FAILED.");
}
void loop() {
if (flag ==0){
delay(5000);
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Supply the parameter for the query
// Here we use the QUERY_POP as the format string and query as the
// destination. This uses twice the memory so another option would be
// to allocate one buffer for all formatted queries or allocate the
// memory as needed (just make sure you allocate enough memory and
// free it when you're done!).
sprintf(query, QUERY_POP, 9000000);
// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(row->values[f]);
List[f] = row->values[f];
}
Serial.println();
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
flag = 1;}
for (int i = 0; i< ARRAYSIZE; i++){
Serial.println(List[i]);}
delay(5000);
}
you are only allocating room for the pointers. the text is constant and should not be modifiable
you need t ensure you have room to store whatever you retrieve from the DB otherwise when the fetched buffer goes away the pointers will point are garbage.
Why read the entire database list into memory? Simply iterate through the records, reading each record, one at a time, as each needed Your approach will inherently limit the maximum number of database entries that can be handled, wasting a lot of memory and CPU cycles.
@RayLivingston , I used the example given by the library creator, and tried to modify it as needed. However, I tried some modifications through the records , but then the connection to db was failed and did not work.
#include <WiFi.h> // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
IPAddress server_addr(***,**,**,*); // IP of the MySQL *server* here
char user[] = "ESP32CAM"; // MySQL user login username
char password[] = "1234"; // MySQL user login password
// WiFi card example
char ssid[] = "Barahim"; // your SSID
char pass[] = "*********"; // your SSID Password
const char QUERY_POP[] = "SELECT City FROM db_esp32.customers";
char query[128];
const uint8_t maxPlateCount = 10;
const uint8_t maxPlateTextLength = 7;
char List[maxPlateCount][maxPlateTextLength+1];
WiFiClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
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.");
else
Serial.println("FAILED.");
}
void loop() {
delay(5000);
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Supply the parameter for the query
// Here we use the QUERY_POP as the format string and query as the
// destination. This uses twice the memory so another option would be
// to allocate one buffer for all formatted queries or allocate the
// memory as needed (just make sure you allocate enough memory and
// free it when you're done!).
sprintf(query, QUERY_POP, 9000000);
// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(row->values[f]);
strlcpy(&(List[f]), row->values[f], maxPlateTextLength+1);
}
Serial.println();
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
}
Why you use 0 instead of the length of the each string, which is maxPlateTextLength inside the strlcpy.
And I upload the code and try to print the List on Serial using the following code:
for (int i = 0; i < maxPlateCount; i++) {
Serial.print(List[i][maxPlateTextLength+1]);
Serial.println();
}
but the serial print empty spaces, I just need to make sure that the duplication is done correctly so that I can compare the list with the plate number detected by the camera.
the first argument has to be the address in memory where you want to copy the content of c-string pointed by the second parameter. The third parameter is the total number of bytes that are available in the destination buffer so that you don't overflow
to print the string it's
for (int i = 0; i < maxPlateCount; i++) Serial.println(List[i]);
you pass the pointer to the first char in the c-string
if you do what you have
Serial.print(List[i][maxPlateTextLength+1]);
then you ask to print only one character, that is at index maxPlateTextLength+1 for the ith string. it's very likely going to be a null char so nothing will get printed.
I appreciate your help @J-M-L , the code finally works. I think I need to read more about how to processing strings and arrays.
however, there was en error with using for loop using f with the number of fields of columns in db since I just need one column with the entire 10 rows, so I modified into this final code and it works fine.
#include <WiFi.h> // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
IPAddress server_addr(***,**,**,**); // IP of the MySQL *server* here
char user[] = "ESP32CAM"; // MySQL user login username
char password[] = "1234"; // MySQL user login password
// WiFi card example
char ssid[] = "Barahim"; // your SSID
char pass[] = "*********"; // your SSID Password
const char QUERY_POP[] = "SELECT City FROM db_esp32.customers";
char query[128];
const uint8_t maxPlateCount = 10;
const uint8_t maxPlateTextLength = 8;
char List[maxPlateCount][maxPlateTextLength+1];
int i = 0;
WiFiClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
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.");
else
Serial.println("FAILED.");
}
void loop() {
delay(5000);
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Supply the parameter for the query
// Here we use the QUERY_POP as the format string and query as the
// destination. This uses twice the memory so another option would be
// to allocate one buffer for all formatted queries or allocate the
// memory as needed (just make sure you allocate enough memory and
// free it when you're done!).
sprintf(query, QUERY_POP, 9000000);
// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
Serial.print(row->values[0]);
strlcpy(List[i], row->values[0], maxPlateTextLength+1) ;
Serial.println();
}
i = i+1;
} while (row != NULL);
i = 0;
// Deleting the cursor also frees up memory used
delete cur_mem;
delay(2000);
Serial.println();
Serial.println("This is my for loop: ");
for (int i = 0; i < maxPlateCount; i++) {
Serial.print(List[i]);
Serial.println();
}
delay(2000);
}
