sduser1
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/s ample.txt" ;
&getFieldFromFile($datafil e,"#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:Ora cle:host=f it.fmr.com ;port=1521 ;service_n ame=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
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/s
&getFieldFromFile($datafil
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:Ora
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
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- 0190F9923C 6E}" isn't numeric in numeric gt (>) at C:/ImageCoversion1.pl line 37, <FILE>
OBJECT_ID does not exist
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-
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-0 190F9923C6 E}" isn't numeric.
Did you mean to check the number of rows the query returned?
Did you mean to check the number of rows the query returned?
if ($sth->rows > 0) {
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I changed the select statement to return the count and it worked.
Thank you.
Thank you.
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:
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.