ChuckBell
commented
6 years ago Hello,
I think I see the problem. You've issued a query in the setup() but you never read the results (yes, you must read the results even for a query that doesn't return anything). I know why you did this - it was to set the database context. But that's not necessary if you do so in the query itself. For example, I would use this instead:
"SELECT depart FROM AGV.trajets"
Do that and take out query1.
Another gotcha is you're using a static query to run the USE and a dynamic query to run the SELECT. Don't mix the two. ;)
Always use the dotted notation in the query (SELECT, INSERT, etc.) to avoid that mistake.
Also, try running the query from your laptop. If you get no results there, something's wrong with the query.
If that doesn't help, don't use PROGMEM for the query.
If that doesn't work, what version of the connector and MySQL are you using? Also, which board?
Dr. Bell
On 3/16/18 3:29 AM, MaximilienStud wrote:
For my project I require to fetch data from the database by my arduino , so far I managed to connect the arduino to the database but whatever Query I try it doesn't work returning me nothing.
`#include
include
include
include
include <avr/pgmspace.h>
byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x10, 0x83, 0xC3 };
IPAddress server_addr(10,78,5,53); // IP of the MySQL /server/ here char user[] = "arduino2"; // MySQL user login username char password[] = "tsiris"; // MySQL user login password // Sample query char query1[] = "USE AGV"; const char PROGMEM query[] = "SELECT depart FROM trajets";
EthernetClient client; MySQL_Connection conn((Client *)&client); // Create an instance of the cursor passing in the connection MySQL_Cursor cur = MySQL_Cursor(&conn);
void setup() {
Serial.begin(115200); while (!Serial); // wait for serial port to connect Ethernet.begin(mac_addr); IPAddress ip = Ethernet.localIP(); Serial.print("IP Address: "); Serial.println(ip); Serial.print("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.print(""); // You would add your code here to run a query once on startup. cur.execute(query1); } else Serial.println("Connection failed."); //conn.close();
}
void loop() {delay(2000);
Serial.println("\nRunning SELECT and printing results\n");
// Initiate the query class instance MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); // Execute the query cur_mem->execute(query); // Fetch the columns and print them column_names cols = cur_mem->get_columns(); for (int f = 0; f < cols->num_fields; f++) { Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(", "); } } 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]); if (f < cols->num_fields-1) { Serial.print(", "); } } Serial.println(); } } while (row != NULL); // Deleting the cursor also frees up memory used delete cur_mem;
}`
`IP Address: 10.78.5.107 Connecting... Running SELECT and printing results
, , �⸮��, , , , , , , , Y⸮⸮⸮⸮⸮⸮&⸮⸮⸮۳⸮⸮⸮�⸮⸮%⸮⸮⸮⸮x⸮⸮⸮Y⸮⸮⸮⸮⸮⸮�⸮ӽz⸮�⸮j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮�Q7V⸮V⸮/⸮/⸮⸮⸮⸮�⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j⸮⸮⸮7⸮⸮⸮⸮⸮�⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݾ⸮�⸮s⸮⸮�⸮⸮⸮]⸮e⸮�⸮\N~⸮⸮⸮⸮⸮⸮⸮�}�⸮⸮⸮?⸮⸮/F⸮⸮⸮⸮y}⸮���, , ⸮⸮�⸮s⸮⸮�⸮⸮⸮]⸮e⸮�⸮\N~⸮⸮⸮⸮⸮⸮⸮�}�⸮⸮⸮?⸮⸮/F⸮⸮⸮⸮y}⸮���, lyc14.ac-caen.fr, �⸮j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮�Q7V⸮V⸮/⸮/⸮⸮⸮⸮�⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j⸮⸮⸮7⸮⸮⸮⸮⸮�⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݣ, E AGV, , , , , , E AGV, E AGV, E AGV, E AGV, E AGV, E AGV⸮�, ⸮⸮۳⸮⸮⸮�⸮⸮%⸮⸮⸮⸮x⸮⸮⸮Y⸮⸮⸮⸮⸮⸮�⸮ӽz⸮�⸮j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮�Q7V⸮V⸮/⸮/⸮⸮⸮⸮�⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j⸮⸮⸮7⸮⸮⸮⸮⸮�⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݣ, E AGV, , , , , , ⸮⸮O⸮⸮⸮⸮P⸮n⸮z⸮V⸮⸮⸮⸮⸮�⸮⸮⸮m⸮r⸮⸮⸮⸮wOX⸮⸮hw�⸮z'oޯ⸮[⸮⸮⸮6⸮⸮ӳ⸮⸮⸮Se⸮Y⸮⸮⸮⸮⸮⸮&⸮⸮⸮۳⸮⸮⸮�⸮⸮%⸮⸮⸮⸮x⸮⸮⸮Y⸮⸮⸮⸮⸮⸮�⸮ӽz⸮�⸮j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮�Q7V⸮V⸮/⸮/⸮⸮_⸮⸮�⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j⸮⸮⸮7⸮⸮⸮⸮⸮�⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݾ⸮�⸮s⸮⸮�⸮⸮⸮]⸮e⸮�⸮\N⸮⸮⸮⸮⸮⸮⸮�}�⸮⸮⸮?⸮⸮/F⸮⸮⸮⸮y}⸮���, , , , , , `
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/36, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4IXLLNtXlNnhr3SHIMUXKNQNZBhyks5te2ndgaJpZM4StWuG.
MaximilienStud
For my project I require to fetch data from the database by my arduino , so far I managed to connect the arduino to the database but whatever Query I try it doesn't work returning me nothing. I could use some help, at least pointing out were it went wrong cause I have no clue, thx for your answer and time.
`#include
include
include
include
include <avr/pgmspace.h>
byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x10, 0x83, 0xC3 };
IPAddress server_addr(10,78,5,53); // IP of the MySQL server here char user[] = "arduino2"; // MySQL user login username char password[] = "tsiris"; // MySQL user login password // Sample query char query1[] = "USE AGV"; const char PROGMEM query[] = "SELECT depart FROM trajets";
EthernetClient client; MySQL_Connection conn((Client *)&client); // Create an instance of the cursor passing in the connection MySQL_Cursor cur = MySQL_Cursor(&conn);
void setup() {
Serial.begin(115200); while (!Serial); // wait for serial port to connect Ethernet.begin(mac_addr); IPAddress ip = Ethernet.localIP(); Serial.print("IP Address: "); Serial.println(ip); Serial.print("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.print(""); // You would add your code here to run a query once on startup. cur.execute(query1); } else Serial.println("Connection failed."); //conn.close();
}
void loop() {delay(2000);
Serial.println("\nRunning SELECT and printing results\n");
// Initiate the query class instance MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); // Execute the query cur_mem->execute(query); // Fetch the columns and print them column_names cols = cur_mem->get_columns(); for (int f = 0; f < cols->num_fields; f++) { Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(", "); } } 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]); if (f < cols->num_fields-1) { Serial.print(", "); } } Serial.println(); } } while (row != NULL); // Deleting the cursor also frees up memory used delete cur_mem;
}`
`IP Address: 10.78.5.107 Connecting... Running SELECT and printing results
, , ⸮, , , , , , , , Y⸮⸮⸮⸮⸮⸮&⸮⸮⸮۳⸮⸮⸮⸮⸮%⸮⸮⸮⸮x⸮⸮⸮Y⸮⸮⸮⸮⸮⸮⸮ӽz⸮⸮~j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮Q7V⸮V⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j~⸮⸮⸮7⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݾ⸮⸮s⸮⸮⸮⸮⸮]⸮e⸮⸮\N~⸮⸮⸮⸮⸮⸮⸮}⸮⸮⸮?⸮⸮/F⸮⸮⸮⸮y}⸮, , ⸮⸮⸮s⸮⸮⸮⸮⸮]⸮e⸮⸮\N~⸮⸮⸮⸮⸮⸮⸮}⸮⸮⸮?⸮⸮/F⸮⸮⸮⸮y}⸮, lyc14.ac-caen.fr, ⸮~j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮Q7V⸮V⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j~⸮⸮⸮7⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݣ, E AGV, , , , , , E AGV, E AGV, E AGV, E AGV, E AGV, E AGV⸮, ⸮⸮۳⸮⸮⸮⸮⸮%⸮⸮⸮⸮x⸮⸮⸮Y⸮⸮⸮⸮⸮⸮⸮ӽz⸮⸮~j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮Q7V⸮V⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j~⸮⸮⸮7⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݣ, E AGV, , , , , , ⸮⸮O⸮⸮⸮⸮P⸮~n⸮~z⸮V~⸮⸮⸮⸮⸮⸮⸮⸮m⸮r⸮⸮⸮⸮wOX⸮⸮hw ⸮z'oޯ⸮[⸮⸮⸮6⸮⸮ӳ⸮⸮⸮Se⸮Y⸮⸮⸮⸮⸮⸮&⸮⸮⸮۳⸮⸮⸮⸮⸮%⸮⸮⸮⸮x⸮⸮⸮Y⸮⸮⸮⸮⸮⸮⸮ӽz⸮⸮~j⸮⸮UK⸮|⸮{⸮⸮Y⸮⸮ ⸮⸮⸮⸮8⸮⸮Q7V⸮V⸮⸮⸮⸮_⸮⸮⸮⸮⸮⸮?⸮⸮⸮rJ⸮⸮w⸮j~⸮⸮⸮7⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮<⸮⸮/w⸮⸮G⸮⸮⸮⸮s⸮⸮⸮J⸮⸮⸮⸮ަa⸮⸮⸮x⸮⸮ݾ⸮⸮s⸮⸮⸮⸮⸮]⸮e⸮⸮\N~⸮⸮⸮⸮⸮⸮⸮}⸮⸮⸮?⸮⸮/F⸮⸮⸮⸮y}⸮, , , , , , `