Hi All,
I'm new to Arduino so muddling my way by learning by example. Can program in VBA,PHP but the Arduino is a little new to me. Trying to read some data from a MYSQL database I have which I want to use for home automation. I Connect, etc and all good. I can read the column headers but just cannot read the row data. I've based this on some examples and hacking them to suit my data.
Can anybody point me in the right direction on how to loop through the rows and get the data. I've been fighting this beast for a week so I'm at a loss as to what I'm doing wrong. My output loops like it's looping bu not stepping to the next row?
Please ignore the comments as this is from the original code I used to start with. Just leaving there so I have reference.
/*
MySQL Connector/Arduino Example : connect by wifi using WiFi 101 shield
This example demonstrates how to connect to a MySQL server from an
Arduino using using the new WiFi Shield 101 from arduino.cc.
For more information and documentation, visit the wiki:
https://github.com/ChuckBell/MySQL_Connector_Arduino/wiki.
INSTRUCTIONS FOR USE
1) Change the address of the server to the IP address of the MySQL server
2) Change the user and password to a valid MySQL user and password
3) Change the SSID and pass to match your WiFi network
4) Connect a USB cable to your Arduino
5) Select the correct board and port
6) Compile and upload the sketch to your Arduino
7) Once uploaded, open Serial Monitor (use 115200 speed) and observe
If you do not see messages indicating you have a connection, refer to the
manual for troubleshooting tips. The most common issues are the server is
not accessible from the network or the user name and password is incorrect.
Note: The MAC address can be anything so long as it is unique on your network.
Created by: Dr. Charles A. Bell
*/
#include <SPI.h>
//#include <WiFi101.h>
#include <ESP8266WiFi.h>
#include <ESP8266mDNS.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
IPAddress server_addr(XXX); // IP of the MySQL *server* here
char user[] = "XXX"; // MySQL user login username
char password[] = "XXX"; // MySQL user login password
// WiFi card example
char ssid[] = "XX"; // your SSID
char pass[] = "XX"; // your SSID Password
// Set web server port number to 80
WiFiServer server(80);
// Sample query
char query[] = "SELECT 'Common_Name','ElementID','Status' FROM A1007A_Arduino.Arduino";
WiFiClient 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
// Connect to Wi-Fi network with SSID and password
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.begin(ssid, pass);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
// Print local IP address and start web server
Serial.println("");
Serial.println("WiFi connected.");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
Serial.println("MYSQL Connection sucess.");
//delay(5000);
}
else
{Serial.println("MYSQL Connection failed.");
conn.close();}
show_strings_from_table();
}
//********************************************************************************************************************************
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("");
Serial.println("************************");
// Read the rows and print them
row_values *row = NULL;
do {
Serial.println("++++++++++++++++++++++++");
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.println("-----------------------");
Serial.println(f);
Serial.println(cols->num_fields);
Serial.println(row->values[f]);
//Serial.println("-----------------------");
if (f < cols->num_fields-1) {
// Serial.print(", ");
}
}
Serial.println("");
}
}
while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
*/
}
void show_strings_from_table() {
char city_name[64];
row_values *row = NULL;
delay(1000);
Serial.println("Reading the Status of the Elements");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
Serial.println("Element Status");
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
// Copying the string from the first column returned to a local variable
Serial.print("> ");
Serial.print(row->values[0]);
Serial.print(", ");
Serial.print(row->values[1]);
Serial.print(", ");
Serial.print(row->values[2]);
Serial.println("");
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
}
Output to Serial Monitor is
WiFi connected.
IP address:
192.168.1.27
Connecting...
...trying...
Connected to server version 5.1.73
MYSQL Connection sucess.
Reading the Status of the Elements
Element Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
> Common_Name, ElementID, Status
Dump of MYSQL Data
===Database A1007A_Arduino
== Table structure for table Arduino
|------
|Column|Type|Null|Default
|------
|//**ID**//|int(11)|No|
|Common_Name|text|No|
|ElementID|text|No|
|Status|int(11)|No|0
|Data_on|int(11)|No|
|Data_off|int(11)|No|
|Element Type|text|No|
== Dumping data for table Arduino
|1|LED 01 - Yellow LED|led1|0|0|0|LIGHT
|3|LEd 02 - Blue LED|led2|0|0|0|LIGHT
|5|LED 3|led3|1|0|0|LIGHT
|9|Infra Red transmiter|IR01|0|0|0|IR
|6|LED 04 - Red Led|led4|0|0|0|LIGHT
|7|led 5|led5|1|0|0|LIGHT
|8|led6|led6|1|0|0|LIGHT
|10|Ifra Red 02|IR02|0|0|0|IR