DBD::MySQL bind variable error

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

Good day monks. I am writing an app to take e-mails out of my MS Outlook client and store them in a MySQL db. Everything goes along fine for about 425 messages and then I get an error: "DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed at E:\mail\mailtosql.pl line 111."

Line 111 is the third line of:

   
if ($msg->{Body}) {
        my $sth = $dbh->prepare($command);
        $sth->execute($msg->{Body}) || die "could not execute MySQL st
+atement: $command";
        $sth->finish();
    }
[download]

and the $command in question is

$command="insert into mailstore.received (SenderName,SenderEmailAddres
+s,SentOnBehalfOfName,ReplyRecipientNames,SenderEmailType,SentOn,Recei
+vedTime,MessageClass,Size,Subj,SentTo,Unread,InternetCodepage,importa
+nce,EntryID,ConversationIndex,ConversationTopic,Class,BodyFormat,Body
+) values ('Bob O\'Neill','bob.oneill@polit.org','Bob O\'Neill','bob.o
+neill@polit.org','SMTP',20060407143605,20060407150746,'IPM.Note',2792
+2,'FW: Less Than a Month to Register - 2nd Annual Sciences Forum','Bo
+b O\'Neill',1,20127,1,'000000007AD91AAD8C99D111B2A90000F843DC060700E4
+22DA54DFB9D011B2710000F843DC0600000170A97100001CA7D120557E1744BFE304E
+F057275DB000000BBAA130000','01C65A8FB33408E229DC00114BA1BB7DBD1E5F218
+50E','Less Than a Month to Register - 2nd Annual Sciences Forum',43,2
+,?)";
[download]

As you can see from the $command, there is one bind at the end, and $msg->{Body} is not null or the insert command would never execute, so I'm a little stumped as to how I can need zero binds. Your monastic thoughts appreciated.

TIA...

Steve

Comment on DBD::MySQL bind variable error Select or Download Code

Replies are listed 'Best First'.
Re: DBD::MySQL bind variable error by japhy (Canon) on May 12, 2006 at 01:20 UTC
You should print $command. You'll notice all your backslashed single quotes aren't backslashed anymore. And your @polit's are probably missing too. That's what you get for using a double-quoted string! $command = q{ insert into mailstore.received (SenderName, SenderEmailAddress, SentOnBehalfOfName, ReplyRecipientN +ames, SenderEmailType, SentOn, ReceivedTime, MessageClass, Size, Subj, Se +ntTo, Unread, InternetCodepage, importance, EntryID, ConversationIndex, ConversationTopic, Class, BodyFormat, Body) values ("Bob O'Neill", "bob.oneill@polit.org", "Bob O'Neill", "bob.oneill@p +olit.org", "SMTP", 20060407143605, 20060407150746, "IPM.Note", 27922, "FW: Less Than a Month to Register - 2nd Annual Sciences Forum", "B +ob O'Neill", 1, 20127, 1, "000...0000", "01C65....1850E", "Less Than a Month to Register - 2nd Annual Sciences Forum", 43, 2, + ?) }; [download] Why are you hard-coding all those fields, though? Why not send all (or many) of them in the execute()? Jeff `japhy` Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and `perl` hacker How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart	[reply] [d/l]
Re: DBD::MySQL bind variable error by Errto (Vicar) on May 12, 2006 at 19:44 UTC
In addition to japhy's comment, I should also point out that by re-preparing the statement each time you go to execute it you're largely losing the value of having a prepared statement. Instead, try `prepare`ing the statement outside the loop and then executing it for each message inside the loop, passing in the appropriate set of bind values each time.	[reply] [d/l]
Re^2: DBD::MySQL bind variable error by UnderMine (Friar) on May 12, 2006 at 22:04 UTC
Something like this at the start `$command='insert into mailstore.received (SenderName,SenderEmailAddres +s,SentOnBehalfOfName,ReplyRecipientNames,SenderEmailType,SentOn,Recei +vedTime,MessageClass,Size,Subj,SentTo,Unread,InternetCodepage,importa +nce,EntryID,ConversationIndex,ConversationTopic,Class,BodyFormat,Body +) values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?)'; $sth=$dbh->prepare($command);` [download] and in the loop `@values= map $msg->{$_}, qw{SenderName SenderEmailAddress SentOnBehalf +OfName ReplyRecipientNames SenderEmailType SentOn ReceivedTime Messag +eClass Size Subj SentTo Unread InternetCodepage importance EntryID Co +nversationIndex ConversationTopic Class BodyFormat Body} ; $rv=$sth->execute(@values);` [download] UnderMine	[reply] [d/l] [select]