Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
What's wrong with this query?
Author: Claude Schneegans
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305554
>>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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305538
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 -----
Author: Claude Schneegans
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305492
>>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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305490
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.
>
Author: Aaron Rouse
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305485
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.
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305483
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 -----
Author: Aaron Rouse
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305475
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.
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305474
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 -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305471
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 -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305470
Thanks for the explanation and code, Claude!
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305469
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.
>
Author: Claude Schneegans
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305465
>>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.
Author: Richard Dillman
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305464
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
Author: Claude Schneegans
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305462
>>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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305463
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 -----
Author: Aaron Rouse
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305459
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.
----- Excess quoted text cut - see Original Post for more -----
Author: Kris Jones
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305457
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305456
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
>
Author: Wim Lemmens
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305449
<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.
Author: Aaron Rouse
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305446
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305442
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 -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305440
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 -----
Author: Wim Lemmens
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305437
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.
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305438
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 -----
Author: Qasim Rasheed
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305439
I meant this how you can write a similar query in Oracle.
----- Excess quoted text cut - see Original Post for more -----
Author: Qasim Rasheed
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305436
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 )
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305434
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
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305433
> 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/
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305431
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 -----
Author: Claude Schneegans
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305429
>>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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305430
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 -----
Author: Aaron Rouse
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305428
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.
----- Excess quoted text cut - see Original Post for more -----
Author: Didgiman
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305425
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 -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305424
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
>
Author: Kris Jones
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305420
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 -----
Author: Aaron Rouse
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305418
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56415#305413
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
