-
Notifications
You must be signed in to change notification settings - Fork 133
Tips and Tricks
This section contains a list of suggestions for making better sketches with the connector. In some cases this is advice and in other cases it is suggested code or techniques. If your sketch will include more complex queries than those shown above, you should read this section for incorporation into your own sketches.
There are many example sketches included with the connector. You should run one or more of these to ensure you understand how the connector works before writing your own sketch. I recommend starting with the connect, basic_insert, and basic_select examples first. Get to know these and test them to ensure your MySQL server is setup correctly and your Arduino can connect to it. If you have trouble with these examples, do not blame the connector (at least not initially). Read the troubleshooting section below to solve one or more of the common problems and try your example again. Don’t forget to change the IP address, user name and password!
This one I feel is a given for writing code for microprocessors, but you may be surprised at the number of requests I’ve had for helping solve problems. The root cause or the significant factor for much of the users’ trouble stems around making the sketch far more complex than it needs to be.
This is especially true for those that write their entire solution before testing it. That is, they write hundreds of lines of code, get it to compile (sometimes not so much) then try to run it. In this case, the user has failed to realize all aspects of their solution should be tested in a step-wise fashion.
For example, write the sketch to do the minimalist steps needed to demonstrate (test) each part. For working with the MySQL database, start with a simple connection test then proceed to testing each and every query using dummy data or simulated values.
Likewise, working with sensors or other devices should be done in isolation so that you can eliminate major portions of the sketch for investigation should something go wrong.
If you adopt this philosophy, your sketches will be easier to write and you will have far more success than the “code it once and pray it works” philosophy.
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!");
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.
It is at this point that I would like to clarify one thing about using libraries such as the connector. This is advice for all who are learning how to program your Arduino. Be sure to do your homework and your own research before asking questions. So many times I get questions about the most basic things (well, basic to the experienced) that have nothing to do with the connector. For example, working with memory, variables, and strings seem to be stumbling blocks for new users.
In the end, you will get far more useful help from library authors and other experienced Arduinistas if you take some time to read a book, web page, or listen to a podcast before contacting the author for help or complain about a compiler error. A small amount of learning on your part will reap dividends when you can ask a specific question or seek help for a complex issue.
A case in point is this document. From my experience, this document is far more detailed than any other library available for the Arduino (with notable exceptions). Part of the motivation for writing this document was to consolidate the information about the connector and to ensure those new to using the connector had a sufficiently detailed tutorial. The following section completes the body of information about the connector by presenting the most common questions asked of users.