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.
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
/**
* 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[] =
*/
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"
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.
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.
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.
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.
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.
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.
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.
