Looping through cfquery for file move is failing

Question

How do I loop through a query and pass one result at a time rather than all results?

I'm running a query and using <cfloop> to loop through the result and for every file name in the query, <cffile> needs to move that file from one folder to the next as follows:

cfquery name="qryGetFilesJustUploaded" datasource="#request.dsn#">                                          <!--- Limit to filed with pdf file type endings --->
            SELECT fileupload
            FROM [DevDBServer].[dbo].[upload_many]
            WHERE (fileupload Like '%.pdf%') and (needs_compression = '1')
            </cfquery>


     cffunction name="MoveCompressReturnFile" access="public" returntype="void" description="Moves file to temp folder, compresses it then returns it to its original location">
      <!---Lets Loop through and move all files references in query--->
        <cfloop query="#qryGetFilesJustUploaded#">
        <cffile action="move" 
 source="C:\inetpub\wwwroot\testingFolder\PDFCompression\pdf\#qryGetFilesJustUploaded.fileupload#"
        destination="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin" >

        <!--- Now lets compress it--->
        <cfexecute name="C:\Program Files (x86)\neeviaPDF.com\PDFcompress\cmdLine\CLcompr.exe"
        arguments="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\#qryGetFilesJustUploaded.fileupload# C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\#qryGetFilesJustUploaded.fileupload# -co -ci jpg -cq 10 -gi jpg -gq 10 -mi jbig2 -mq 1"
        outputfile="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\output.txt"
        timeout="250">
        </cfexecute>

        <!---Now Lets Return the file back to its original folder--->
        <cffile action="move" 
        source="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\#qryGetFilesJustUploaded.fileupload#"
        destination="C:\inetpub\wwwroot\testingFolder\PDFCompression\pdf" >
        </cfloop>
        </cffunction>

When it comes to the point of running the first cffile command, I get an error message that C:.... C:.... is not a valid source and what it looks like its doing is specifying multiple files at once rather than grab them one at a time until done grabbing all files referenced in the query. How do I fix this?

Update: Code is not successfully looping through the query results. 1. Tested the code using filename="document1.pdf" and it worked without issue passing that into the FindFilePath method and bringing back the file path and executing the remainder of the code. However when its replaced with the filename="qryGetFilesJustUploaded", each of the file names in the query doesn't seem to be getting passed in successfully so the file path of that file can be returned.

2. Did a dump of the query to verify my query works and the list of files is available.
3. Also verified the files exist and that there is no odd formatting in the file names.

Answer 1

You can try putting sleep(5000) after each file operation. This is because file operation take some time to complete. And it may be possible that while the first file operation is going on, the next file operation started. And thus in some cases you might encounter such kind of issues. Try this.

<cfloop query="#qryGetFilesJustUploaded#">
    <cffile action="move" source="C:\inetpub\wwwroot\testingFolder\PDFCompression\pdf\#qryGetFilesJustUploaded.fileupload#"
    destination="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin" >
    <cfset sleep(5000)>
    <!--- Now lets compress it--->
    <cfexecute name="C:\Program Files (x86)\neeviaPDF.com\PDFcompress\cmdLine\CLcompr.exe"
    arguments="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\#qryGetFilesJustUploaded.fileupload# C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\#qryGetFilesJustUploaded.fileupload# -co -ci jpg -cq 10 -gi jpg -gq 10 -mi jbig2 -mq 1"
    outputfile="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\output.txt"
    timeout="250">
    </cfexecute>
    <cfset sleep(5000)>
    <!---Now Lets Return the file back to its original folder--->
    <cffile action="move" 
    source="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\#qryGetFilesJustUploaded.fileupload#"
    destination="C:\inetpub\wwwroot\testingFolder\PDFCompression\pdf" >
    <cfset sleep(5000)>
</cfloop>

Answer 2

Resolved

When I set my query to the filename, I forgot to set it to "queryname.columnname" syntax.

Working Code:

<cffunction name="testFunc" access="public">

<!--Lets Get the file names from the DB table--->
<cfquery name="qryGetFilesJustUploaded" datasource="#request.dsn#"> 
    SELECT fileupload
    FROM [First_Title_Services_Dev].[dbo].[upload_many]
    WHERE (fileupload Like '%.pdf%') and (needs_compression = '1')
    </cfquery>
    <cfset qryRecordCount = #qryGetFilesJustUploaded.RecordCount# >

<!--Set the methods and variables--->
<cfset fileSys = CreateObject('component','cfc.FileManagement')>

<cfloop query="qryGetFilesJustUploaded" startRow="1" endRow="#qryRecordCount#">
<cfset filename="#qryGetFilesJustUploaded.fileupload#" >
<cfset filePath = #fileSys.FindFilePath('#filename#','file')# >
<cffile action="move" source="#filePath#" destination="C:\compressionBin">
<cfset sleep(5000)>

<!---Compress the file now--->
<cfexecute name="C:\Program Files (x86)\neeviaPDF.com\PDFcompress\cmdLine\CLcompr.exe"
arguments="C:\compressionBin\#filename# C:\compressionBin\#filename# -co -ci jpg -cq 10 -gi jpg -gq 10 -mi jbig2 -mq 1"
outputfile="C:\inetpub\wwwroot\testingFolder\PDFCompression\bin\output.txt"
timeout="250">
</cfexecute>
<cfset sleep(5000)>
<!---Lets Return the file now--->
<!---Back to where you came from! :D--->
<cffile action="move" 
source="C:\compressionBin\#filename#" destination="#filePath#" >
<cfset sleep(5000)>
</cfloop>
</cffunction>