"Siegfried Heintze" <[EMAIL PROTECTED]> wrote on 06/29/2005 03:09:28 PM:
> 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount > WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert > one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES > (209689,'2005-06-26',1) > > 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount > WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert > one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES > (209689,'2005-06-26',1) > > 676 $result = $sth->execute(); > > 678 Insert must of have worked! > > DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 > at ./crawl-hot-jobs.pl line 675. > > 676 $result = $sth->execute(); > At the end are my print messages from a perl program using MySQL (v 4.1, how > do I tell for sure?) with the DBI interface. The first integer on the left > is the line number. > > I first check to see if the record exists: > SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND > dtSnapShot = '2005-06-26' > > When I don't find an entry, I try an insert: > > INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES > (209689,'2005-06-26',1) > > This indicates success. > But then it tells me I have a syntax error! > DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 > at ./crawl-hot-jobs.pl line 675. > > The primary key consists of two fields: fkJobPosting (integer) and > dtSnapshot (date). > > Now if my SQL had a syntax error, would it not give me an error every time? > So why do I get "syntax" error? A syntax error is not your first error message. I see a message that you are attempting to duplicate a key value that already exists. Are you sure that your initial check is returning FALSE when you look for a matching record? Have you considered using the INSERT ... ON DUPLICATE KEY format or possibly the INSERT IGNORE format? Either one of those will let you deal with the case of what you should do if you attempt to create a record that would duplicate an existing records PK values. I don't use DBD or I could offer better advice. However, some database libraries force you to execute your commands one at a time. Could this be what is happening to you bewteen lines 683 and 686? I would also check (print so that you can see) the full text of the statement you are attempting to execute in line 686. It could be that you have a mismatched set of single quotes. You have to remember to escape all of the special characters used in a string literal or it will corrupt your statement. If, for example, you are building an INSERT statement that contains the name of a buisiness plus some other fields and that business has an apostrophe in its name, that apostrophe needs to be escaped or it will break your INSERT statement. > I don't get a syntax error every time. Most of the time, everything works > fine. > > Thanks, > Siegfried > ----------------------------------------------------------------------- > > > > 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, > cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount > WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' > > select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND > dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 > > DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = > 209689' at li at ./crawl-hot-jobs.pl line 686. > > Use of uninitialized value in concatenation (.) or string at > ./crawl-hot-jobs.pl line 707. > > 707 $nDBVersion[0] >= 1 version=1 nDBVersion=() fkJobId = 209689 No need to > update database,it is more recent. > Shawn Green Database Administrator Unimin Corporation - Spruce Pine
