Examples
This page presents documentation on how to start using the connector in your sketches. You will see helpful, brief introductions to the more commonly used example sketches as well as how to implement common solutions. Be sure to skim through this page for answers to most of your getting started questions.
The following are some of the more popular example sketches available with the connector. The following shows the complete list of example sketches. Be sure to start with one of these as you start using the connector. It is recommended you start with the first one named connect.ino.
You can find these examples sketches under the File | Examples | MySQL Connector Arduino menu. The first example contains the most general information about using the connector. Later examples omit repeating the discussion for brevity. So, read through the first one thoroughly before jumping to the others.
The following are links to the example sketches explained here. As you will see, they are listed in increasing complex order.
- Hello, MySQL!
- Connect By Hostname
- Basic Insert
- Complex Insert
- Basic Select
- Complex Select
- Connect with WiFi
For more help on how to write code for common solutions, see the common solutions page.
The simplest and thus the first example sketch you should attempt is the connect.ino sketch also referred to as the "Hello, MySQL!" sketch. This sketch shows you how to connect to a MySQL server. That's all it does - just a basic connect. If you can get this sketch to work, you've solved many of the common problems using the connector.
Create a new user that has permission to login. For example, you can execute the following statements to create the user shown in the sketch. Note that the following is an extreme and possibly poor security for MySQL servers. Use at your own risk or modify as needed. CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'secret'; GRANT ALL ON . TO 'root'@'%';
#include <Ethernet.h>
#include <MySQL_Connection.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
EthernetClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
// You would add your code here to run a query once on startup.
}
else
Serial.println("Connection failed.");
conn.close();
}
void loop() {
}
We must include the Ethernet class header along with the MySQL_Connector header files. These are all you need to connect to MySQL using an Ethernet or Ethernet2 shield. If you need to use WiFi, see the connect to WiFi example below.
The next section of the code defines the following items.
- MAC Address: this is a 6 element array of binary values that represent the MAC address for your board. If your board has a MAC address, you can include that or you can use anything you want as shown provided it is unique on your network.
- Server Address: this is the MySQL server IP address. Not the address of your Arduino, your PC (unless it also has MySQL installed), or the IP address of your cloud service. This is a common mistake people make.
- User Id: this is the user name for the user you are connecting to MySQL with, not your PC user name or your real name.
- Password: this is the password for the user you are connecting to MySQL with.
NOTE: Be sure to check that you are using the right values for user and password!
Next, we see instantiations for a variable named client of the EthernetClient class followed by and instantiation of a variable named conn of the MySQL_Connection class, which uses the client variable in its constructor. We use the conn variable to initiate the connection to MySQL.
After that, we see in the setup() method we start the Ethernet class (included from above) by calling the Ethernet.begin() method passing in the MAC address defined previously. This sets up the Ethernet networking capability and is required.
This is followed by a call to conn.connect() method that takes the server address (IP address) of the MySQL server defined earlier, the port we want to connect with (3306 is the default), and the user name and password.
Notice this call is inside a conditional that, if successful, issues a 1 second delay. If it is not successful, an error is printed to the Serial Monitor.
Once again, this is the most basic of sketches and demonstrates the basic layout of any Ethernet-based sketch. We will see the changes for a WiFi connection a bit later.
This example demonstrates how to connect to a MySQL server by hostname instead of an IP address. Note that this sketch may not work for all shields or modules or even all Arduino hardware. This is because the sketch relies on a method available in the core networking library to lookup the hostname. If your hardware libraries do not include this method or you have no access to a DNS server on your network, you may not be able to use this solution without modifying it to use an alternative lookup mechanism.
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <Dns.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char hostname[] = "www.google.com"; // change to your server's hostname/URL
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
IPAddress server_ip;
EthernetClient client;
MySQL_Connection conn((Client *)&client);
DNSClient dns_client; // DNS instance
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
// Begin DNS lookup
dns_client.begin(Ethernet.dnsServerIP());
dns_client.getHostByName(hostname, server_ip);
Serial.println(server_ip);
// End DNS lookup
Serial.println("Connecting...");
if (conn.connect(server_ip, 3306, user, password)) {
delay(1000);
// You would add your code here to run a query once on startup.
}
else
Serial.println("Connection failed.");
conn.close();
}
void loop() {
}
Notice beyond the core code for using the connector, we see several new additions.
First, we include the DNS library. Again, this is needed to lookup the IP address by hostname. Then, we define a variable to store the server IP address and a variable named dns_client of the DNSClient class. We use this to do the IP lookup.
Next, we add code to use the DNS library to lookup the IP address. The methods include calling the dns_client.begin() method passing in the Ethernet.dnsServer() method, which returns the IP address of the DNS server. If you cannot connect to a DNS server or your network does not define that parameter, this call may fail and if so, you will not be able to lookup the IP address of the MySQL server by hostname.
Next, we call the dns_client.getHostByName() method passing in the hostname and a variable to store the IP address of the server. Note that if your networking library does not support this method, you will get a compilation error. If the call fails (cannot find the hostname), you could get a value of 0.0.0.0 returned.
Now that we have the IP address of the MySQL server, we can use that variable in our conn.connect() call.
This example demonstrates how to do the most basic of data insert into a MySQL table. This is an extension of the "Hello, MySQL!" example above. It inserts a single row into a MySQL table every 2 seconds. While it may not be practical, it shows how you can structure a sketch to collect data.
To run the script, you will need to create a database named test_arduino and a table in that database named hello_arduino as follows. As you will see, the table has a timestamp column that stores the datetime when the row was inserted.
CREATE DATABASE test_arduino;
CREATE TABLE test_arduino.hello_arduino (
num integer primary key auto_increment,
message char(40),
recorded timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// Sample query
char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
}
void loop() {
delay(2000);
Serial.println("Recording data.");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(INSERT_SQL);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
}
Notice here we include one new header file; the MySQL_Cursor.h file. This will permit us to create a variable of the MySQL_Cursor class and use it to execute queries. We also create a string to contain the INSERT statement. For this example, the string is static. The next example shows how to make it dynamic to add values from variables.
The query portion of the example appears in the loop() method. Here, we create a new variable named cur_mem of type MySQL_Cursor where we pass in the variable for the connector class. Next, we call cur_mem->execute() passing in the query string. Since there are no results returned, we can then destroy the cursor variable.
This example builds on the basic insert sketch but shows how you can substitute data from variables into the SQL string for inserting into a table. Rather than place this in the loop() method, we place the query code in the setup() method so that it adds only one row when executed. But, you can move the query code to loop() if you want to adapt this code to your own sketch.
For this example, you will need to create a database named test_arduino and a table named hello_sensor as follows.
CREATE DATABASE test_arduino;
CREATE TABLE test_arduino.hello_sensor (
num integer primary key auto_increment,
message char(40),
sensor_num integer,
value float,
recorded timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// Sample query
char INSERT_DATA[] = "INSERT INTO test_arduino.hello_sensor (message, sensor_num, value) VALUES ('%s',%d,%s)";
char query[128];
char temperature[10];
EthernetClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Save
dtostrf(50.125, 1, 1, temperature);
sprintf(query, INSERT_DATA, "test sensor", 24, temperature);
// Execute the query
cur_mem->execute(query);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
Serial.println("Data recorded.");
}
else
Serial.println("Connection failed.");
conn.close();
}
void loop() {
}
While the code is very similar to the basic insert example, you will notice we have a different INSERT statement that takes 3 variables or in other words, the string has 3 placeholders for data for the row. We will see how to substitute data in variables for the placeholders.
Notice we have two additional strings; each used as a buffer for converting data in variables to strings. We need to do this because we send all data to MySQL via an INSERT query, which is a string. How we denote (use of quotes) helps MySQL know which are strings and which must be converted to their native types on insert.
As mentioned, the query code is now in the setup() method. It's the same basic 3 statements as before but this time we have more work to do converting the 3 placeholders to actual values (or values from variables).
First, we see an example of how to convert a float to a string with the dtostrf() method. Here, we pass in a buffer (character array or string) and the variable with the value. The result is a formatted string we can use in the sprintf() method to convert the placeholders (the INSERT_SQL is also called a format string containing the formatting specifiers).
The sprintf() method takes as parameters, a buffer followed by variables or constants (in order!) for the substitution. The results is a properly formatted query string that we can use to send to MySQL.
Note: You must ensure the buffers you define are large enough to hold the formatted string. Failure to do so may not produce a compilation error, but is likely to make the sketch unstable or fail during execution.
Now we turn our attention to how to execute queries that return results or SELECT queries. In this example, you will see how to use a SELECT query to retrieve a value from a table row and save it to a variable. You will also see how to use the cursor class in both a dynamic and static declaration.
For this example, you must download and install the sample world database from Oracle's MySQL Documentation Site. Once installed, you can execute the sketch.
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// Sample query
char query[] = "SELECT population FROM world.city WHERE name = 'New York'";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
// Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
}
void loop() {
row_values *row = NULL;
long head_count = 0;
delay(1000);
Serial.println("1) Demonstrating using a cursor dynamically allocated.");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
// Show the result
Serial.print(" NYC pop = ");
Serial.println(head_count);
delay(500);
Serial.println("2) Demonstrating using a local, global cursor.");
// Execute the query
cur.execute(query);
// Fetch the columns (required) but we don't use them.
cur.get_columns();
// Read the row (we are only expecting the one)
do {
row = cur.get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Now we close the cursor to free any memory
cur.close();
// Show the result but this time do some math on it
Serial.print(" NYC pop = ");
Serial.println(head_count);
Serial.print(" NYC pop increased by 12 = ");
Serial.println(head_count+12);
}
The basic setup code is the same as previous examples, but this time we use a string that contains our SELECT query. Here, we retrieve the population for New York city.
In the loop() method, you will see we first execute the query using a dynamic cursor, which is created then deleted after the query is run. Later, we see how to use a static cursor, which is where we create a single cursor and reuse it. The difference is in how the space is allocated. In the dynamic cursor, memory is allocated when created and freed when the cursor is deleted. Depending on the needs of your sketch, one or the other may be more applicable, but the dynamic cursor is the recommended approach.
Whichever option you choose, how we retrieve the data from the row is the same. After executing the query, we first fetch the columns. You must do this to 'clear' the data read from the server. If you wanted to, you could loop through the columns to find the names of the columns.
Next, we retrieve the rows one row at a time using the get_next_row() method of the cursor in a while loop until that method returns NULL. If there is a row returned, we then loop through the columns in the row result capturing the data from the row array and placing it in a variable. If you had more than one column to retrieve, you would need to save those values you want. Finally, we close the cursor to free memory used.
Note: You must read all rows from the result. You cannot stop reading when you get to the row you want.
This is all that is stricly needed for results that return a single row, but for most solutions you may want to consider calling the free_row_buffer() after each row is read.
This example improves on the last example by adding a variable portion to the SELECT query where we can provide a value via a variable using the placeholder concept from the basic insert examples. The code for this sketch is similar to the last example except that we only see the dynamic cursor example and we are retrieving multiple rows from the result.
For this example, you must download and install the sample world database from Oracle's MySQL Documentation Site. Once installed, you can execute the sketch.
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// Sample query
//
// Notice the "%lu" - that's a placeholder for the parameter we will
// supply. See sprintf() documentation for more formatting specifier
// options
const char QUERY_POP[] = "SELECT name, population FROM world.city WHERE population > %lu ORDER BY population DESC;";
char query[128];
EthernetClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
}
void loop() {
delay(1000);
Serial.println("> Running SELECT with dynamically supplied parameter");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Supply the parameter for the query
// Here we use the QUERY_POP as the format string and query as the
// destination. This uses twice the memory so another option would be
// to allocate one buffer for all formatted queries or allocate the
// memory as needed (just make sure you allocate enough memory and
// free it when you're done!).
sprintf(query, QUERY_POP, 9000000);
// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name);
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(row->values[f]);
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
free_row_buffer();
Serial.println();
}
} while (row != NULL);
free_columns_buffer();
// Deleting the cursor also frees up memory used
delete cur_mem;
}
Like the complex insert example, we are going to format a new query string using data from a variable. In this case, we provide a large integer in the sprintf() call to generate a new SELECT query with the value substituted in the placeholder.
We then execute the query, retieve the columns and loop through the columns displaying the columns in the Serial Monitor. We use a comma to separate the values.
Once we've read all of the columns, we begin reading the rows one at a time and looping through the columns values displaying those in the Serial Monitor. We use a comma to separate the values.
Notice the use of free_row_buffer() after each row is read. This is the recommended way to ensure any data allocated to the row buffer is deleted (freed). If you do not do this, and you read a lot of rows, you could have a memory leak and eventually run out of memory making the sketch unstable or causing the board to reboot. Finally, the free_columns_buffer() method is called to do the same for the columns buffer.
If you use this sketch, try replacing the value 90000000 with other values to see how the resulting rows differ. Be careful not to use too small a value or you will get many rows returned.
The final example demonstrates how to use a WiFi shield rather than an Ethernet shield. As you will see, this adds a lot more code to the declaration and setup() method.
For this example, you only need a WiFi network to connect that is accessible and has a route to your MySQL server. Be sure to note the SSID and password. Naturally, you will need a WiFi shield that is compatible with your WiFi network.
You also need to modify the MySQL_Packet.h file to import the WiFi.h header instead of th Ethernet.h header. Failure to do so will make your sketch fail to connect to MySQL (cause the connetor to fail). Similarly, if you are using a different WiFi library, you would place that in the MySQL_Packet.h file. See modifying the connector section in the hardware guide for more details.
#include <WiFi.h> // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// WiFi card example
char ssid[] = "horse_pen"; // your SSID
char pass[] = "noname"; // your SSID Password
WiFiClient client; // Use this for WiFi instead of EthernetClient
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect. Needed for Leonardo only
// Begin WiFi section
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi connection");
while(true);
}
// 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);
}
// End WiFi section
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
conn.close();
}
void loop() {
}
The code for this example is similar to the connect example, but much more complex on setup given the WiFi portion is longer.
To begin, we declare variables for the SSID and password. Yes, the password ends up being in plain text in your code. We also use the WiFi equivalent of the Ethernet classes to define the client and pass that to the the connector.
Once that is done, we then add code to the setup() method to first start the WiFi client with the WiFi.begin() method where we pass in the SSID and password saving the result to a variable. We check the variable to see if it succeeded. If it did not (the result is WL_CONNECTED), we send an error to the Serial Monitor and then hang intentionally with an endless while(true) loop. If the connection successfully connects, we display the IP address of the connection to the Serial Monitor.
Note: there are many WiFi shields and monitors that you can use. Most use this basic path for setup and connecting, but there are a few that have their own steps. Be sure to check the documentation for the library that comes with your shield or module for more details.