SQL Syntax Errors
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? 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Errors
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
(oops, corrections to that last email message) RE: SQL Syntax Errors
Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: 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) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 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 = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? 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? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (oops, corrections to that last email message) RE: SQL Syntax Errors
Siegfried Heintze wrote: Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: 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) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Either you incorrectly interpreted the SELECT result, or someone/something inserted a row into the table between your SELECT and your INSERT, or one of your queries isn't what you think it is. It would help if you would show us your real code (not an edited version). Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 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 = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? Yes. The syntax is DELETE FROM tablename You are missing the FROM. See the manual for details http://dev.mysql.com/doc/mysql/en/delete.html. 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? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. Thanks, Siegfried --- Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
