Skip to content

Examples

Jump to bottom
Dr. Charles Bell edited this page Mar 29, 2020 · 11 revisions

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.

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.

Example MySQL Connector Sketches

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)

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.

Code

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

Discussion

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.

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

Specify Default Database

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

Clone this wiki locally