ColdFusion - Need to loop through a folder to find specific files from a SQL query and copy or zip them to a temp folder

Question

I'm trying to loop through a folder on a server to get a list of specific files and then have those files copied to a temp directory or zipped and copied to a temp directory. However I keep running into wall as I'm not sure how to do this. (Still an amateur with ColdFusion)

so here I do a SQL Query where I retrieve the exact attachments from specific dates I am looking for:

<cfquery name="test" datasource="test" cachedwithin="#CreateTimeSpan(0,0,10,0)#" result="r">
SELECT Test.TestNum, Test1.Test1Date, TestReport.Attachment
FROM Test
INNER JOIN more SQL code here..
WHERE Test1.Test1Date >= '#daterangevariablehere#'
AND NOT more SQL code here as well..
ORDER BY Test.TestNum
</cfquery>

The SQL Query is correct as in SQL Server Mgmt Studio, it executes properly and even before I try to do a cfdump it shows the correct list of files I am trying to get. However when I try to loop through them to copy or zip them, it's a total fail -> blank page.

I tried to:

<cfloop query="test">
<cfif test.recordcount gt 0>
<cfzip action="zip" file="#LocationOfwhereIwantTheEndResult" source="#WhereTheFolderWithTheAttachmentsReside#">
</cfzip>
</cfloop>

I even tried to do this, wrapped around the above code:

<cfdirectory action="list" name="test" directory="#WhereTheFolderWithTheAttachmentsReside#"></cfdirectory>

Side note: some of the variables and code are made up for the sake of security and anonymity..but I wanted to show the structure of what I'm trying to accomplish I hope I was clear(!) if otherwise please do let me know and I will provide more detail or information. ANY help is really appreciated, I have been banging my head against a wall on this one and feel like it might be something super simple. Please help! :)

Answer 1

Without knowing the actual values, it sounds like your query contains absolute paths of individual files. One option is to loop through the query and add a cfzipparam for each file. Here's a stand alone example, you can adapt. (It uses CFML because that's what's in your example, but if your comfortable with script syntax, look for the cfscript based versions in the link above).

CFML Example:

<!--- For Demo only to simulate your database query --->
<cfset test = queryNew("Attachment"
            , "varchar"
            , [{Attachment="c:\path\to\fileA.png"}
                , {Attachment="c:\path\to\fileB.png"}
                , {Attachment="c:\path\to\fileC.png"}
            ])>

<cfzip action="zip" file="c:\path\to\TheNameYouWant.zip">
    <cfloop query="test">
        <!--- If Attachment only contains a file name (i.e. "someFileName.pdf") 
           use your variables to build a full path, i.e. "c:\path\to\someFileName.pdf" 
         --->
        <cfzipparam source="#Attachment#">
    </cfloop>
</cfzip>

CFScript Example

<cfscript>
   test = queryNew("Attachment"
            , "varchar"
            , [{Attachment="c:\path\to\fileA.png"}
                , {Attachment="c:\path\to\fileB.png"}
                , {Attachment="c:\path\to\fileC.png"}
            ]);

    cfzip (action="zip", file="c:\path\to\TheNameYouWant.zip") {
        for (row in test) {
            cfzipparam (source=row.Attachment);
        }
    }
</cfscript>

Brief aside, you don't have to post actual paths. Most of the time, it's irrelevant anyway ;-) However please do post hard coded values that represent the actual path - NOT variables. The reason being variables leave a LOT of open questions, that could easily be answered by using a dummy path like c:\path\to\fileA.pdf. In this specific case, knowing things like your o/s, whether the sources are files/directories or relative/absolute paths, would help us rule out several potential issues right off the bat.

Answer 2

<cfif test.recordcount>
  <cfloop query="test">
    <cfzip action="zip" file="#LocationOfwhereIwantTheEndResult#" source="#WhereTheFolderWithTheAttachmentsReside#">
    </cfzip>
  </cfloop>
</cfif>

I suspect that your file and source locations are incorrect. Use ExpandPath() function to make sure its going to the correct place.