Hello, fellow tinkerers!
I have been using the MySQL Connector/Arduino library (Chuck's Blog: Introducing MySQL Connector/Arduino) in a college project of mine. It has been of great help but still it's not in a presentable form because of some irregularities due to inexperience on my part. I would appreciate if anyone from you could take a look on my code and comment on how I could sort out these problems. Given the length of code/background info, I do not expect an immediate answer, or an answer at all, but it would definitely be a great help if someone could look at it and reply in their own time!
My project is basically a smartphone-controlled Home Automation solution. It uses Arduino Uno and Wi-Fi Shield on hardware end. I have developed companion mobile app and MySQL database to go along with the system. Here's how the system works: There are some devices (lights, fans) connected to Arduino's digital I/O pins via relay channel, and these are also listed in the 'devices' table of my database. Some of the attributes of a device are its name, status (1 or 0 for ON or OFF), pin number (the digital I/O pin to which the device is connected), and a flag called 'pending_s' (whose value can be 1 or 0). There is a UI on mobile app with ON/OFF buttons from where user 'sets' the 'pending_s' flag of a device. Arduino, on the other end, 'scans' the database after a set interval (say 10 seconds) by using a 'Select' query based on 'pending_s' flag. If it finds a device whose flag has been raised from 0 to 1, it 'toggles' the 'status' of that device, both on the actual pin (using digitalWrite) and the database. It then removes the flag, and starts scanning for toggle requests all over again...
There are, however, a few problems. Based on my limited experience with Arduino, I haven't been able to write an overly efficient code. I even don't believe that this 'scanning' solution is elegant, but it's something that I though could work as I could not find any solution which could 'trigger' the Arduino as soon as it received a toggle request. Some code first (in fact, all of it), I have tried to detail the code with comments as much as possible:
#include <WiFi.h>
#include <mysql.h>
#include <SPI.h>
#include <sha1.h>
char ssid[] = "MyNetwork";
char pass[] = "MyNetwork-Password";
byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x0E, 0xEC, 0x57 }; // MAC address of device
IPAddress server_addr(148,251,111,196); // IP address of DB server
Connector my_conn;
char user[] = "admin";
char password[] = "admin1247";
char select_p[] = "SELECT id, pin, status from iotautom_master.devices where pending_s = '1'";
// select a device for which flag is high
int tempPin = NULL; // variables
int tempID = NULL;
int tempStatus = NULL;
int pin1 = 2; // Arduino pins to which...
int pin2 = 3; // devices are connected
int pin3 = 5;
bool wifiConn() { // connect to Wi-Fi
if ( WiFi.status()) {
return 1;
}
else {
Serial.println("Connecting to Wi-Fi...");
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a WiFi connection.");
return 0;
}
else {
Serial.println("Connected to Wi-Fi.");
IPAddress ip = WiFi.localIP();
Serial.print("The IP address is: ");
Serial.println(ip);
return 1;
}
}
}
bool dbConn() { // connect to database
if(my_conn.is_connected()) {
return 1;
}
else {
Serial.println("Connecting to database...");
if (my_conn.mysql_connect(server_addr, 3306, user, password)) { // attempt connection
return 1;
}
else {
Serial.println("Connection to database failed.");
return 0;
}
}
}
void updateStatus(int ID2, int Status2, int Pin2) { // update status of a device
// both on pin and database
int newStatus;
if (Status2 == 0) { // received status is OFF,
newStatus = 1; // so new status is ON
}
else
{
newStatus = 0; // or vice-versa
}
char update_s[75]; // UPDATE query
sprintf(update_s, "UPDATE iotautom_master.devices set status = '%d', pending_s = '0' where id = '%d'", newStatus, ID2);
Serial.println(update_s);
Serial.print("New status: ");
Serial.print(newStatus);
// <-- SOMEWHERE AROUND HERE ALL HELL BREAKS LOOSE.
// <-- THE UPDATE QUERY IS PRINTED IN SERIAL MONITOR
// <-- BUT THEN... (SEE ATTACHED IMAGE OF SERIAL MONITOR)
my_conn.cmd_query(update_s); // perform query
digitalWrite(Pin2, newStatus); // update pin status of device
}
void doQuery(const char *q) { // do a query
// based on 'pending_s'
column_names *c;
row_values *r;
my_conn.cmd_query(q);
c = my_conn.get_columns();
int num_cols = c->num_fields;
int rows = 0;
//do { // I am NOT doing a loop here because it
// becomes too complicated. Instead,
// only deal with FIRST flag, and the
// next pending_s will be handled in next scan, if any.
r = my_conn.get_next_row();
if (r) {
rows++;
for (int i = 0; i < num_cols; i++) { // this loop gets
// all relevent fields/attributes
// related to device
if (i==0) {
tempID = atoi(r->values[i]); // first its ID
Serial.println("ID: ");
Serial.println(tempID);
}
else if (i==1) {
tempPin = atoi(r->values[i]); // then the PIN number
Serial.println("Pin: ");
Serial.println(tempPin);
}
else if (i==2) {
tempStatus = atoi(r->values[i]); // and finally
Serial.println("Status: "); // the 'current' status
Serial.println(tempStatus);
break;
} // from the DB
}
my_conn.free_row_buffer(); // ONE row handled, so free the buffer
//break;
}
//} while (r);
Serial.print(rows);
Serial.println(" rows in result.");
my_conn.free_columns_buffer();
if (rows == 0) { // no raised flags?
delay(500); // very well!
}
else { // found a flag?
updateStatus(tempID, tempStatus, tempPin); // toggle the device
delay(1500);
}
}
void setup() {
Serial.begin(115200);
pinMode(pin1, OUTPUT); // set output
pinMode(pin2, OUTPUT); // pin modes
pinMode(pin3, OUTPUT); // for devices
digitalWrite(pin1, LOW); // write LOW
digitalWrite(pin2, LOW); // on all pins
digitalWrite(pin3, LOW); // for now
wifiConn(); // Connect to Internet
dbConn(); // Connect to Database
}
void loop() {
delay(3000);
if (wifiConn()) { // If network and...
if (dbConn()) { // database connectivity are established...
doQuery(select_p); // then scan for raised flags
delay(3000);
my_conn.disconnect(); // and then disconnect
}
else {
delay(500);
}
}
else {
delay(500);
}
}
So, the biggest problem: the Arduino loses connection to the database somewhere mid-way during the program and doesn't connect again. It starts out perfectly, scans the database, and keeps on scanning until a high pending_s flag is found, gets the ID of that device, but just as it tries to update the status of the device using UPDATE query in updateStatus() function, the program crashes. It gives out partially incomprehensible words on Serial Monitor (image attached) and doesn't connect again.
My guess here is that I do not fully understand the way Arduino/MySQL connector retrieves and uses rows and columns, and how to clear buffers of data received from database. There could still be some data from previous (SELECT for pending_s) query and then I give the command for UPDATE query, and it gets mixed up. Could that be a contributing factor? Or is there something else wrong here? Could it be because of sandwiching the functionality between a lot of functions/methods? Or because of delays/lack of delays?
Any other comment/feedback on making the code more efficient and less likely to crash is also welcome!
I highly appreciate ANY sort of help on this issue or any other that you find in my program. Kindly take your time to review the program but please DO reply!
but as I know, your project has been completed successfully and your efforts were appreciated by the team. Congratulation to you and your partner for getting good grades 
