-
Notifications
You must be signed in to change notification settings - Fork 134
Common Solutions
The following are code fragments and suggestions for solving some of the more common problems working with MySQL, data, and the connector.
The DEBUG mode adds additional messages to the Serial Monitor when connecting and running queries. It can be especially helpful when developing your sketches. It is highly recommended to enable DEBUG until you are satisfied your sketches are running correctly.
To enable debug mode, open the MySQL_Packet.h file and add the following line near the top but before the class declaration. You should now see the additional messages as well as the version of the MySQL server when connected.
#define DEBUG
DEBUG mode also allows you to use the print_packet() method for deeper debugging of the connector as well as seeing the text of any error messages returned from the server.
If you want to avoid using the database.object syntax in your SQL commands, you can specify a default database when you connect to MySQL. Simply pass the name of the database as a string to the connect() method as shown below.
...
char default_db = "test_arduino;
...
if (conn.connect(server_addr, 3306, user, password, default_db)) {
delay(1000);
// You would add your code here to run a query once on startup.
}
else
Serial.println("Connection failed.");
...
If you execute a query and want to know the number of rows affected or the last insert id for the auto increment column, you can use the following methods in the MySQL_Cursor class. You must call these methods immediately after the query is executed. The default "NULL" or "no value" is -1.
int get_rows_affected();
int get_last_insert_id();
This technique is demonstrated in several of the example sketches. In essence, we use a format string and the sprintf() function to convert format specifiers (also called placeholders) to values from constants or variables. Typically, we create a string that contains the SQL command with the placeholders and a buffer to place the formatted string. Note that you should ensure the buffer is large enough to contain the string. The following demonstrates this concept. Notice we create the string and the buffer then use sprintf() to create the string we can execute. You must use the dotostr() function to make strings of floating point values (variables). This is because there is no support in the sprintf() function in the Arduino platform for converting these values.
char INSERT_DATA[] = "INSERT INTO test_arduino.hello_sensor (message, sensor_num, value) VALUES ('%s',%d,%s)";
char query[128];
char temperature[10];
...
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
dtostrf(50.125, 1, 1, temperature);
sprintf(query, INSERT_DATA, "test sensor", 24, temperature);
cur_mem->execute(query);
delete cur_mem;
If you want to place floating point values into your database from a variable or even a constant, you must use the dotostr() function to make strings of floating point values (variables). This is because there is no support in the sprintf() function in the Arduino platform for converting these values.
char INSERT_DATA[] = "INSERT INTO test_arduino.hello_sensor (sensor_num, value) VALUES (%d,%s)";
char query[128];
char temperature[10];
int sensor_num;
...
sensor_num = 9;
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
dtostrf(50.125, 1, 1, temperature);
sprintf(query, INSERT_DATA, sensor_num, temperature);
cur_mem->execute(query);
delete cur_mem;
If you want to save several values at the same time but each in its own row, you can use a feature of MySQL that permits this. To do this, include multiple lists of comma-separated column values, with lists enclosed within parentheses and separated by commas as shown below. Note that you must ensure you do not exceed the memory of your board so keep the multiple values to a minimum (or use a "bigger" board).
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);
Use a timestamp column in your table. This will be updated with the current time and date when the row is inserted.
This concept is demonstrated in the example sketches. This is a case where you need to lookup a row in a table for one or more values. Since the memory of the Arduino is limited, you should use SQL statements that return as few rows as possible (best to get one row) and as few columns as necessary. For example, retrieving the entire row of a 20 column table with many character fields (such as name and address) to get a single value is wasteful. You should construct the query to return the one column needed. Always think smaller is better on returning rows. The following code fragment shows a simple example where we look up the id for a city from the world sample database.
char query[] = "SELECT id FROM world.city WHERE name = 'Midland'";
...
row_values *row = NULL;
int city_id = 0;
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
cur_mem->execute(query);
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
city_id = atoi(row->values[0]);
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
To use PROGMEM to save space for data, include the program memory space header then declare your string with the keyword as shown. Remember to use the optional second parameter in the execute() method when passing in these strings for queries.
#include <avr/pgmspace.h>
...
const PROGMEM char query[] = "SELECT name, population FROM world.city";
...
conn.execute(query, true);
Most sketches are written to connect once at startup. However, for complex solutions that collect or interact with the database, the connection is critical for longer running projects. It is often the case that networks can become unreliable. Indeed, there is nothing in the specification of the networking protocols or equipment to suggest it is always lossless. In fact, the network is design to be “mostly” reliable with some acceptable loss.
When loss occurs, it can sometimes cause errors in the connector when reading from the database or can cause the Ethernet shield to drop its connection. In extreme cases, it can cause the sketch to hang or loop out of control (depending on how the conditional statements are written).
To combat this, we can use a technique whereby we connect and close on each pass through the loop. This will work, but there is a more elegant solution that allows you to reconnect whenever the connection is dropped. The following demonstrates this concept.
void loop() {
delay(1000);
if (conn.connected()) {
// do something
} else {
conn.close();
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(500);
Serial.println("Successful reconnect!");
} else {
Serial.println("Cannot reconnect! Drat.");
}
}
}
Notice here we check the status of the connector and if it is not connected, we reconnect. This will save you from cases where the connection is dropped to network or database errors.
Closely related to the connect/close technique is a technique to reboot the Arduino should something bad happen. This can be really handy if you have a project that must work but is Ok if there are short data gaps. For example, if you are monitoring something and performing calculations it is possible your hardware could have periodic issues as well as logic errors or simple networking failures.
To overcome these situations, you can program the Arduino to reboot using the following code. Note that this shows this technique used with the connect/close option as they are complimentary. After all, if you cannot connect after N tries, a reboot cannot hurt and in most cases where it is a problem with memory or the Ethernet shield or related, it works.
void soft_reset() {
asm volatile("jmp 0");
}
void loop() {
delay(1000);
if (conn.connected()) {
// do something
num_fails = 0;
} else {
conn.close();
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(500);
Serial.println("Successful reconnect!");
} else {
num_fails++;
if (num_fails == MAX_FAILED_CONNECTS) {
Serial.println("Ok, that's it. I'm outta here. Rebooting...");
delay(2000);
soft_reset();
}
}
}
}
Notice here we use an assembler call to jump to position 0. This effectively reboots the Arduino microcode. Cool, eh? And you thought you’d have to slog out to the pig farm and press the wee little reboot button.
Another useful technique is monitoring or diagnosing memory problems by calculating how much memory is remaining. We do this with the following method.
int get_free_memory()
{
extern char __bss_end;
extern char *__brkval;
int free_memory;
if((int)__brkval == 0)
free_memory = ((int)&free_memory) - ((int)&__bss_end);
else
free_memory = ((int)&free_memory) - ((int)__brkval);
return free_memory;
}
You can use this method anywhere in the code. I like to use it with a print statement to print out the value calculated as follows.
Serial.print(" RAM: ");
Serial.println(get_free_memory());
Placing this code strategically in the sketch and watching the results in the serial monitor can help you spot memory leaks and situations where you run out of memory.