ureco:

In addition to using placeholders, you'll also benefit from using prepared statements. It's pretty simple to do, because as you surmised, there's a one-to-one relationship between the list of values you provide and the question mark in the SQL:

# Prepare your statement at the start of your program
my $ST = $DB->prepare(q{
   REPLACE INTO met_office_raw_data
      SET observation_datetime_utc   = ?,
          observation_datetime_local = ?,
          observation_datetime       = ?,
          station_id                 = ?,
          station_name               = ?,
          temp_c                     = ?,
          dewpoint_c                 = ?,
          humidity_rh                = ?,
          wind_dir_compass           = ?,
          wind_speed_mph             = ?,
          wind_gust_mph              = ?,
          visibility_metres          = ?,
          pressure_hpa               = ?,
          pressure_tendency          = ?,
          weather_type               = ?
});

while (....) {
   # Then for each reading, get the values
   my ( 
      $file_obs_datetime_utc, $file_obs_datetime_local,
      $file_obs_datetime,     $file_station_id,
      $file_station_name,     $file_temp_c,
      $file_dewpoint_c,       $file_humidity_rh,
      $file_wind_dir_compass, $file_wind_speed_mph,
      $file_wind_gust_mph,    $file_visibility_metres,
      $file_pressure_hpa,     $file_pressure_tendency,
      $file_weather_type 
   ) = get_my_data();

   # and write them to the table
   $ST->execute(
      $file_obs_datetime_utc, $file_obs_datetime_local,
      $file_obs_datetime,     $file_station_id,
      $file_station_name,     $file_temp_c,
      $file_dewpoint_c,       $file_humidity_rh,
      $file_wind_dir_compass, $file_wind_speed_mph,
      $file_wind_gust_mph,    $file_visibility_metres,
      $file_pressure_hpa,     $file_pressure_tendency,
      $file_weather_type 
   );
}
[download]

It's as easy as that. In fact, it's usually simpler: It's often easy enough to arrange that the order of values needed by the SQL operation matches that of your function that retrieves data. In that case, the while loop can boil down to:

while (....) {
   # Then for each reading, get the values
   my @data_fields = get_my_data();

   # and write them to the table
   $ST->execute(@data_fields);
}
[download]

(Assuming you're not using the variable names elsewhere in your code.)

...roboticus

When your only tool is a hammer, all problems look like your thumb.

I like the look of this actually, makes for nice neat code. I'll look to adopt this into my script as well. With all the SQL statements as you say up near the beginning of the script they are easily found for making changes. Thanks for the advice.

What does the the met_office_raw_data table look like.
Using mysql client you can use the describe command like this.

mysql> describe test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)
[download]

The columns to look at are 'Null' (YES/NO) and the 'Default' values

Hi, tables looks like this...

+----------------------------+-----------------------+------+-----+---
+------+----------------+
| Field                      | Type                  | Null | Key | De
+fault | Extra          |
+----------------------------+-----------------------+------+-----+---
+------+----------------+
| met_office_id              | int(11)               | NO   | PRI | NU
+LL    | auto_increment |
| observation_datetime_utc   | datetime              | NO   |     | NU
+LL    |                |
| observation_datetime_local | datetime              | NO   |     | NU
+LL    |                |
| observation_datetime       | datetime              | NO   |     | NU
+LL    |                |
| station_id                 | char(4)               | NO   | MUL | NU
+LL    |                |
| station_name               | char(25)              | YES  |     | NU
+LL    |                |
| temp_c                     | decimal(4,2)          | YES  |     | NU
+LL    |                |
| dewpoint_c                 | decimal(4,2)          | YES  |     | NU
+LL    |                |
| humidity_rh                | decimal(5,2) unsigned | YES  |     | NU
+LL    |                |
| wind_dir_compass           | char(3)               | YES  |     | NU
+LL    |                |
| wind_speed_mph             | decimal(5,2) unsigned | YES  |     | NU
+LL    |                |
| wind_gust_mph              | decimal(5,2) unsigned | YES  |     | NU
+LL    |                |
| visibility_metres          | mediumint(8) unsigned | YES  |     | NU
+LL    |                |
| pressure_hpa               | decimal(6,2) unsigned | YES  |     | NU
+LL    |                |
| pressure_tendency          | char(1)               | YES  |     | NU
+LL    |                |
| weather_type               | char(2)               | YES  |     | NU
+LL    |                |
+----------------------------+-----------------------+------+-----+---
+------+----------------+
[download]

Fields setup to default to NULL (at least I think so)

As Anonymous Monk suggested, switch to using place holders instead of manifest strings in the SQL. Also, if you aren't already, make sure you are using strictures (use strict; use warnings; - see The strictures, according to Seuss). They will pick up dodgy code and run time errors such as using undef variables in inappropriate ways.

If your problem persists try writing a little test script, maybe using SQLite instead of MySQL (because it's just a CPAN install - no server needed), that demonstrates the issue.

Perl is the programming world's equivalent of English

Must admit I'm not entirely sure how to use placeholders in the context of my code - kind of read one way to do MySQL updates and not sure how to apply different methods to the code I've now written - would using placeholder help with the undef / NULL problem?

It’s been recommended but perhaps not forcefully enough. SQL work without placeholders is tragically, criminally insecure. I know just getting things working is sometimes a necessary first step but placeholders are not something to file under, Hmmm, interesting, but, Say, I could destroy my company with one line of this code.

See also: Exploits of a mom and bobby-tables.com.

I'll do some of the work for you:

my $mo_raw_sql = "
    REPLACE INTO met_office_raw_data
        SET observation_datetime_utc   = ?,
        observation_datetime_local     = ?
";

my $mo_raw = $dbh->do(
    $mo_raw_sql,
    undef,
    $file_obs_datetime_utc,
    $file_obs_datetime_local,
);
[download]

Just fill out the rest and you should be fine, so long as your schema definition allows NULL values.

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".

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.