Go Down

Topic: Problem with uploading to MySQL (Read 1 time) previous topic - next topic

andersonsamalex

Hi all,

I've been attempting to create a weather station to gather as much weather data as possible.
The code functions exactly as intended, for around 24 hours. After which the arduino simply stops sending data to the database. I have absolutely no idea why.

Initially I thought that the network may have been disconnecting after a period of time, and I added a function to detect whether or not it was connected. This function runs on every loop and if there is no connection then one is established. I thought this fixed the problem, until I found out this morning that again, the arduino just stopped uploading.

Code below:

Code: [Select]
#include "arduino_secrets.h"
#include "thingProperties.h"
#include <Arduino_MKRENV.h>
/*
  Repeating Wifi Web Client

  This sketch connects to a a web server and makes a request
  using a WiFi equipped Arduino board.

  created 23 April 2012
  modified 31 May 2012
  by Tom Igoe
  modified 13 Jan 2014
  by Federico Vanzati

  http://www.arduino.cc/en/Tutorial/WifiWebClientRepeating
  This code is in the public domain.
*/

#include <SPI.h>
#include <WiFiNINA.h>

int status = WL_IDLE_STATUS;

// Initialize the Wifi client library
WiFiClient client;

void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(9600);

  // check for the WiFi module:
  if (WiFi.status() == WL_NO_MODULE) {
    Serial.println("Communication with WiFi module failed!");
    // don't continue
    while (true);
  }

  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
    Serial.println("Please upgrade the firmware");
  }

  // attempt to connect to Wifi network:
  WiFi_Connect();
  // you're connected now, so print out the status:
  printWifiStatus();
  //Start arduino cloud.
  //ArduinoCloud.begin(ArduinoIoTPreferredConnection);
  //Begin ENV shield.
  if (!ENV.begin()) {
    Serial.println("Failed to initialize MKR ENV shield!");
    while (1);
  }
}

void loop() {
  //ArduinoCloud.update();
  WiFi_Connect();
  // if there's incoming data from the net connection.
  // send it out the serial port.  This is for debugging
  // purposes only:
  while (client.available()) {
    char c = client.read();
    Serial.write(c);
  }
    Serial.println("Start reading sensors.");
    relativeHumidity = int(ENV.readHumidity());
    Serial.println(relativeHumidity);
    lightIntensity = int(ENV.readIlluminance());
    Serial.println(lightIntensity);
    airPressure = int(ENV.readPressure(MILLIBAR));
    Serial.println(airPressure);
    temperature = int(ENV.readTemperature());
    Serial.println(temperature);
    UV_Index = int(ENV.readUVIndex());
    Serial.println(UV_Index);
    if (digitalRead(2) == LOW) {
      rain = 1;
    } else if (digitalRead(2) == HIGH) {
      rain = 0;
    }
    Serial.println(rain);
    Serial.println("Sensor data read.");
    Serial.println("Start http request.");
    httpRequest();
    Serial.println("Stop http request");
    delay(600000);
}

// this method makes a HTTP connection to the server:
void httpRequest() {
  // close any connection before send a new request.
  // This will free the socket on the Nina module
  client.stop();

  // if there's a successful connection:
  if (client.connect(server, 80)) {
    //Serial.println("connecting...");
    // send the HTTP PUT request:
    client.print("GET /weather_data.php?temp=");
    client.print(temperature);
    client.print("&pressure=");
    client.print(airPressure);
    client.print("&humidity=");
    client.print(relativeHumidity);
    client.print("&illumination=");
    client.print(lightIntensity);
    client.print("&UV_Index=");
    client.print(UV_Index);
    client.print("&rain=");
    client.print(rain);
    client.print(" ");
    client.println("HTTP/1.1");
    client.println("Host: 192.168.0.29");
    client.println("User-Agent: ArduinoWiFi/1.1");
    client.println("Connection: close");
    client.println();
  } else {
    // if you couldn't make a connection:
    Serial.println("connection failed");
    httpRequest();
  }
}

void WiFi_Connect(){
  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(SSID);
    // Connect to WPA/WPA2 network. Change this line if using open or WEP network:
    status = WiFi.begin(SSID, PASS);

    // wait 10 seconds for connection:
    delay(10000);
  }
}

void printWifiStatus() {
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print your board's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}


Code: [Select]
#include <WiFiNINA.h>
#include <SPI.h>
//#include <ArduinoIoTCloud.h>
//#include <Arduino_ConnectionHandler.h>
 
const char THING_ID[] = "c942a18f-7696-4431-952c-76eaea3214ea";

const char SSID[]     = SECRET_SSID;    // Network SSID (name)
const char PASS[]     = SECRET_PASS;    // Network password (use for WPA, or use as key for WEP)
IPAddress server(192,168,0,29);         // Server IP Address

int airPressure;
int lightIntensity;
int relativeHumidity;
int rain;
int temperature;
int UV_Index;

void initProperties(){
  //ArduinoCloud.setThingId(THING_ID);
  //ArduinoCloud.addProperty(airPressure, READ, 60 * SECONDS, NULL);
  //ArduinoCloud.addProperty(lightIntensity, READ, 60 * SECONDS, NULL);
  //ArduinoCloud.addProperty(relativeHumidity, READ, 60 * SECONDS, NULL);
  //ArduinoCloud.addProperty(rain, READ, 60 * SECONDS, NULL);
  //ArduinoCloud.addProperty(temperature, READ, 60 * SECONDS, NULL);
}
//WiFiConnectionHandler ArduinoIoTPreferredConnection(SSID, PASS);


And just for reference here is my PHP code:
Code: [Select]
<?php
  
//initialise connection variables.
  
$host "***";
  
$port = ***;
  
$socket "";
  
$username "***";
  
$password "***";
  
$dbname "weather";

  
//connect to database.
  
$conn = new mysqli($host$username$password$dbname$port$socket);
  
//check connection.
  
if ($conn->connect_error){
    die(
"Connection Failed: " $conn->connect_error);
  }

  
//define variables and set to empty values.
  
$temp $pressure $humidity $illumination $UV $rain "";
  
//use post method to input values and assign them.
  
if ($_SERVER["REQUEST_METHOD"] == "GET"){
    
$temp test_input($_GET["temp"], $conn);
    
$pressure test_input($_GET["pressure"], $conn);
    
$humidity test_input($_GET["humidity"], $conn);
    
$illumination test_input($_GET["illumination"], $conn);
    
$UV test_input($GET["UV_index"], $conn);
    
$rain test_input($_GET["rain"], $conn);
  }

  
//screen input data function.
  
function test_input($data_){
    
$data htmlspecialchars($data_ENT_QUOTES);
    return 
$data;
  }

  
//Insert data into table. Prepare statements to prevent SQL injection.
  
$stmt "INSERT INTO weather_data (temp, pressure, humidity, illumination, UV_Index, rain) VALUES (?,?,?,?,?,?)";
  if (!
$result $conn->prepare($stmt)){
    die(
'Query Failed: (' .$conn->errno ') ' $conn->error);
  }
  if (!
$result->bind_param("iiiiii"$temp$pressure$humidity$illumination$UV$rain)){
    die(
'Binding parameters failed: (' .$result->errno ')' $result->error);
  }
  if (!
$result->execute()){
    die(
'Execute failed: (' $result->errno ') ' $result->error);
  }
  
$result->close();
?>


Also while I'm at it, I attempted to use the LowPower.sleep(600000) in place of the delay(600000). However the arduino never seemed to wake up.

wildbill

What does your serial data show once the Arduino has stopped sending?

andersonsamalex

Apologies for the late response, I left the weather station plugged into my computer to wait for the error. So that it would show up on serial.

The serial output right now is showing "connection failed" so it seems that there is some kind of error in the client.connect command. There was no serial output showing that the arduino ever disconnected from the internet.

Go Up