JSON-formatted Sensor Data Moved Over Wifi to mySQL Database —
In Part 11 of this series, temperature and photocell data were sent as JSON-formatted data from a YellowJacket Arduino data server (YJ56) to a YellowJacket Arduino client (YJ57) using the local wifi network.
In Part 12, sensor data from one YellowJacket was sent via wifi to a local network Mac-based Apache/php server, and php submitted the data to a mySQL data base table for storage.
In this Part 13, examples from Parts 11 and 12 will be merged. The JSON-formatted data from the data server (YJ56) will be transmitted to the YJ57 client where the JSON data will be parsed and sent over the local wifi network to the Mac-based Apache/php server, and php will submit the data to a mySQL data base table.
Hardware Setup
- YellowJacket YJ56 Arduino with MRF24WB0MA Wifi backpack, Sensor Shield, and SD Card Shield with Real-time clock.
- YellowJacket YJ57 Arduino with MRF24WB0MA Wifi backpack.
- Mac running Apache, php, and mySQL.
- Local wifi network.
Brief Detour: One Fix for blanks in dtostrf output to php
In Part 12, the Arduino code “Send2Values_04.ino” used dtostrf(temperature, 0, 2, data1) to output a non-padded char array “data1” with 2 digits after the decimal point. dtostrf appears to use the necessary number of digits before the decimal point to hold the integer portion of temperature when width=0 is used in the function. Using any width greater than the total width that is required (e.g. 8,2 or -8,2 when only 5,2 is required), either left or right justified, caused failure of data to load into the mySQL data table. By using 0,2, there were no padding spaces either before or after the number stored into char array data1.
Another method to fix the space-padded char array problem is that by contributor “unwind” who shared a deblank function in Ref: http://stackoverflow.com/questions/13084236/function-to-remove-spaces-from-string-char-array-in-c
char * deblank(char *str)
{
char *out = str, *put = str;
for(; *str != '\0'; ++str)
{
if(*str != ' ')
*put++ = *str;
}
*put = '\0';
return out;
}
which is implemented as:
double df;
df = temperatureF;
char data1[10];
dtostrf(dF, 8, 2, data1); // converts float to char[] array as: data1=>>>79.31\0, where ">" = space padding and data1 is \0 terminated
// Remove blanks from data1 after dtostrf
deblank(data1);
result: data1=79.31\0, with no leading or trailing blanks,
passes data1 to php with no errors loading the data into mySQL.
Just add the deblank function before “void setup ()” in the Arduino sketch and “deblank(your_char_array)” to remove any blanks in “your_char_array”. The \0 termination is added by deblank at the end of the number.
YJ56 Sensor Shield WiServer
The SimpleJsonServer_5Sensors_V3 WiServer sketch (on YJ56) collects sensor data and submits it as a response to a GetRequest from a client, e.g., Safari browser or WiClient.
Sensors on Sensor Shield include: time (RTC on SD Card Shield), tempF (pin A0), light (pin A1), button (pin D3 INT1), and led (pin 5).
Pin D3 INT1 would automatically turn on when the board was powered. To fix this, pin D3 was initally held high through 10K resistor to 3V3 (for safety since MRF24 is 3V3), and when the push button was pushed, the pin was grounded through the push button, turning on the interrupt. INT1 was set to trigger on FALLING. Arduino has internal pullup resistors that can be turned on so that external resistors are not required.
The YJ56 Arduino does not have a real-time clock, so time was set using the RTC chip on the SD Card Shield which was backpacked to the YJ56 and Sensor Shield.
/* SimpleJsonServer_5Sensors_V3
* A simple sketch that uses WiServer to serve a web page
* containing JSON formatted data
*
* 1) GETrequest from Client triggers this server
* 2) Sensors are read
* 3) Sensor data formatted into JSON string
* 4) HTTP response including JSON string is sent over Wifi
*
* Sensor Data:
* tSensor "YYYY-MM-DD hh:mm:ss"
* tempF VARCHAR XXX.YY
* light VARCHAR XXX
* button int 0 off, 1 on (button sensor, set on with button push)
* led1, int 0 off, 1 on (led state off or on)
*
* 10.0.1.56/json GETrequest for JSON formatted data
* 10.0.1.56/sensorOn GETrequest to Set Sensor TRUE
* 10.0.1.56/sensorOff GETrequest to clear Sensor FALSE
* 10.0.1.56/ledOn GETrequest to Set LED HIGH
* 10.0.1.56/ledOff GETrequest to clear LED LOW
*
* button sensor sets interrupt INT1 on pin D3. Pin D3 is held high through 10K resistor to 3V3.
* Pin D3 sensor_status is set false on initalization of sketch. Int pins can also use an
* internal interrupt "pinMode(buttonPin, INPUT_PULLUP);"
* On button push, pin D3 is grounded, which sets INT1 sensor_status to true.
*
* RTC I2C pins A4 and A5 need to be connected between YJ and SD Card Shield.
*/
#include <WiServerIO.h>
// Date and time functions using a DS1307 RTC connected via I2C and Wire lib
#include <Wire.h>
#include <RTClib.h>
RTC_DS1307 rtc;
DateTime now;
#define WIRELESS_MODE_INFRA 1
#define WIRELESS_MODE_ADHOC 2
// Wireless configuration parameters ----------------------------------------
unsigned char local_ip[] = {10, 0, 1, 56}; // IP address of WiShield
unsigned char gateway_ip[] = {10, 0, 1, 1}; // router or gateway IP address
unsigned char subnet_mask[] = {255, 255, 255, 0}; // subnet mask for the local network
const char ssid[] PROGMEM = {"Network"}; // max 32 bytes
const char* myhost = "WiServer";
char* myhost_ip = "10.0.1.56";
unsigned char security_type = 3; // 0 - open; 1 - WEP; 2 - WPA; 3 - WPA2
// WPA/WPA2 passphrase
const char security_passphrase[] PROGMEM = {"Network_Passcode"}; // max 64 characters
// WEP 128-bit keys
// sample HEX keys
const uint8_t wep_keys[] PROGMEM = { 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09, 0x0a, 0x0b, 0x0c, 0x0d, // Key 0
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // Key 1
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // Key 2
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 // Key 3
};
// setup the wireless mode
// infrastructure - connect to AP
// adhoc - connect to another WiFi device
unsigned char wireless_mode = WIRELESS_MODE_INFRA;
//unsigned char wireless_mode = WIRELESS_MODE_ADHOC;
unsigned char ssid_len;
unsigned char security_passphrase_len;
//---------------------------------------------------------------------------
/****************************** Data Logger **********************************
*
* Assign the data sensors to the Arduino analog pins
*
*/
//#define aref_voltage 3.3// tie 3.3V to ARef
#define aref_voltage 5.0 // default internal 5V to ARef
boolean sensor_status = false;
int photocellPin = 1; // the cell and 10K pulldown are connected to a1
int photocellReading; // the analog reading from the analog resistor divider
int temperaturePin = 0; //the analog pin connected to the TMP36's Vout (sense) pin
int temperatureReading; // the analog reading from the sensor
int buttonPin = 3; // digital pin 3 int1; int0 pin D2 is used by YJ MRF24
// D2 MUST have internal or external 10K pullup resistor to either 5V0 or 3V3
int buttonReading; // the digital reading from the sensor
int ledPin = 5; //the digital pin for output to LED
int ledReading; // the digital reading from the sensor
char inCMD[24] = "/write_json_TTLSL1.php?"; // php file to process the json POSTrequest
char tStamp[20]; // time stamp as char array
char timeStamp[20]; // time stamp as char array
char tF[10]; // sensor 1 data as char array
char light[10]; // sensor 2 data as char array
char button[5]; // sensor 3 data as char array
char led[5]; // sensor 4 data as char array
float tempF; // float sensor 1 data
// int tF; // int sensor 1 data if desired
/*************************** Sketch functions ************************************/
/*************************** mySensor ************************************/
// loop Function that assembles data to send to the server
void mySensor() {
// Read sensors data
char tS[20] = "0000-00-00 00:00:00"; // Arduino has no clock, need an RTC on Sensor board
photocellReading = analogRead(photocellPin);
temperatureReading = analogRead(temperaturePin);
//buttonReading = digitalRead(buttonPin);
buttonReading = sensor_status;
ledReading = digitalRead(ledPin);
// converting that reading to voltage, which is based off the reference voltage
float voltage = temperatureReading * aref_voltage / 1024;
// calculate temperature
float temperatureC = (voltage - 0.5) * 100 ; //converting from 10 mv per degree wit 500 mV offset
// //to degrees ((voltage - 500mV) times 100)
//Serial.print(temperatureC); Serial.println(" degrees C");
// convert C to Fahrenheit
tempF = (temperatureC * 9 / 5) + 32;
/*
// Print the sensor values to the Serial Monitor
Serial.println(F(">>> Read sensors "));
Serial.print(tS); Serial.println(F(" TIMESTAMP"));
Serial.print(tempF); Serial.println(F(" degrees F"));
Serial.print(photocellReading); Serial.println(F(" Photocell"));
Serial.print(buttonReading); Serial.println(F(" Button"));
Serial.print(sensor_status); Serial.println(F(" sensor_status"));
Serial.print(ledReading); Serial.println(F(" LED1"));
*/
// convert int to char uses sprintf
// convert float to char uses dtostrf
// note: <dtostrf(tempF, 6, 2, data);> will add a leading blank for the - sign
// which fails to load the data into MySQL table. Use <dtostrf(tempF, 0, 2, data);>
// which will left justify the data xyz.dd, and will load into MySQL
dtostrf(tempF, 0, 1, tF); // converts float to char[] array (data)
//Serial.print(data1); Serial.println(F(" data1 char dtostrf degrees F"));
// tF = (int)tempF; // if int value is desired rather than float
// sprintf(v1, "%i", tF); // converts int to char[] array (data)
//Serial.print(tF); Serial.println(F(" int degrees F"));
sprintf(light, "%i", photocellReading); // converts int to char[] array (data)
//Serial.print(v2); Serial.println(F(" data char int degrees Photocell"));
sprintf(button, "%i", buttonReading); // converts int to char[] array (data)
sprintf(led, "%i", ledReading); // converts int to char[] array (data)
sprintf(tStamp, "%s", tS); // converts char[] array to char[] array (data)
sprintf(timeStamp, "%s", tS); // default 0-0-0 0:0:0 if rtc is not available
timestampNow();
}
void sensorOn() {
sensor_status = true;
buttonReading = sensor_status;
//Serial.println(F(" >> Sensor Status: Activated >> "));
// for testing, set the LED status to sensor_status
//ledOn();
}
void sensorOff() {
sensor_status = false;
buttonReading = sensor_status;
//Serial.println(F(" >> Sensor Status: Cleared >> "));
}
void ledOn() {
digitalWrite(ledPin, HIGH);
//Serial.println(F(" >> LED Status: On >> "));
}
void ledOff() {
digitalWrite(ledPin, LOW);
//Serial.println(F(" >> LED Status: Off >> "));
}
// Convert DateTime to char array
void timestampNow() {
//DateTime now = rtc.now();
//desired format: 0000-00-00 00:00:00
int y = (now.year());
int m = (now.month());
int d = (now.day());
int h = (now.hour());
int n = (now.minute());
int s = (now.second());
sprintf(timeStamp, "%04d%s%02d%s%02d%s%02d%s%02d%s%02d", y, "-", m, "-", d, " ", h, ":", n, ":", s );
}
// print DateTime to Serial Monitor
void printNow() {
//DateTime now = rtc.now();
//char tS[20] = "0000-00-00 00:00:00";
Serial.print(now.year(), DEC);
Serial.print('-');
Serial.print(now.month(), DEC);
Serial.print('-');
Serial.print(now.day(), DEC);
Serial.print(' ');
Serial.print(now.hour(), DEC);
Serial.print(':');
Serial.print(now.minute(), DEC);
Serial.print(':');
Serial.print(now.second(), DEC);
Serial.println();
}
/*************************** sendMyPage function *********************************/
// This is our page serving function that generates web pages
boolean sendMyPage(char* URL) {
// Check if the requested URL matches "/"
if (strcmp(URL, "/") == 0) {
WiServer.print("");
WiServer.print(F(" >> GETrequest server_ip/json.......return JSON formatted data "));
WiServer.print(F(" >> GETrequest server_ip/sensorOn...set sensor ON "));
WiServer.print(F(" >> GETrequest server_ip/sensorOff..set sensor OFF "));
WiServer.print(F(" >> GETrequest server_ip/ledOn......set led ON "));
WiServer.print(F(" >> GETrequest server_ip/ledOff.....set led OFF "));
WiServer.print("");
// URL was recognized
return true;
}
// Check if the requested URL matches "/json"
if (strcmp(URL, "/json") == 0) {
// Use WiServer's print and println functions to write out the page content
WiServer.println("<html>");
// for Arduino to Arduino over wifi, use <json> and </json> tags as delimiters for parsing
// print ... on one contiguous line
WiServer.print("<json>");
// test example json data array
//char json[] = "{\"val1\":\"12.34\",\"val2\":\"56\"}"; // example JSON
//WiServer.print(json);
// obtain the sensor values
mySensor();
Serial.println("");
Serial.print("timeStamp: ");
Serial.println(timeStamp);
// place sensor data into JSON format
WiServer.print("{\"tSensor\":\"");
WiServer.print(timeStamp);
WiServer.print("\",\"tempF\":\"");
WiServer.print(tF);
WiServer.print("\",\"light\":\"");
WiServer.print(light);
WiServer.print("\",\"button\":\"");
WiServer.print(button);
WiServer.print("\",\"led\":\"");
WiServer.print(led);
WiServer.print("\"}");
WiServer.println("</json>");
WiServer.println("</html>");
// URL was recognized
return true;
}
// Check if the requested URL matches "/sensorOn"
if (strcmp(URL, "/sensorOn") == 0) {
sensorOn(); // set sensor status; normally set by button push
WiServer.print("<html>");
WiServer.print(F(" >> GETrequest server_ip/sensorOn: Sensor set ON >> "));
WiServer.print("</html>");
// URL was recognized
return true;
}
// Check if the requested URL matches "/sensorOff"
if (strcmp(URL, "/sensorOff") == 0) {
sensorOff(); // probably not good to clear status until some action is taken
WiServer.print("("<html>");
WiServer.print(F(" >> GETrequest server_ip/sensorOff: Sensor set OFF >> "));
WiServer.print("</html>");
// URL was recognized
return true;
}
// Check if the requested URL matches "/ledOn"
if (strcmp(URL, "/ledOn") == 0) {
ledOn(); // set LED On by URL request
WiServer.print("<html>");
WiServer.print(F(" >> GETrequest server_ip/ledOn: LED set ON >> "));
WiServer.print("</html>");
// URL was recognized
return true;
}
// Check if the requested URL matches "/ledOff"
if (strcmp(URL, "/ledOff") == 0) {
ledOff(); // probably not good to clear status until some action is taken
WiServer.print("<html>");
WiServer.print(F(" >> GETrequest server_ip/ledOff: LED set OFF >> "));
WiServer.print("</html>");
// URL was recognized
return true;
}
WiServer.print("<html>");
WiServer.print(F(" >> URL not found >> "));
WiServer.print("</html>");
// URL not found
return false;
}
/*************************** setup function *********************************/
void setup() {
// If you want to set the aref to something other than 5v
//analogReference(EXTERNAL); // enable EXTERNAL if using 3.3 AREF, ie, SD Card Shield and UNO
analogReference(DEFAULT); // DEFAULT for no external AREF, ie, SD Card Shield and YellowJacket
// Enable Serial output
Serial.begin(115200);
Wire.begin();
rtc.begin();
if (! rtc.isrunning()) {
Serial.println("RTC is NOT running!");
// following line sets the RTC to the date & time this sketch was compiled
//rtc.adjust(DateTime(F(__DATE__), F(__TIME__)));
}
// Attach interrupt INT1 to pin 3 held normally high through 10K resistor to 3V3. Button grounds pin 3.
pinMode(buttonPin, INPUT_PULLUP); // sets the interupt pin 3 as input with the internal pullup
attachInterrupt(digitalPinToInterrupt(buttonPin), sensorOn, FALLING); // buttonPin senses immediate state
pinMode(temperaturePin, INPUT); // sets the analog pin 0 as input
pinMode(photocellPin, INPUT); // sets the analog pin 1 as input
pinMode(ledPin, OUTPUT); // sets the digital pin 5 as output
delay(100); // wait for power to stabilize
buttonReading = 0;
sensorOff();
ledOff();
// /*
// Print the timestamp
now = rtc.now();
timestampNow();
Serial.println("");
Serial.print("timeStamp: ");
Serial.println(timeStamp);
Serial.println("");
Serial.println(">> SimpleJsonServer_5Sensors_V3 sketch ");
Serial.print(">> WiServer.server_task initialized on ");
Serial.print(myhost);
Serial.print(", ip: ");
Serial.println(myhost_ip);
// */
// Initialize WiServer and have it use the sendMyPage function to serve pages
WiServer.init(sendMyPage);
// Verbose=true >> ask WiServer to generate log messages to Serial Monitor
WiServer.enableVerboseMode(true);
Serial.println("");
Serial.println(F(">> Listening for connections..."));
}
/*************************** loop function *********************************/
void loop() {
now = rtc.now();
// Run WiServer
WiServer.server_task();
delay(10);
}
/*************************** end loop function *********************************/
The YJ56 server “SimpleJsonServer_5Sensors_V3” sketch (above), transmits a JSON formatted data packet to the client
(e.g. {“tSensor”:”2016-03-26 14:55:29″,”tempF”:”79.1″,”light”:”281″,”button”:”1″,”led”:”1″} )
each time it receives a GetRequest “http://10.0.1.56/json”, either through wifi or ethernet.
On the Mac, mySQL requires a table to log that 5-sensor data. A new Arduino data base table, “TTLSL1” was added with 7 fields: 1) id, INT 11, unsigned, primary key, auto_increment; 2) time, TIMESTAMP, default CURRENT_TIMESTAMP (mySQL timestamp); 3) tSensor, VARCHAR 20 (YJ56 timestamp of sensor data); 4) tempF, VARCHAR 10; 5) light, VARCHAR 10; 6) button, INT 1; and, 7) led, INT 1. The field names match those in the JSON data packet.
Also on the Mac, a php file is required to send the JSON data to mySQL.
<?php
// File "write_json_TTLSL1.php"
// written as part of Arduino sketch "SimpleJsonWiClient_5Sensors_V3"
// to insert five values into MySql table using POSTrequest
// Prepare variables for database connection
$dbhost = 'localhost:3306';
$dbuser = "arduino"; // enter database username, I used "arduino" in step 2.2
$dbpass = "arduinotest"; // enter database password, I used "arduinotest" in step 2.2
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db(‘Arduino’);
// For data submitted as JSON POSTrequest
//read the json input stream as 'php://input' from client
$jsondata = file_get_contents('php://input');
// echo $jsondata;
//convert json object to php associative array
$data = json_decode($jsondata, true);
// echo $data;
// get the data value details
$v1 = $data['tSensor'];
$v2 = $data['tempF'];
$v3 = $data['light'];
$v4 = $data['button'];
$v5 = $data['led'];
// Prepare the SQL statement
$sql = "INSERT INTO Arduino.TTLSL1 (tSensor,tempF,light,button,led) VALUES ('$v1', '$v2', '$v3', '$v4', '$v5')";
//echo $sql;
// Execute SQL statement
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not enter data: ' . mysql_error());
}
echo "\nEntered 5 data values successfully into Table TTLSL1\n";
mysql_close($conn);
?>
The YJ57 Client sketch, “SimpleJsonWiClient_5Sensors_V3”, sends a GetRequest to YJ56 server, obtains the HTML – JSON data packet, parses the JSON data fields, and transmits them to php on a Mac for storage in a mySQL data table.
/* SimpleJsonWiClient_5Sensors_V3
*
* March 17, 2016
*
* A simple sketch that uses MRF24 WiClient to read web page from MRF24 WiServer
* The WiServer web page content includes a JSON formatted data string between
* <json> json data </json> tags
* Parse out the json data as a char array and send that to ArduinoJson to parse into
* individual field names and values.
*
* Write the JSON data to Mac15 MySQL Arduino DB, table TTLSL1.
* Sensors: time, wtempF (pin A0), light (pin A1), button (pin D3 INT1), and led (pin 5).
*
* Ref: ArduinoJson Library
*
* Compiled with WiShield_V3 library
*
* Uses apps-conf.h "#define APP_WISERVER"
*
* Connecting Two YellowJackets for Data Transfer
*
* Hardware:
* YJ Arduino board (YJ56) at ip=10.0.1.56
* YJ Arduino board (YJ57) at ip=10.0.1.57
* Local network gateway ip 10.0.1.1
*
* Example: WiClient YJ57 requests web page from WiServer YJ56.
* a) Load sketch SimpleJsonServer_5Sensors_V3 into YJ56. Sketch uses
* “WiServer.init(sendMyPage);” to transmit web page with JSON content by Wifi.
* b) Load sketch SimpleJsonWiClient_5Sensors_V3 into YJ57. Sketch uses
* GetRequest to Host: “10.0.1.56”, URL=“/json“
* c) Result: WiServer web page is requested every 30 seconds by WiClient
* d) Returned request stream is parsed for <json> . . . </json> string
* e) <json> string is parsed by ArduinoJSON to extract individual field_name,field_data
* f) json data are transmitted to Mac php to MySQL table.
*
* This sketch
*
* loop function calls GETrequest every 30 seconds
* GETrequest sent to WiServer returns JSON data string within HTTP "data" array
*
* extractJSON function extracts the JSON formatted string from the HTTP "data" array
*
* extractJSON function calls jsonParser()
* jsonParser() function parses the field titles and data into individual variables
* (reads 5 sensor values as JSON formatted data within HTML stream from WiServer)
*
* jsonParser() function calls doJSON() function
* doJSON() function Serial.prints the parsed data elements
*
* doJSON() function calls write_MySQL() funtion
*
* write_MySQL() function assembles a POSTrequest to create a php request
* setContentType(1); is used for JSON POSTs
* POSTrequest sendMyRequest(request_ip, 80, myhost, inCMD, createBody);
* createBody calls createBody function
* createbody function uses WiServer.print to create the body for the POST
* setContentType(0); is reset for TEXT GETs
*
* sends the php request to Apache
* the php request is submitted to mysql
* data elements are written to mysql table
*
* jsonArray = {"tSensor":"2016-03-26 14:55:29","tempF":"79.1","light":"281","button":"1","led":"1"}
*
* // A WiClient or Browser request that gets web page from server
* // or controls sensors on server YJ56
* // >> GETrequest getWebPage(server_ip, 80, myhost_ip, "/"); // return Commands Help webpage
* // >> GETrequest server_ip/json.......return JSON formatted data in HTTP stream
* // >> GETrequest server_ip/sensorOn...set sensor ON
* // >> GETrequest server_ip/sensorOff..set sensor OFF
* // >> GETrequest server_ip/ledOn......set led ON
* // >> GETrequest server_ip/ledOff.....set led OFF
*
*/
#include <WiServerIO.h>
#include "SPI.h" // Arduino SPI
#include <ArduinoJson.h>
/****************************** Configure Wifi **********************************/
#define WIRELESS_MODE_INFRA 1
#define WIRELESS_MODE_ADHOC 2
unsigned char local_ip[] = {10, 0, 1, 57}; // IP address of WiShield
unsigned char gateway_ip[] = {10, 0, 1, 1}; // router or gateway IP address
unsigned char subnet_mask[] = {255, 255, 255, 0}; // subnet mask for the local network
const char ssid[] PROGMEM = {"Network"}; // max 32 bytes
unsigned char security_type = 3; // 0 - open; 1 - WEP; 2 - WPA; 3 - WPA2
// WPA/WPA2 passphrase
const char security_passphrase[] PROGMEM = {"Network_Passcode"}; // max 64 characters
// WEP 128-bit keys
// sample HEX keys
const uint8_t wep_keys[] PROGMEM = { 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09, 0x0a, 0x0b, 0x0c, 0x0d, // Key 0
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // Key 1
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // Key 2
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 // Key 3
};
// setup the wireless mode
// WIRELESS_MODE_INFRA - connect to AP
// WIRELESS_MODE_ADHOC - connect to another WiFi device
unsigned char wireless_mode = WIRELESS_MODE_INFRA;
unsigned char ssid_len;
unsigned char security_passphrase_len;
/****************************** End Configure Wifi **********************************/
/****************************** Define global variables **********************************/
//#define aref_voltage 3.3// we tie 3.3V to ARef and measure it with a multimeter!
#define aref_voltage 5.0 // default internal 5V to ARef
float tempF;
//int tF;
/****************************** Setup JSON **********************************/
//
// define the jsonArray that will hold the json data string for data transfer
// example: {"tSensor":"2016-03-26 14:55:29","tempF":"79.1","light":"281","button":"1","led":"1"}
// ArduinoJson will be used to parse this array to extract the data elements
const int js = 100; // set the jsonArray max dimension size (adjust as needed for data string)
char jsonArray[js]; // adjust the size of this character array to hold incoming JSON data string
char jsonAcopy[js]; // copy of jsonArray[] sinse jsonArray gets clobbered
int jsonLength = 0; // length of json string from extractJson function
// Required: define the json data from the server. This could be a struct.
const char id1[] = "tSensor"; // field names used in MySQL table
char cv1[20]; // holds the returned WiServer DateTime, which may be 0 if server has no clock.
const char id2[] = "tempF";
double v2; // holds the returned val2
const char id3[] = "light";
int v3;
const char id4[] = "button";
int v4;
const char id5[] = "led";
int v5;
// create char arrays to hold the json int or float data elements as char
// char cv1[20]; // sensor 1 data as char array defined above
char cv2[10]; // sensor 2 data as char array
char cv3[5]; // sensor 3 data as char array
char cv4[5]; // sensor 4 data as char array
char cv5[5]; // sensor 5 data as char array
// The jsonArray is used for POSTing the data to php
/*************************** GETrequest function ****************************
* The GETrequest function is run every 30 seconds by call from loop
*/
uint8 server_ip[] = {10, 0, 1, 56}; // IP Address for WiServer YJ56
// Server for GETrequest for JSON formatted data
const char* myServer = "WiServer";
char* myhost_ip = "10.0.1.56";
// A request that gets web page from server
// >> GETrequest getWebPage(server_ip, 80, myhost_ip, "/"); // return Commands Help webpage
// >> GETrequest server_ip/json.......return JSON formatted data in HTTP stream
// >> GETrequest server_ip/sensorOn...set sensor ON
// >> GETrequest server_ip/sensorOff..set sensor OFF
// >> GETrequest server_ip/ledOn......set led ON
// >> GETrequest server_ip/ledOff.....set led OFF
GETrequest getWebPage(server_ip, 80, myhost_ip, "/json"); // request JSON formatted data
/*************************** end GETrequest setup ****************************/
/*************************** POSTrequest setup ****************************
* This POST defines the request for sending JSON data to MySQL database
*/
uint8_t request_ip[] = {10, 0, 1, 11}; // IP Address for requested server Mac
char* myhost = "Mac_localhost";
char inCMD[24] = "/write_json_TTLSL1.php?"; // php file to process the json POSTrequest
POSTrequest sendMyRequest(request_ip, 80, myhost, inCMD, createBody);
/*************************** end POSTrequest setup ****************************/
/*************************** POSTrequest createBody function ******************/
// POSTrequest setBodyFunc(bodyFunction body);
void createBody()
{
WiServer.print (jsonAcopy); // just print the json formatted string
//
/* below is code to build the JSON string from the individual parsed values
// place sensor data into JSON format using individual variable names and values
WiServer.print("{\"tSensor\":\"");
WiServer.print(cv1);
WiServer.print("\",\"tempF\":\"");
WiServer.print(cv2);
WiServer.print("\",\"light\":\"");
WiServer.print(cv3);
WiServer.print("\",\"button\":\"");
WiServer.print(cv4);
WiServer.print("\",\"led\":\"");
WiServer.print(cv5);
WiServer.println("\"}");
//
*/
}
/*************************** end createBody function *********************************/
/***************************** End Global variables **********************************/
/*************************** sketch setup() ****************************/
void setup() {
// Enable Serial output
Serial.begin(115200);
// If you want to set the aref to something other than 5v
// analogReference(EXTERNAL); // for 3.3V ARef
analogReference(DEFAULT);
/*************************** Setup Wifi ****************************/
Serial.println (" ");
Serial.println (F("=== Initializing WiClient to display WiServer web page on Serial Monitor ==="));
// Initialize WiServer (we'll pass NULL for the page serving function since we don't need to serve web pages)
WiServer.init(NULL);
// Ask WiServer to generate log messages (optional)
WiServer.enableVerboseMode(true);
// Have the printData function called when data is returned by the server
getWebPage.setReturnFunc(printJsonData);
// Have the createBody function called to POST data to MySQL
sendMyRequest.setReturnFunc(printData);
sendMyRequest.setBodyFunc(createBody);
Serial.println (" ");
Serial.println (F("=== WiClient is running... ==="));
Serial.println (F(">>> Request response on 30 second intervals... "));
Serial.println (" ");
}
/*************************** end sketch setup() ****************************/
/*************************** sketch loop() ****************************/
// Time (in millis) when the data should be retrieved
unsigned long updateTime = 0;
unsigned long waitTime = 1000UL * 60UL; // * UL unsigned long number format used to avoid overflow
void loop() {
// Check if it's time to get an update
if (millis() >= updateTime) {
getWebPage.submit();
// Get another update 30 seconds from now
updateTime += waitTime;
}
// Run WiServer
WiServer.server_task();
delay(10);
}
/*************************** end sketch loop() ****************************/
/*************************** function routines ************************/
// Must extract JSON string from HTTP stream
//
/********************************* extractJSON ************************/
//
// Function that extracts JSON data from incoming data stream from the server
void extractJSON(char* data, int len) {
// Note that the data stream is not null-terminated,
// may be broken up into smaller packets, and
// includes the HTTP header.
// This code expects data stream to be short and in one array.
// This could be a bad assumption, since the json array might be split
/******* Parse out JSON section from data Stream ***************/
// Use care with data array. It is the returned stream from the server.
// Server needs to wrap the JSON data array within . . tags
// These tags are used to parse out the json data array from the incoming http data stream
const char str1[] = "<json>";
const char str2[] = "</json>";
char* ptr1;
char* ptr2;
char* ptr3;
int jsLength;
//Serial.println(len);
// make sure there is content in data array
if (len > 0) // if length of data array is > 0, then proceed
{
ptr1 = strstr (data, str1); // get index location of string "<" within data array
if (ptr1 != NULL) // if substring is found, continue
{
ptr2 = strstr(ptr1, "{"); // move the pointer to the first brace
//Serial.println(ptr2); // print from brace to end of data array
if (ptr2 != NULL) // leading brace found, so continue
{
ptr3 = strstr(data, str2); // move ptr3 to "<" in
if (ptr3 != NULL) // "<" found, so now extract the json string { // calc chars between index2 and index3 jsLength = ptr3 - ptr2; Serial.println(""); Serial.print(F(" > JSON Length = ")); Serial.println(jsLength);
if (jsLength < js) { // make sure // length is OK, continue memcpy(jsonArray, ptr2, jsLength); // copy json string to jsonArray memcpy(jsonAcopy, ptr2, jsLength); // copy jsonArray[jsLength] = '\0'; // for insurance, add NULL to end of jsonArray jsonAcopy[jsLength] = '\0'; // for insurance, add NULL to end of jsonAcopy //size_t sizeof_jA = strlen(jsonArray); // gives array char count jsonLength = jsLength; // copy string length to global value // Serial.print(F("jsonArray=")); Serial.println(jsonArray); // will print: {"val... xx"} // if extract is successful, report out //Serial.println (""); Serial.println (F(" > JSON Array was Extracted from GETrequest stream"));
Serial.println ("");
// Parse jsonArray with ArduinoJson
//
jsonParser();
} else {
//Serial.println ("");
Serial.println (F(" *** Error extracting JSON Array from GETrequest stream"));
Serial.println (F(" ** json length > jsonArray dimension"));
Serial.println ("");
}
// while (ptr2 != NULL) {}; // hang here to test above code
} // end if (ptr3 != NULL)
} // end if (ptr2 != NULL)
} else { // end if (ptr1 != NULL)
Serial.println ("");
Serial.println (F(" *** Error JSON Array Not Found in GETrequest stream"));
Serial.println ("");
}
} // end if (len > 0)
} // end function
/***************************** End extractJSON function**********************/
/*************************** jsonParser function ****************************/
// ref: ArduinoJson Library
//
// jsonParser function
//
void jsonParser() {
StaticJsonBuffer jsonBuffer; // limit size so as to not exceed available memory
JsonObject& root = jsonBuffer.parseObject(jsonArray);
// Serial.println(" *** root.printTo(Serial)");
// root.printTo(Serial);
// Serial.println(" *** end root.printTo(Serial)");
// Test if parsing succeeds.
if (!root.success()) {
Serial.println(" *** ArduinoJson Parse failed");
return;
}
// Fetch values.
// place json data into char arrays
// json variables defined as globals for use elsewhere in sketch
const char* s1 = root[id1]; // tSensor datetime requires cast from char* to char[]
sprintf(cv1, "%s", s1); // tSensor datetime
const char* s2 = root[id2]; // cast from char* to char[]
sprintf(cv2, "%s", s2); // tempF
const char* s3 = root[id3]; // cast from char* to char[]
sprintf(cv3, "%s", s3); // light
const char* s4 = root[id4]; // cast from char* to char[]
sprintf(cv4, "%s", s4); // button
const char* s5 = root[id5]; // cast from char* to char[]
sprintf(cv5, "%s", s5); // led
// place the json data into char, int, or float as desired
// sprintf(cv1, "%s", s1); // tSensor datetime already exists
v2 = root[id2]; // tempF xx.xx
v3 = root[id3]; // light int
v4 = root[id4]; // button 0,1
v5 = root[id5]; // led 0,1
// Now use the json array data as desired
doJSON();
}
//
/*************************** End jsonParser function ****************************/
/****************************** doJSON function ************************/
//
// Function that uses the JSON data
void doJSON() {
// do something with the JSON data
Serial.println (F(" > JSON Array was parsed for values"));
// Print json int, float, or char values.
Serial.print(F(" tSensor: ")); Serial.println(cv1);
Serial.print(F(" tempF: ")); Serial.println(v2, 2);
Serial.print(F(" light: ")); Serial.println(v3);
Serial.print(F(" button: ")); Serial.println(v4);
Serial.print(F(" led: ")); Serial.println(v5);
/*
// Print json as char values.
Serial.print(F(" tSensor: ")); Serial.println(cv1);
Serial.print(F(" tempF: ")); Serial.println(cv2);
Serial.print(F(" light: ")); Serial.println(cv3);
Serial.print(F(" button: ")); Serial.println(cv4);
Serial.print(F(" led: ")); Serial.println(cv5);
*/
write_MySQL(); // write the json data through Wifi to MySQL table via php
}
/****************************** end doJSON function ************************/
/****************************** write_MySQL function ************************/
void write_MySQL() {
// Submit the POSTrequest to a php server at request_ip
// Create an instance of POSTrequest with the JSON text in the body
// choose text or json data; sets value in WiServerIO.h, .cpp
setContentType(1); // set JSON 0=Text, 1=JSON
sendMyRequest.submit();
Serial.println("");
Serial.println(F(" ^^ POST JSON to MySQL submitted"));
Serial.println("");
// choose text or json data; sets value in WiServerIO.h, .cpp
setContentType(0); // reset TEXT 0=Text, 1=JSON
}
/****************************** end write_MySQL function ************************/
/****************************** printData ************************/
//
// Function that prints data from the server
void printJsonData(char* data, int len) {
// Extract the JSON data as char array jsonArray from the server data stream
// if the server data stream has content
if (len > 0) {
extractJSON(data, len); // extract JSON data from incoming data stream
}
// Print the data returned by the server
// Note that the data is not null-terminated, may be broken up into smaller packets, and
// includes the HTTP header.
Serial.println ("");
Serial.println (F("<< Print GET Json Data HTTP Stream from Server >>"));
while (len-- > 0) {
Serial.print(*(data++));
}
Serial.println (F("<< End Print GET Json Data HTTP Stream from Server >>")); // terminate messages with CR
Serial.println ("");
}
void printData(char* data, int len) {
// Print the data returned by the server
// Note that the data is not null-terminated, may be broken up into smaller packets, and
// includes the HTTP header.
Serial.println ("");
Serial.println (F("<< Print POSTrequest HTTP Stream from Apache Server >>"));
while (len-- > 0) {
Serial.print(*(data++));
}
Serial.println (F("<< End Print POSTrequest >>")); // terminate messages with CR
Serial.println ("");
}
/********************************* End printData ************************/
Output from the YJ57 Client sketch displays on Serial Monitor as:
=== Initializing WiClient to display WiServer web page on Serial Monitor ===
=== WiClient is running... ===
>>> Request response on 30 second intervals...
Connected to 10.0.1.56
TX 65 bytes
RX 0 bytes from 10.0.1.56
RX 136 bytes from 10.0.1.56
> JSON Length = 85
> JSON Array was Extracted from GETrequest stream
> JSON Array was parsed for values
tSensor: 2016-03-26 13:31:40
tempF: 80.90
light: 217
button: 0
led: 0
^^ POST JSON to MySQL submitted
<< Print GET Json Data HTTP Stream from Server >>
HTTP/1.0 200 OK
<html>
<json>{"tSensor":"2016-03-26 13:31:40","tempF":"80.9","light":"217","button":"0","led":"0"}</json>
<html>
<< End Print GET Json Data HTTP Stream from Server >>
Ended connection with 10.0.1.56
<< Print GET Json Data HTTP Stream from Server >>
<< End Print GET Json Data HTTP Stream from Server >>
Connected to Mac_localhost
TX 244 bytes
RX 0 bytes from Mac_localhost
RX 241 bytes from Mac_localhost
<< Print POSTrequest HTTP Stream from Apache Server >>
HTTP/1.1 200 OK
Date: Wed, 26 Mar 2016 17:34:21 GMT
Server: Apache/2.4.18 (Unix) PHP/5.5.34
X-Powered-By: PHP/5.5.34
Content-Length: 54
Connection: close
Content-Type: text/html
Entered 5 data values successfully into Table TTLSL1
<< End Print POSTrequest >>
Ended connection with Mac_localhost
<< Print POSTrequest HTTP Stream from Apache Server >>
<< End Print POSTrequest >>
(Mar 26, 2016)
