Help: mySQL need to check for no data and then pass NULL **solved**

[galfert]

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:

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#')

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.

[galfert]

I figured it out!

This works:

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]', CASE '#if#{*[air0pm-age:999]<300*}#then#GOOD#else#BAD#fi#' WHEN 'GOOD' THEN '[air0pm-max5=.0]' ELSE NULL END)

In particular this is the part where the PM2.5 data passed to SQL or not.
CASE '#if#{*[air0pm-age:999]<300*}#then#GOOD#else#BAD#fi#' WHEN 'GOOD' THEN '[air0pm-max5=.0]' ELSE NULL END

This is really neat. Two things are going on here. There is a Meteobridge IF THEN ELSE statement within an SQL CASE WHEN THEN ELSE statement. The Meteobridge part of the statement tests the age of the PM2.5 data and then reports GOOD if the age is less than 5 minutes, and it reports BAD otherwise. Then the SQL part with CASE tests if the value returned from the Meteobridge is GOOD or BAD, and then if GOOD it uploads the PM2.5 data and if BAD then it uploads NULL.

But that will only work if your SQL database is configured for PM2.5 column to accept NULL value which was not set by default when I created the column in the table.

The neat thing now is that if my PM2.5 runs out of power it will not stop all other data items from being uploaded to SQL.
I have also created a Meteobridge trigger alarm email alert to warn me when PM2.5 stops reporting basically when [air0pm-age]>600.