parsing data file into mysql table - help

[smrtalec]

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. ++
+++++++++++++++++++++++++++++++

 

[Bob Walton]

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

------------------------------^^^^^^

This seems unclear: is it the third time or the second time that the
problem happens? Or do you mean after the second time the name is
inserted and the third time it is attempted to be looked up? Also, the
lookup you do is based on more than just the name -- does the address
and city match also in your problem data?

data from the previous row though inserted has not been registered. see code
snippet below

Well, your example isn't complete enough to attempt to debug without
putting much more work into it than I feel like doing. So I'll just
make some comments:


....

#! /usr/bin/perl -w

use DBI;

Missing "use strict;" -- let Perl give you all the help it can.

read_file();

sub insert_data {
my @$value = @_;

---------^^^^^^^
Does that actually work? It doesn't in Perl version 5.8.0. Are you
running an ancient version of Perl, like 5.005? What about DBI? And
mySQL? If so, save yourself (and us) grief and upgrade. Better would
probably be:

my $value=[@_];

But that is also awkward and tougher to understand than necessary. Why
not just pass around an array reference?

my $rdate=get_date();

What is 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
);";

It would be good to learn about and use placeholders here. Then you
wouldn't have to worry about all the quoting.

my $sth_i = $dbh->prepare ($sql) or err_trap("failed to prepare
statement\n");

If you use {RaiseError=>1} in your database connection, you can avoid
all the awkward error checking. And then you don't have to worry about
missing one or trying to test them all somehow.

$sth_i->execute() or err_trap("failed to execute statement\n");
$sth_i->finish();

Why the call to finish()? It probably does no harm, but is clearly not
needed in this situation. It is for SELECT statements that return
multiple rows and are terminated early.

$dbh->commit() or err_trap("failed to execute statement\n");
commit??------------------------------------^^^^^^^


}

sub verify_row {
my @$vrow = @_;

??-------^^^^^^-------see above

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');

This "or" clause will never be executed, as the given string value will
always be true. And if it were executed, the error message would be
misleading, as no prepare statement would have been involved.

my $sth_v = $dbh->prepare ($sql) or err_trap("failed to prepare
statement\n");

sub err_trap isn't shown. Does it die()?

$sth_v->execute or err_trap("failed to execute statement\n");

Does err_trap include the value of $dbh->errstr in the message it
prints? Hopefully so, as that will give the reason for the error.

my @own_name = $sth_v->fetchrow();

There is no "fetchrow" method in DBI (well, not documented, anyway).
Perhaps you meant fetchrow_array?

$sth_v->finish();

If you're only fetching one row, you should probably call
selectrow_array instead of the whole prepare...execute...fetch sequence.
Then the call to finish() would not be needed. And the statement
handle will effectively be finished anyway when the handle variable goes
out of scope.

return @own_name;

You are returning an array here, and using it in the call as a scalar.
This is technically OK, but usually one would expect call and returns to
match in usage.

}

sub read_file {
my @own_name;
open (SDDB, "/home/rowan/perl/ho_info.csv") || die print "::$!\n";
huh?-------------------------------------------------^^^^^^^^^


$dbh = connect_try("rowan","******");

Odd logic here -- a sub named "read_file" also opens and closes a
database. Seems pretty misleading to me -- I wouldn't expect that side
effect out of a sub with that name.

Also, you should declare:

my $dbh;

back in your main program, as $dbh is intended to be in the scope of
most of your subs. For clarity (in my opinion), you should open and
close the database there also, or call subs to do it from there.

while (<SDDB>) {
chomp $_;
my $row = [(split (/,/,$_))];
## verify existance of property and retrieve property id.
unless (verify_row(@$row)) {

You sub verify_row returns an array.

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",

useless use of quotes--------------------------------------^--------^

"$db_password",{AutoCommit => 0 }) or

--^------------^--useless use of quotes. Note that this could be more
than just two wasted keystrokes -- with the quotes, the variable will be
stringified if it wasn't already a string.

err_trap("error connecting to DB");
return $dbh;
}

....


I don't see where your program would have the described behavior, but
the example is too complex to attempt to debug it with the supplied code
and info. I recommend you boil your example down to a complete
self-contained piece of code including data which can be
cut/paste/executed on anyone's computer (perhaps using DBD::CSV instead
of mySQL) and shows the problem you are having. Also use warnings; and
use strict;.

 

[gnari]

#! /usr/bin/perl -w

there is a space after #!
I have no idea if it matters, but it scares me
use strict;
also, instead of -w :
use warnings;

always use strict, specially if you are having problems
use warnings too, at least when debugging

[snip]

my @$value = @_;

don't do this

[snipped positional INSERT with $dbh->quote()'s]
general form:
my $sql = "INSERT INTO footable (col1,col2,col3...)
VALUES (?,?,?...)";
my $sth_i = $dbh->prepare ($sql);
my $res=$sth_i->execute($val1,$val2,$val3);
add error handling to taste

sub verify_row {
my @$vrow = @_;
my $name_l = $dbh->quote($vrow->[0]);

don't do this
my @vrow = @_;
my $name_l = $dbh->quote($vrow[0]);
but look at placeholders here too

[snip more of the same]

while (<SDDB>) {
chomp $_;
my $row = [(split (/,/,$_))];

what is this fixation on arrayrefs?
then you do all these @$row constructs

gnari

 

[Bob Walton]

You've got AutoCommit set to off when you connect -- your database wont see
the inserted data until after you issue a $dbh->Commit();

c-------------------------------------------------^

Case matters.