Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using CFDIRECTORY, CFLOOP, CFQUERY causes the NAME variable to become static inside a CFQUERY.

Posted on 2003-10-23
7
Medium Priority
?
1,958 Views
Last Modified: 2013-12-24
00  <cfdirectory action="list" directory="\\teletran\stockphotography" name="StockDirectories">
01  <cfloop query="StockDirectories">
02   <cfif StockDirectories.Type is "DIR">
03   <cfoutput>#Name#</cfoutput><br>
04    <cfdirectory action="list" filter="*.jpg" directory="\\teletran\stockphotography\#StockDirectories.Name#\HighResolutionVersions" name="CurrentSelectedDirectory">
05  
06    <cfoutput query="CurrentSelectedDirectory">
07  
08    <cfquery name="LookForFile" datasource="#dn#" username="#un#" password="#pw#">
09    select *
10    from Stock
11    where ImageFileName='#Name#' and SourceFolder='#Name#'
12    </cfquery>
13              
14    <cfif LookForFile.RecordCount is 0>
15                    
16     <cfquery name="InsertTheFile" datasource="#dn#" username="#un#" password="#pw#">
17     insert into Stock (ImageFileName,SourceFolder,FileSize)
18     values ('#Name#','#StockDirectories.Name#','#Size#')
19     </cfquery>
20                          
21     <cfset FilesInserted=FilesInserted+1>
22     <cfelse>
23      <cfset FilesIgnored=FilesIgnored+1>
24     </cfif>
25              
26     </cfoutput>
27    </cfif>
28   </cfloop>
0
Comment
Question by:tomkinite
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Author Comment

by:tomkinite
ID: 9608149
Line numbering would be an awesome feature for EE! But, that's not my problem right now.

Here's the application.

On line 00 I read the contents of the directory (stockphotography). In this directory contains hundreds of sub directories, each contain hundreds of JPEGS.  Through each loop of the StockDirectories query (CFDIRECTORY) I then do another loop to list JPEGS (CFDIRECTORY).  

The loop checks to see if the file exists in the database (line 08 to 12) and if it doesn't, it inserts it into the database (line 16 to 19).  

Here's the problem.

On line 03 you can see that I output the #Name# variable from StockDirectories perfectly fine.  The output is exactly what is expected.  On line 18 I use this very same variable but it now mysteriously static to the first folder's name in the CFDIRECTORY query.  Why is it looping as normal anywhere else (I have put the output of #NAME# inside of the loop as well) but when I use CFQUERY to insert the value of #NAME# it thinks it is the first folder?
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9608242
What happens when you scope the Name variable with the query name?  IE:

06    <cfoutput query="CurrentSelectedDirectory">
07  
08    <cfquery name="LookForFile" datasource="#dn#" username="#un#" password="#pw#">
09    select *
10    from Stock
11    where ImageFileName='#CurrentSelectedDirectory.Name#' and SourceFolder='#StockDirectories.Name#'
12    </cfquery>
13            
14    <cfif LookForFile.RecordCount is 0>
15                
16     <cfquery name="InsertTheFile" datasource="#dn#" username="#un#" password="#pw#">
17     insert into Stock (ImageFileName,SourceFolder,FileSize)
18     values ('#CurrentSelectedDirectory.Name#','#StockDirectories.Name#','#Size#')
19     </cfquery>
20                      
21     <cfset FilesInserted=FilesInserted+1>
22     <cfelse>
23      <cfset FilesIgnored=FilesIgnored+1>
24     </cfif>
25            
26     </cfoutput>
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9608256
Oops, forgot to post which lines I modified. 11 and 18
0
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 
LVL 25

Expert Comment

by:James Rodgers
ID: 9608285
save the following as a cfm called

dirInsertRecursive.cfm

<cfif isDefined('attributes.dirPath')>
      <cftry>
            <cfset path=#expandPath(#attributes.dirPath#)#>
      <cfcatch>
            <cfset path=#attributes.dirPath#>
      </cfcatch>
      </cftry>
      <cfif findNoCase(rootPath,#getDirectoryFromPath(path)#,1)>
      <cfif right(path,1) eq '\'>
            <cfset path=left('#attributes.dirPath#',len('#attributes.dirPath#')-1)>
      <cfelse>
            <cfset path=#attributes.dirPath#>
      </cfif>
<cfelse>
      <cfset path=rootPath>
</cfif>
</cfif>
<cfdirectory action="list" directory="#path#" name="fileList" sort="type,name">
<cfloop query="fileList">
<cfif #type# is "dir" and #name# neq "." and #name# neq "..">
<CF_dirInsertRecursive dirPath= "#attributes.dirPath#\#name#">
<cfdirectory action="delete"  directory="#attributes.dirPath#\#name#">
<cfelseif #type# eq "file">
#name#<br>
<!---
      <cfquery name="LookForFile" datasource="#dn#" username="#un#" password="#pw#">
    select *
    from Stock
    where ImageFileName='#Name#' and SourceFolder='#Name#'
   </cfquery>
           
    <cfif LookForFile.RecordCount is 0>
                 
    <cfquery name="InsertTheFile" datasource="#dn#" username="#un#" password="#pw#">
     insert into Stock (ImageFileName,SourceFolder,FileSize)
     values ('#Name#','#StockDirectories.Name#','#Size#')
     </cfquery>
                     
     <cfset FilesInserted=FilesInserted+1>
     <cfelse>
      <cfset FilesIgnored=FilesIgnored+1>
     </cfif>
       --->    
     </cfoutput>

</cfif>

</cfloop>

and call it from another cfm as

<cf_dirInsertRecursive.cfm dirPath="\\teletran\stockphotography">

test it in a small directory structure first, this will just list the contents, to do the insert remove the comments and delete or comment out the  #name#<br> line

0
 
LVL 2

Accepted Solution

by:
jonnygo55 earned 2000 total points
ID: 9608355
I have come across this before...
I cfoutput of a query inside a cfloop causes the cfloop not to advance as expected it seems...

one thing that worked for me was after the cfloop and before the cfoutput to set the qurey variable into another temp variable:

<cfloop query="StockDirectories">
<cfset thisDir = StockDirectories.name>

and then to refrerence #thisDir# on line 18 within the cfoutput of the query...
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9608862
i agree with jonnygo - i had done a similar workaround & it did the trick !

u cld try changing the code in ur line 6 from CFOUTPUT to CFLOOP & see if it helps
else what jonnygo suggested shld help u out for sure.

K'Rgds
Anand
0
 
LVL 10

Expert Comment

by:Mause
ID: 9609196
Also had that problem and solved it like jonnygo55 did

Anand cfloop inside a cfloop will still give the same problem....
well in CF5, not test it in MX yet.

Mause
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question