Passing Parameters to Perl DBI Queries

When we run SQL Queries in Perl using DBI Module, the below mentioned code will prepare and execute the query. This will prepare the query every time, the query is executed.
This will increase the total execution of the script.


Psuedo Code: 
my $dbh = DBI->connect($dbname,$dbuser,$dbpass);

while(<INPUT>)
{
    my $my_emp_id = chomp;
    my $query = "select emp_id,emp_name,dept
                from emp_table
                where
                emp_id = '$my_emp_id'";
    my $query_handle = $dbh->prepare($query) or die print "Error in Query";
    $query_handle = $dbh->execute();
    while(my @result = $query_handle->fetchrow_array())
    {
        print OUT "@result\n";
    }
    $query_handle->finish()
}

The above mentioned issue can be handled by preparing the query once and passing the variable in the query dynamically.

my $query = "select emp_id,emp_name,dept
from emp_table
where
emp_id = :p1";
#Passing the parameter to the Query as :p1. if more than one parameters are to be passed :p2. :p3 can be used.
 
my $dbh = DBI->connect($dbname,$dbuser,$dbpass);
my $query_handle = $dbh->prepare($query) or die print "Error in Query";
#passing the value of the parameters passed as :p1
 
while(<INPUT>)
{
    my $my_emp_id = chomp;
    $query_handle = $dbh->execute($my_emp_id);
    while(my @result = $query_handle->fetchrow_array())
    {
        print OUT "@result\n";
    }
    $query_handle->finish()
}
I hope this is informative.

OBIEE 10g and 11g Training Program

Hi Fellas,

For a Very Good Training Program on OBIEE 10g and 11g by an Expert Trainer. Follow OBIEE Training

Hope you all have a good time..!