Help: mySQL need to check for no data and then pass NULL **solved**
Posted: Tue Sep 17, 2019 6:11 pm
I have a PM2.5 sensor that is solar and battery operated. Trouble is sometimes the battery gets depleted as where it is situated is not sufficient for the solar to keep batteries charged for longer than 6 weeks. So ever 6 weeks my PM2.5 stops working and I have to rechange. Trouble is that I miss out on data in mySQL database. The Meteobridge fails to upload all data to mySQL if just one sensor stops providing data.
I then tried to craft a better mySQL upload query that would upload NULL if there was no data for PM2.5....which in the Meteobridge is variable air0pm.
Failed attempt at code:
The key part is the end where it reads '#if#{*[air0pm-age:999]<300*}#then#[air0pm-max5=.0]#else#--#fi#'
Using that if the PM2.5 is not operational I get the following error still with no mySQL data upload:
2019-09-17 12:00:09 Error: 2019-09-17 12:00:02 MYSQL query 'INSERT INTO `mystation` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`, `UV`, `Solar`, `PM2.5`) VALUES (NULL, '2019-09-17 12:00:02', '90.3', '56.0', '29.97', '72.5', '98.2', '90.3', '77.7', '51.0', '2.2', '1.8', '302.0', 'WNW', '4.5', '276.0', 'W', '0.00', '0.00', '0.00', '3.78', '4.27', '7.0', '782.0', '--')' failed: Incorrect integer value: '--' for column 'PM2.5' at row 1 (no more tries):
Notice it says failed for value '--'
I've also tried to type in the word NULL instead of -- and it still doesn't work.
I'm thinking I need to do the IF THEN ELSE in mySQL instead. But I need help with the syntax. Another thought is to use IFNULL but again I need help with the syntax. Another idea is to use CASE IF THEN ELSE. I just have no idea yet how to implement the correct syntax.
Bottom line: I want to have a mySQL upload query that works even if [air0pm-max5=.0] is not providing any data. I think I'm on the right track but I need a little help.
I then tried to craft a better mySQL upload query that would upload NULL if there was no data for PM2.5....which in the Meteobridge is variable air0pm.
Failed attempt at code:
Code: Select all
INSERT INTO `mystation` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`, `UV`, `Solar`, `PM2.5`) VALUES (NULL, '[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]', '[th0temp-act=F]', '[th0hum-act]', '[thb0seapress-act=inHg.2]', '[th0dew-act=F]', '[th0heatindex-act=F]', '[wind0chill-act=F]', '[thb0temp-act=F]', '[thb0hum-act]', '[wind0wind-act=mph]', '[wind0avgwind-act=mph]', '[wind0dir-act]', '[wind0dir-act=endir]', '[wind0wind-max10=mph]', '[wind0dir-avg10]', '[wind0dir-avg10=endir]', '[rain0rate-act=in.2]', '[rain0total-daysum=in.2]', '[rain0total-ydaysum=in.2]', '[rain0total-monthsum=in.2]', '[rain0total-yearsum=in.2]', '[uv0index-act]', '[sol0rad-act]', '#if#{*[air0pm-age:999]<300*}#then#[air0pm-max5=.0]#else#--#fi#')Using that if the PM2.5 is not operational I get the following error still with no mySQL data upload:
2019-09-17 12:00:09 Error: 2019-09-17 12:00:02 MYSQL query 'INSERT INTO `mystation` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`, `UV`, `Solar`, `PM2.5`) VALUES (NULL, '2019-09-17 12:00:02', '90.3', '56.0', '29.97', '72.5', '98.2', '90.3', '77.7', '51.0', '2.2', '1.8', '302.0', 'WNW', '4.5', '276.0', 'W', '0.00', '0.00', '0.00', '3.78', '4.27', '7.0', '782.0', '--')' failed: Incorrect integer value: '--' for column 'PM2.5' at row 1 (no more tries):
Notice it says failed for value '--'
I've also tried to type in the word NULL instead of -- and it still doesn't work.
I'm thinking I need to do the IF THEN ELSE in mySQL instead. But I need help with the syntax. Another thought is to use IFNULL but again I need help with the syntax. Another idea is to use CASE IF THEN ELSE. I just have no idea yet how to implement the correct syntax.
Bottom line: I want to have a mySQL upload query that works even if [air0pm-max5=.0] is not providing any data. I think I'm on the right track but I need a little help.