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;