Rename files based on values from a DB

Question

I am very new to Perl and I need to complete a project that is going to rely on it. I need to take pictures that are in a folder, look up their name in a DB and then rename the picture based on another value in that row.

So say I need to rename the picture

010300000000001002.jpg

I need to then have the script look in the db for

010300000000001002

in the USERID. Then when it finds the match I need it to look for the EMPNUM value in the same row. Then take the EMPNUM value and rename the picture to that value.jpg, which in this case equals 1002. So the end product would end up like this:

Old Picture: 010300000000001002.jpg
New Picture: 1002.jpg

Then repeat for all of the pictures in that folder.

Read from the DB Script:

#!/usr/bin/perl -w
use strict;

use DBI;
# Replace datasource_name with the name of your data source.  AdventureWorksDW \dbo.DimEmployee
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/not giving the data source/;
my $user = q/Not giving the user/;
my $password = q/Not Giving the password /;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# Catch and display status messages with this error handler.
sub err_handler {
   my ($sqlstate, $msg, $nativeerr) = @_;
   # Strip out all of the driver ID stuff
   $msg =~ s/^(\[[\w\s:]*\])+//;
   print $msg;
   print "===> state: $sqlstate msg: $msg nativeerr: $nativeerr\n";
   return 0;
}

$dbh->{odbc_err_handler} = \&err_handler;

$dbh->{odbc_exec_direct} = 1;

# Prepare your sql statement (perldoc DBI for much more info).
my $sth = $dbh->prepare('select userid, empnum from dbo.emp0');

# Execute the statement.
if ($sth->execute)
{
    # This will keep returning until you run out of rows.
    while (my $row = $sth->fetchrow_hashref)
    {
        print "ID = $row->{userid}, Name = $row->{empnum}\n";
    }
}

$dbh->disconnect;

Copy and rename file script:

#!/usr/bin/perl -w
use strict;
use warnings;

my $i = 1;
my @old_names = glob "/root/pics/*.jpg";


foreach my $old_name (@old_names) {

    my $new_name = "picture$i" . ".jpg";

    rename($old_name, "/root/pics/$new_name") or die "Couldn't rename $old_name to $new_name: $!\n";

} continue { $i++ }

print "Pictures have been renamed.\n";

Edit: This is what I ended up with and it does the job.

#!/usr/bin/perl -w
use strict;
use File::Copy;
use DBI;

my $data_source = q/db/;
my $user = q/user/;
my $password = q/password/;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# Catch and display status messages with this error handler.
sub err_handler {
   my ($sqlstate, $msg, $nativeerr) = @_;
   # Strip out all of the driver ID stuff
   $msg =~ s/^(\[[\w\s:]*\])+//;
   print $msg;
   print "===> state: $sqlstate msg: $msg nativeerr: $nativeerr\n";
   return 0;
}
$dbh->{odbc_err_handler} = \&err_handler;
$dbh->{odbc_exec_direct} = 1;

sub move_image 
{
    my $user_id = $_[0];
    my $emp_num = $_[1];

    my $old_name = "/root/picS/${user_id}.jpg";

    my $new_name = "/root/picD/${emp_num}.jpg";

    move($old_name, $new_name) or return 0;

    return 1;
}

# Prepare your sql statement (perldoc DBI for much more info).
my $sth = $dbh->prepare('select userid, empnum from dbo.emp0');

# Execute the statement.
if ($sth->execute)
{
    # This will keep returning until you run out of rows.
    while (my $row = $sth->fetchrow_hashref)
    {
        my $user_id = $row->{userid};
        my $emp_num = $row->{empnum};

        if (move_image($user_id, $emp_num))
        {
            print "Moved image $user_id to $emp_num successfully\n";
        }
        else
        {
            print "Could not move image $user_id to $emp_num successfully\n";
        }
    }
}

$dbh->disconnect;

Answer 1

First off, I would suggest using move From File::Copy. The rename subroutine has restrictions on when it can be used. However, if you are certain that both images will always be in the same folder, then rename should be fine and may even be more efficient.

Add the following to the top of your code to be able to use File::Copy:

use File::Copy;

Second, if you want to integrate the functionality here, I would make the "move" code into a subroutine.

sub move_image 
{
    my $user_id = $_[0];
    my $emp_num = $_[1];

    my $old_name = "/root/pics/${user_id}.jpg";

    my $new_name = "/root/pics/picture${emp_num}.jpg";

    move($old_name, $new_name) or return 0;

    return 1;
}

Then call the subroutine:

# This will keep returning until you run out of rows.
while (my $row = $sth->fetchrow_hashref)
{
    my $user_id = $row->{userid};
    my $emp_num = $row->{empnum};

    if (move_image($user_id, $emp_num))
    {
        print "Moved image $user_id to $emp_num successfully\n";
    }
    else
    {
        print "Could not move image $user_id to $emp_num successfully\n";
    }
}

Just add the subroutine to your original script like I showed. Don't try to use two different scripts. Subroutines are very useful.