Chapter 6 ■ Building low-Cost MysQl data nodes
Using Connector/Arduino
Let’s begin with a simple sketch designed to insert a single row into a table in MySQL. You are creating
a “Hello, world!” sketch (but saved in a database table). All database-enabled sketches share the same
common building blocks. These include setting up a database to use, creating a sketch with a specific set of
include files, connecting to the database server, and executing queries. This section walks through the basic
steps needed to create and execute a database-enabled sketch.
■Tip the library includes a number of examples sketches to get you going quickly. Check out the examples
in your quest to master the library. you will find examples of how to connect using wiFi and even how to build
complex queries from variables in your sketch.
The first thing you need is a database server! Begin by creating a database and a table to use to store
the data. For this experiment, you create a simple table with two columns: a text column (char) to store a
message and a TIMESTAMP column to record the date and time the row was saved. I find the TIMESTAMP data
type to be an excellent choice for storing sensor data. It is rare that you would not want to know when the
sample was taken! Best of all, MySQL makes it easy to use. In fact, you need pass only a token NULL value to
the server, and it generates and stores the current timestamp itself.
Listing 6-4 shows a MySQL client (named mysql) session that creates the database and the table and
inserts a row into the table manually. The sketch will execute a similar INSERT statement from your Arduino.
By issuing a SELECT command, you can see each time the table was updated.
Listing 6-4. Creating the Test Database
$ mysql -uroot -psecret
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 102
Server version: 5.6.14-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test_arduino;
Query OK, 1 row affected (0.00 sec)
mysql> USE test_arduino;
Database changed
mysql> CREATE TABLE hello (source char(20), event_date timestamp);
Query OK, 0 rows affected (0.01 sec)