There is a new release of the Connector/Arduino on Launchpad! See
https://launchpad.net/mysql-arduino. The new version supports a
number of refinements and a few new features. These
include:
- Improved support for processing result sets
- Conditional compilation to omit result set handling features to save program space
- Support for the Arduino WiFi shield
- New version() method to check version of the connector
- Simplified download (no more patching SHA1!)
So What is It?
If you have never heard of Connector/Arduino, it is simply a
library designed to allow the Arduino platform to connect to and
issue queries to a MySQL Database server.
Simply add an Ethernet shield to your Arduino and use the library
to connect your Arduino to a MySQL database server. Yes, no more
web-based hand waving or third party systems! Cool.
New Feature : Improved Support for Result Sets
In the previous version of the connector, there was a method
named show_results() which demonstrated how to read result sets
(rows returned from the server from a SHOW or SELECT
query).
Unfortunately, this method was too obtuse to be of any use to all
but the most devoted connector fan (you had to know the source
code really well). Perhaps worse, you had to modify the library
directly to use the methods demonstrated.
Why was it like that? Simply because I felt SELECT queries would
be very rare and used by only a very small number of people. I
was wrong. Live and learn, eh?
The good news is the new version has additional methods that can
be called from outside the library making it much, much easier to
get results from your database. Let's see how to do this.
Example: Getting a Lookup Value
I think the most popular request for supporting SELECT queries
was to allow for an easy way to query the database for a lookup
value. Since lookup queries are (or should be) designed to return
exactly one row, we can simplify the code as follows.
Recall when the MySQL server returns a result set, the first
thing returned is a list of the columns in the result set. Next
are the rows. So we must process the columns first.
// SELECT query for lookup value (1 row returned)
// Here we get a value from the database and use it.
long head_count = 0;
my_conn.cmd_query(QUERY_POP);
// We ignore the columns but we have to read them to get
that data out of the queue
my_conn.get_columns();
// Now we read the rows.
row_values *row = NULL;
do {
row = my_conn.get_next_row();
// We use the first value returned in the row
- population of NYC!
if (row != NULL) {
head_count =
atol(row->values[0]);
}
} while (row != NULL);
// We're done with the buffers so Ok to clear them (and
save precious memory).
my_conn.free_columns_buffer();
my_conn.free_row_buffer();
// Now, let's do something with the data.
Serial.print("NYC pop = ");
Serial.println(head_count);
In this example, I query the database for the population of New
York City (nervemind the validity of that value), then use the
value by printing it out. Notice the basic structure is still
there - read columns then read rows but in this case we ignore
the columns because we don't need that data. We still need the
free_*_buffer() calls to free memory however. I explain these
methods in the next example.
Example: Processing Result Sets
The next most popular request for supporting result queries was
being able to loop through a result set and do something with the
data. In this example, I create a method in my sketch to execute
the query and process the results. Let's look at the code
first.
/**
* do_query - execute a query and display results
*
* This method demonstrates how to execute a query, get the
column
* names and print them, then read rows printing the values.
It
* is a mirror of the show_results() example in the
connector class.
*
* You can use this method as a template for writing methods
that
* must iterate over rows from a SELECT and operate on the
values read.
*
*/
/*
void do_query(const char *q) {
column_names *c; // pointer to column values
row_values *r; // pointer to row values
// First, execute query. If it returns a value
pointer,
// we have a result set to process. If not, we exit.
if (!my_conn.cmd_query(q)) {
return;
}
// Next, we read the column names and display them.
// NOTICE: You must *always* read the column names even
if
// you do
not use them. This is so the connector can
// read
the data out of the buffer. Row data follows the
// column
data and thus must be read first.
c = my_conn.get_columns();
for (int i = 0; i < c->num_fields; i++) {
Serial.print(c->fields[i]->name);
if (i < c->num_fields - 1) {
Serial.print(",");
}
}
Serial.println();
// Next, we use the get_next_row() iterator and read rows
printing
// the values returned until the get_next_row() returns
NULL.
int num_cols = c->num_fields;
int rows = 0;
do {
r = my_conn.get_next_row();
if (r) {
rows++;
for (int i = 0; i < num_cols;
i++) {
Serial.print(r->values[i]);
if (i < num_cols -
1) {
Serial.print(", ");
}
}
Serial.println();
// Note: we free the row read to
free the memory allocated for it.
// You should do this after you've
processed the row.
my_conn.free_row_buffer();
}
} while (r);
Serial.print(rows);
Serial.println(" rows in result.");
// Finally, we are done so we free the column
buffers
my_conn.free_columns_buffer();
}
So what's going on here? Notice how the code is structured to
execute the query and if there are results (cmd_query() does not
return NULL), we read the column headers. Why? Because the server
always sends the column data back first for every result
set.
The return from the get_columns() method is a structure that
contains an array of field structures. Here are the
structures:
// Structure for retrieving a field (minimal
implementation).
typedef struct {
char *db;
char *table;
char *name;
} field_struct;
// Structure for storing result set metadata.
typedef struct {
int num_fields; // actual number
of fields
field_struct *fields[MAX_FIELDS];
} column_names;
Notice the column_names structure has a fields array. Use that
array to get information about each field in the form of the
field_struct (see above) structure. In that structure, you will
be able to get the database name, table name, and column name.
Notice in the example I simply print out the column name and a
comma after each except the last column.
Next, we read the rows using a special iterator named
get_next_row() which returns a pointer to a row structure that
contains an array of the field values as follows:
// Structure for storing row data.
typedef struct {
char *values[MAX_FIELDS];
} row_values;
In this case, while get_next_row() returns a valid pointer (not
NULL indicating a row has been read), we access each field and
print out the values.
You may be wondering what is MAX_FIELDS? Well, it is an easy way
to make sure we limit our array to a maximum number of columns.
This is defined in mysql.h and is set to 32. If you want to save
a few bytes, you can change that value to something lower but
beware: if you exceed that value, your code will wander off into
la-la-land (via an unreferenced pointer). There is no end of
array checking so tread lightly.
Notice also there are calls to free_row_buffer() and
free_columns_buffer(). These are memory cleanup methods needed to
free any memory allocated when reading columns and row values
(hey - we got to put it somewhere!).
We call the free_row_buffer() after we are finished
processing the row and the free_columns_buffer() at the end of
the method. If you fail to add these to your own query handler
method, you will run out of memory quickly.
Why is it a manual process? Well, like the MAX_FIELDS setting, I
wanted to keep it simple and therefore save as much space as
possible. Automatic garbage collection would have added a
significant amount of code. Likewise array bound checking would
have add a bit more.
You can use this method as a template to build your own custom
query handler. For example, instead of printing the data to the
serial monitor, you could display it in an LCD or perhaps use the
information in another part of your sketch.
New Feature : Conditional Compilation
If you find you do not need the result set support, you can use
conditional compilation to remove the methods and code from the
connector. This can save you about 2k of program memory!
To do this, simply edit the mysql.h file and comment out this
code:
//#define WITH_SELECT // Comment out this for use without
SELECT capability
// to save space.
This will tell the compiler to ignore key result set handling
methods and code from the connector.
If you do this but find there are methods suddenly missing (via
compilation errors), check your sketch to make sure you are not
using show_results(), get_columns(), get_next_row(), and similar
methods. This is because with the SELECT code turned off, these
methods no longer exist in the compiled library. Uncomment the
#define WITH_SELECT to add them back.
New Feature : Support for WiFi Shield
To use the WiFi shield, you need only make a few changes to your
sketch and a minor change to the library.
Note: You will need to download the WiFi library and
install it to use the WiFi shield. See
http://arduino.cc/en/Main/ArduinoWiFiShield for more
information.
First, add the #include for the WiFi library *before* the include
for the connector (mysql.h).
#include <WiFi.h> // Use this for WiFi
#include <mysql.h>
Next, setup your choice of WiFi connection options in your
setup() method. While you're there, comment out the
Ethernet.begin() call.
// WiFi card example
char ssid[] = "my_lonely_ssid";
char pass[] = "horse_no_name";
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect.
Needed for Leonardo only
// Ethernet.begin(mac_addr);
// WiFi section
int status = WiFi.begin(ssid, pass);
// if you're not connected, stop here:
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi
connection");
while(true);
}
// if you are connected, print out info about the
connection:
else {
Serial.println("Connected to network");
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
...
Lastly, you need to make one small change to the connector
itself. Open the mysql.h file and uncomment these two
lines:
#define WIFI // Uncomment out
this for use with the WiFi shield
#include <WiFi.h> // Uncomment out this for use with
the WiFi shield
This tells the connector to use the conditional compilation
sections to turn on support for the WiFi shield.
New Feature : version() method
I've added a method to return the version of the connector as a
string. If you don't have this method, you're using an old
version of the connector. As more releases of the connector
occur, this method will be key in diagnosing problems or checking
for support of certain features.
(Somewhat) New Feature : Single File Download
This was actually added to the Launchpad site for the previous
version of the connector (version 1.0.0 alpha). But I'm making it
the default download method from now on. You can still get the
code the old way (by using bzr to clone the tree) but the single
file download makes it much easier.
Simply download the file, extract it, then place the two folders;
mysql_connector and sha1 in your libraries folder then restart
the IDE. Install done!
I hope you enjoy the new enhancements.