Closed MikeAUT closed 2 years ago
I will look at this more, but first an unsolicited comment. Why so many variables? You’re effectively storing your data from your database in memory. My first inclination is you’re going to have memory issues. But I could be mistaken. ;) Regardless, I strongly recommend you spend some time thinking about reducing your memory usage. For example, is it possible that you can retrieve only the data you need when you need it and discard it once it has been processed, displayed, etc?
On May 2, 2020, at 4:12 AM, MikeAUT notifications@github.com wrote:
Hi veryone, my NodeMCU has two major tasks at the moment:
Communicate with MySQL Database Fetch weather from OpenWeatherMap. Every task on its own works well. But if I execute both tasks the Output on the Monitor looks strange. If everything's alright, it should look like this:
15:56:26.844 -> current_weather_main0 Clouds 15:56:26.844 -> current_weather_description0 Ein paar Wolken But it only looks this way if getWeather() is executed PRIOR MySQLHandling(). When getWeather() is executed and after that MySQLHandling() then i get something like that:
15:50:08.745 -> current_weather_main0 X_TIMESTAMP(date) 15:50:08.745 -> current_weather_description0 ? or
19:16:38.404 -> current_weather_main0 r�calendar�allday�allday? 19:16:38.404 -> current_weather_description0 llday? All the other text outputs (ie daily_weather_description) are looking normal. In the faulty outputs you can see fragments of the SQL Query or the respond. As I said, all the other outputs are OK. The cause is devinitely the call of MySQLHandling() in line 186. If I comment this out, everything is normal. Just as I comment out everything in MySQLHandling() after line 251. So as soon cur_mem->execute(query) gets executed faulty outputs occur.
Here is the code, unrelevant parts are cut out.
include
WiFiClient weatherclient; WiFiClient mysqlclient;
include
include
MySQL_Connection conn(&mysqlclient); MySQL_Cursor* cursor;
include
TimeChangeRule myDST = {"UTC", Last, Sun, Mar, 3, +120}; TimeChangeRule mySTD = {"UTC", Last, Sun, Oct, 3, +60}; Timezone myTZ(myDST, mySTD);
include
include
WiFiUDP Udp;
include
PulseTimer pulseCheckWiFi; PulseTimer pulseGetWeather; PulseTimer pulseOutput; PulseTimer pulseMySQL;
// ----------- // NTP static const char ntpServerName[] = "pool.ntp.org"; const int timeZoneNtp = 0; time_t getNtpTime(); void sendNTPpacket(IPAddress &address);
// ----------- // Common Variables char ssid[] = "XXXXXXXXXXXXXXXX"; char pass[] = "XXXXXXXXXXXXXXXX"; byte mac[6]; long signalStrength; unsigned int localPort = 8888;
// MySQL IPAddress server_addr(192, 168, 1, 5); char user[] = "XXXXXXXXXXXXXXXX"; char password[] = "XXXXXXXXXXXXXXXX"; char default_db[] = "arduino"; const int maxRows = 5;
// Queries char selectQuery[] = "SELECT id,UNIX_TIMESTAMP(date),text,allday,icon FROM calendar ORDER BY date, id LIMIT %d;"; char insertQuery[] = "INSERT INTO calendar (date,text,icon) VALUES (FROM_UNIXTIME(%ld),'%s',%d);"; char deleteQuery[] = "DELETE FROM calendar WHERE id=%d;";
unsigned int id[maxRows]; unsigned long date[maxRows]; String text[maxRows]; bool allday[maxRows]; unsigned int icon[maxRows]; unsigned int upcoming[maxRows];
// -------------------------------------- // Weather variables byte forceRun = 3; // Number of runs with short delay
const char apiowm[] = "api.openweathermap.org";
const byte maxWeatherEntries = 3;
float lat; float lon; const char* timezone; // "Europe/Vienna" // Current long current_dt; // 1586763444 long current_sunrise; // 1586751436 long current_sunset; // 1586800256 float current_temp; // 14.74 float current_feels_like; // 12.34 int current_pressure; // 1013 int current_humidity; // 62 float current_dew_point; // 7.54 int current_clouds; // 100 float current_uvi; // 4.62 int current_visibility; // 10000 float current_wind_speed; // 2.6 float current_wind_gust; int current_wind_deg; // 270 float current_rain_1h; // 0.11 float current_snow_1h; // 0.11
int current_weather_id[maxWeatherEntries]; // 500 const char current_weather_main[maxWeatherEntries]; // "Rain" const char current_weather_description[maxWeatherEntries]; // "Leichter Regen" const char* current_weather_icon[maxWeatherEntries]; // "10d" // Hourly long hourly_dt[48]; // 1586761200 float hourly_temp[48]; // 14.74 float hourly_feels_like[48]; // 12.07 int hourly_pressure[48]; // 1013 int hourly_humidity[48]; // 62 float hourly_dew_point[48]; // 7.54 int hourly_clouds[48]; // 100 int hourly_visibility[48]; float hourly_wind_speed[48]; // 2.99 float hourly_wind_gust[48]; int hourly_wind_deg[48]; // 280 float hourly_rain_1h[48]; // 0.11 float hourly_snow_1h[48];
int hourly_weather_id[48][maxWeatherEntries]; // 500 const char hourly_weather_main[48][maxWeatherEntries]; // "Rain" const char hourly_weather_description[48][maxWeatherEntries]; // "Leichter Regen" const char* hourly_weather_icon[48][maxWeatherEntries]; // "10d" // Daily long daily_dt[8]; // 1586775600 long daily_sunrise[8]; // 1586751436 long daily_sunset[8]; // 1586800256
float daily_temp_day[8]; // 18.49 float daily_temp_min[8]; // 4.83 float daily_temp_max[8]; // 18.49 float daily_temp_night[8]; // 4.83 float daily_temp_eve[8]; // 10.11 float daily_temp_morn[8]; // 14.74
float daily_feels_like_day[8]; // 13.94 float daily_feels_like_night[8]; // 1.48 float daily_feels_like_eve[8]; // 7.09 float daily_feels_like_morn[8]; // 12.77
int daily_pressure[8]; // 1011 int daily_humidity[8]; // 50 float daily_dew_point[8]; // 7.88 float daily_wind_speed[8]; // 5.79 int daily_wind_deg[8]; // 275 float daily_wind_gust[8]; int daily_visibility[8]; int daily_clouds[8]; // 100 float daily_rain[8]; // 9.48 float daily_snow[8]; // 0.79 float daily_uvi[8]; // 4.62
int daily_weather_id[8][maxWeatherEntries]; // 600 const char daily_weather_main[8][maxWeatherEntries]; // "Snow" const char daily_weather_description[8][maxWeatherEntries]; // "Mäßiger Schnee" const char* daily_weather_icon[8][maxWeatherEntries]; // "13d" // Array description: Hour/day, Entry-Nr
void setup() { //Initialize serial and wait for port to open: Serial.begin(500000); WiFi.mode(WIFI_STA); WiFi.hostname("NodeMaster"); connectWiFi(); printWiFiStatus();
// Get NTP Serial.print("IP number assigned by DHCP is "); Serial.println(WiFi.localIP()); Serial.println("Starting UDP"); Udp.begin(localPort); Serial.print("Local port: "); Serial.println(Udp.localPort()); Serial.println("waiting for sync"); setSyncProvider(getNtpTime); setSyncInterval(72000); // 20 Stunden
pinMode(LED_BUILTIN, OUTPUT); digitalWrite(LED_BUILTIN, HIGH); }
void loop() { pulseCheckWiFi.set(10000); pulseGetWeather.set(600000); pulseMySQL.set(15000); pulseOutput.set(30000);
if (timeStatus() != 2) { setSyncProvider(getNtpTime); setSyncInterval(10); } else { setSyncInterval(72000); // 20 Stunden }
if (conn.connected()) { if (pulseMySQL.pulse && timeStatus() == 2) { MySQLHandling(); } } else { conn.close(); Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password, default_db)) { delay(500); Serial.println("Successful reconnect to SQL!"); } else { Serial.println("Cannot reconnect to SQL! Drat."); } }
if (pulseCheckWiFi.pulse) { checkWiFi(); } signalStrength = WiFi.RSSI();
if (forceRun != 0) { pulseGetWeather.set(30000); }
if ((pulseGetWeather.pulse) && (WiFi.status() == WL_CONNECTED)) { if (forceRun > 0) { forceRun --; } getWeather(); }
if (pulseOutput.pulse) { Serial.println("#############"); serialOutputWeather(); serialOutputMySQL(); }
// SHOW UPCOMING IDs unsigned long dt0 = now() + (86400L * 3); int x = 0; for (int i = 0; i < maxRows; i++) { if (date[i] <= dt0) { upcoming[x] = id[i]; } else { upcoming[x] = 0; } x++; }
digitalWrite(LED_BUILTIN, HIGH); }
void MySQLHandling() { Serial.println("Start MySQL Handling"); // ----------------- MySQL for (int i = 0 ; i < maxRows; i++) { id[i] = 0; date[i] = 0; text[i] = ""; allday[i] = false; icon[i] = 0; }
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); char query[150]; sprintf(query, selectQuery, maxRows); digitalWrite(LED_BUILTIN, LOW); cur_mem->execute(query); cur_mem->get_columns();
// Read the rows and print them row_values *row = NULL; int index = 0; do { row = cur_mem->get_next_row(); if (row != NULL) { id[index] = atoi(row->values[0]); date[index] = atoi(row->values[1]); text[index] = row->values[2]; allday[index] = atoi(row->values[3]); icon[index] = atoi(row->values[4]); index++; } } while (row != NULL);
// DELETE EVENT IF LIES IN PAST for (int i = 0; i < maxRows; i++) { if ((date[i] != 0) && (date[i] < now())) { char query2[50]; sprintf(query2, deleteQuery, id[i]); cur_mem->execute(query2); Serial.printf("Termin ID %d geloescht\n", id[i]); } } delete cur_mem; Serial.println("MySQL Handling finished"); }
void getWeather() { digitalWrite(LED_BUILTIN, LOW); Serial.println("Start getWeather"); Serial.println(F("Connecting..."));
// Connect to HTTP server weatherclient.setTimeout(10000); if (!weatherclient.connect(apiowm, 80)) { Serial.println(F("Connection failed")); return; }
Serial.println(F("Connected!"));
// Send HTTP request weatherclient.println(F("GET /data/2.5/onecall?lat=xxxxxxxxxxxx&lon=xxxxxxxxxxxxx&appid=XXXXXXXXXXXXXXXX&mode=json&units=metric&lang=de HTTP/1.0")); weatherclient.println(F("Host: api.openweathermap.org")); weatherclient.println(F("Connection: close")); if (weatherclient.println() == 0) { Serial.println(F("Failed to send request")); return; }
// Check HTTP status char httpstatus[32] = {0}; weatherclient.readBytesUntil('\r', httpstatus, sizeof(httpstatus)); if (strcmp(httpstatus, "HTTP/1.1 200 OK") != 0) { Serial.print(F("Unexpected response: ")); Serial.println(httpstatus); return; }
// Skip HTTP headers char endOfHeaders[] = "\r\n\r\n"; if (!weatherclient.find(endOfHeaders)) { Serial.println(F("Invalid response")); return; }
// Allocate the JSON document // Use arduinojson.org/v6/assistant to compute the capacity. DynamicJsonDocument doc(30000);
// Parse JSON object DeserializationError error = deserializeJson(doc, weatherclient); if (error) { Serial.print(F("deserializeJson() failed: ")); Serial.println(error.c_str()); return; }
// Disconnect weatherclient.stop();
lat = doc["lat"]; lon = doc["lon"]; timezone = doc["timezone"]; // "Europe/Vienna" // Current JsonObject current = doc["current"]; current_dt = current["dt"]; // 1586763444 current_sunrise = current["sunrise"]; // 1586751436 current_sunset = current["sunset"]; // 1586800256 current_temp = current["temp"]; // 14.74 current_feels_like = current["feels_like"]; // 12.34 current_pressure = current["pressure"]; // 1013 current_humidity = current["humidity"]; // 62 current_dew_point = current["dew_point"]; // 7.54 current_clouds = current["clouds"]; // 100 current_uvi = current["uvi"]; // 4.62 current_visibility = current["visibility"]; // 10000 current_wind_speed = current["wind_speed"]; // 2.6 current_wind_gust = current["wind_gust"]; current_wind_deg = current["wind_deg"]; // 270 current_rain_1h = current["rain"]["1h"]; // 0.11 current_snow_1h = current["snow"]["1h"]; // 0.11 for (int i = 0; i < maxWeatherEntries; i++) { JsonObject current_weather_0 = current["weather"][i]; current_weather_id[i] = current_weather_0["id"]; // 500 current_weather_main[i] = current_weather_0["main"]; // "Rain" current_weather_description[i] = current_weather_0["description"]; // "Leichter Regen" current_weather_icon[i] = current_weather_0["icon"]; // "10d" }
// Hourly JsonArray hourly = doc["hourly"]; for (int i = 0 ; i < 48; i++) { JsonObject hourly_0 = hourly[i]; hourly_dt[i] = hourly_0["dt"]; // 1586761200 hourly_temp[i] = hourly_0["temp"]; // 14.74 hourly_feels_like[i] = hourly_0["feels_like"]; // 12.07 hourly_pressure[i] = hourly_0["pressure"]; // 1013 hourly_humidity[i] = hourly_0["humidity"]; // 62 hourly_dew_point[i] = hourly_0["dew_point"]; // 7.54 hourly_clouds[i] = hourly_0["clouds"]; // 100 hourly_visibility[i] = hourly_0["visibility"]; hourly_wind_speed[i] = hourly_0["wind_speed"]; // 2.99 hourly_wind_gust[i] = hourly_0["wind_gust"]; hourly_wind_deg[i] = hourly_0["wind_deg"]; // 280 hourly_rain_1h[i] = hourly_0["rain"]["1h"]; // 0.11 hourly_snow_1h[i] = hourly_0["snow"]["1h"]; for (int x = 0; x < maxWeatherEntries; x++) { JsonObject hourly_0_weather_0 = hourly_0["weather"][x]; hourly_weather_id[i][x] = hourly_0_weather_0["id"]; // 500 hourly_weather_main[i][x] = hourly_0_weather_0["main"]; // "Rain" hourly_weather_description[i][x] = hourly_0_weather_0["description"]; // "Leichter Regen" hourly_weather_icon[i][x] = hourly_0_weather_0["icon"]; // "10d" } }
// Daily JsonArray daily = doc["daily"]; for (int i = 0; i < 8; i++) { JsonObject daily_0 = daily[i]; daily_dt[i] = daily_0["dt"]; // 1586775600 daily_sunrise[i] = daily_0["sunrise"]; // 1586751436 daily_sunset[i] = daily_0["sunset"]; // 1586800256 JsonObject daily_0_temp = daily_0["temp"]; daily_temp_day[i] = daily_0_temp["day"]; // 18.49 daily_temp_min[i] = daily_0_temp["min"]; // 4.83 daily_temp_max[i] = daily_0_temp["max"]; // 18.49 daily_temp_night[i] = daily_0_temp["night"]; // 4.83 daily_temp_eve[i] = daily_0_temp["eve"]; // 10.11 daily_temp_morn[i] = daily_0_temp["morn"]; // 14.74 JsonObject daily_0_feels_like = daily_0["feels_like"]; daily_feels_like_day[i] = daily_0_feels_like["day"]; // 13.94 daily_feels_like_night[i] = daily_0_feels_like["night"]; // 1.48 daily_feels_like_eve[i] = daily_0_feels_like["eve"]; // 7.09 daily_feels_like_morn[i] = daily_0_feels_like["morn"]; // 12.77 daily_pressure[i] = daily_0["pressure"]; // 1011 daily_humidity[i] = daily_0["humidity"]; // 50 daily_dew_point[i] = daily_0["dew_po "]; // 7.88 daily_wind_speed[i] = daily_0["wind_speed"]; // 5.79 daily_wind_deg[i] = daily_0["wind_deg"]; // 275 daily_wind_gust[i] = daily_0["wind_gust"]; daily_visibility[i] = daily_0["visibility"]; daily_clouds[i] = daily_0["clouds"]; // 100 daily_rain[i] = daily_0["rain"]; // 9.48 daily_snow[i] = daily_0["snow"]; // 0.79 daily_uvi[i] = daily_0["uvi"]; // 4.62 for (int x = 0; x < maxWeatherEntries; x++) { JsonObject daily_0_weather_0 = daily_0["weather"][x]; daily_weather_id[i][x] = daily_0_weather_0["id"]; // 600 daily_weather_main[i][x] = daily_0_weather_0["main"]; // "Snow" daily_weather_description[i][x] = daily_0_weather_0["description"]; // "Mäßiger Schnee" daily_weather_icon[i][x] = daily_0_weather_0["icon"]; // "13d" } } Serial.println("GetWeather finished"); } // END getWeather()
/-------- 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() { digitalWrite(LED_BUILTIN, LOW); 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 + timeZoneNtp * 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) { digitalWrite(LED_BUILTIN, LOW); // 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(); } /-------- END NTP code ----------/
void serialOutputWeather() { Serial.print("current_dt "); Serial.println(current_dt); Serial.print("current_sunrise "); Serial.println(current_sunrise); Serial.print("current_sunset "); Serial.println(current_sunset); Serial.print("current_temp "); Serial.println(current_temp); Serial.print("current_rain_1h "); Serial.println(current_rain_1h); Serial.print("current_weather_id0 "); Serial.print(" "); Serial.println(current_weather_id[0]); Serial.print("current_weather_main0 "); Serial.print(" "); Serial.println(current_weather_main[0]); Serial.print("current_weather_description0 "); Serial.print(" "); Serial.println(current_weather_description[0]); Serial.print("current_weather_main1 "); Serial.print(" "); Serial.println(current_weather_main[1]); Serial.print("current_weather_description1 "); Serial.print(" "); Serial.println(current_weather_description[1]);
for (int i = 0; i < 8; i++) { char buf[50]; unixToChar(daily_dt[i], buf, 0); Serial.print("Wetter fuer "); Serial.print(buf); Serial.print(" "); Serial.println(daily_weather_description[i][0]); }
for (int i = 0; i < 24; i++) { if (hourly_rain_1h[i] != 0) { char clockBuf[70]; unixToChar(hourly_dt[i], clockBuf, 0); printf("Regen um %s, Menge %f \n", clockBuf, hourly_rain_1h[i]); } } Serial.println(ESP.getHeapFragmentation()); Serial.println(ESP.getFreeHeap()); Serial.println(millis()); }
void serialOutputMySQL() { Serial.println("###"); time_t nowTime = myTZ.toLocal(now()); time_t dateReadable[maxRows]; for (int i = 0; i < maxRows; i++) { dateReadable[i] = myTZ.toLocal(date[i]); }
Serial.print("Now Time "); Serial.println(nowTime); Serial.print(year(nowTime)); Serial.print('/'); Serial.print(month(nowTime)); Serial.print('/'); Serial.print(day(nowTime), DEC); Serial.print(' '); Serial.print(hour(nowTime), DEC); Serial.print(':'); Serial.print(minute(nowTime), DEC); Serial.print(':'); Serial.println(second(nowTime), DEC); Serial.println();
for (int i = 0; i < maxRows; i++) { if (id[i] != 0) { Serial.print(id[i]); Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } }
for (int i = 0; i < maxRows; i++) { if (upcoming[i] != 0) { Serial.print("Upcoming: "); Serial.print(i); Serial.print(" "); Serial.print(upcoming[i]); if (upcoming[i] == id[i]) { Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } } }
if (inYear != 0)Serial.println(inYear); if (inMonth != 0)Serial.println(inMonth); if (inDay != 0) Serial.println(inDay); if (inHour != 0) Serial.println(inHour); if (inMinute != 0) Serial.println(inMinute); if (inText != 0) Serial.println(inText); if (inIcon != 0) Serial.println(inIcon); Serial.print("millis "); Serial.println(millis()); } I hope you can help me, don't know whats wrong here. I also tried to write the parsed Json Data to other variables, but it's still the same, as the parser itself already contains faulty data. So it seems that MySQLConnector writes somewhere where it souldn't.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/136, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYHLRL6OW7UGHNNRLDDRPPIWDANCNFSM4MXSVYZQ.
One observation/correction. You should close the cursor and release memory before issuing a second query. Be sure to use the free_row_buffer() and free_column_buffer() like the example in show_results().
On May 2, 2020, at 4:12 AM, MikeAUT notifications@github.com wrote:
Hi veryone, my NodeMCU has two major tasks at the moment:
Communicate with MySQL Database Fetch weather from OpenWeatherMap. Every task on its own works well. But if I execute both tasks the Output on the Monitor looks strange. If everything's alright, it should look like this:
15:56:26.844 -> current_weather_main0 Clouds 15:56:26.844 -> current_weather_description0 Ein paar Wolken But it only looks this way if getWeather() is executed PRIOR MySQLHandling(). When getWeather() is executed and after that MySQLHandling() then i get something like that:
15:50:08.745 -> current_weather_main0 X_TIMESTAMP(date) 15:50:08.745 -> current_weather_description0 ? or
19:16:38.404 -> current_weather_main0 r�calendar�allday�allday? 19:16:38.404 -> current_weather_description0 llday? All the other text outputs (ie daily_weather_description) are looking normal. In the faulty outputs you can see fragments of the SQL Query or the respond. As I said, all the other outputs are OK. The cause is devinitely the call of MySQLHandling() in line 186. If I comment this out, everything is normal. Just as I comment out everything in MySQLHandling() after line 251. So as soon cur_mem->execute(query) gets executed faulty outputs occur.
Here is the code, unrelevant parts are cut out.
include
WiFiClient weatherclient; WiFiClient mysqlclient;
include
include
MySQL_Connection conn(&mysqlclient); MySQL_Cursor* cursor;
include
TimeChangeRule myDST = {"UTC", Last, Sun, Mar, 3, +120}; TimeChangeRule mySTD = {"UTC", Last, Sun, Oct, 3, +60}; Timezone myTZ(myDST, mySTD);
include
include
WiFiUDP Udp;
include
PulseTimer pulseCheckWiFi; PulseTimer pulseGetWeather; PulseTimer pulseOutput; PulseTimer pulseMySQL;
// ----------- // NTP static const char ntpServerName[] = "pool.ntp.org"; const int timeZoneNtp = 0; time_t getNtpTime(); void sendNTPpacket(IPAddress &address);
// ----------- // Common Variables char ssid[] = "XXXXXXXXXXXXXXXX"; char pass[] = "XXXXXXXXXXXXXXXX"; byte mac[6]; long signalStrength; unsigned int localPort = 8888;
// MySQL IPAddress server_addr(192, 168, 1, 5); char user[] = "XXXXXXXXXXXXXXXX"; char password[] = "XXXXXXXXXXXXXXXX"; char default_db[] = "arduino"; const int maxRows = 5;
// Queries char selectQuery[] = "SELECT id,UNIX_TIMESTAMP(date),text,allday,icon FROM calendar ORDER BY date, id LIMIT %d;"; char insertQuery[] = "INSERT INTO calendar (date,text,icon) VALUES (FROM_UNIXTIME(%ld),'%s',%d);"; char deleteQuery[] = "DELETE FROM calendar WHERE id=%d;";
unsigned int id[maxRows]; unsigned long date[maxRows]; String text[maxRows]; bool allday[maxRows]; unsigned int icon[maxRows]; unsigned int upcoming[maxRows];
// -------------------------------------- // Weather variables byte forceRun = 3; // Number of runs with short delay
const char apiowm[] = "api.openweathermap.org";
const byte maxWeatherEntries = 3;
float lat; float lon; const char* timezone; // "Europe/Vienna" // Current long current_dt; // 1586763444 long current_sunrise; // 1586751436 long current_sunset; // 1586800256 float current_temp; // 14.74 float current_feels_like; // 12.34 int current_pressure; // 1013 int current_humidity; // 62 float current_dew_point; // 7.54 int current_clouds; // 100 float current_uvi; // 4.62 int current_visibility; // 10000 float current_wind_speed; // 2.6 float current_wind_gust; int current_wind_deg; // 270 float current_rain_1h; // 0.11 float current_snow_1h; // 0.11
int current_weather_id[maxWeatherEntries]; // 500 const char current_weather_main[maxWeatherEntries]; // "Rain" const char current_weather_description[maxWeatherEntries]; // "Leichter Regen" const char* current_weather_icon[maxWeatherEntries]; // "10d" // Hourly long hourly_dt[48]; // 1586761200 float hourly_temp[48]; // 14.74 float hourly_feels_like[48]; // 12.07 int hourly_pressure[48]; // 1013 int hourly_humidity[48]; // 62 float hourly_dew_point[48]; // 7.54 int hourly_clouds[48]; // 100 int hourly_visibility[48]; float hourly_wind_speed[48]; // 2.99 float hourly_wind_gust[48]; int hourly_wind_deg[48]; // 280 float hourly_rain_1h[48]; // 0.11 float hourly_snow_1h[48];
int hourly_weather_id[48][maxWeatherEntries]; // 500 const char hourly_weather_main[48][maxWeatherEntries]; // "Rain" const char hourly_weather_description[48][maxWeatherEntries]; // "Leichter Regen" const char* hourly_weather_icon[48][maxWeatherEntries]; // "10d" // Daily long daily_dt[8]; // 1586775600 long daily_sunrise[8]; // 1586751436 long daily_sunset[8]; // 1586800256
float daily_temp_day[8]; // 18.49 float daily_temp_min[8]; // 4.83 float daily_temp_max[8]; // 18.49 float daily_temp_night[8]; // 4.83 float daily_temp_eve[8]; // 10.11 float daily_temp_morn[8]; // 14.74
float daily_feels_like_day[8]; // 13.94 float daily_feels_like_night[8]; // 1.48 float daily_feels_like_eve[8]; // 7.09 float daily_feels_like_morn[8]; // 12.77
int daily_pressure[8]; // 1011 int daily_humidity[8]; // 50 float daily_dew_point[8]; // 7.88 float daily_wind_speed[8]; // 5.79 int daily_wind_deg[8]; // 275 float daily_wind_gust[8]; int daily_visibility[8]; int daily_clouds[8]; // 100 float daily_rain[8]; // 9.48 float daily_snow[8]; // 0.79 float daily_uvi[8]; // 4.62
int daily_weather_id[8][maxWeatherEntries]; // 600 const char daily_weather_main[8][maxWeatherEntries]; // "Snow" const char daily_weather_description[8][maxWeatherEntries]; // "Mäßiger Schnee" const char* daily_weather_icon[8][maxWeatherEntries]; // "13d" // Array description: Hour/day, Entry-Nr
void setup() { //Initialize serial and wait for port to open: Serial.begin(500000); WiFi.mode(WIFI_STA); WiFi.hostname("NodeMaster"); connectWiFi(); printWiFiStatus();
// Get NTP Serial.print("IP number assigned by DHCP is "); Serial.println(WiFi.localIP()); Serial.println("Starting UDP"); Udp.begin(localPort); Serial.print("Local port: "); Serial.println(Udp.localPort()); Serial.println("waiting for sync"); setSyncProvider(getNtpTime); setSyncInterval(72000); // 20 Stunden
pinMode(LED_BUILTIN, OUTPUT); digitalWrite(LED_BUILTIN, HIGH); }
void loop() { pulseCheckWiFi.set(10000); pulseGetWeather.set(600000); pulseMySQL.set(15000); pulseOutput.set(30000);
if (timeStatus() != 2) { setSyncProvider(getNtpTime); setSyncInterval(10); } else { setSyncInterval(72000); // 20 Stunden }
if (conn.connected()) { if (pulseMySQL.pulse && timeStatus() == 2) { MySQLHandling(); } } else { conn.close(); Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password, default_db)) { delay(500); Serial.println("Successful reconnect to SQL!"); } else { Serial.println("Cannot reconnect to SQL! Drat."); } }
if (pulseCheckWiFi.pulse) { checkWiFi(); } signalStrength = WiFi.RSSI();
if (forceRun != 0) { pulseGetWeather.set(30000); }
if ((pulseGetWeather.pulse) && (WiFi.status() == WL_CONNECTED)) { if (forceRun > 0) { forceRun --; } getWeather(); }
if (pulseOutput.pulse) { Serial.println("#############"); serialOutputWeather(); serialOutputMySQL(); }
// SHOW UPCOMING IDs unsigned long dt0 = now() + (86400L * 3); int x = 0; for (int i = 0; i < maxRows; i++) { if (date[i] <= dt0) { upcoming[x] = id[i]; } else { upcoming[x] = 0; } x++; }
digitalWrite(LED_BUILTIN, HIGH); }
void MySQLHandling() { Serial.println("Start MySQL Handling"); // ----------------- MySQL for (int i = 0 ; i < maxRows; i++) { id[i] = 0; date[i] = 0; text[i] = ""; allday[i] = false; icon[i] = 0; }
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); char query[150]; sprintf(query, selectQuery, maxRows); digitalWrite(LED_BUILTIN, LOW); cur_mem->execute(query); cur_mem->get_columns();
// Read the rows and print them row_values *row = NULL; int index = 0; do { row = cur_mem->get_next_row(); if (row != NULL) { id[index] = atoi(row->values[0]); date[index] = atoi(row->values[1]); text[index] = row->values[2]; allday[index] = atoi(row->values[3]); icon[index] = atoi(row->values[4]); index++; } } while (row != NULL);
// DELETE EVENT IF LIES IN PAST for (int i = 0; i < maxRows; i++) { if ((date[i] != 0) && (date[i] < now())) { char query2[50]; sprintf(query2, deleteQuery, id[i]); cur_mem->execute(query2); Serial.printf("Termin ID %d geloescht\n", id[i]); } } delete cur_mem; Serial.println("MySQL Handling finished"); }
void getWeather() { digitalWrite(LED_BUILTIN, LOW); Serial.println("Start getWeather"); Serial.println(F("Connecting..."));
// Connect to HTTP server weatherclient.setTimeout(10000); if (!weatherclient.connect(apiowm, 80)) { Serial.println(F("Connection failed")); return; }
Serial.println(F("Connected!"));
// Send HTTP request weatherclient.println(F("GET /data/2.5/onecall?lat=xxxxxxxxxxxx&lon=xxxxxxxxxxxxx&appid=XXXXXXXXXXXXXXXX&mode=json&units=metric&lang=de HTTP/1.0")); weatherclient.println(F("Host: api.openweathermap.org")); weatherclient.println(F("Connection: close")); if (weatherclient.println() == 0) { Serial.println(F("Failed to send request")); return; }
// Check HTTP status char httpstatus[32] = {0}; weatherclient.readBytesUntil('\r', httpstatus, sizeof(httpstatus)); if (strcmp(httpstatus, "HTTP/1.1 200 OK") != 0) { Serial.print(F("Unexpected response: ")); Serial.println(httpstatus); return; }
// Skip HTTP headers char endOfHeaders[] = "\r\n\r\n"; if (!weatherclient.find(endOfHeaders)) { Serial.println(F("Invalid response")); return; }
// Allocate the JSON document // Use arduinojson.org/v6/assistant to compute the capacity. DynamicJsonDocument doc(30000);
// Parse JSON object DeserializationError error = deserializeJson(doc, weatherclient); if (error) { Serial.print(F("deserializeJson() failed: ")); Serial.println(error.c_str()); return; }
// Disconnect weatherclient.stop();
lat = doc["lat"]; lon = doc["lon"]; timezone = doc["timezone"]; // "Europe/Vienna" // Current JsonObject current = doc["current"]; current_dt = current["dt"]; // 1586763444 current_sunrise = current["sunrise"]; // 1586751436 current_sunset = current["sunset"]; // 1586800256 current_temp = current["temp"]; // 14.74 current_feels_like = current["feels_like"]; // 12.34 current_pressure = current["pressure"]; // 1013 current_humidity = current["humidity"]; // 62 current_dew_point = current["dew_point"]; // 7.54 current_clouds = current["clouds"]; // 100 current_uvi = current["uvi"]; // 4.62 current_visibility = current["visibility"]; // 10000 current_wind_speed = current["wind_speed"]; // 2.6 current_wind_gust = current["wind_gust"]; current_wind_deg = current["wind_deg"]; // 270 current_rain_1h = current["rain"]["1h"]; // 0.11 current_snow_1h = current["snow"]["1h"]; // 0.11 for (int i = 0; i < maxWeatherEntries; i++) { JsonObject current_weather_0 = current["weather"][i]; current_weather_id[i] = current_weather_0["id"]; // 500 current_weather_main[i] = current_weather_0["main"]; // "Rain" current_weather_description[i] = current_weather_0["description"]; // "Leichter Regen" current_weather_icon[i] = current_weather_0["icon"]; // "10d" }
// Hourly JsonArray hourly = doc["hourly"]; for (int i = 0 ; i < 48; i++) { JsonObject hourly_0 = hourly[i]; hourly_dt[i] = hourly_0["dt"]; // 1586761200 hourly_temp[i] = hourly_0["temp"]; // 14.74 hourly_feels_like[i] = hourly_0["feels_like"]; // 12.07 hourly_pressure[i] = hourly_0["pressure"]; // 1013 hourly_humidity[i] = hourly_0["humidity"]; // 62 hourly_dew_point[i] = hourly_0["dew_point"]; // 7.54 hourly_clouds[i] = hourly_0["clouds"]; // 100 hourly_visibility[i] = hourly_0["visibility"]; hourly_wind_speed[i] = hourly_0["wind_speed"]; // 2.99 hourly_wind_gust[i] = hourly_0["wind_gust"]; hourly_wind_deg[i] = hourly_0["wind_deg"]; // 280 hourly_rain_1h[i] = hourly_0["rain"]["1h"]; // 0.11 hourly_snow_1h[i] = hourly_0["snow"]["1h"]; for (int x = 0; x < maxWeatherEntries; x++) { JsonObject hourly_0_weather_0 = hourly_0["weather"][x]; hourly_weather_id[i][x] = hourly_0_weather_0["id"]; // 500 hourly_weather_main[i][x] = hourly_0_weather_0["main"]; // "Rain" hourly_weather_description[i][x] = hourly_0_weather_0["description"]; // "Leichter Regen" hourly_weather_icon[i][x] = hourly_0_weather_0["icon"]; // "10d" } }
// Daily JsonArray daily = doc["daily"]; for (int i = 0; i < 8; i++) { JsonObject daily_0 = daily[i]; daily_dt[i] = daily_0["dt"]; // 1586775600 daily_sunrise[i] = daily_0["sunrise"]; // 1586751436 daily_sunset[i] = daily_0["sunset"]; // 1586800256 JsonObject daily_0_temp = daily_0["temp"]; daily_temp_day[i] = daily_0_temp["day"]; // 18.49 daily_temp_min[i] = daily_0_temp["min"]; // 4.83 daily_temp_max[i] = daily_0_temp["max"]; // 18.49 daily_temp_night[i] = daily_0_temp["night"]; // 4.83 daily_temp_eve[i] = daily_0_temp["eve"]; // 10.11 daily_temp_morn[i] = daily_0_temp["morn"]; // 14.74 JsonObject daily_0_feels_like = daily_0["feels_like"]; daily_feels_like_day[i] = daily_0_feels_like["day"]; // 13.94 daily_feels_like_night[i] = daily_0_feels_like["night"]; // 1.48 daily_feels_like_eve[i] = daily_0_feels_like["eve"]; // 7.09 daily_feels_like_morn[i] = daily_0_feels_like["morn"]; // 12.77 daily_pressure[i] = daily_0["pressure"]; // 1011 daily_humidity[i] = daily_0["humidity"]; // 50 daily_dew_point[i] = daily_0["dew_po "]; // 7.88 daily_wind_speed[i] = daily_0["wind_speed"]; // 5.79 daily_wind_deg[i] = daily_0["wind_deg"]; // 275 daily_wind_gust[i] = daily_0["wind_gust"]; daily_visibility[i] = daily_0["visibility"]; daily_clouds[i] = daily_0["clouds"]; // 100 daily_rain[i] = daily_0["rain"]; // 9.48 daily_snow[i] = daily_0["snow"]; // 0.79 daily_uvi[i] = daily_0["uvi"]; // 4.62 for (int x = 0; x < maxWeatherEntries; x++) { JsonObject daily_0_weather_0 = daily_0["weather"][x]; daily_weather_id[i][x] = daily_0_weather_0["id"]; // 600 daily_weather_main[i][x] = daily_0_weather_0["main"]; // "Snow" daily_weather_description[i][x] = daily_0_weather_0["description"]; // "Mäßiger Schnee" daily_weather_icon[i][x] = daily_0_weather_0["icon"]; // "13d" } } Serial.println("GetWeather finished"); } // END getWeather()
/-------- 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() { digitalWrite(LED_BUILTIN, LOW); 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 + timeZoneNtp * 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) { digitalWrite(LED_BUILTIN, LOW); // 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(); } /-------- END NTP code ----------/
void serialOutputWeather() { Serial.print("current_dt "); Serial.println(current_dt); Serial.print("current_sunrise "); Serial.println(current_sunrise); Serial.print("current_sunset "); Serial.println(current_sunset); Serial.print("current_temp "); Serial.println(current_temp); Serial.print("current_rain_1h "); Serial.println(current_rain_1h); Serial.print("current_weather_id0 "); Serial.print(" "); Serial.println(current_weather_id[0]); Serial.print("current_weather_main0 "); Serial.print(" "); Serial.println(current_weather_main[0]); Serial.print("current_weather_description0 "); Serial.print(" "); Serial.println(current_weather_description[0]); Serial.print("current_weather_main1 "); Serial.print(" "); Serial.println(current_weather_main[1]); Serial.print("current_weather_description1 "); Serial.print(" "); Serial.println(current_weather_description[1]);
for (int i = 0; i < 8; i++) { char buf[50]; unixToChar(daily_dt[i], buf, 0); Serial.print("Wetter fuer "); Serial.print(buf); Serial.print(" "); Serial.println(daily_weather_description[i][0]); }
for (int i = 0; i < 24; i++) { if (hourly_rain_1h[i] != 0) { char clockBuf[70]; unixToChar(hourly_dt[i], clockBuf, 0); printf("Regen um %s, Menge %f \n", clockBuf, hourly_rain_1h[i]); } } Serial.println(ESP.getHeapFragmentation()); Serial.println(ESP.getFreeHeap()); Serial.println(millis()); }
void serialOutputMySQL() { Serial.println("###"); time_t nowTime = myTZ.toLocal(now()); time_t dateReadable[maxRows]; for (int i = 0; i < maxRows; i++) { dateReadable[i] = myTZ.toLocal(date[i]); }
Serial.print("Now Time "); Serial.println(nowTime); Serial.print(year(nowTime)); Serial.print('/'); Serial.print(month(nowTime)); Serial.print('/'); Serial.print(day(nowTime), DEC); Serial.print(' '); Serial.print(hour(nowTime), DEC); Serial.print(':'); Serial.print(minute(nowTime), DEC); Serial.print(':'); Serial.println(second(nowTime), DEC); Serial.println();
for (int i = 0; i < maxRows; i++) { if (id[i] != 0) { Serial.print(id[i]); Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } }
for (int i = 0; i < maxRows; i++) { if (upcoming[i] != 0) { Serial.print("Upcoming: "); Serial.print(i); Serial.print(" "); Serial.print(upcoming[i]); if (upcoming[i] == id[i]) { Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } } }
if (inYear != 0)Serial.println(inYear); if (inMonth != 0)Serial.println(inMonth); if (inDay != 0) Serial.println(inDay); if (inHour != 0) Serial.println(inHour); if (inMinute != 0) Serial.println(inMinute); if (inText != 0) Serial.println(inText); if (inIcon != 0) Serial.println(inIcon); Serial.print("millis "); Serial.println(millis()); } I hope you can help me, don't know whats wrong here. I also tried to write the parsed Json Data to other variables, but it's still the same, as the parser itself already contains faulty data. So it seems that MySQLConnector writes somewhere where it souldn't.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/136, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYHLRL6OW7UGHNNRLDDRPPIWDANCNFSM4MXSVYZQ.
Ok, before you do anything else, I recommend you create a smaller version of this sketch and remove all of the code that doesn’t relate to MySQL. And, correct the issue I mentioned with the cursor. Get that to work correctly first. Then, go back and add your other code.
The sketch is simply too complex to debug otherwise. Especially since this smells like memory issues.
On May 2, 2020, at 4:12 AM, MikeAUT notifications@github.com wrote:
Hi veryone, my NodeMCU has two major tasks at the moment:
Communicate with MySQL Database Fetch weather from OpenWeatherMap. Every task on its own works well. But if I execute both tasks the Output on the Monitor looks strange. If everything's alright, it should look like this:
15:56:26.844 -> current_weather_main0 Clouds 15:56:26.844 -> current_weather_description0 Ein paar Wolken But it only looks this way if getWeather() is executed PRIOR MySQLHandling(). When getWeather() is executed and after that MySQLHandling() then i get something like that:
15:50:08.745 -> current_weather_main0 X_TIMESTAMP(date) 15:50:08.745 -> current_weather_description0 ? or
19:16:38.404 -> current_weather_main0 r�calendar�allday�allday? 19:16:38.404 -> current_weather_description0 llday? All the other text outputs (ie daily_weather_description) are looking normal. In the faulty outputs you can see fragments of the SQL Query or the respond. As I said, all the other outputs are OK. The cause is devinitely the call of MySQLHandling() in line 186. If I comment this out, everything is normal. Just as I comment out everything in MySQLHandling() after line 251. So as soon cur_mem->execute(query) gets executed faulty outputs occur.
Here is the code, unrelevant parts are cut out.
include
WiFiClient weatherclient; WiFiClient mysqlclient;
include
include
MySQL_Connection conn(&mysqlclient); MySQL_Cursor* cursor;
include
TimeChangeRule myDST = {"UTC", Last, Sun, Mar, 3, +120}; TimeChangeRule mySTD = {"UTC", Last, Sun, Oct, 3, +60}; Timezone myTZ(myDST, mySTD);
include
include
WiFiUDP Udp;
include
PulseTimer pulseCheckWiFi; PulseTimer pulseGetWeather; PulseTimer pulseOutput; PulseTimer pulseMySQL;
// ----------- // NTP static const char ntpServerName[] = "pool.ntp.org"; const int timeZoneNtp = 0; time_t getNtpTime(); void sendNTPpacket(IPAddress &address);
// ----------- // Common Variables char ssid[] = "XXXXXXXXXXXXXXXX"; char pass[] = "XXXXXXXXXXXXXXXX"; byte mac[6]; long signalStrength; unsigned int localPort = 8888;
// MySQL IPAddress server_addr(192, 168, 1, 5); char user[] = "XXXXXXXXXXXXXXXX"; char password[] = "XXXXXXXXXXXXXXXX"; char default_db[] = "arduino"; const int maxRows = 5;
// Queries char selectQuery[] = "SELECT id,UNIX_TIMESTAMP(date),text,allday,icon FROM calendar ORDER BY date, id LIMIT %d;"; char insertQuery[] = "INSERT INTO calendar (date,text,icon) VALUES (FROM_UNIXTIME(%ld),'%s',%d);"; char deleteQuery[] = "DELETE FROM calendar WHERE id=%d;";
unsigned int id[maxRows]; unsigned long date[maxRows]; String text[maxRows]; bool allday[maxRows]; unsigned int icon[maxRows]; unsigned int upcoming[maxRows];
// -------------------------------------- // Weather variables byte forceRun = 3; // Number of runs with short delay
const char apiowm[] = "api.openweathermap.org";
const byte maxWeatherEntries = 3;
float lat; float lon; const char* timezone; // "Europe/Vienna" // Current long current_dt; // 1586763444 long current_sunrise; // 1586751436 long current_sunset; // 1586800256 float current_temp; // 14.74 float current_feels_like; // 12.34 int current_pressure; // 1013 int current_humidity; // 62 float current_dew_point; // 7.54 int current_clouds; // 100 float current_uvi; // 4.62 int current_visibility; // 10000 float current_wind_speed; // 2.6 float current_wind_gust; int current_wind_deg; // 270 float current_rain_1h; // 0.11 float current_snow_1h; // 0.11
int current_weather_id[maxWeatherEntries]; // 500 const char current_weather_main[maxWeatherEntries]; // "Rain" const char current_weather_description[maxWeatherEntries]; // "Leichter Regen" const char* current_weather_icon[maxWeatherEntries]; // "10d" // Hourly long hourly_dt[48]; // 1586761200 float hourly_temp[48]; // 14.74 float hourly_feels_like[48]; // 12.07 int hourly_pressure[48]; // 1013 int hourly_humidity[48]; // 62 float hourly_dew_point[48]; // 7.54 int hourly_clouds[48]; // 100 int hourly_visibility[48]; float hourly_wind_speed[48]; // 2.99 float hourly_wind_gust[48]; int hourly_wind_deg[48]; // 280 float hourly_rain_1h[48]; // 0.11 float hourly_snow_1h[48];
int hourly_weather_id[48][maxWeatherEntries]; // 500 const char hourly_weather_main[48][maxWeatherEntries]; // "Rain" const char hourly_weather_description[48][maxWeatherEntries]; // "Leichter Regen" const char* hourly_weather_icon[48][maxWeatherEntries]; // "10d" // Daily long daily_dt[8]; // 1586775600 long daily_sunrise[8]; // 1586751436 long daily_sunset[8]; // 1586800256
float daily_temp_day[8]; // 18.49 float daily_temp_min[8]; // 4.83 float daily_temp_max[8]; // 18.49 float daily_temp_night[8]; // 4.83 float daily_temp_eve[8]; // 10.11 float daily_temp_morn[8]; // 14.74
float daily_feels_like_day[8]; // 13.94 float daily_feels_like_night[8]; // 1.48 float daily_feels_like_eve[8]; // 7.09 float daily_feels_like_morn[8]; // 12.77
int daily_pressure[8]; // 1011 int daily_humidity[8]; // 50 float daily_dew_point[8]; // 7.88 float daily_wind_speed[8]; // 5.79 int daily_wind_deg[8]; // 275 float daily_wind_gust[8]; int daily_visibility[8]; int daily_clouds[8]; // 100 float daily_rain[8]; // 9.48 float daily_snow[8]; // 0.79 float daily_uvi[8]; // 4.62
int daily_weather_id[8][maxWeatherEntries]; // 600 const char daily_weather_main[8][maxWeatherEntries]; // "Snow" const char daily_weather_description[8][maxWeatherEntries]; // "Mäßiger Schnee" const char* daily_weather_icon[8][maxWeatherEntries]; // "13d" // Array description: Hour/day, Entry-Nr
void setup() { //Initialize serial and wait for port to open: Serial.begin(500000); WiFi.mode(WIFI_STA); WiFi.hostname("NodeMaster"); connectWiFi(); printWiFiStatus();
// Get NTP Serial.print("IP number assigned by DHCP is "); Serial.println(WiFi.localIP()); Serial.println("Starting UDP"); Udp.begin(localPort); Serial.print("Local port: "); Serial.println(Udp.localPort()); Serial.println("waiting for sync"); setSyncProvider(getNtpTime); setSyncInterval(72000); // 20 Stunden
pinMode(LED_BUILTIN, OUTPUT); digitalWrite(LED_BUILTIN, HIGH); }
void loop() { pulseCheckWiFi.set(10000); pulseGetWeather.set(600000); pulseMySQL.set(15000); pulseOutput.set(30000);
if (timeStatus() != 2) { setSyncProvider(getNtpTime); setSyncInterval(10); } else { setSyncInterval(72000); // 20 Stunden }
if (conn.connected()) { if (pulseMySQL.pulse && timeStatus() == 2) { MySQLHandling(); } } else { conn.close(); Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password, default_db)) { delay(500); Serial.println("Successful reconnect to SQL!"); } else { Serial.println("Cannot reconnect to SQL! Drat."); } }
if (pulseCheckWiFi.pulse) { checkWiFi(); } signalStrength = WiFi.RSSI();
if (forceRun != 0) { pulseGetWeather.set(30000); }
if ((pulseGetWeather.pulse) && (WiFi.status() == WL_CONNECTED)) { if (forceRun > 0) { forceRun --; } getWeather(); }
if (pulseOutput.pulse) { Serial.println("#############"); serialOutputWeather(); serialOutputMySQL(); }
// SHOW UPCOMING IDs unsigned long dt0 = now() + (86400L * 3); int x = 0; for (int i = 0; i < maxRows; i++) { if (date[i] <= dt0) { upcoming[x] = id[i]; } else { upcoming[x] = 0; } x++; }
digitalWrite(LED_BUILTIN, HIGH); }
void MySQLHandling() { Serial.println("Start MySQL Handling"); // ----------------- MySQL for (int i = 0 ; i < maxRows; i++) { id[i] = 0; date[i] = 0; text[i] = ""; allday[i] = false; icon[i] = 0; }
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); char query[150]; sprintf(query, selectQuery, maxRows); digitalWrite(LED_BUILTIN, LOW); cur_mem->execute(query); cur_mem->get_columns();
// Read the rows and print them row_values *row = NULL; int index = 0; do { row = cur_mem->get_next_row(); if (row != NULL) { id[index] = atoi(row->values[0]); date[index] = atoi(row->values[1]); text[index] = row->values[2]; allday[index] = atoi(row->values[3]); icon[index] = atoi(row->values[4]); index++; } } while (row != NULL);
// DELETE EVENT IF LIES IN PAST for (int i = 0; i < maxRows; i++) { if ((date[i] != 0) && (date[i] < now())) { char query2[50]; sprintf(query2, deleteQuery, id[i]); cur_mem->execute(query2); Serial.printf("Termin ID %d geloescht\n", id[i]); } } delete cur_mem; Serial.println("MySQL Handling finished"); }
void getWeather() { digitalWrite(LED_BUILTIN, LOW); Serial.println("Start getWeather"); Serial.println(F("Connecting..."));
// Connect to HTTP server weatherclient.setTimeout(10000); if (!weatherclient.connect(apiowm, 80)) { Serial.println(F("Connection failed")); return; }
Serial.println(F("Connected!"));
// Send HTTP request weatherclient.println(F("GET /data/2.5/onecall?lat=xxxxxxxxxxxx&lon=xxxxxxxxxxxxx&appid=XXXXXXXXXXXXXXXX&mode=json&units=metric&lang=de HTTP/1.0")); weatherclient.println(F("Host: api.openweathermap.org")); weatherclient.println(F("Connection: close")); if (weatherclient.println() == 0) { Serial.println(F("Failed to send request")); return; }
// Check HTTP status char httpstatus[32] = {0}; weatherclient.readBytesUntil('\r', httpstatus, sizeof(httpstatus)); if (strcmp(httpstatus, "HTTP/1.1 200 OK") != 0) { Serial.print(F("Unexpected response: ")); Serial.println(httpstatus); return; }
// Skip HTTP headers char endOfHeaders[] = "\r\n\r\n"; if (!weatherclient.find(endOfHeaders)) { Serial.println(F("Invalid response")); return; }
// Allocate the JSON document // Use arduinojson.org/v6/assistant to compute the capacity. DynamicJsonDocument doc(30000);
// Parse JSON object DeserializationError error = deserializeJson(doc, weatherclient); if (error) { Serial.print(F("deserializeJson() failed: ")); Serial.println(error.c_str()); return; }
// Disconnect weatherclient.stop();
lat = doc["lat"]; lon = doc["lon"]; timezone = doc["timezone"]; // "Europe/Vienna" // Current JsonObject current = doc["current"]; current_dt = current["dt"]; // 1586763444 current_sunrise = current["sunrise"]; // 1586751436 current_sunset = current["sunset"]; // 1586800256 current_temp = current["temp"]; // 14.74 current_feels_like = current["feels_like"]; // 12.34 current_pressure = current["pressure"]; // 1013 current_humidity = current["humidity"]; // 62 current_dew_point = current["dew_point"]; // 7.54 current_clouds = current["clouds"]; // 100 current_uvi = current["uvi"]; // 4.62 current_visibility = current["visibility"]; // 10000 current_wind_speed = current["wind_speed"]; // 2.6 current_wind_gust = current["wind_gust"]; current_wind_deg = current["wind_deg"]; // 270 current_rain_1h = current["rain"]["1h"]; // 0.11 current_snow_1h = current["snow"]["1h"]; // 0.11 for (int i = 0; i < maxWeatherEntries; i++) { JsonObject current_weather_0 = current["weather"][i]; current_weather_id[i] = current_weather_0["id"]; // 500 current_weather_main[i] = current_weather_0["main"]; // "Rain" current_weather_description[i] = current_weather_0["description"]; // "Leichter Regen" current_weather_icon[i] = current_weather_0["icon"]; // "10d" }
// Hourly JsonArray hourly = doc["hourly"]; for (int i = 0 ; i < 48; i++) { JsonObject hourly_0 = hourly[i]; hourly_dt[i] = hourly_0["dt"]; // 1586761200 hourly_temp[i] = hourly_0["temp"]; // 14.74 hourly_feels_like[i] = hourly_0["feels_like"]; // 12.07 hourly_pressure[i] = hourly_0["pressure"]; // 1013 hourly_humidity[i] = hourly_0["humidity"]; // 62 hourly_dew_point[i] = hourly_0["dew_point"]; // 7.54 hourly_clouds[i] = hourly_0["clouds"]; // 100 hourly_visibility[i] = hourly_0["visibility"]; hourly_wind_speed[i] = hourly_0["wind_speed"]; // 2.99 hourly_wind_gust[i] = hourly_0["wind_gust"]; hourly_wind_deg[i] = hourly_0["wind_deg"]; // 280 hourly_rain_1h[i] = hourly_0["rain"]["1h"]; // 0.11 hourly_snow_1h[i] = hourly_0["snow"]["1h"]; for (int x = 0; x < maxWeatherEntries; x++) { JsonObject hourly_0_weather_0 = hourly_0["weather"][x]; hourly_weather_id[i][x] = hourly_0_weather_0["id"]; // 500 hourly_weather_main[i][x] = hourly_0_weather_0["main"]; // "Rain" hourly_weather_description[i][x] = hourly_0_weather_0["description"]; // "Leichter Regen" hourly_weather_icon[i][x] = hourly_0_weather_0["icon"]; // "10d" } }
// Daily JsonArray daily = doc["daily"]; for (int i = 0; i < 8; i++) { JsonObject daily_0 = daily[i]; daily_dt[i] = daily_0["dt"]; // 1586775600 daily_sunrise[i] = daily_0["sunrise"]; // 1586751436 daily_sunset[i] = daily_0["sunset"]; // 1586800256 JsonObject daily_0_temp = daily_0["temp"]; daily_temp_day[i] = daily_0_temp["day"]; // 18.49 daily_temp_min[i] = daily_0_temp["min"]; // 4.83 daily_temp_max[i] = daily_0_temp["max"]; // 18.49 daily_temp_night[i] = daily_0_temp["night"]; // 4.83 daily_temp_eve[i] = daily_0_temp["eve"]; // 10.11 daily_temp_morn[i] = daily_0_temp["morn"]; // 14.74 JsonObject daily_0_feels_like = daily_0["feels_like"]; daily_feels_like_day[i] = daily_0_feels_like["day"]; // 13.94 daily_feels_like_night[i] = daily_0_feels_like["night"]; // 1.48 daily_feels_like_eve[i] = daily_0_feels_like["eve"]; // 7.09 daily_feels_like_morn[i] = daily_0_feels_like["morn"]; // 12.77 daily_pressure[i] = daily_0["pressure"]; // 1011 daily_humidity[i] = daily_0["humidity"]; // 50 daily_dew_point[i] = daily_0["dew_po "]; // 7.88 daily_wind_speed[i] = daily_0["wind_speed"]; // 5.79 daily_wind_deg[i] = daily_0["wind_deg"]; // 275 daily_wind_gust[i] = daily_0["wind_gust"]; daily_visibility[i] = daily_0["visibility"]; daily_clouds[i] = daily_0["clouds"]; // 100 daily_rain[i] = daily_0["rain"]; // 9.48 daily_snow[i] = daily_0["snow"]; // 0.79 daily_uvi[i] = daily_0["uvi"]; // 4.62 for (int x = 0; x < maxWeatherEntries; x++) { JsonObject daily_0_weather_0 = daily_0["weather"][x]; daily_weather_id[i][x] = daily_0_weather_0["id"]; // 600 daily_weather_main[i][x] = daily_0_weather_0["main"]; // "Snow" daily_weather_description[i][x] = daily_0_weather_0["description"]; // "Mäßiger Schnee" daily_weather_icon[i][x] = daily_0_weather_0["icon"]; // "13d" } } Serial.println("GetWeather finished"); } // END getWeather()
/-------- 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() { digitalWrite(LED_BUILTIN, LOW); 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 + timeZoneNtp * 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) { digitalWrite(LED_BUILTIN, LOW); // 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(); } /-------- END NTP code ----------/
void serialOutputWeather() { Serial.print("current_dt "); Serial.println(current_dt); Serial.print("current_sunrise "); Serial.println(current_sunrise); Serial.print("current_sunset "); Serial.println(current_sunset); Serial.print("current_temp "); Serial.println(current_temp); Serial.print("current_rain_1h "); Serial.println(current_rain_1h); Serial.print("current_weather_id0 "); Serial.print(" "); Serial.println(current_weather_id[0]); Serial.print("current_weather_main0 "); Serial.print(" "); Serial.println(current_weather_main[0]); Serial.print("current_weather_description0 "); Serial.print(" "); Serial.println(current_weather_description[0]); Serial.print("current_weather_main1 "); Serial.print(" "); Serial.println(current_weather_main[1]); Serial.print("current_weather_description1 "); Serial.print(" "); Serial.println(current_weather_description[1]);
for (int i = 0; i < 8; i++) { char buf[50]; unixToChar(daily_dt[i], buf, 0); Serial.print("Wetter fuer "); Serial.print(buf); Serial.print(" "); Serial.println(daily_weather_description[i][0]); }
for (int i = 0; i < 24; i++) { if (hourly_rain_1h[i] != 0) { char clockBuf[70]; unixToChar(hourly_dt[i], clockBuf, 0); printf("Regen um %s, Menge %f \n", clockBuf, hourly_rain_1h[i]); } } Serial.println(ESP.getHeapFragmentation()); Serial.println(ESP.getFreeHeap()); Serial.println(millis()); }
void serialOutputMySQL() { Serial.println("###"); time_t nowTime = myTZ.toLocal(now()); time_t dateReadable[maxRows]; for (int i = 0; i < maxRows; i++) { dateReadable[i] = myTZ.toLocal(date[i]); }
Serial.print("Now Time "); Serial.println(nowTime); Serial.print(year(nowTime)); Serial.print('/'); Serial.print(month(nowTime)); Serial.print('/'); Serial.print(day(nowTime), DEC); Serial.print(' '); Serial.print(hour(nowTime), DEC); Serial.print(':'); Serial.print(minute(nowTime), DEC); Serial.print(':'); Serial.println(second(nowTime), DEC); Serial.println();
for (int i = 0; i < maxRows; i++) { if (id[i] != 0) { Serial.print(id[i]); Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } }
for (int i = 0; i < maxRows; i++) { if (upcoming[i] != 0) { Serial.print("Upcoming: "); Serial.print(i); Serial.print(" "); Serial.print(upcoming[i]); if (upcoming[i] == id[i]) { Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } } }
if (inYear != 0)Serial.println(inYear); if (inMonth != 0)Serial.println(inMonth); if (inDay != 0) Serial.println(inDay); if (inHour != 0) Serial.println(inHour); if (inMinute != 0) Serial.println(inMinute); if (inText != 0) Serial.println(inText); if (inIcon != 0) Serial.println(inIcon); Serial.print("millis "); Serial.println(millis()); } I hope you can help me, don't know whats wrong here. I also tried to write the parsed Json Data to other variables, but it's still the same, as the parser itself already contains faulty data. So it seems that MySQLConnector writes somewhere where it souldn't.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/136, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYHLRL6OW7UGHNNRLDDRPPIWDANCNFSM4MXSVYZQ.
Thank you, I'll take care of this, and give you response
Hi, I still haven't found a solution for this problem, but thats not a big deal. I just use current_weather_id instead of current_weather_main and current_weather_description. So this is a workaround I can live with ;) Thank you
Hi, Ok, I have a wild idea. Try putting Serial.flush() after the troublesome print() statements. See if that helps. :)
On Jun 13, 2020, at 1:38 AM, MikeAUT notifications@github.com wrote:
Hi, I still haven't found a solution for this problem, but thats not a big deal. I just use current_weather_id instead of current_weather_main and current_weather_description. So this is a workaround I can live with ;) Thank you
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/136#issuecomment-643573659, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYA5IXCP272MEVBEHXLRWMGGBANCNFSM4MXSVYZQ.
Thank you, but it didn't help. But as I said, it's not a big problem for me anymore.
Closed due to inactivity. Please open a new ticket if this is still relevant.
Hi veryone, my NodeMCU has two major tasks at the moment:
Every task on its own works well. But if I execute both tasks the Output on the Monitor looks strange. If everything's alright, it should look like this:
But it only looks this way if getWeather() is executed PRIOR MySQLHandling(). When getWeather() is executed and after that MySQLHandling() then i get something like that:
or
All the other text outputs (ie daily_weather_description) are looking normal. In the faulty outputs you can see fragments of the SQL Query or the respond. As I said, all the other outputs are OK. The cause is devinitely the call of MySQLHandling() in line 186. If I comment this out, everything is normal. Just as I comment out everything in MySQLHandling() after line 251. So as soon cur_mem->execute(query) gets executed faulty outputs occur.
Here is the code, unrelevant parts are cut out.
I hope you can help me, don't know whats wrong here. I also tried to write the parsed Json Data to other variables, but it's still the same, as the parser itself already contains faulty data. So it seems that MySQLConnector writes somewhere where it souldn't.