Read And Update Databases With Temboo

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}