Perl DBI and sql now()

Question

I have been trying to use sql NOW() function while I update a table. But nothing happens to that date field, I think DBI just ignores that value. Code is :

dbUpdate($id, (notes => 'This was an update', filesize => '100.505', dateEnd => 'NOW()'));

and the function is :

sub dbUpdate {
    my $id = shift;
    my %val_hash = @_;
    my $table = 'backupjobs';

    my @fields = keys %val_hash;
    my @values = values %val_hash;

    my $update_stmt = "UPDATE $table SET ";
    my $count = 1;
    foreach ( @fields ) {
        $update_stmt .=  "$_ = ? ";
        $update_stmt .=  ', ' if ($count != scalar @fields);
        $count++;
    }
    $update_stmt .= " WHERE ID = ?";
    print "update_stmt is : $update_stmt\n";
    my $dbo = dbConnect();
    my $sth = $dbo->prepare( $update_stmt );
    $sth->execute( @values, $id ) or die "DB Update failed : $!\n";
    $dbo->disconnect || die "Failed to disconnect\n";
    return 1;
}#dbUpdate

As you can see this is a "dynamic" generation of the sql query and hence I dont know where an sql date function(like now()) come.

In the given example the sql query will be

UPDATE backupjobs SET filesize = ? , notes = ? , dateEnd = ?  WHERE ID = ?

with param values

100.55, This was an update, NOW(), 7

But the date column still shows 0000-00-........

Any ideas how to fix this ?

Answer 1

I have been trying to use sql NOW() function while I update a table. But nothing happens to that date field, I think DBI just ignores that value.

No, it doesn't. But the DB thinks you are supplying a datetime or timestamp data type when you are in fact trying to add the string NOW(). That of course doesn't work. Unfortunately DBI cannot find that out for you. All it does is change ? into an escaped (via $dbh->quote()) version of the string it got.

There are several things you could do:

1. Supply a current timestamp string in the appropriate format instead of the string NOW().

   If you have it available, you can use DateTime::Format::MySQL like this:

   use DateTime::Format::MySQL;
   dbUpdate(
     $id,
     (
       notes => 'This was an update',
       filesize => '100.505',
       dateEnd => DateTime::Format::MySQL->format_datetime(DateTime->now),
     )
   );
   

   If not, just use DateTime or Time::Piece.

       use DateTime;
       my $now = DateTime->now->datetime;
       $now =~ y/T/ /;
       dbUpdate(
         $id,
         (notes => 'This was an update', filesize => '100.505', dateEnd => $now));
   

2. Tell your dbUpdate function to look for things like NOW() and react accordingly.

   You can do something like this. But there are better ways to code this. You should also consider that there is e.g. CURRENT_TIMESTAMP() as well.

   sub dbUpdate {
       my $id = shift;
       my %val_hash = @_;
       my $table = 'backupjobs';
   
       my $update_stmt = "UPDATE $table SET ";
   
       # Check for the NOW() value
       # This could be done with others too
       foreach ( keys %val_hash ) {
         if ($val_hash{$_} =~ m/^NOW\(\)$/i) {
           $update_stmt .= "$_ = NOW()";
           $update_stmt .= ', ' if scalar keys %val_hash > 1;
           delete $val_hash{$_};
         }
       }
   
       # Put everything together, join keeps track of the trailing comma
       $update_stmt .= join(', ', map { "$_=?" } keys %val_hash );
       $update_stmt .= " WHERE ID = ?";
       say "update_stmt is : $update_stmt";
       say "values are: ", join(', ', values %val_hash);
   
       my $dbo = dbConnect();
       my $sth = $dbo->prepare( $update_stmt );
       $sth->execute( values %val_hash, $id ) or die "DB Update failed : $!\n";
       $dbo->disconnect || die "Failed to disconnect\n";
       return 1;
   }
   

3. Write your queries yourself.

   You're probably not going to do it and I'll not add an example since you know how to do it anyway.

---

Here's something else: Is this the only thing you do with your database while your program runs? It is not wise to connect and disconnect the database every time you make a query. It would be better for performance to connect the database once you need it (or at the beginning of the program, if you always use it) and just use this dbh/dbo everywhere. It saves a lot of time (and code).