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.

Example Sketches

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.

Hello, MySQL! (connect.ino)

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.

Code

#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() {
}

Discussion

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.

Connect by Hostname (connect_by_hostname.ino)

Code

Discussion

Basic Insert (basic_insert.ino)

Code

Discussion

Complex Insert (complex_insert.ino)

Code

Discussion

Basic Select (basic_select.ino)

Code

Discussion

Complex Select (complex_select.ino)

Code

Discussion

Connect with WiFi (connect_wifi.ino)

Code

Discussion

Common Solutions

The following are code fragments and suggestions for solving some of the more common problems working with MySQL, data, and the connector.

Enabling DEBUG Mode

Replacing Values in SQL Commands for Variables

Storing Floating Point Values

Inserting Multiple Rows in a Single Query

Recording Datetime for a Row

Using a Lookup Table

Retrieving a Field Value in a Variable

Preventing Dropped Connections

Using PROGMEM for Query Strings (SQL)

Connect/Reconnect

Reboot