Project tutorial
Read And Update Databases With Temboo

Read And Update Databases With Temboo © GPL3+

We'll show you how to get your Arduino Yún to write to and read from a MySQL database. This will get you started with storing historic data.

  • 2,404 views
  • 2 comments
  • 5 respects

Components and supplies

Apps and online services

About this project

1. Get Set Up

  • Make sure you have a Temboo account. If you don't already have one, you can register for a free account here.
  • You'll also need an Amazon AWS account, which you can create here.
  • Make sure to select the US East (N. Virginia) region from the menu in the upper right side of the RDS Management Dashboard before creating the new instance. Amazon provides a detailed guide to setting up an RDS instance here. Important Note: make sure to select the smallest RDS instance possible, and only have one instance running at a time, so that you stay within the bounds of Amazon's free tier. Otherwise, you can run up an expensive AWS bill.

2. Configure your RDS

Configure your RDS instance by adding a security rule to allow access by Temboo. If you're familiar with the RDS command line tools, you should follow these instructions. Otherwise, we'll guide you through the process using one of our Amazon Choreos.

First, find your AWS Access Key and Secret Access Key in the Security Credentials pane of the AWS management dashboard. If you haven't yet created an Access Key, you will be able to do so from there. Next, go to the Amazon > RDS > AuthorizeDBSecurityGroupIngress Choreo and enter the required inputs. You've already got the first two, and you can find the others below. Make sure to leave CIDRIP blank and to update the DBSecurityGroupName value if you changed it from Amazon's default value.

CIDRIP:

  • DBSecurityGroupName: default (unless you changed it)
  • EC2SecurityGroupName: Temboo Access
  • EC2SecurityGroupOwnerId: 114370834540

Finally, run the Choreo by clicking the Try it Out button. All going well you'll see XML output listing the access permissions in your RDS security group and Temboo will now have access to your RDS database.

3. Get started with the sketch

Now you're all set to get started with the sketch. You'll need to overwrite the placeholders in the code with the following information about your database, available from Instances pane of the RDS dashboard:

  • The address of the DB server (e.g., topsecretdata.abc3xy6ijklmn.us-east-1.rds.amazonaws.com). Note: remember to remove the port number (e.g., ':3306') from the end of the address after you copy/paste from the RDS dashboard.
  • The database name
  • The username (this user should have read, write and create access to the database)
  • The password associated with the username

Make sure that your Yún is connected to the Internet.

4. Write the Sketch

Copy the sketch code below into a new tab in your Arduino IDE. Replace the placeholder values in the code with the details of your own database.

/*
  SendDataToMySQL
  Demonstrates storing and reading values in a MySQL database using
  the Temboo Arduino Yun SDK. This example will periodically
  update a MySQL instance with dummy sensor values from the Yun, and
  then read those values back from the database.
  This example code is in the public domain.
*/
#include <Bridge.h>
#include <Temboo.h>
#include "TembooAccount.h" // contains Temboo account information
/*** SUBSTITUTE YOUR VALUES BELOW: ***/
// Note that for additional security and reusability, you could
// use #define statements to specify these values in a .h file.
const String DATABASE_SERVER = "server-address-of-your-MySQL-instance";
const String DATABASE_NAME = "your-MySQL-database-name";
const String DATABASE_USERNAME = "your-MySQL-user-name";
const String DATABASE_PASSWORD = "your-MySQL-user-password";
// the name of the table to write to and read from
const String DATABASE_TABLE_NAME = "readings";
// how often to run the Choreo (in milliseconds)
const unsigned long RUN_INTERVAL_MILLIS = 60000; 
// the last time we ran the Choreos (initialized to 60 seconds ago,
// so the Choreos are run immediately when we start up)
unsigned long lastRun = (unsigned long)-60000;
// a flag to indicate if we were able to create the table
// (or that it already existed)
bool haveTable = false;
void setup() {
  
  // for debugging, wait until a serial console is connected
  Serial.begin(9600);
  delay(4000);
  while(!Serial);
  Serial.print("Initializing the bridge...");
  Bridge.begin();
  Serial.println("OK");
  // make sure the table we need exists or create it if it doesn't
  // (see the comments in the createTable function for details)
  unsigned int result = createTable();
  // set the 'haveTable' flag to true on success,
  // or false if there was a problem creating the table
  haveTable = (result == 0);
}
void loop()
{
  // get the number of milliseconds this sketch has been running
  unsigned long now = millis();
  // run again if it's been RUN_INTERVAL_MILLIS milliseconds since we last ran
  if (now - lastRun >= RUN_INTERVAL_MILLIS) {
    // remember 'now' as the last time we ran the choreo
    lastRun = now;
    // do the database write/read only if we have the table.
    if (haveTable) { 
      // get the value we want to add to our table
      int sensorValue = getSensorValue();
      // add a record containing the cpu time and sensor value
      appendRow(now, sensorValue);
      // Read the values just written to verify that they really
      // did get sent to the database. (Not necessary in a real
      // sketch, but done here to demonstrate retrieving data.)
      retrieveRow(now);
    } else {
      Serial.println("Table creation failed, not appending row.");
    }
 }
}
/* 
 * createTable is a function that executes a SQL statement to
 * create a table with the correct columns needed for this sketch.
 */
unsigned int createTable() {
  Serial.print("Creating table '" + DATABASE_TABLE_NAME + "' (if needed)...");
  // We need a table with columns to contain the cpu time and the sensor value.
  // cpu time is an 'unsigned long' which is 4 bytes on the Yun, 
  // so an INT UNSIGNED in MySQL will hold any possible value.
  // Our sensor values are 'int' values on the Yun, 
  // so an INT in MySQL will hold all possible values.
  // Create a String containing the SQL statement to create the table.
  String sql = "CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE_NAME + " (cpu_time INT UNSIGNED, sensor_value INT);";
  // Send the SQL to Temboo so it can forward it to your database.
  unsigned int result = executeSQLCommand(sql, false);
  // If there was an error, the executeSQLCommand function will have 
  // printed it to the Serial console.  Otherwise, print OK.
  if (result == 0) {
    Serial.println("OK");
  }
  return result;
}
/* 
 * appendRow is a function that executes a SQL statement to
 * insert a new row of data into the database.
 * cpuTime is the value to be inserted into the cpu_time column
 * value is the value wot be inserted into the sensor_value column
 */
unsigned int appendRow(unsigned long cpuTime, int value) {
  Serial.print("Inserting row " + String(cpuTime) + ", " + String(value) + "...");
  // Create a String containing the SQL statement to insert a row.
  String sql = "INSERT " + DATABASE_TABLE_NAME + " VALUES (" + cpuTime + "," + value + ");";
  // Send the SQL to Temboo so it can forward it to your database.
  unsigned int result = executeSQLCommand(sql, false);
  // If there was an error, the executeSQLCommand function will have 
  // printed it to the Serial console.  Otherwise, print OK.
  if (result == 0) {
    Serial.println("OK");
  }
  return result;
}
/*
 * retrieveRow is a function that executes a SQL statement to
 * retrieve a row of data from the database.
 * cpuTime is the cpu_time value of the row to be retrieved.
 * (note that it must exactly match the value stored.)
 */
unsigned int retrieveRow(unsigned long cpuTime) {
  Serial.println("Retrieving row with time " + String(cpuTime) + "...");
  // Create a String containing the SQL statement to retrieve a row.
  String sql = "SELECT * FROM " + DATABASE_TABLE_NAME + " WHERE cpu_time = '" + cpuTime + "';";
  // Send the SQL to Temboo so it can forward it to your database.
  // In this case, we want to print the raw output we get from 
  // Temboo.  This is just to demonstrate that the data really did
  // get written to the database.
  unsigned int result = executeSQLCommand(sql, true);
  return result;
}
/*
 * executeSQLCommand is a function for sending SQL statements to
 * Temboo for execution on your database.
 * sql is a String containing the SQL statement to be executed.
 * showOutput is a boolean flag which if set to 'true' will cause
 * the raw Choreo results to be printed to the Serial console.
 */
unsigned int executeSQLCommand(String sql, bool showOutput) {
  // Create a TembooChoreo object
  TembooChoreo choreo;
  // Initialize the Choreo object.
  choreo.begin();
  // set Temboo account credentials
  choreo.setAccountName(TEMBOO_ACCOUNT);
  choreo.setAppKeyName(TEMBOO_APP_KEY_NAME);
  choreo.setAppKey(TEMBOO_APP_KEY);
  // specify the Choreo to be run
  choreo.setChoreo("/Library/MySQL/RunCommand");
  // add inputs specifying the RDS endpoint address
  // and the database name within that RDS instance
  choreo.addInput("Server", DATABASE_SERVER);
  choreo.addInput("DatabaseName", DATABASE_NAME);
  
  // add inputs for the MySQL user credentials
  choreo.addInput("Username", DATABASE_USERNAME);
  choreo.addInput("Password", DATABASE_PASSWORD);
  // add the SQL command to be executed
  choreo.addInput("SQL", sql);
  // run the Choreo and wait for the results
  // The return code (returnCode) will indicate success or failure 
  unsigned int returnCode = choreo.run();
  // return code of zero (0) means success
  if (returnCode == 0) {
    // print the raw output if requested.
    if (showOutput) {
      while (choreo.available()) {
        char c = choreo.read();
        Serial.print(c);
      }
    }
  } else {
    // return code of anything other than zero means failure  
    // read and display any error messages
    while (choreo.available()) {
      char c = choreo.read();
      Serial.print(c);
    }
    Serial.println("");
  }
  choreo.close();
  return returnCode;
}
/*
 * getSensorValue is a function to simulates reading the value of a sensor
 */
int getSensorValue() {
  Serial.print("Reading sensor value...");
  int value = analogRead(A0);
  Serial.println("OK");
  return value;
}

5. Create Your Header File

The sketch above references the TembooAccount.h header file, which contains your Temboo account information.

If you are currently logged in, you'll see your account details in the code snippet below (otherwise you'll see placeholder values). Copy the code snippet into a new tab in Arduino and call it TembooAccount.h.

With both files in place you are ready to upload the sketch and talk to your MySQL database from your Yún. Go forth and master your data.

#define TEMBOO_ACCOUNT "accountName"  // your Temboo account name 
#define TEMBOO_APP_KEY_NAME "myFirstApp"  // your Temboo app key name
#define TEMBOO_APP_KEY  "abc123xxxxxxxxxxxxxx"  // your Temboo app key

6. What's Next?

Now that you've got databases under control, why not check out the other 2000+ Choreos in our Library and start thinking about all the possibilities for your next Yún project.

7. Need Help?

We're always happy to help. Just email us at support@temboo.com, and we'll answer your questions.

Code

Code snippet #1Arduino
/*
  SendDataToMySQL

  Demonstrates storing and reading values in a MySQL database using
  the Temboo Arduino Yun SDK. This example will periodically
  update a MySQL instance with dummy sensor values from the Yun, and
  then read those values back from the database.

  This example code is in the public domain.
*/

#include <Bridge.h>
#include <Temboo.h>
#include "TembooAccount.h" // contains Temboo account information

/*** SUBSTITUTE YOUR VALUES BELOW: ***/

// Note that for additional security and reusability, you could
// use #define statements to specify these values in a .h file.

const String DATABASE_SERVER = "server-address-of-your-MySQL-instance";
const String DATABASE_NAME = "your-MySQL-database-name";
const String DATABASE_USERNAME = "your-MySQL-user-name";
const String DATABASE_PASSWORD = "your-MySQL-user-password";

// the name of the table to write to and read from
const String DATABASE_TABLE_NAME = "readings";


// how often to run the Choreo (in milliseconds)
const unsigned long RUN_INTERVAL_MILLIS = 60000; 

// the last time we ran the Choreos (initialized to 60 seconds ago,
// so the Choreos are run immediately when we start up)
unsigned long lastRun = (unsigned long)-60000;

// a flag to indicate if we were able to create the table
// (or that it already existed)
bool haveTable = false;

void setup() {
  
  // for debugging, wait until a serial console is connected
  Serial.begin(9600);
  delay(4000);
  while(!Serial);

  Serial.print("Initializing the bridge...");
  Bridge.begin();
  Serial.println("OK");

  // make sure the table we need exists or create it if it doesn't
  // (see the comments in the createTable function for details)
  unsigned int result = createTable();

  // set the 'haveTable' flag to true on success,
  // or false if there was a problem creating the table
  haveTable = (result == 0);
}

void loop()
{
  // get the number of milliseconds this sketch has been running
  unsigned long now = millis();

  // run again if it's been RUN_INTERVAL_MILLIS milliseconds since we last ran
  if (now - lastRun >= RUN_INTERVAL_MILLIS) {

    // remember 'now' as the last time we ran the choreo
    lastRun = now;

    // do the database write/read only if we have the table.
    if (haveTable) { 

      // get the value we want to add to our table
      int sensorValue = getSensorValue();

      // add a record containing the cpu time and sensor value
      appendRow(now, sensorValue);

      // Read the values just written to verify that they really
      // did get sent to the database. (Not necessary in a real
      // sketch, but done here to demonstrate retrieving data.)
      retrieveRow(now);

    } else {
      Serial.println("Table creation failed, not appending row.");
    }
 }
}

/* 
 * createTable is a function that executes a SQL statement to
 * create a table with the correct columns needed for this sketch.
 */
unsigned int createTable() {
  Serial.print("Creating table '" + DATABASE_TABLE_NAME + "' (if needed)...");

  // We need a table with columns to contain the cpu time and the sensor value.
  // cpu time is an 'unsigned long' which is 4 bytes on the Yun, 
  // so an INT UNSIGNED in MySQL will hold any possible value.
  // Our sensor values are 'int' values on the Yun, 
  // so an INT in MySQL will hold all possible values.

  // Create a String containing the SQL statement to create the table.
  String sql = "CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE_NAME + " (cpu_time INT UNSIGNED, sensor_value INT);";

  // Send the SQL to Temboo so it can forward it to your database.
  unsigned int result = executeSQLCommand(sql, false);

  // If there was an error, the executeSQLCommand function will have 
  // printed it to the Serial console.  Otherwise, print OK.
  if (result == 0) {
    Serial.println("OK");
  }

  return result;
}

/* 
 * appendRow is a function that executes a SQL statement to
 * insert a new row of data into the database.
 * cpuTime is the value to be inserted into the cpu_time column
 * value is the value wot be inserted into the sensor_value column
 */
unsigned int appendRow(unsigned long cpuTime, int value) {
  Serial.print("Inserting row " + String(cpuTime) + ", " + String(value) + "...");

  // Create a String containing the SQL statement to insert a row.
  String sql = "INSERT " + DATABASE_TABLE_NAME + " VALUES (" + cpuTime + "," + value + ");";

  // Send the SQL to Temboo so it can forward it to your database.
  unsigned int result = executeSQLCommand(sql, false);

  // If there was an error, the executeSQLCommand function will have 
  // printed it to the Serial console.  Otherwise, print OK.
  if (result == 0) {
    Serial.println("OK");
  }
  return result;
}

/*
 * retrieveRow is a function that executes a SQL statement to
 * retrieve a row of data from the database.
 * cpuTime is the cpu_time value of the row to be retrieved.
 * (note that it must exactly match the value stored.)
 */
unsigned int retrieveRow(unsigned long cpuTime) {
  Serial.println("Retrieving row with time " + String(cpuTime) + "...");

  // Create a String containing the SQL statement to retrieve a row.
  String sql = "SELECT * FROM " + DATABASE_TABLE_NAME + " WHERE cpu_time = '" + cpuTime + "';";

  // Send the SQL to Temboo so it can forward it to your database.
  // In this case, we want to print the raw output we get from 
  // Temboo.  This is just to demonstrate that the data really did
  // get written to the database.
  unsigned int result = executeSQLCommand(sql, true);

  return result;
}

/*
 * executeSQLCommand is a function for sending SQL statements to
 * Temboo for execution on your database.
 * sql is a String containing the SQL statement to be executed.
 * showOutput is a boolean flag which if set to 'true' will cause
 * the raw Choreo results to be printed to the Serial console.
 */
unsigned int executeSQLCommand(String sql, bool showOutput) {

  // Create a TembooChoreo object
  TembooChoreo choreo;

  // Initialize the Choreo object.
  choreo.begin();

  // set Temboo account credentials
  choreo.setAccountName(TEMBOO_ACCOUNT);
  choreo.setAppKeyName(TEMBOO_APP_KEY_NAME);
  choreo.setAppKey(TEMBOO_APP_KEY);

  // specify the Choreo to be run
  choreo.setChoreo("/Library/MySQL/RunCommand");

  // add inputs specifying the RDS endpoint address
  // and the database name within that RDS instance
  choreo.addInput("Server", DATABASE_SERVER);
  choreo.addInput("DatabaseName", DATABASE_NAME);
  
  // add inputs for the MySQL user credentials
  choreo.addInput("Username", DATABASE_USERNAME);
  choreo.addInput("Password", DATABASE_PASSWORD);

  // add the SQL command to be executed
  choreo.addInput("SQL", sql);

  // run the Choreo and wait for the results
  // The return code (returnCode) will indicate success or failure 
  unsigned int returnCode = choreo.run();

  // return code of zero (0) means success
  if (returnCode == 0) {

    // print the raw output if requested.
    if (showOutput) {
      while (choreo.available()) {
        char c = choreo.read();
        Serial.print(c);
      }
    }
  } else {
    // return code of anything other than zero means failure  
    // read and display any error messages
    while (choreo.available()) {
      char c = choreo.read();
      Serial.print(c);
    }
    Serial.println("");
  }

  choreo.close();

  return returnCode;
}

/*
 * getSensorValue is a function to simulates reading the value of a sensor
 */
int getSensorValue() {
  Serial.print("Reading sensor value...");
  int value = analogRead(A0);
  Serial.println("OK");
  return value;
}

Comments

Created by

Temboo_OFFICIAL

Are you Temboo_OFFICIAL? Claim this project and add it to your profile.

This is mine

Posted by

Arduino scuola
Arduino_Scuola
  • 32 projects
  • 97 followers

Published on

July 8, 2016

Members who respect this project

Default

and 4 others

See similar projects
you might like

Similar projects you might like

Update Your Facebook Status with Temboo

by Temboo_OFFICIAL

  • 595 views
  • 1 comment
  • 0 respects

Simple, Robust M2M Messaging Via Amazon with Temboo

by Temboo_OFFICIAL

  • 487 views
  • 0 comments
  • 0 respects

Send an SMS using Twilio with Temboo

Project tutorial by Temboo_OFFICIAL

  • 1,479 views
  • 1 comment
  • 1 respect

Send An Email From Your Gmail Account With Temboo

Project tutorial by Temboo_OFFICIAL

  • 1,388 views
  • 0 comments
  • 2 respects

Post Data to a Google Spreadsheet with Temboo

Project tutorial by Temboo_OFFICIAL

  • 1,223 views
  • 0 comments
  • 7 respects

Upload a File to your Dropbox Account with Temboo

Project tutorial by Temboo_OFFICIAL

  • 1,044 views
  • 0 comments
  • 1 respect
Add projectSign up / Login