I have a script which function is to parse through a comma seperated data
file, check if the entry is already existing, then if not enter the data
into a mysql table. However, it only seems to be finding a match on the
third time around. ie if 'john brown' is in the data file it will only
recognise it as a match the second 'john brown'. It would almost seem the
data from the previous row though inserted has not been registered. see code
snippet below
<snippet>
#! /usr/bin/perl -w
use DBI;
read_file();
sub insert_data {
my @$value = @_;
my $rdate=get_date();
my $p01=$dbh->quote ("0"); # own_id
my $p02=$dbh->quote ($rdate); # prop_date_lmod
...........repetive code removed.................
my $p12=$dbh->quote ($value->[9]); # own_mobile_phone
my $sql = "INSERT INTO owner_info_inglewood
VALUES ($p01,$p02,$p03,$p04,$p05,$p06,
$p07,$p08,$p09,$p10,$p11,$p12
);";
my $sth_i = $dbh->prepare ($sql) or err_trap("failed to prepare
statement\n");
$sth_i->execute() or err_trap("failed to execute statement\n");
$sth_i->finish();
$dbh->commit() or err_trap("failed to execute statement\n");
}
sub verify_row {
my @$vrow = @_;
my $name_l = $dbh->quote($vrow->[0]);
my $name_f = $dbh->quote($vrow->[1]);
my $own_str_addr = $dbh->quote($vrow->[4]);
my $own_city_addr = $dbh->quote($vrow->[5]);
## query property info table to retrieve property id.
my $sql = ("SELECT own_name_l,own_name_f,
own_str_addr,own_city_addr
FROM owner_info_inglewood
WHERE own_name_l LIKE $name_l
AND own_name_f LIKE $name_f
AND own_str_addr LIKE $own_str_addr
AND own_city_addr LIKE $own_city_addr;")
or err_trap('failed to prepare statement\n');
my $sth_v = $dbh->prepare ($sql) or err_trap("failed to prepare
statement\n");
$sth_v->execute or err_trap("failed to execute statement\n");
my @own_name = $sth_v->fetchrow();
$sth_v->finish();
return @own_name;
}
sub read_file {
my @own_name;
open (SDDB, "/home/rowan/perl/ho_info.csv") || die print "::$!\n";
$dbh = connect_try("rowan","******");
while (<SDDB>) {
chomp $_;
my $row = [(split (/,/,$_))];
## verify existance of property and retrieve property id.
unless (verify_row(@$row)) {
my $frow = format_data(@$row);
insert_data(@$frow);
}else{
@own_name = verify_row(@$row);
print "read_file::duplicate found ->
$own_name[0],$own_name[1],$own_name[2],$own_name[3]\n";
print "read_file::duplicate original->
$row->[0],$row->[1],$row->[4],$row->[5]\n";
}
}
$dbh->disconnect or err_trap("failed to disconnect statement\n");
close SDDB;
}
sub connect_try {
my ($db_user,$db_password) = @_;
my $dbh = DBI->connect("dbi:mysql:studio_3:67.**.**.**", "$db_user",
"$db_password",{AutoCommit => 0 }) or
err_trap("error connecting to DB");
return $dbh;
}
</snippet
+++++++++++++++++++++++++++++++
++REMOVE _NO_SPAM from return ++
++e-mail address in order to respond. ++
+++++++++++++++++++++++++++++++
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
