Introducing MySQL Connector/Arduino 1.0.0 beta

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.