What's wrong with this query?

I don't remember ever running a query like this one, so I'm not sure it's even a valid approach. But, should this run?  Or do I need to run a separate query to the existing photo_filenames from hmls_photos to use as a variable? <cfquery name="insert_photo" datasource="c21ar">           insert into hmls_photos                  (photo_filename, photo_mls_number)           values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,                   <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">)            where photolist.name not in                  (select photo_filename from hmls_photos)             </cfquery> I'm just checking the table to insure that I'm not already inserting a photo with a filename already in the table. Rick

When I have done this, I used a NOT EXISTS, so something like this random example I just pulled from Google: http://archives.postgresql.org/pgsql-general/2003-06/msg01683.php On Fri, May 16, 2008 at 9:40 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Thanks for the reply, Aaron, but the CF app log is saying there's an error with "not exists" or "not in"... So, the problem is definitely in that part.  Just got to find the right syntax. I've always used "not in" in subqueries, but never used (that I can remember) a subquery inside an insert query. > When I have done this, I used a NOT EXISTS, so something like this random > example I just pulled from Google: > > http://archives.postgresql.org/pgsql-general/2003-06/msg01683.php >

Rick, I do not know what database you are using but your posted example is different than what I posted up.  You have a where clause after the paranthesis'd section for your inserted values where the method I use is to use a sub-query within that section.  I think your placement of the where clause is the root of your problems but depending on what database you are using the ultimate syntax to do what you want to do might vary. On Fri, May 16, 2008 at 10:04 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Sorry for not stating the db... it's MySQL 5. > Rick, I do not know what database you are using but your posted example is > different than what I posted up.  You have a where clause after the > paranthesis'd section for your inserted values where the method I use is to > use a sub-query within that section.  I think your placement of the where > clause is the root of your problems but depending on what database you are > using the ultimate syntax to do what you want to do might vary. > M

You could always create a unique index on the photo_filename column. Wim. On Fri, May 16, 2008 at 4:57 PM, Aar ----- Excess quoted text cut - see Original Post for more -----

Not being so well-versed in indexing (just starting using those in MySQL 5), I don't understand how having a unique index on the photo_filename column will prevent an insert of a duplicate value. Is that function a part of creating a unique index?  Does it cause MySQL 5 to screen inserts for duplicates and reject them? ??? Rick ----- Excess quoted text cut - see Original Post for more -----

This is something that I would do on Oracle INSERT INTO t_table (tableid,table_name) SELECT  1 AS tableid         , 'Table1' AS tablename FROM    dual WHERE   NOT EXISTS  ( SELECT    1                         FROM    t_user u                         WHERE   u.tableid = 1 ) On Fri, May 16, 2008 at 11:12 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

I meant this how you can write a similar query in Oracle. On Fri, May 16, 2008 at 11:17 AM, Qasim Rasheed <qasim.lists@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

I'm not that familiar with MySQL, but by definition (on Oracle at least) a unique index will reject any query that tries to insert a duplicate value and throws an error. You can catch that error in your application. Wim.

I think you'd need hash-marks (string delimiters) around photolist.name in the where clause, right? ... where '#photolist.name#' not in (select photo_filename from htmls_photos) ... -KJ ----- Excess quoted text cut - see Original Post for more -----

Tried that at the same time I put the "where clause" before the "values" clause...       insert into hmls_photos            (photo_filename, photo_mls_number)         where #photolist.name# not in              (select photo_filename from hmls_photos)        values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,            <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) ----- Excess quoted text cut - see Original Post for more -----

mysql has an extremely useful extension to standard sql: ON DUPLICATE KEY UPDATE ... check out mysql ref manual for details - it sounds like what you may want to use... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Rick Faircloth wrote: ----- Excess quoted text cut - see Original Post for more -----

>>I don't remember ever running a query like this one, so I'm not sure it's even a valid approach. My approach is to rename every file using CreateUUID <Functions53.html#1102827>() function. to make sure there is no duplicate problem. -- _______________________________________ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: piegeacon@internetique.com) Thanks.

Can you be a little more explicit about how you do that, Claude? I've never used CreateUUID before. Do you create a temporary UUID for each file to use for comparison with potential insert values? ----- Excess quoted text cut - see Original Post for more -----

>>Can you be a little more explicit about how you do that, Claude? I've never used CreateUUID before. I rename the file when it is uploaded. Ie: <CFSET fileName = "#CreateUUID()#.temp"> <CFIF fileUpload NEQ "">     <!--- File received --->     <CFFILE ACTION="Upload"        FILEFIELD="#fileUpload#"     DESTINATION="#Path#"     NAMECONFLICT="MakeUnique">     <CFFILE ACTION="rename"       SOURCE="#Path##CFFile.ServerFile#"       DESTINATION="#fileName#"> ... Then I analyse the true file type:     <CFX_IMAGE ACTION="READ" FILE="#Path##FileName#">     <CFIF isDefined ("img_type") AND img_type NEQ "" AND "jpeggifpng" CONTAINS img_type>         <CFSET fileNewName = listFirst(fileName, ".") & "." & img_type>     <CFELSE>       <H3>The file uploaded does not contain a valid image...</H3>       <CFABORT> ... Then I either reduce the image, either just rename the file     <CFIF max_width GT 0 AND img_width GT max_width>         <!--- Reduce image and rename the file --->         <CFX_IMAGECR3             GETIMAGEINFO             LOAD="#path##fileName#"             RESIZE="#max_width#x"             SAVE="#Path##FileNewName#">             <!--- Delete the temporary file --->             <CFFILE ACTION="delete"    FILE="#Path##FileName#">         <CFSET width = IMAGECR.width>         <CFSET height = IMAGECR.height>         <CFSET fileSize = IMAGECR.filesize>     <CFELSE>             <!--- no resize: just rename the file --->         <CFSET width = img_width>         <CFSET height = img_height>         <CFSET fileSize = img_size>         <CFFILE ACTION="rename"            SOURCE="#Path##FileName#"         DESTINATION="#fileNewName#">     </CFIF> Then register the file in the database... -- _______________________________________ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: piegeacon@internetique.com) Thanks.

Thanks for the explanation and code, Claude! ----- Excess quoted text cut - see Original Post for more -----

> Not being so well-versed in indexing (just starting using > those in MySQL 5), I don't understand how having a unique > index on the photo_filename column will prevent an insert of > a duplicate value. > > Is that function a part of creating a unique index?  Does it > cause MySQL 5 to screen inserts for duplicates and reject them? Yes. That's why it's called a "unique" index. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/

Ok... I created a unique index on photo_filename and it definitely stopped the process by throwing an error.  In the CF app log, it states "Error Executing Database Query.Duplicate entry '94219_0.JPG' for key 2" So how do I modify the query, which now looks like this... insert into hmls_photos             (photo_filename, photo_mls_number)              values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,              <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) ... to allow the query to skip duplicates and continue to execute? <pseudocode>   and if you find a duplicate value, just skip it, and go on to the next value </pseudocode> ----- Excess quoted text cut - see Original Post for more -----

Looks like the process for MySQL is very similar to the Oracle one I posted a link to earlier and someone else wrote an example of. http://forums.macrumors.com/showthread.php?t=403320 On Fri, May 16, 2008 at 10:25 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

I tried this but still get a syntax error... insert into hmls_photos             (photo_filename, photo_mls_number)      select photo_filename        from dual       where not exists #photolist.name#      values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,              <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) > Looks like the process for MySQL is very similar to the Oracle one I posted > a link to earlier and someone else wrote an example of. > > http://forums.macrumors.com/showthread.php?t=403320 >

Try this (EXISTS checks needs to be tested against returned results): insert into htmls_photos (photo_filename, photo_mls_number) values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,             <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) where not exists(select 1 from htmls_photo where photo_filename = '#photolist.name#') -KJ On Fri, May 16, 2008 at 11:54 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Your attempt though does not match the syntax used in the link I provided. It is failing because of the SQL after your WHERE EXISTS.  Also your SELECT should be pulling out the two values you want to insert into the table, just like how they did in the example on that link. On Fri, May 16, 2008 at 10:54 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

This seems to be appropriate for my case, but still doesn't work. insert   into hmls_photos        (photo_filename, photo_mls_number) select photo_filename   from dual where not exists (select photo_filename             from hmls_photos             where photo_filename = '#photolist.name#') values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,         <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) However, I am getting a different error: Error Executing Database Query.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 'values ('94219_0.JPG', '94219')' at line 10 Does my syntax match your referenced code?  And the referenced code doesn't include a reference to the values to be inserted, so maybe the "values" clause is in the wrong position? > Your attempt though does not match the syntax used in the link I provided. > It is failing because of the SQL after your WHERE EXISTS.  Also your SELECT > should be pulling out the two values you want to insert into the table, just > like how they did in the example on that link. >

You want to insert values into two columns, photo_filename and photo_mls_number, but you are only selecting out one value and on top of that it is from dual and that in itself will thrown an error, just try running: select photo_filename from dual and you will see what I mean. You need to be selecting out from dual the two values you want to insert in, so: select <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#ListFist(photolist.name, '_')#" cfsqltype="cf_sql_varchar" /> from dual Now you want to select those out when they do not exist so you need to add the WHERE NOT EXISTS to that, something like: select <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#ListFist(photolist.name, '_')#" cfsqltype="cf_sql_varchar" /> from dual WHERE NOT EXISTS (SELECT photo_filename FROM HMLS_PHOTOS WHERE PHOTO_FILENAME = <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />) Now you want to insert this into your table so something like: INSERT INTO HMLS_PHOTOS (photo_filename, photo_mls_number) select <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#ListFist(photolist.name, '_')#" cfsqltype="cf_sql_varchar" /> from dual WHERE NOT EXISTS (SELECT photo_filename FROM HMLS_PHOTOS WHERE PHOTO_FILENAME = <cfqueryparam value="#photolist.name#" cfsqltype="cf_sql_varchar" />) That was all written without testing so might have typos but it is the exact idea from those links I posted, I just applied their answers to your fields.  I'd hope that would work, assuming no typos on my part. On Fri, May 16, 2008 at 11:28 AM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

I'm still getting this error: Error Executing Database Query.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 'values ('94219_0.JPG', '94219')' at line 10 From this code: insert   into hmls_photos        (photo_filename, photo_mls_number) select '#photolist.name#', '#listfirst(photolist.name, '_')#'   from dual where not exists (select photo_filename               from hmls_photos              where photo_filename = '#photolist.name#') values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,         <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) Am I still missing something? ----- Excess quoted text cut - see Original Post for more -----

Yes you are, you added code to what I put in my last email.  Why do you have the VALUES part on there?  The SELECT from dual is handling the VALUES that you need.  What I put in my last email should be all you need with the exception of needing the opening and closing CFQUERY tags and again I may have a typo or two in mine but it should be really close to what you need. On Fri, May 16, 2008 at 12:14 PM, Rick Faircloth <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Alright... looks like we have a winner...       insert         into hmls_photos            (photo_filename, photo_mls_number)                          select '#photolist.name#', '#listfirst(photolist.name, '_')#'         from dual        where     not exists (select photo_filename              from hmls_photos             where photo_filename = '#photolist.name#') After leaving out the value clause (which I figured was to be in there somewhere) things worked well.  I haven't checked all the data, but at least the query ran! I'll check it out further. Thanks for all your help and patience! All to everyone else for your responses, as well! Rick > Yes you are, you added code to what I put in my last email.  Why do you have > the VALUES part on there?  The SELECT from dual is handling the VALUES that > you need.  What I put in my last email should be all you need with the > exception of needing the opening and closing CFQUERY tags and again I may > have a typo or two in mine but it should be really close to what you need. >

<cftry>     <cfquery datasource="#request.yourDsn#">     insert into hmls_photos            (photo_filename, photo_mls_number)       values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,             <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">)   </cfquery>   <cfcatch>     <cfif cfcatch.message contains "Duplicate entry">         <cfoutput><br />Duplicate entry: #photolist.name#</cfoutput>     <cfelse>        <cfrethrow><!--- Not a duplicate entry error, throw error --->     </cfif>   </cfcatch> </cftry> That should do the trick! Wim.

Should query be after the cfcatch?  The "Duplicate entry" error is still thrown because of the unique index and it doesn't make it past that error before CF halts the process. I just get the same "HTTP 500 Internal Server Error" in the browser and the "Duplicate entry..." error in the CF app log. ----- Excess quoted text cut - see Original Post for more -----

Wouldnt you put an IF at the begining of the statement? <cfquery name="insert_photo" datasource="c21ar"> IF NOT EXISTS (SELECT PHOTO_FILENAME                FROM   HMLS_PHOTOS                WHERE  PHOTO_FILENAME = <cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">)   INSERT INTO HMLS_PHOTOS              (PHOTO_FILENAME,               PHOTO_MLS_NUMBER)   VALUES     (<cfqueryparam cfsqltype="cf_sql_varchar" value="# photolist.name#">,               <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst( photolist.name,'_')#">) </cfquery> -- -- Richard Dillman rdillman@gmail.com "There are painters who transform the sun to a yellow spot, but there are others who with the help of their art and their intelligence, transform a yellow spot into the sun." - Pablo Picasso

Using this query: if not exists (select photo_filename     from hmls_photos         where photo_filename = <cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">)                  insert into hmls_photos               (photo_filename, photo_mls_number)        values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#photolist.name#">,                <cfqueryparam cfsqltype="cf_sql_varchar" value="#listfirst(photolist.name, '_')#">) I get this error concerning this syntax... don't know about the "if" part... Error Executing Database Query. 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 'if not exists (select photo_filename from hmls_photos where pho' at line 1 ----- Excess quoted text cut - see Original Post for more -----

>>So how do I modify the query, which now looks like this... This is it, a unique index will detect the error, but it will not solve it. Checking if the file already exists will not solve the problem either. So better make sure the file name is unique first and eliminate the possibility of a problem. -- _______________________________________ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: piegeacon@internetique.com) Thanks.

The problem is that, in this case, I'm adding photos to a directory that will contain duplicates.  I don't want to rename the files and include them, I want to avoid adding the files to the directory completely. ----- Excess quoted text cut - see Original Post for more -----

>>I don't want to rename the files and include them, I want to avoid adding the files to the directory completely. I see, but what if two users want to enter two different images having the same file name? -- _______________________________________ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: piegeacon@internetique.com) Thanks.

No users will be adding images to the system.  I'm using FTP to grab data from a provider and they use a numbering system that insures that every photo is uniquely named.  So image name conflict won't be a problem. ----- Excess quoted text cut - see Original Post for more -----

>>So image name conflict won't be a problem. I see, then checking in the query is your solution. -- _______________________________________ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: piegeacon@internetique.com) Thanks.