Inserting an Email Address fails.
I'm trying to insert an email address into a mysql database and it seems to fail no matter what I do. Here are two different cuts at the code, neither of which works and both return pretty much the same error message. sub update_Unprocessed { my $From = shift; # From my $MsgID = shift; # MsgID my $Subject = shift;# Subject my $Date = shift; # Date my $Reason = shift; # Reason my $Attachment = shift; # Body or attachment $rows = $dbh->do("INSERT INTO UnprocessedMail SET From = '$From', MsgID = '$MsgID', Subject = '$Subject', Date = \'$Date\', Reason = '$Reason', Attachment = '$Attachment'"); if ( $DBI::errstr ) { $errmsg = 'Can not updated UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } This produces this error message: Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From = '[EMAIL PROTECTED]', MsgID = 'prepare(q{INSERT INTO UnprocessedMail (From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( ?, ?, ?, ?, ?, ? )}); if ( $DBI::errstr ) { $errmsg = 'Can not prepare UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not prepare UnprocessedMail. Error: $DBI::errstr\n"; } } $sth->execute(@values); if ( $DBI::errstr ) { $errmsg = 'Can not update UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } Gives me THIS error message Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( 'KH If I change the insert order it still blows up on the email address, IE if I put MsgID first and the email address last it still dies on the mail address. I've tried various $sth->quote(), perl quotemeta, qq, q, etc., options. I'm sure it's something very simple and stupid since I don't do this much but. Can anyone please offer me some help? Thanks Kent
RE: Inserting an Email Address fails.
Isn't the syntax for INSERT ... INSERT INTO tab [(col_list)] values (value_list); ?? - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Hamilton, Kent [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 1:16 PM To: [EMAIL PROTECTED] Subject: Inserting an Email Address fails. I'm trying to insert an email address into a mysql database and it seems to fail no matter what I do. Here are two different cuts at the code, neither of which works and both return pretty much the same error message. sub update_Unprocessed { my $From = shift; # From my $MsgID = shift; # MsgID my $Subject = shift;# Subject my $Date = shift; # Date my $Reason = shift; # Reason my $Attachment = shift; # Body or attachment $rows = $dbh->do("INSERT INTO UnprocessedMail SET From = '$From', MsgID = '$MsgID', Subject = '$Subject', Date = \'$Date\', Reason = '$Reason', Attachment = '$Attachment'"); if ( $DBI::errstr ) { $errmsg = 'Can not updated UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } This produces this error message: Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From = '[EMAIL PROTECTED]', MsgID = 'prepare(q{INSERT INTO UnprocessedMail (From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( ?, ?, ?, ?, ?, ? )}); if ( $DBI::errstr ) { $errmsg = 'Can not prepare UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not prepare UnprocessedMail. Error: $DBI::errstr\n"; } } $sth->execute(@values); if ( $DBI::errstr ) { $errmsg = 'Can not update UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } Gives me THIS error message Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( 'KH If I change the insert order it still blows up on the email address, IE if I put MsgID first and the email address last it still dies on the mail address. I've tried various $sth->quote(), perl quotemeta, qq, q, etc., options. I'm sure it's something very simple and stupid since I don't do this much but. Can anyone please offer me some help? Thanks Kent This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Inserting an Email Address fails.
Hamilton, Kent [mailto:[EMAIL PROTECTED] wrote: > > Can not update UnprocessedMail. Error: You have an error in your SQL > syntax. Check the manual that corresponds to your MySQL server version > for the right syntax to use near 'From = '[EMAIL PROTECTED]', > MsgID = '
Re: Inserting an Email Address fails.
FROM is a keyword, change the name of the column Hamilton, Kent wrote on 9/23/2004, 3:15 PM: > I'm trying to insert an email address into a mysql database and it seems > to fail no matter what I do. Here are two different cuts at the code, > neither of which works and both return pretty much the same error message. > > sub update_Unprocessed > { > my $From = shift; # From > my $MsgID = shift; # MsgID > my $Subject = shift;# Subject > my $Date = shift; # Date > my $Reason = shift; # Reason > my $Attachment = shift; # Body or attachment > $rows = $dbh->do("INSERT INTO UnprocessedMail SET > From = '$From', > MsgID = '$MsgID', > Subject = '$Subject', > Date = \'$Date\', > Reason = '$Reason', > Attachment = '$Attachment'"); > if ( $DBI::errstr ) { > $errmsg = 'Can not updated UnprocessedMail. Error: ' . > $DBI::errstr . "\n"; > doerror($errmsg); > if ( $DEBUG ) { > print STDERR "Can not update UnprocessedMail. > Error: $DBI::errstr\n"; > } > } > } > > This produces this error message: > > Can not update UnprocessedMail. Error: You have an error in your SQL > syntax. Check the manual that corresponds to your MySQL server version > for the right syntax to use near 'From = '[EMAIL PROTECTED]', > MsgID = ' > Changing the subroutine to look like: > > sub update_Unprocessed > { > my $count = 0; > $values[$count++] = shift; # From > $values[$count++] = shift; # MsgID > $values[$count++] = shift; # Subject > $values[$count++] = shift; # Date > $values[$count++] = shift; # Reason > $values[$count] = shift;# Body or attachment > $sth = $dbh->prepare(q{INSERT INTO UnprocessedMail > (From, MsgID, Subject, Date, Reason, Attachment ) > VALUES ( ?, ?, ?, ?, ?, ? )}); > if ( $DBI::errstr ) { > $errmsg = 'Can not prepare UnprocessedMail. Error: ' . > $DBI::errstr . "\n"; > doerror($errmsg); > if ( $DEBUG ) { > print STDERR "Can not prepare UnprocessedMail. > Error: $DBI::errstr\n"; > } > } > $sth->execute(@values); > if ( $DBI::errstr ) { > $errmsg = 'Can not update UnprocessedMail. Error: ' . > $DBI::errstr . "\n"; > doerror($errmsg); > if ( $DEBUG ) { > print STDERR "Can not update UnprocessedMail. > Error: $DBI::errstr\n"; > } > } > } > > Gives me THIS error message > > Can not update UnprocessedMail. Error: You have an error in your SQL > syntax. Check the manual that corresponds to your MySQL server version > for the right syntax to use near 'From, MsgID, Subject, Date, Reason, > Attachment ) > VALUES ( 'KH > > If I change the insert order it still blows up on the email address, IE if > I put MsgID first and the email address last it still dies on the mail > address. > > I've tried various $sth->quote(), perl quotemeta, qq, q, etc., options. > > I'm sure it's something very simple and stupid since I don't do this much > but. > > Can anyone please offer me some help? > > Thanks > > Kent -- Your Friendly Neighborhood DBA, Chuck
RE: Inserting an Email Address fails.
Hi Kent, looks like you have SQL syntax error. according to your log, that is. have you tried to execute the SQL using mysql client? don't see typical syntax error but when $ usually get that output i try to work out my sql syntax first then try to integrate with script later, when SQL issue is resolved. -Original Message- From: "Hamilton, Kent" <[EMAIL PROTECTED]> Date: 09/23/04 01:14 PM To: <[EMAIL PROTECTED]> Subject: Inserting an Email Address fails. I'm trying to insert an email address into a mysql database and it seems to fail no matter what I do. Here are two different cuts at the code, neither of which works and both return pretty much the same error message. sub update_Unprocessed { my $From = shift; # From my $MsgID = shift; # MsgID my $Subject = shift;# Subject my $Date = shift; # Date my $Reason = shift; # Reason my $Attachment = shift; # Body or attachment $rows = $dbh->do("INSERT INTO UnprocessedMail SET From = '$From', MsgID = '$MsgID', Subject = '$Subject', Date = \'$Date\', Reason = '$Reason', Attachment = '$Attachment'"); if ( $DBI::errstr ) { $errmsg = 'Can not updated UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } This produces this error message: Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From = '[EMAIL PROTECTED]', MsgID = 'prepare(q{INSERT INTO UnprocessedMail (From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( ?, ?, ?, ?, ?, ? )}); if ( $DBI::errstr ) { $errmsg = 'Can not prepare UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not prepare UnprocessedMail. Error: $DBI::errstr\n"; } } $sth->execute(@values); if ( $DBI::errstr ) { $errmsg = 'Can not update UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } Gives me THIS error message Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( 'KH If I change the insert order it still blows up on the email address, IE if I put MsgID first and the email address last it still dies on the mail address. I've tried various $sth->quote(), perl quotemeta, qq, q, etc., options. I'm sure it's something very simple and stupid since I don't do this much but. Can anyone please offer me some help? Thanks Kent These words brought to you by Ogo. Find out more at www.ogo.com
RE: Inserting an Email Address fails.
Scroll down. :) he does have insert as well as update queries embedded, unfortunately neither doing what he's telling to do. -Original Message- From: "Reidy, Ron" <[EMAIL PROTECTED]> Date: 09/23/04 01:02 PM To: "Hamilton, Kent" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: Inserting an Email Address fails. Isn't the syntax for INSERT ... INSERT INTO tab [(col_list)] values (value_list); ?? - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Hamilton, Kent [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 1:16 PM To: [EMAIL PROTECTED] Subject: Inserting an Email Address fails. I'm trying to insert an email address into a mysql database and it seems to fail no matter what I do. Here are two different cuts at the code, neither of which works and both return pretty much the same error message. sub update_Unprocessed { my $From = shift; # From my $MsgID = shift; # MsgID my $Subject = shift;# Subject my $Date = shift; # Date my $Reason = shift; # Reason my $Attachment = shift; # Body or attachment $rows = $dbh->do("INSERT INTO UnprocessedMail SET From = '$From', MsgID = '$MsgID', Subject = '$Subject', Date = \'$Date\', Reason = '$Reason', Attachment = '$Attachment'"); if ( $DBI::errstr ) { $errmsg = 'Can not updated UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } This produces this error message: Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From = '[EMAIL PROTECTED]', MsgID = 'prepare(q{INSERT INTO UnprocessedMail (From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( ?, ?, ?, ?, ?, ? )}); if ( $DBI::errstr ) { $errmsg = 'Can not prepare UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not prepare UnprocessedMail. Error: $DBI::errstr\n"; } } $sth->execute(@values); if ( $DBI::errstr ) { $errmsg = 'Can not update UnprocessedMail. Error: ' . $DBI::errstr . "\n"; doerror($errmsg); if ( $DEBUG ) { print STDERR "Can not update UnprocessedMail. Error: $DBI::errstr\n"; } } } Gives me THIS error message Can not update UnprocessedMail. Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'From, MsgID, Subject, Date, Reason, Attachment ) VALUES ( 'KH If I change the insert order it still blows up on the email address, IE if I put MsgID first and the email address last it still dies on the mail address. I've tried various $sth->quote(), perl quotemeta, qq, q, etc., options. I'm sure it's something These words brought to you by Ogo. Find out more at www.ogo.com
RE: Inserting an Email Address fails.
On Thu, 23 Sep 2004 [EMAIL PROTECTED] wrote: > Changing the subroutine to look like: > > sub update_Unprocessed > { > my $count = 0; > $values[$count++] = shift; # From > $values[$count++] = shift; # MsgID > $values[$count++] = shift; # Subject > $values[$count++] = shift; # Date > $values[$count++] = shift; # Reason > $values[$count] = shift;# Body or attachment > $sth = $dbh->prepare(q{INSERT INTO UnprocessedMail > (From, MsgID, Subject, Date, Reason, Attachment ) ^ Try chaning that to `From` or "From" depending on whether you are running your server in ansi mode. > Gives me THIS error message > > Can not update UnprocessedMail. Error: You have an error in your SQL > syntax. Check the manual that corresponds to your MySQL server version > for the right syntax to use near 'From, MsgID, Subject, Date, Reason, > Attachment ) > VALUES ( 'KH > If the above does not fix it, take a look at a trace(9) to see exactly what is being sent to the backend. And if you see something wrong let me know, but I suspect that the quoting will fix the error. Rudy.
RE: Inserting an Email Address fails.
Oh Now don't I feel pretty stupid. Fixed thank you. > -Original Message- > From: Ronald J Kimball [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 23, 2004 15:04 > To: Hamilton, Kent; [EMAIL PROTECTED] > Subject: RE: Inserting an Email Address fails. > > Hamilton, Kent [mailto:[EMAIL PROTECTED] wrote: > > > > Can not update UnprocessedMail. Error: You have an error in your SQL > > syntax. Check the manual that corresponds to your MySQL > server version > > for the right syntax to use near 'From = '[EMAIL PROTECTED]', > > MsgID = ' > FROM is a reserved word in SQL. Personally, I recommend > renaming the column > to something that won't cause this problem. Barring that you > can put the > column name in double-quotes everywhere, e.g.: > > INSERT INTO UnprocessedMail > ("From", MsgID, ...) > > > Ronald > > >