Perl efficient DB Call

I wrote the below script to update the file.
The script works but I had a small concern with the script I wrote, it makes multiple database calls.
Is there any way you think I can modify this and make just one DB call.

This is what the script currently does:

1)      Go thru each line of the file and get the 3 fields that are required ($fields[6], $fields[8], $fields[12])
2)      Run query and get the results using the 3 fields
3)      Print output file line by line by appending the results at the end of each line.

#!/usr/bin/perl

use 5.006;
use strict;
use warnings;
use Pod::Usage;
use Getopt::Long;
use Cwd;
use DateTime;
use DBD::mysql;
use DBI;

my $helpme = 0;
my $man = 0;
my $inputFileName = 'C:\\temp\\test.txt';
my $outputFileName = 'C:\\temp\\output.txt';
my $dsn = 'DBI:ODBC:Driver={SQL Server}';
my $host = 'testdb';
my $database = 'MYAPP';
my $user = 'admin';
my $auth = 'p@ssword';


GetOptions('help' => \$helpme, 'man' => \$man, 'infile=s' => \$inputFileName, 'outfile=s' => \$outputFileName) or pod2usage(2);

pod2usage(1) if $helpme;
pod2usage(-verbose => 2) if $man;

die "No input file name specified!" if !$inputFileName;
die "No output file name specified!" if !$outputFileName;
my $dbh = DBI->connect("$dsn;Server=$host;Database=$database", $user, $auth,{ RaiseError => 1, AutoCommit => 1}) || die "Database connection not made: $DBI::errstr"; 

open(INFILE, "<", $inputFileName) || die "Could not open input file!";
open(OUTFILE, ">", $outputFileName) || die "Could not open/create output file!";

while (<INFILE>) {
    chomp;
   my @fields = split /\s*\|\s*/; 
   my $sql = "SELECT e.id1, d.id2, s.id3   
              FROM  Employee e 	
              JOIN  Department d 
                      ON d.did = '$fields[12]' AND d.status ='A'
              JOIN  Student s  
                      ON s.sid= '$fields[6]' AND s.status ='A'	 
              Where e.eid= '$fields[8]' AND e.status ='A'";
   my $sth = $dbh->prepare($sql);
   $sth->execute();
   while (my @row = $sth->fetchrow_array) {
    print OUTFILE $_."|".$row[0]."|".$row[1]."|".$row[2]."\n";
   }    
}
close INFILE;
close OUTFILE;

Open in new window


Thanks,
shragiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FishMongerCommented:
The first thing I'd recommend is to use placeholders in the sql statement and move the prepare statement to just before the loop.

Can you post a reasonable sample of the input file?
shragiAuthor Commented:
Sample file is something like the attached.
shragiAuthor Commented:
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

shragiAuthor Commented:
well if i want to append at the end of the file I can see that I create the sql first and execute it at the end, but i need to modify each line
FishMongerCommented:
Your sample input data shows that there is a total of 12 fields in each row.  Since arrays are 0 indexed, @fields will be indexed 0-11 , not 1-12.

I don't see anyway to achieve your goal with only 1 sql statement execution.  You need to execute it once for each line.  However, the prepare statement is a different matter.  It's more efficient to do the prepare statement once (outside of the loop) and do the execute statement inside the loop.

On an OT note, there are some stylistic issues that makes the code a little less readable than it should be.  And, questionable naming conventions for several of the vars.  When a var is made up of several words, it's a lot easier to read if you join them with an underscore instead using TitleCase.  It's also better to use lexical vars for filehandles instead of barewords.  And finally, you should keep your line lengths below 80 characters and line up elements of a list in column format if/when they span multiple lines.

Here's an adjusted version which addresses those issues.
#!/usr/bin/perl

use 5.006;
use strict;
use warnings;
use Pod::Usage;
use Getopt::Long;
use Cwd;
use DateTime;
use DBD::mysql;
use DBI;

my $helpme         = 0;
my $man            = 0;
my $dsn            = 'DBI:ODBC:Driver={SQL Server}';
my $host           = 'testdb';
my $database       = 'MYAPP';
my $user           = 'admin';
my $auth           = 'p@ssword';
my $input_file;#     = 'C:/temp/test.txt';
my $output_file;#    = 'C:/temp/output.txt';

GetOptions('help'      => \$helpme,
           'man'       => \$man,
           'infile=s'  => \$input_file,
           'outfile=s' => \$output_file)
or pod2usage(2);

pod2usage(1) if $helpme;
pod2usage(-verbose => 2) if $man;
pod2usage("No input file name specified!") if ! $input_file;
pod2usage("No output file name specified!") if ! $output_file;


my $dbh = DBI->connect("$dsn;Server=$host;Database=$database", $user, $auth,
                       { RaiseError => 1, AutoCommit => 1})
       || die "Database connection not made: $DBI::errstr"; 

open(my $in_fh, "<", $input_file) || die "Could not open $input_file <$!>";
open(my $out_fh, ">", $output_file) || die "Could not create $output_file <$!>";

my $sql = "SELECT e.id1, d.id2, s.id3   
           FROM  Employee e 	
           JOIN  Department d 
                   ON d.did = ? AND d.status ='A'
           JOIN  Student s  
                   ON s.sid= ? AND s.status ='A'	 
           Where e.eid= ? AND e.status ='A'";
my $sth = $dbh->prepare($sql);

while (<$in_fh>) {
    chomp;
    my ($did, $sid, $eid) = (split /\s*\|\s*/)[11,5,7]; 
    $sth->execute($did, $sid, $eid);
    
    while (my @row = $sth->fetchrow_array) {
        print $out_fh join('|', $_, @row) , "\n";
    }    
}
close $in_fh;
close $out_fh;

Open in new window


Since you didn't include the POD section, I left it out as well.  I'll assume it's included in your full script like I demonstrated in a prior related thread of yours.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FishMongerCommented:
I did not do any testing, but your join statements look a little fishy.  Do they actually do what you need?
wilcoxonCommented:
FishMonger is correct in most cases.  However, there are databases where using placeholders is less efficient than executing a static sql each time (Sybase is (or at least was) one).
FishMongerCommented:
FishMonger is correct in most cases.
What?  You mean that I'm not perfect?
:-)  lol  ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.