Introducing MySQL Connector/Arduino

Have you ever wanted to use a local database server to store data from your Arduino projects? Would you like to be able to send queries directly to a MySQL database from your Arduino sketch? Well, now you can!

The MySQL Connector/Arduino is a new technology made for the Arduino permitting you to connect your Arduino project to a MySQL server via an Ethernet shield without using an intermediate computer or a web-based service. 

Having direct access to a database server means you can store data acquired from your project as well as check values stored in tables on the server and keep the network local to your facility including having a network that isn't connected to the internet or any other network.

Example Code

The Connector/Arduino is an Arduino library that encapsulates everything you need to communicate with a MySQL server. It's also very easy to use. The following shows a simple sketch to connect to a MySQL server and insert a row of data at startup.

/**
* Example: Hello, MySQL!
*
* This code module demonstrates how to create a simple 

* database-enabled sketch.
*/
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"

/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10, 0, 1, 23);

/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference

char user[] = "root";
char password[] = "secret";
char INSERT_SQL[] = 

 "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)";

void setup() {
  Ethernet.begin(mac_addr);
  Serial.begin(115200);
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password))
  {
    delay(500);
     /* Write Hello, World to MySQL table test_arduino.hello */
     my_conn.cmd_query(INSERT_SQL);
     Serial.println("Query Success!");
  }
  else
    Serial.println("Connection failed.");
}

void loop() {
}

As you can see, the library adds very few methods for communicating with a MySQL server.

What Can It Do?

The Connector/Arduino library allows you to issue queries to the database server in much the same manner as you would through the MySQL client application. You can insert, delete, and update data, call functions, create objects, etc. Issuing SELECT queries are also possible but they incur a bit more thought concerning memory management.

When issuing SELECT queries, the query strings must fit into memory and the largest row of a result set must also fit in memory. This is because result sets are read one row at a time and the class uses an internal buffer for building data packets to send to the server. The connector reads one packet-at-a-time and since the Arduino has a limited data size, the combined length of all fields must be less than available memory. It is suggested long strings be stored in program memory using PROGMEM (see cmd_query_P in the mysql.cpp file http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/view/head:/mysql.cpp).

Most projects are those that need to store data and in that case the only memory requirements are those for the SQL statements. However, with careful planning, you can preserve memory by using parametrized queries.

Limitations

As you can imagine, a library that communicates with a MySQL server is larger than most libraries. Indeed, it consumes about 16-20k of program space. Fortunately, the latest Arduino boards have enough memory that only the most complex projects need be concerned. And in that case you can move to a larger Arduino board like the Arduino Mega.

Aside from memory, the following are some limitations you may want to consider when planning your sketches.

• Query strings (the SQL statements) must fit into memory.
• Result sets are read one row-at-a-time and one field-at-a-time.
• The combined length of a row in a result set must fit into memory.
• Server error responses are processed immediately with the error code and text written via Serial.print.

How To Get MySQL Connector/Arduino

You can download Connector/Arduino from LaunchPad (https://launchpad.net/mysql-arduino). The library is open source, licensed as GPLv2, and owned by Oracle Corporation. Thus, any modifications to the library that you intend to share must meet the GPLv2 license.

By popular demand, I have made available a zip file that contains the mysql_connector and patched sha1 code. Go to https://launchpad.net/mysql-arduino and download the zip file, extract it, and copy the mysql_connector and sha1 folders to your Arduino/Libraries folder.

For More Information

If you would like to get started using the library, please feel free to download it and checkout the example and the text files for specifics of installation and use.

However, if you'd like a full tutorial and learn more about using the Connector/Arduino in your project and to learn more about sensor networks, look for my book entitled Beginning Sensor Networks with Arduino and Raspberry Pi (Apress) due June 2013.

http://www.apress.com/

Note

The Connector/Arduino library was created to demonstrate the versatility of the MySQL client protocol and to provide a unique capability for the Arduino platform. While offered under the GPLv2 license, the library is not supported by Oracle.