Link to home
Start Free TrialLog in
Avatar of sduser1
sduser1Flag for United States of America

asked on

Validating the data using Oracle DBD module.

Please help me with this. I am stuck in comparing the value from the text file to a database field.

I am reading a text file and getting each field from the file and connecting to the database to see if there is a value in the database. In the below code,  it is always going to the else loop and was never executing the if loop. Some of the fields are strings and some of the fields are numbers in the text file.

Below is the code.

 #!/usr/bin/perl

use DBI;

use DBD::Oracle;

my $datafile = "C:/Perl/bin/PerlScripts/sample.txt";

&getFieldFromFile($datafile,"#OBJECT_ID");

sub getFieldFromFile {

my ($datafile,$Field) = @_;

open(FILE,$datafile) or die "No, can't open the file";
while (my $line = <FILE>) {
chomp($line);
# get the fields from the text file
my ($OBJECT_ID, $DocumentTitle, $AccountNumber, $BatchID,
$BatchCount, $BrokerDealer, $CertNumber, $CheckApproved,
$CheckNumber, $ClientID, $Comment, $DocSetCount)
= split( /\|/, $line, 5);

if ($Field eq "#OBJECT_ID")

{

my $dbh=DBI->connect("dbi:Oracle:host=fit.fmr.com;port=1521;service_name=fbctd","CONV", "fbctd4_conv_app_userdev");
my $sql = "select OBJECT_ID from metlife_indices where OBJECT_ID = '$OBJECT_ID'";      # the query to execute
my $sth = $dbh->prepare($sql);          # prepare the query
$sth->execute();                        # execute the query
my @row;
 my $row;
while (($row) = $sth->fetchrow_array) {  # retrieve one row

 my $row;
   
   if ($row >0) {
          
     print "OBJECT_ID exists in DB\n";
     
    }
          else {
                
          print "OBJECT_ID does not exist\n";      
          }
}
 
 
} # end of IF loop

} # end of While loop

}# end of sub routine
Avatar of FishMonger
FishMonger
Flag of United States of America image

There are a number of problems with the script but the first one is that it's missing the strict and warnings pragmas which would inform you of several of the problems.

Add those 2 pragmas and fix the issues they point out.

One issue which may or may not show up in the issues raised by the pragmas is this section:
while (($row) = $sth->fetchrow_array) {  # retrieve one row

 my $row;
   
   if ($row >0) {

Open in new window

The while loop initialization assigns $row then you immediately overwrite that when you declared my $row, which is the reason why it always goes the the else block.

Actually, you didn't overwrite the var.  They are both lexical vars declared in different scopes and your conditional is testing the wrong var.
Avatar of sduser1

ASKER

Thank you. I have added pragmas and changed the below in the section of the script that you mentioned.
while ((my $row) = $sth->fetchrow_array) {  # retrieve one row
   
   if ($row >0)  {
          
     print "OBJECT_ID exists in DB\n";
     
    }
          else {
                
          print "OBJECT_ID does not exist\n";      
          }
}

I cleaned up few things. I see it is still going to the else loop. Below is the error I got.

Argument "{C8D0D04B-2B5E-46F0-B3A1-0190F9923C6E}" isn't numeric in numeric gt (>) at C:/ImageCoversion1.pl line 37, <FILE>
OBJECT_ID does not exist
Your conditional is is doing a numerical test to see if the value in $row is greater than 0.  The error is telling you that "{C8D0D04B-2B5E-46F0-B3A1-0190F9923C6E}" isn't numeric.

Did you mean to check the number of rows the query returned?
if ($sth->rows > 0) {

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of FishMonger
FishMonger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sduser1

ASKER

I changed the select statement to return the count and it worked.

Thank you.