I’ve been writing a perl script which has to read and update a MySQL database which uses MySQL’s own date functions. Using Perl’s DBI interface this is easy as the SQL statements are up to the programmer however I’ve been moving into the 21st century a little and using ORM. As it’s the one I started with I’m using Class::DBI (yes I know that DBIx::Class is more popular but I’m not using it yet) and Class::DBI does not include a way to call MySQL date functions in update.
Google didn’t provide a quick answer on how to do this however Class::DBI does provide a way to do this. It’s called set_sql and it allows one to declare a custom method on an object which can run any SQL statement. Using this is a 2 stage process – declare the new method and then execute it. The easiest way to show this is with an example:
MyDB->set_sql(“NextMonth”, << ”);
set field = DATE_ADD(field, INTERVAL 1 MONTH)
my $obj = MyDB->retrieve($id);
In the example above the set_sql statement creates a new method called sql_NextMonth which simply updates the field called “field” to increment the date by one month. Obviously it’s expected that “field” will be a date or datetime data type.
This method is then executed by calling sql_NextMonth()->execute($id) where $id is the record identifier.