Wednesday, October 23, 2013

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.

28 comments:

  1. Excuse me
    Would you tell me how your code download/upload data?
    Thank you very much!

    ReplyDelete
    Replies
    1. See the examples in the code. Code is available on Launchpad: https://launchpad.net/mysql-arduino

      Delete
  2. I am not sure what you mean. This is a database connector. So we use INSERT and UPDATE SQL statements to insert data and SELECT SQL statements to retrieve the data.

    ReplyDelete
  3. Hi Chuck,

    Sorry for bothering you directly, but couldn't find my answer in posts nor in google...

    I am running the "mysql_connector_basic" sketch from the examples folder.

    At first I had to change the line :
    Ethernet.begin(mac_addr);
    To :
    Ethernet.begin(mac_addr, ip_addr);

    Otherwise, it would just get stuck at :
    if (my_conn.mysql_connect(server_addr, 3306, user, password))

    Without any error message (at least shown in the serial monitor).

    Now I can connect. Do "insert", use your do_query() function. But I can not use both in the same sketch.

    Either I do a "insert" or I do a "select". I can do multiple "select" or multiple "insert" but can't do both.

    When I do have both in the same sketch. The connection doesn't even get through. It stops at :
    if (my_conn.mysql_connect(server_addr, 3306, user, password))

    Still no error message.
    I am thinking this is a memory problem. The IDE shows :
    Binary sketch size: 19,308 bytes (of a 32,256 byte maximum)

    So this seems to be alright.


    This is the setup code :

    void setup() {
    Ethernet.begin(mac_addr, ip_addr);
    Serial.begin(115200);

    delay(2000);
    Serial.println("Connecting...");
    Serial.println(server_addr);
    if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
    Serial.println("Connected");
    delay(500);

    my_conn.cmd_query(INSERT_TEXT);
    Serial.println("Query Success!");
    delay(2000);
    do_query(HELLO_SQL);
    Serial.println("Query Success!");

    }
    else
    Serial.println("Connection failed.");
    }

    Nothing in the loop.

    Well I don't know what else I can do. I am starting to look in the cpp and the h file. But I doubt the problem is in there. I'll try the reduced size sha1 library might help if it is a memory problem.

    Thank you for you patience.

    Brian

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Hi,

      Finaly got it,

      It was a memory problem. Downloaded the lastest IDE (the 1.5.6 r2) and I can see warnings at bottom :

      Global variables use 1,608 bytes (78%) of dynamic memory, leaving 440 bytes for local variables. Maximum is 2,048 bytes.
      Low memory available, stability problems may occur

      Indeed!

      2 way to go :

      -Arduino Mega with 8kb of SRAM
      -Going trought the files and commenting any Serial.println(" somestring"); commenting any unecessary function to your needs.

      Delete
    4. Sorry I haven't replied yet. You are correct and with this evidence it is clearly a memory problem.

      Interestingly, I've had a small number of people report this on the newer boards. I wonder if there have been updates or something that has decreased available memory. I say this because the connector works on my older boards.

      You are also correct that the Mega will fix the problem. So will trimming the connector library. I think maybe I should have a separate, low memory version. Cut the fat as it were. What do you think?

      Delete
    5. Please send me private email (d r c h a r l e s b e l l at g m a i l dot c o m) with your complete sketch. I'd like to use it to test my ideas for reducing memory use in the connector.

      Delete
  4. Hello Mr. Chuck,

    I have purchased your book Beginning Sensor Network with Arduino and Raspberry Pi. I was working on Chapter 7, Project for Reading values from DHT22 and storing it on a MySQL server. I have followed the code you have posted in the book, changed the IP address to match the IP of my database server. When I upload the sketch and log into the Serial Monitor, it says connecting.. and remains at that for a really long time. I individually uploaded the sketch for DHT22 and MySQL-Arduino connector. I am able to read values from the DHT22 and I am able to connect to the database server. However, when integrated it doesn't work. The serial monitor just remains stuck at "connecting..."

    ReplyDelete
    Replies
    1. Sorry for the delay. I have been ill but getting better now.

      It may be a memory issue. Since the connector uses a lot of memory, you must try to reduce the number of variables and especially strings used in your sketch. Reduce memory used and try again.

      I have some plans to work on memory use but until then you will need to be careful to reduce memory use as much as possible.

      Delete
  5. hello Mr Chuck,
    I am working on a project which needs my arduino to connect directly to sql database. Also it should support a two way communication like reading to and from sql database.I am trying to do this without using an ethernet shield.Instead i want to use serial port directly which is connected to a computer having internet access.
    Can i use your MySql library connector for this purpose and if so please help me!!

    ReplyDelete
    Replies
    1. Hi,

      No, this library is not designed to communicate over serial - only Ethernet. You can use processing to write sketches to 'talk' to your Arduino and there are several examples out there. I would search for processing and see how others have written sketches to do similar things.

      Delete
  6. Hi, I chuck,

    I use mysql_connector-1.01b version and ardunio 1.05, I use your example code mysql_connector_basic. I keep hang in "connecting ..."

    but when I comment out the example 5 which inserting real time data into a table. then everything work. I think sprintf(query, insert_data, temperature) give me trouble. my program is about 19K. would it still be memory problem?
    much appreciate your help

    James

    ReplyDelete
  7. Hi, Chuck
    I just download 1.0.0 b earlier version, seems work better than 1.0.1B, only this time it hangs on example 4, if I comment out, it works. any reason why

    James

    Thanks

    ReplyDelete
    Replies
    1. Hi. If you are trying to run all of the examples at one time, it is too much memory for the processor (the strings primarily).

      If, OTOH, you are running them individually, there still may be a memory issue on some boards. I am working on a solution and hope to have it soon that should help considerably.

      The older version of the connector is better because it is smaller. Hence, less memory usage.

      Again, I am working on a solution and hope to have a better version of the connector soon.

      Delete
    2. Thanks. I will limit my sql statment, one question, Is Msql arduino connector persistance to DB. As a sensors network, will MYSQL server have issue on max connections

      Delete
    3. Yes and no. The server will eventually disconnect a client if it is dormant. To avoid this, you can use the disconnect() method. You can disconnect after issuing a query then reconnect at the start of the loop(). See the hello_mysql example for one way to do this.

      Delete
  8. excuse me sir i want to ask
    I have a project for monitoring electricity 3 phase use Arduino with current sensor (CT) the problem is how to store the data in a data base so that the web server is not stored in memory Arduino
    I have learned if there's a script that I can make reference thanks

    ReplyDelete
    Replies
    1. I am not sure what you are asking. There are a number of examples in the code for inserting data into a MySQL database.

      Also, my other blogs have information about using the connector.

      If you need help from the ground up, you might want to check my book, Beginning Sensor Networks. It has a couple of chapters that walk you through using the connector.

      Delete
  9. Hello,

    i would like use the mysql_connector
    but i get a lot of errors if i use it.
    i use arduino mega 2560 R3 with ethernet shield.
    my mysql-db is on a raspberry pi in the lokal network.

    if i connect to sb and i use the serial monitor then i get some crazy signs
    like
    äbPq ...
    but i get no ... connection ok or wrong

    what kind of librarie i should use?

    best regards
    Jürgen

    ReplyDelete
    Replies
    1. The baud rate of your serial monitor must match the baud rate in your sketch.

      Delete
  10. Hi Chuck,

    I was planning to use arduino with RFID on my project. Is it possible to not run mysql_connector manually but it will run automatic once it detects RFID activity and update my external database?

    ReplyDelete
    Replies
    1. Sure. Just put the usual setup steps but move the mysql_connect call to a new method with your query then a disconnect() call at the end. This new method would be triggered by the RFID code.

      This way, the code only connects to the server when it needs to send data.

      Delete
  11. Hi Chuck
    Thanks for your blog.
    I have error on serial monitor:
    Connecting...
    Connection failed.
    PACKET ERROR
    "packet error" it means something with my router?

    ReplyDelete
    Replies
    1. It most likely means you have no connection to your server or there is a long network latency that the connector cannot overcome.

      You should test your connection using a second computer and the mysql client. Use the same server IP, username, and password in your sketch and see if you can connect. If you cannot, correct the problems until you do then transfer any changes to IP, username, password, port, to your sketch and try it again.

      OTOH if it is latency related you may need to improve your network connection. For example, if you are attempting to connect to a server on the internet over unreliable or unpredictable bandwidth, the Arduino Ethernet and the connector may not be able to handle the lag. I have some small adjustments that could be made but you must determine and demonstrate you can connect to your server with a mysql client to rule out the most likely problems.

      Delete
  12. Hello, Chuck
    I am wondering if it is possible to transform the response I get from a SELECT statement into a VARIABLE in the Arduino memory (preferably a string) to use in a comparison between a read value. In my case would be the ID from a RFID card.
    Thanks for the time.

    ReplyDelete
    Replies
    1. I forgot to specify that the "read value" comes from the RFID reader. By far, all I can do is to display the values in the serial monitor. I need to compare the read ID from the card to the stored in the database.

      Delete