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

I am trying to read a file and if the record is in my database then update it otherwise insert it. This would be based on a field in the file $Control. Below is the script that I wrote. Can anyone help me with the right syntax. I am inserting into a MS Sql server table. Thanks
use strict; use warnings; use DBI; use DBD::ODBC; if (-e "v:/tf_out.txt") { } else { die "File does not exist\n"; } my ($dbh,$sth); $dbh = DBI->connect( "dbi:ODBC:RETDEV_SQL", "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or die "Unable to connect: ";#. $DBI::errstr . "\n"; # Open your file open( INFILE, "v:/tf_out.txt" ) or die "Couldn't open file for reading +.$!\n"; while( $line = <INFILE> ) { my @data = split( "\t", $line ); $CLIENT = $data[0]; $RECORD = $data[1]; $CONTROL = $data[2]; $CUSIP = $data[3]; $FROMACCT = $data[4]; $F_IND = $data[5]; $TOACCT = $data[6]; $T_IND = $data[7]; $FP = $data[8]; $ORIGQTY = $data[9]; $DIV = $data[10]; $LTCG = $data[11]; $STCG = $data[12]; $SOCSEC = $data[13]; $BKRNO = $data[14]; $THIRDPARTYCD = $data[15]; $USERID = $data[16]; $EDATE = $data[17]; $ORIGAMT = $data[18]; $CLOSEDIND = $data[19]; $ADPSECNO = $data[20]; $BRNCH = $data[21]; $ACNT = $data[22]; $TYPE = $data[23]; $CHKDIGIT = $data[24]; $PDATE = $data[25]; $ACTLQTY = $data[26]; $ACTLAMT = $data[27]; $CERTIND = $data[28]; $REQST = $data[29]; $RDATE = $data[30]; $COMNTS = $data[31]; $COMNTS=~s/\'/''/g; $TRNFTYPE = $data[32]; $FILLER1 = $data[33]; $ADR_L1 = $data[34]; $ADR_L1=~s/\'/''/g; $ADR_L2 = $data[35]; $ADR_L2=~s/\'/''/g; $ADR_L3 = $data[36]; $ADR_L3=~s/\'/''/g; $ADR_L4 = $data[37]; $ADR_L4=~s/\'/''/g; $ADR_L5 = $data[38]; $ADR_L5=~s/\'/''/g; $ADR_L6 = $data[39]; $ADR_L6=~s/\'/''/g; $FILLER_B = $data[40]; $CERTNO1 = $data[41]; $CERTSHRS1 = $data[42]; $CERTNO2 = $data[43]; $CERTSHRS2 = $data[44]; $CERTNO3 = $data[45]; $CERTSHRS3 = $data[46]; $CERTNO4 = $data[47]; $CERTSHRS4 = $data[48]; $CERTNO5 = $data[49]; $CERTSHRS5 = $data[50]; $CERTNO6 = $data[51]; $CERTSHRS6 = $data[52]; $CERTNO7 = $data[53]; $CERTSHRS7 = $data[54]; $CERTNO8 = $data[55]; $CERTSHRS8 = $data[56]; $CERTNO9 = $data[57]; $CERTSHRS9 = $data[58]; $CERTNO10 = $data[59]; $CERTSHRS10 = $data[60]; $FILLER_G = $data[61]; $sth = $dbh->prepare("Select Control From mf_forms where control = $C +ontrol") || die "Prepare failed: " ;#. $DBI::errstr . "\n"; $sth->execute()|| die "Couldn't execute query: ";#. $DBI::errstr . "\n"; my $matches=$sth->rows(); unless ($matches) { my $sql = ("Update mf_forms Set CLIENT = $CLIENT, RECORD = $RECORD +, CONTROL = $CONTROL,CUSIP = $CUSIP,FROMACCT = $FROMACCT"); #print "Sorry, there are no matches\n"; }else{ my $sql = ("INSERT INTO mf_forms (CLIENT,RECORD,CONTROL,CUSIP,FROMACC +T)values ('". $CLIENT. "','" .$RECORD. "','" .$CONTROL. "','" +.$CUSIP. "','" .$FROMACCT."')" ); #print "$matches matches found:\n"; while (my @row = $sth ->fetchrow_array) { print "@row\n"; } } $sth->finish (); $dbh->disconnect ||die " Failed to disconnect\n"; $dbh->do( $sql ) || die "Couldn't insert record. $!\n"; } close(INFILE); $dbh->disconnect;

Replies are listed 'Best First'.
Re: Errors in Code
by Enlil (Parson) on Apr 15, 2003 at 21:41 UTC
    The first thing that comes pops out is that fact that you seem to be disconnecting from the database before you run the last command.(i.e.
    $dbh->disconnect ||die " Failed to disconnect\n"; $dbh->do( $sql ) || die "Couldn't insert record. $!\n";

    The second thing is that in the INSERT statement you are putting single quotes around $CLIENT, $RECORD, etc, which is fine but I would assume that you need to put these single quotes around the same things in the UPDATE statement and the SELECT statement if they are not numerical.

    The last thing is that you should probably have a WHERE clause in your UPDATE statement so that you don't change all the records.


Re: Errors in Code
by boo_radley (Parson) on Apr 15, 2003 at 22:44 UTC
    $COMNTS=~s/\'/''/g; $ADR_L1=~s/\'/''/g; $ADR_L2=~s/\'/''/g; $ADR_L3=~s/\'/''/g;
    Original question aside (Enlil seems to be on track), why aren't you using DBI's quote methods? Also, maybe consider using a hash for all these fields, something like
    foreach (qw(CLIENT RECORD CONTROL ...)) { $hash{$_} =$data[$idx++]; }
Re: Errors in Code
by AcidHawk (Vicar) on Apr 16, 2003 at 09:40 UTC

    Some things I found...

      1. This doesn't compile as you have NOT declared any of your variables.

      2. You are using $CONTROL = $data[2] when you do the split but in your $sth statement you try to select where  control = $Control $CONTROL does NOT equal $Control.

      3. Your logic seems a little confusing, you are saying in the unless($matches) { that if you get no matches you will then update. That seems backwards what can you update if you get NO match and if you DO get a match you will then insert..?

      4. As Enlil says you disconnect before you run the do

    Some good node can be found here at the Monastery talking about SQL queries etc here are a few I found interesting

    Added: You also might like to change

    if (-e "v:/tf_out.txt") { } else { die "File does not exist\n"; }
    to something like
    unless (-e "v:/tf_out.txt") { die "File does not exist\n"; }

    Of all the things I've lost in my life, its my mind I miss the most.
Re: Errors in Code
by dragonchild (Archbishop) on Apr 15, 2003 at 21:09 UTC
    What's the problem you're seeing? Upon quick inspection, it looks like that script should work. (Of course, I have no way of verifying that.)

    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.