Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
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;

In reply to Errors in Code by SamueD2

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (3)
As of 2023-10-04 15:51 GMT
Find Nodes?
    Voting Booth?

    No recent polls found