That's a fierce insert statement to reparse each time. With Oracle you might be lucky because it will find it in its library of recently parsed statements, but all the same...

You want to prepare this statement ahead of time, with something like the following:

my $sth = $db->prepare( qq{
INSERT INTO mf_forms (CLIENT,RECORD,CONTROL,CUSIP,FROMACCT,F_IND,TOACC
+T,T_IND,
FP,ORIGQTY,DIV,LTCG,STCG,SOCSEC,BKRNO,THIRDPARTYCD,USERID,EDATE,
ORIGAMT,CLOSEDIND,ADPSECNO,BRNCH,ACNT,TYPE,CHKDIGIT,PDATE,ACTLQTY,
ACTLAMT,CERTIND,REQST,RDATE,COMNTS,TRNFTYPE,FILLER1,ADR_L1,ADR_L2,ADR_
+L3,
ADR_L4,ADR_L5,ADR_L6,FILLER_B,CERTNO1,CERTSHRS1,CERTNO2,CERTSHRS2,
CERTNO3,CERTSHRS3,CERTNO4,CERTSHRS4,CERTNO5,CERTSHRS5,CERTNO6,CERTSHRS
+6
,CERTNO7,CERTSHRS7,CERTNO8,CERTSHRS8,CERTNO9,CERTSHRS9,CERTNO10,CERTSH
+RS10,FILLER_B
) VALUES (
?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?)
}) or die "Cannot prepare statement: ${\$db->errstr}\n";

[download]

I might note in passing that you had written VALUE, not VALUES, which will cause an SQL syntax error. In your loop, this prepared statement can then be called thusly:

$sth->execute($CLIENT,$RECORD,$CONTROL,$CUSIP,$FROMACCT,$F_IND,$TOACCT
+,$T_IND,
$FP,$ORIGQTY,$DIV,$LTCG,$STCG,$SOCSEC,$BKRNO,$THIRDPARTYCD,$USERID,$ED
+ATE,
$ORIGAMT,$CLOSEDIND,$ADPSECNO,$BRNCH,$ACNT,$TYPE,$CHKDIGIT,$PDATE,
$ACTLQTY,$ACTLAMT,$CERTIND,$REQST,$RDATE,$COMNTS,$TRNFTYPE,$FILLER1,$A
+DR_L1,$
ADR_L2,$ADR_L3,$ADR_L4,$ADR_L5,$ADR_L6,$FILLER_B,$CERTNO1,$CERTSHRS1,$
+CERTNO2,$CERT
SHRS2,$CERTNO3,$CERTSHRS3,$CERTNO4,$CERTSHRS4,$CERTNO5,$CERTSHRS5,$CER
+TNO6,$CERTSHR
S6,$CERTNO7,$CERTSHRS7,$CERTNO8,$CERTSHRS8,$CERTNO9,$CERTSHRS9,$CERTNO
+10,$CERTSHRS10,$FILLER_B)
  or die "Could not insert: ${\$sth->errstr}\n";

[download]

You will note that it is a bit of a bear to create the INSERT statement, and to make sure that you have sufficient ?s (or placeholders) for your columns being inserted. Be lazy, and get Perl to do it for you:

my @columns = qw/
CLIENT RECORD CONTROL CUSIP FROMACCT F_IND TOACCT T_IND 
FP ORIGQTY DIV LTCG STCG SOCSEC BKRNO THIRDPARTYCD USERID EDATE 
ORIGAMT CLOSEDIND ADPSECNO BRNCH ACNT TYPE CHKDIGIT PDATE ACTLQTY 
ACTLAMT CERTIND REQST RDATE COMNTS TRNFTYPE FILLER1 ADR_L1 ADR_L2 ADR_
+L3 
ADR_L4 ADR_L5 ADR_L6 FILLER_B CERTNO1 CERTSHRS1 CERTNO2 CERTSHRS2 
CERTNO3 CERTSHRS3 CERTNO4 CERTSHRS4 CERTNO5 CERTSHRS5 CERTNO6 CERTSHRS
+6
 CERTNO7 CERTSHRS7 CERTNO8 CERTSHRS8 CERTNO9 CERTSHRS9 CERTNO10 CERTSH
+RS10 FILLER_B
/;

my $sql = 'inset into mf_forms ('
    . join( ',', @columns )
    . ') values ('
    . join( ',', ('?') x @columns )
    . ')';

[download]

This way you eliminate the need to keep the placeholders in sync. Binding the variables remains a problem, but there are ways around that too, although more complex to set in motion. Come to think of it, as you already have an array with the values, you are already set, just pass it as the parameter like this $sth->execute(@data). Easy peasy.

Uh, hang on, you split to @cols and refer to @data. That's not going to work, you know?


print@_{sort keys %_},$/if%_=split//,'= & *a?b:e\f/h^h!j+n,o@o;r$s-t%t#u'

In reply to Re: Problem with code by grinder
in thread Problem with code by SamueD2

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


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.