Arduino_Scuola
Published © GPL3+

Read And Update Databases With Temboo

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.

IntermediateFull instructions provided1 hour4,588
Read And Update Databases With Temboo

Things used in this project

Hardware components

Arduino Yun
Arduino Yun
×1

Software apps and online services

Arduino IDE
Arduino IDE

Story

Read more

Code

Code snippet #1

Arduino
/*
  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;
}

Credits

Temboo_OFFICIAL

Posted by Arduino_Scuola

Comments

Related channels and tags
  • Arduino
  • Internet of Things
  • Internet of Things