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

Hi Monks,
This is a piece of my code that when it runs it prints to the screen what I want, but when I want to do an INSERT of the values into a new table it is only dropping two values into the new DB table, where or what am I missing here?
Thank you!!! 
while (my $pointer = $sth->fetchrow_hashref){

$CT5 = $pointer->{'CT5'};
$STR = $pointer->{'STR'};
$FNAME = $pointer->{'FNAME'};
$LNAME = $pointer->{'LNAME'};

$EMAIL=$EMFNAME.$EMLNAME."\@tstcompany.com";
$EMAIL=~s/\s+//;
$EMAIL=~s/\s+//;
$EMAIL = lc($EMAIL);

$sql="INSERT INTO email (f_name, l_name, code, ext, email) 
                VALUES ('$FNAME', '$LNAME', '$STR', '$CT5', '$EMAIL')"
+; 
#$sth = $dbh->prepare($sql);
#$sth->execute() || die $sth->errstr;
#$sth->finish;

print "<br><b>EXT# = $CT5 / Code = $STR / First Name = $FNAME / Last N
+ame = $LNAME / E-Mail = $EMAIL</b> ";

}

$sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;
$sth->finish;

[download]

Replies are listed 'Best First'.
Re: INSERT Problem
by matija (Priest) on Apr 28, 2004 at 18:47 UTC
    You have the prepare and execute inside the loop commented out, and only the one outside the loop is active.

    Whoa! I just realized the prepare statement in the loop overwrites the $sth you use for fetching. Use another name for the handle, like $ins.

    Incidentaly, you could move the prepare statement before the loop, if you did it like this:

    $sql="INSERT INTO email (f_name, l_name, code, ext, email) 
                VALUES (?, ?, ?, ?, ?)"; 
$ins=$dbh->prepare($sql);

    [download]
    and replace the execute with 
    $sth->execute($FNAME, $LNAME, $STR, $CT5, $EMAIL) || die $sth->errstr;

    [download]
    Not only is that more maintainable (IMHO), but it should also be faster, since it only has to parse the SQL statements once. In addition, it protects you from things like single quotes in your input.
[reply]
[d/l]
[select]
Re: INSERT Problem
by b10m (Vicar) on Apr 28, 2004 at 18:50 UTC

    You'd better go check your logs to see what the script is complaining about (I bet there's some useful info in there ;-)

    I would slightly alter your code though, using placeholders (and die with the error message):

    $sth = $dbh->prepare("INSERT INTO email (f_name, l_name, code, ext, em
+ail)
                      VALUES (?,?,?,?,?)");
$sth->execute($FNAME, $LNAME, $STR, $CT5, $EMAIL) || 
   die $sth->errstr;

    [download]

    And don't forget we have some nice Tutorials on DBI related subjects :)

    --
    b10m

    All code is usually tested, but rarely trusted.
[reply]
[d/l]
      Still doesn't work I am reading from two different DB and while I am reading from the first DB I am inserting the data into the other table in another DB.
[reply]
        please show your 'new' attempt

[reply]
Re: INSERT Problem
by perlinux (Deacon) on Apr 29, 2004 at 08:06 UTC
    You have to put your prepare out of the while...it's incorrect! :-) It's the power of execute in DBI module: you have a generic statement and then (in the while loop) you use execute with your params! See perldoc DBI...

[reply]

Back to Seekers of Perl Wisdom