parsing data file into mysql table - help

Discussion in 'Perl Misc' started by smrtalec, Feb 22, 2004.

  1. smrtalec

    smrtalec Guest

    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. ++
    +++++++++++++++++++++++++++++++
     
    smrtalec, Feb 22, 2004
    #1
    1. Advertising

  2. smrtalec

    Bob Walton Guest

    smrtalec wrote:

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

    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Bob Walton, Feb 22, 2004
    #2
    1. Advertising

  3. smrtalec

    gnari Guest

    "smrtalec" <> wrote in message
    news:3vUZb.21103$...
    >
    > #! /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
     
    gnari, Feb 22, 2004
    #3
  4. smrtalec

    Bob Walton Guest

    Greg Patnude wrote:

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

    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Bob Walton, Feb 23, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. impulse()
    Replies:
    0
    Views:
    2,540
    impulse()
    Oct 13, 2006
  2. Hans Müller
    Replies:
    4
    Views:
    385
    Hans Müller
    Dec 18, 2007
  3. julian
    Replies:
    8
    Views:
    451
    Avatar
    Apr 6, 2006
  4. John Levine
    Replies:
    0
    Views:
    733
    John Levine
    Feb 2, 2012
  5. Sam
    Replies:
    4
    Views:
    73
    Mark Lawrence
    Jan 17, 2014
Loading...

Share This Page