ESP8266 Community Forum

- Wed Mar 28, 2018 7:47 am #74958 Hi

Ive got 4 Wemos D1 mini working with DS18B20 temp sensors and they report back to a MYSQL database on my raspberry pi.

This has been successfully reporting back every 15 minutes since October last year (2017) but I wanted to include OTA as they are hard to get to places and a webpage for each sensor.

I now have 1 wemos d1 mini working on the old script but the 3 that I have tried to re-configure have the following issues

Originally the NTP Time has stopped and instead of only reporting and uploading the temperature to the MYSQL Database on 00, 15, 30 and 45 minutes past. It then started to jus update every 15 minutes from when the powered on after I changed the code around. This has now stopped working altogether and reports ip address for ntp as 0.0.0.0 and nothing is updated to the MYSQL database

The webserver works and reports the temperature successfully

Code: Select all// Including the ESP8266 WiFi library

#include <TimeLib.h>

#include <WiFiUdp.h>
#include <WiFiClient.h>

#include <ESP8266WiFi.h>

#include <OneWire.h>

#include <DallasTemperature.h>

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

// Replace with your network details
// WiFi
char ssid[] = "Wifi"; // SSID NAME
char password[] = "Wifi-Password"; // SSID PASSWORD


// NTP Servers:
static const char ntpServerName[] = "0.uk.pool.ntp.org";
const int timeZone = 1;
WiFiUDP Udp;
unsigned int localPort = 8888;

time_t getNtpTime();
void sendNTPpacket(IPAddress &address);

WiFiClient client;
MySQL_Connection conn((Client *)&client);

// Data wire is plugged into pin D1 on the ESP8266 12-E - GPIO 5

#define ONE_WIRE_BUS 2

// Setup a oneWire instance to communicate with any OneWire devices (not just Maxim/Dallas temperature ICs)

OneWire oneWire(ONE_WIRE_BUS);
DallasTemperature sensors(&oneWire);
 

// Pass our oneWire reference to Dallas Temperature.

DallasTemperature DS18B20(&oneWire);

char temperatureCString[7];

char temperatureFString[7];

// MySQL
IPAddress server_addr(192,168,0,15);
char user[] = "sensors";
char pass[] = "SensorsPassword";
char INSERT_DATA[] = "UPDATE sensors.thermostat SET TheTemp = %s, TheUpdated = NOW() WHERE TheLocation = 'Top Bedroom'";
char INSERT_DATA_HISTORY[] = "INSERT INTO sensors.history (HisDate, HisThermostat, HisTemp) VALUES (NOW(),'3',%s)";
char query[128];
char query_HISTORY[128];
char temperature[10];
char temperature_HISTORY[10];
 

// Web Server on port 80

WiFiServer server(80);

 

// only runs once on boot

void setup() {

  // Initializing serial port for debugging purposes

  Serial.begin(115200);

  delay(10);

 //.30 = Room1 .31 = Room2 .32 = Top Bedroom .33 = Outside
  IPAddress ip(192, 168, 0, 32);
  IPAddress subnet(255, 255, 255, 0);
  IPAddress gt(192, 168, 0, 1);
  WiFi.config(ip, gt, subnet);
  WiFi.hostname("Top-Room-Temp-Sensor");

  DS18B20.begin(); // IC Default 9 bit. If you have troubles consider upping it 12. Ups the delay giving the IC more time to process the temperature measurement

 

  // Connecting to WiFi network

  Serial.println();

  Serial.print("Connecting to ");

  Serial.println(ssid);

 

  WiFi.begin(ssid, password);
  Udp.begin(localPort);
 

  while (WiFi.status() != WL_CONNECTED) {

    delay(500);

    Serial.print(".");

  }

  Serial.println("");

  Serial.println("WiFi connected");

 

  // Starting the web server

  server.begin();

  Serial.println("Web server running. Waiting for the ESP IP...");

  delay(10000);

 

  // Printing the ESP IP address

  Serial.println(WiFi.localIP());

  Udp.begin(localPort);
 setSyncProvider(getNtpTime);
 setSyncInterval(300);

}

void saveTempData() {
if (((minute() == 0) || (minute() ==15) || (minute() ==30) || (minute() ==45)) && (second() == 0)) {
//if (((now.minute() == 0) || (now.minute() ==15) || (now.minute() ==30) || (now.minute() ==45)) && (now.second() == 0)) {
 // if ( minute() == 00 && second() == 00 ) {
 sensors.requestTemperatures();
 MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
 Serial.print("Temp : ");
 Serial.println(sensors.getTempCByIndex(0));
  Serial.print("Query : ");
 dtostrf(sensors.getTempCByIndex(0), 2, 2, temperature);
 dtostrf(sensors.getTempCByIndex(0), 2, 2, temperature_HISTORY);
 sprintf(query, INSERT_DATA,temperature);
 sprintf(query_HISTORY, INSERT_DATA_HISTORY,temperature_HISTORY);
 cur_mem->execute(query);
 cur_mem->execute(query_HISTORY);
 Serial.println(query);
 Serial.println(query_HISTORY);
 delete cur_mem;
 Serial.println("Data stored!");
 }
 }



void getTemperature() {

  float tempC;

  float tempF;

  do {

    DS18B20.requestTemperatures();

    tempC = DS18B20.getTempCByIndex(0);

    dtostrf(tempC, 2, 2, temperatureCString);

    tempF = DS18B20.getTempFByIndex(0);

    dtostrf(tempF, 3, 2, temperatureFString);

    delay(100);

  } while (tempC == 85.0 || tempC == (-127.0));

}

 
time_t prevDisplay = 0; // when the digital clock was displayed
// runs over and over again

void loop() {
  saveTempData();

  // Listenning for new clients

  WiFiClient client = server.available();

 

  if (client) {

    Serial.println("New client");

    // bolean to locate when the http request ends

    boolean blank_line = true;

    while (client.connected()) {

      if (client.available()) {

        char c = client.read();

       

        if (c == '\n' && blank_line) {

            getTemperature();

            client.println("HTTP/1.1 200 OK");

            client.println("Content-Type: text/html");

            client.println("Connection: close");

            client.println();

            // your actual web page that displays temperature

            client.println("<!DOCTYPE HTML>");

            client.println("<html>");

            client.println("<head></head><body><h1>Nytram Top Bedroom - Temperature</h1><h3>Temperature in Celsius: ");

            client.println(temperatureCString);

            client.println("*C</h3><h3>Temperature in Fahrenheit: ");

            client.println(temperatureFString);

            client.println("*F</h3></body></html>");

            break;

        }

        if (c == '\n') {

          // when starts reading a new line

          blank_line = true;

        }

        else if (c != '\r') {

          // when finds a character on the current line

          blank_line = false;

        }

      }

    }

    // closing the client connection

    delay(1);

    client.stop();

    Serial.println("Client disconnected.");

  }

} 


/*-------- NTP code ----------*/

const int NTP_PACKET_SIZE = 48; // NTP time is in the first 48 bytes of message
byte packetBuffer[NTP_PACKET_SIZE]; //buffer to hold incoming & outgoing packets

time_t getNtpTime()
{
 IPAddress ntpServerIP; // NTP server's ip address

 while (Udp.parsePacket() > 0) ; // discard any previously received packets
 Serial.println("Transmit NTP Request");
 // get a random server from the pool
 WiFi.hostByName(ntpServerName, ntpServerIP);
 Serial.print(ntpServerName);
 Serial.print(": ");
 Serial.println(ntpServerIP);
 sendNTPpacket(ntpServerIP);
 uint32_t beginWait = millis();
 while (millis() - beginWait < 1500) {
 int size = Udp.parsePacket();
 if (size >= NTP_PACKET_SIZE) {
 Serial.println("Receive NTP Response");
 Udp.read(packetBuffer, NTP_PACKET_SIZE); // read packet into the buffer
 unsigned long secsSince1900;
 // convert four bytes starting at location 40 to a long integer
 secsSince1900 = (unsigned long)packetBuffer[40] << 24;
 secsSince1900 |= (unsigned long)packetBuffer[41] << 16;
 secsSince1900 |= (unsigned long)packetBuffer[42] << 8;
 secsSince1900 |= (unsigned long)packetBuffer[43];
 return secsSince1900 - 2208988800UL + timeZone * SECS_PER_HOUR;
 }
 }
 Serial.println("No NTP Response :-(");
 return 0; // return 0 if unable to get the time
}

// send an NTP request to the time server at the given address
void sendNTPpacket(IPAddress &address)
{
 // set all bytes in the buffer to 0
 memset(packetBuffer, 0, NTP_PACKET_SIZE);
 // Initialize values needed to form NTP request
 // (see URL above for details on the packets)
 packetBuffer[0] = 0b11100011; // LI, Version, Mode
 packetBuffer[1] = 0; // Stratum, or type of clock
 packetBuffer[2] = 6; // Polling Interval
 packetBuffer[3] = 0xEC; // Peer Clock Precision
 // 8 bytes of zero for Root Delay & Root Dispersion
 packetBuffer[12] = 49;
 packetBuffer[13] = 0x4E;
 packetBuffer[14] = 49;
 packetBuffer[15] = 52;
 // all NTP fields have been given values, now
 // you can send a packet requesting a timestamp:
 //Udp.beginPacket(address, 123); //NTP requests are to port 123
 Udp.write(packetBuffer, NTP_PACKET_SIZE);
 Udp.endPacket();
}


Please could someone point me in the right direction ?

Thanks in advance

Martyn