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.

Jun 15, 2016

•

6398 views

•

6 respects

iot

internet of things

Devices & Components

Arduino Yun

Software & Tools

Arduino IDE

Project description

Code

Code snippet #1

arduino

1/*
2  SendDataToMySQL
3
4  Demonstrates storing and reading values  in a MySQL database using
5  the Temboo Arduino Yun SDK. This example will periodically
6  update a MySQL instance with dummy sensor values from the Yun, and
7  then read  those values back from the database.
8
9  This example code is in the public  domain.
10*/
11
12#include <Bridge.h>
13#include <Temboo.h>
14#include "TembooAccount.h"  // contains Temboo account information
15
16/*** SUBSTITUTE YOUR VALUES BELOW:  ***/
17
18// Note that for additional security and reusability, you could
19//  use #define statements to specify these values in a .h file.
20
21const String  DATABASE_SERVER = "server-address-of-your-MySQL-instance";
22const String DATABASE_NAME  = "your-MySQL-database-name";
23const String DATABASE_USERNAME = "your-MySQL-user-name";
24const  String DATABASE_PASSWORD = "your-MySQL-user-password";
25
26// the name of the  table to write to and read from
27const String DATABASE_TABLE_NAME = "readings";
28
29
30//  how often to run the Choreo (in milliseconds)
31const unsigned long RUN_INTERVAL_MILLIS  = 60000; 
32
33// the last time we ran the Choreos (initialized to 60 seconds  ago,
34// so the Choreos are run immediately when we start up)
35unsigned long  lastRun = (unsigned long)-60000;
36
37// a flag to indicate if we were able to  create the table
38// (or that it already existed)
39bool haveTable = false;
40
41void  setup() {
42  
43  // for debugging, wait until a serial console is connected
44  Serial.begin(9600);
45  delay(4000);
46  while(!Serial);
47
48  Serial.print("Initializing  the bridge...");
49  Bridge.begin();
50  Serial.println("OK");
51
52  //  make sure the table we need exists or create it if it doesn't
53  // (see the comments  in the createTable function for details)
54  unsigned int result = createTable();
55
56  // set the 'haveTable' flag to true on success,
57  // or false if there was  a problem creating the table
58  haveTable = (result == 0);
59}
60
61void loop()
62{
63  // get the number of milliseconds this sketch has been running
64  unsigned long  now = millis();
65
66  // run again if it's been RUN_INTERVAL_MILLIS milliseconds  since we last ran
67  if (now - lastRun >= RUN_INTERVAL_MILLIS) {
68
69    //  remember 'now' as the last time we ran the choreo
70    lastRun = now;
71
72    // do the database write/read only if we have the table.
73    if (haveTable)  { 
74
75      // get the value we want to add to our table
76      int sensorValue  = getSensorValue();
77
78      // add a record containing the cpu time and sensor  value
79      appendRow(now, sensorValue);
80
81      // Read the values just  written to verify that they really
82      // did get sent to the database. (Not  necessary in a real
83      // sketch, but done here to demonstrate retrieving  data.)
84      retrieveRow(now);
85
86    } else {
87      Serial.println("Table  creation failed, not appending row.");
88    }
89 }
90}
91
92/* 
93 * createTable  is a function that executes a SQL statement to
94 * create a table with the correct  columns needed for this sketch.
95 */
96unsigned int createTable() {
97  Serial.print("Creating  table '" + DATABASE_TABLE_NAME + "' (if needed)...");
98
99  // We need a table  with columns to contain the cpu time and the sensor value.
100  // cpu time is an  'unsigned long' which is 4 bytes on the Yun, 
101  // so an INT UNSIGNED in MySQL  will hold any possible value.
102  // Our sensor values are 'int' values on the  Yun, 
103  // so an INT in MySQL will hold all possible values.
104
105  // Create  a String containing the SQL statement to create the table.
106  String sql = "CREATE  TABLE IF NOT EXISTS " + DATABASE_TABLE_NAME + " (cpu_time INT UNSIGNED, sensor_value  INT);";
107
108  // Send the SQL to Temboo so it can forward it to your database.
109  unsigned int result = executeSQLCommand(sql, false);
110
111  // If there was  an error, the executeSQLCommand function will have 
112  // printed it to the Serial  console.  Otherwise, print OK.
113  if (result == 0) {
114    Serial.println("OK");
115  }
116
117  return result;
118}
119
120/* 
121 * appendRow is a function that executes  a SQL statement to
122 * insert a new row of data into the database.
123 * cpuTime  is the value to be inserted into the cpu_time column
124 * value is the value wot  be inserted into the sensor_value column
125 */
126unsigned int appendRow(unsigned  long cpuTime, int value) {
127  Serial.print("Inserting row " + String(cpuTime)  + ", " + String(value) + "...");
128
129  // Create a String containing the  SQL statement to insert a row.
130  String sql = "INSERT " + DATABASE_TABLE_NAME  + " VALUES (" + cpuTime + "," + value + ");";
131
132  // Send the SQL to  Temboo so it can forward it to your database.
133  unsigned int result = executeSQLCommand(sql,  false);
134
135  // If there was an error, the executeSQLCommand function will have  
136  // printed it to the Serial console.  Otherwise, print OK.
137  if (result  == 0) {
138    Serial.println("OK");
139  }
140  return result;
141}
142
143/*
144  * retrieveRow is a function that executes a SQL statement to
145 * retrieve a row  of data from the database.
146 * cpuTime is the cpu_time value of the row to be  retrieved.
147 * (note that it must exactly match the value stored.)
148 */
149unsigned  int retrieveRow(unsigned long cpuTime) {
150  Serial.println("Retrieving row with  time " + String(cpuTime) + "...");
151
152  // Create a String containing the  SQL statement to retrieve a row.
153  String sql = "SELECT * FROM " + DATABASE_TABLE_NAME  + " WHERE cpu_time = '" + cpuTime + "';";
154
155  // Send the SQL to Temboo  so it can forward it to your database.
156  // In this case, we want to print the  raw output we get from 
157  // Temboo.  This is just to demonstrate that the data  really did
158  // get written to the database.
159  unsigned int result = executeSQLCommand(sql,  true);
160
161  return result;
162}
163
164/*
165 * executeSQLCommand is a function  for sending SQL statements to
166 * Temboo for execution on your database.
167 *  sql is a String containing the SQL statement to be executed.
168 * showOutput is  a boolean flag which if set to 'true' will cause
169 * the raw Choreo results to  be printed to the Serial console.
170 */
171unsigned int executeSQLCommand(String  sql, bool showOutput) {
172
173  // Create a TembooChoreo object
174  TembooChoreo  choreo;
175
176  // Initialize the Choreo object.
177  choreo.begin();
178
179  //  set Temboo account credentials
180  choreo.setAccountName(TEMBOO_ACCOUNT);
181  choreo.setAppKeyName(TEMBOO_APP_KEY_NAME);
182  choreo.setAppKey(TEMBOO_APP_KEY);
183
184  // specify the Choreo to be run
185  choreo.setChoreo("/Library/MySQL/RunCommand");
186
187  // add inputs specifying  the RDS endpoint address
188  // and the database name within that RDS instance
189  choreo.addInput("Server", DATABASE_SERVER);
190  choreo.addInput("DatabaseName",  DATABASE_NAME);
191  
192  // add inputs for the MySQL user credentials
193  choreo.addInput("Username",  DATABASE_USERNAME);
194  choreo.addInput("Password", DATABASE_PASSWORD);
195
196  // add the SQL command to be executed
197  choreo.addInput("SQL", sql);
198
199  // run the Choreo and wait for the results
200  // The return code (returnCode)  will indicate success or failure 
201  unsigned int returnCode = choreo.run();
202
203  // return code of zero (0) means success
204  if (returnCode == 0) {
205
206    //  print the raw output if requested.
207    if (showOutput) {
208      while (choreo.available())  {
209        char c = choreo.read();
210        Serial.print(c);
211      }
212    }
213  } else {
214    // return code of anything other than zero means failure  
215    // read and display any error messages
216    while (choreo.available())  {
217      char c = choreo.read();
218      Serial.print(c);
219    }
220    Serial.println("");
221  }
222
223  choreo.close();
224
225  return returnCode;
226}
227
228/*
229 * getSensorValue  is a function to simulates reading the value of a sensor
230 */
231int getSensorValue()  {
232  Serial.print("Reading sensor value...");
233  int value = analogRead(A0);
234  Serial.println("OK");
235  return value;
236}

/*
  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

Only logged in users can leave comments

Arduino_Scuola

0 Followers

•

0 Projects

Intro

Trademarks & CopyrightsWhistleblowingDigital Services ActTerms of ServicePrivacy PolicySecurityCookie Settings

Report content