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
sduser1Asked:
Who is Participating?
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:
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.
0
sduser1Author Commented:
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
0
FishMongerCommented:
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

0
FishMongerCommented:
The rows() method after a select statement may not work as expected if you check it before all rows are fetched.  But it will work as expected if you change the select statement to return a count.
my $sql = "select count(OBJECT_ID) .... ";

Open in new window

Or, you could check if $row is defined instead of checking its numerical value.
0

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
sduser1Author Commented:
I changed the select statement to return the count and it worked.

Thank you.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.