Re: Writing NULL values to a MySQL record via DBI

Are you sure that the variables loaded from empty fields in your CSV are indeed undef and not simply empty (in string context) or zero (in numerical context)?

A bit "off topic", but having NULL values in your database is generally a bad idea. NULL should indicate an error (which you do not want to have in your database, so it should never happen), not simply "missing data, but that is OK because it is unimportant anyhow".

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

Comment on Re: Writing NULL values to a MySQL record via DBI Select or Download Code

Replies are listed 'Best First'.
Re^2: Writing NULL values to a MySQL record via DBI by ureco (Acolyte) on Feb 27, 2015 at 16:48 UTC
Yes, most of the fields are arriving in Perl as undef, there are a few that arrive as empty but I'm converting these into undef when found. The use of NULL's is by design. The data I'm collecting my not be complete and as I don't control the raw data there is not much I can do about this. The information (as you may have guessed) is weather data and I'll be calculating average and various other stats from it. I have to use NULL to differentiate between actual data and missing data - I can't use zero for example because it's a real value 0 degrees Celsius is a valid temperature. It's even worse with other parameters as prior to the solution to my Barometric Pressure was being populated with 0mb! Calculating averages with these values would not work as the calculation included the zeros. By inserting NULL the average only calculates based on the actual figures it has and returns the best value possible given the information it has. Not sure if there was a better way to solve this problem but it appears to be working ok for me at the moment.	[reply]

Replies are listed 'Best First'.

Re^2: Writing NULL values to a MySQL record via DBI
by ureco (Acolyte) on Feb 27, 2015 at 16:48 UTC

Yes, most of the fields are arriving in Perl as undef, there are a few that arrive as empty but I'm converting these into undef when found. The use of NULL's is by design. The data I'm collecting my not be complete and as I don't control the raw data there is not much I can do about this. The information (as you may have guessed) is weather data and I'll be calculating average and various other stats from it. I have to use NULL to differentiate between actual data and missing data - I can't use zero for example because it's a real value 0 degrees Celsius is a valid temperature. It's even worse with other parameters as prior to the solution to my Barometric Pressure was being populated with 0mb! Calculating averages with these values would not work as the calculation included the zeros. By inserting NULL the average only calculates based on the actual figures it has and returns the best value possible given the information it has. Not sure if there was a better way to solve this problem but it appears to be working ok for me at the moment.

[reply]