error in sql query while using DBI

mahicool has asked for the wisdom of the Perl Monks concerning the following question:

Hello everyone
i am using DBI module in database query.I am going to feed data for query by external file. using placeholder in prepare statement. i sent data to placeholder by using execute statement like
$sth ->execute($kk) or die "Couldn't execute statement: " . $sth->errstr;
but its not working and also not giving any error.
is any thing wrong in my code please suggest me.

#!usr/bin/perl -w
use DBI;
use strict;
my $data_source = "xxxxx";
my $user = "track";
my $password = "none";
#conect
#my $dup;
my $dbh = DBI->connect ($data_source, $user, $password)
      or die "Can't connect to $data_source: $DBI::errstr";

my $sth = $dbh->prepare("select MFR_CODE
                           from ED_PART_TRACKING
                          where PART_NUMBER=?")
                           or die "can't prepare statement: $DBI::errs
+tr";

open( FILE, "<", "mahi.txt") or die "Unable to open $.t file";
while(<FILE>)
{
my $kk = $_;
chomp($kk);
print "$kk\n";
$sth ->execute($kk) or die "Couldn't execute statement: " . $sth->errs
+tr;
my ($dup) = $sth->fetchrow_array();
print "$dup";
if($dup eq $kk)
{
print"persent";
}
}
exit;
[download]

Comment on error in sql query while using DBI Download Code

Replies are listed 'Best First'.
Re: error in sql query while using DBI by pc88mxer (Vicar) on Jul 16, 2008 at 16:33 UTC
I don't see any obvious problems, but I would make your script produce more verbose output so it is clear what is going on (at least for debugging purposes): `open(FILE, "<", "mahi.txt") or die "Unable to open mahi.txt: $!"; while (<FILE>) { chomp; print "Looking up PART_NUMBER: >$_<\n"; $sth->execute($_) or die "execute failed: ".$sth->errstr; my ($dup) = $sth->fetchrow_array(); print " Query returned: >$dup<\n"; if ($dup eq $_) { print " PART_NUMBER $_ is present\n"; } else { print " Failed to locate PART_NUMBER >$_<\n"; } }` [download] If you run this script perhaps it will tell you more about what is failing.	[reply] [d/l]
Re: error in sql query while using DBI by moritz (Cardinal) on Jul 16, 2008 at 16:31 UTC
but its not working So what's the behaviour you observe, and what would you expect?	[reply]
Re: error in sql query while using DBI by runrig (Abbot) on Jul 16, 2008 at 17:45 UTC
Rather than having all of the "or die ..." clauses, it would be simpler to use RaiseError on the connect(). You would also get the benefit of error checking on the fetch statement (e.g. deadlocks?).	[reply]
Re: error in sql query while using DBI by LesleyB (Friar) on Jul 16, 2008 at 17:18 UTC
Hi Can't be to helpful without knowing what sort of error you are seeing. Is it failing when attempting to fetch the row or before that? Is the error not in database retrieval at all - e.g. does the file exist and have the data expected? You could use indenting to make the code a little easier to read and you could change that while loop as below or use $_ as pc88mxer has already suggested. `while(my $kk = <FILE>) { chomp($kk); print "$kk\n"; $sth ->execute($kk) or die "Couldn't execute statement: " . $sth->er +rstr; my ($dup) = $sth->fetchrow_array(); print "$dup"; if($dup eq $kk) { print"persent"; } }` [download]	[reply] [d/l]