Using Query of Queries with CFDirectory

So.. I was using CFDirectory to get a list of files. I then wanted to use the result and use Query of Queries to get files that had a modified date more than 2 days ago.

Sounds pretty basic right? I thought so too. Then I wrote the code and found out otherwise.

This was the code I started with:

view plain print about
1<cfdirectory action="list" directory="f:\temp" name="cfdFileList" recurse="true">
2
3<cfquery name="fileList" dbtype="query">
4    select *
5    from cfdFileList
6    where type = 'File'
7    and datelastmodified < #dateAdd( "d", -2, now())#
8</cfquery>

No matter what this always returned 0 results. After some trial and error I figured out the date comparison in the where clause was failing. From what I could tell ColdFusion was not treating the data types as dates.

I tried everything I could think of to get CF to treat them as dates. Then, John Dowdle pointed me to using CAST() on the dates.

First couple tries did not work. So, with my new found knowledge I hit the interwebs to see how to do it. After a quick search I found a post by Ben Nadel about this very subject. However, Ben's blog post was a few years old so I was not sure if it was still relevant. I figured I got nothing to loose at this point so I used his example.

What I ended up with was this:

view plain print about
1<cfquery name="fileList" dbtype="query">
2    select *
3    from cfdFileList
4    where type = 'File'
5    and CAST( [datelastmodified] as date) < cast(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateAdd( "d", -2, now())#"/> as date)
6</cfquery>

I had to use cast on both sides of the comparison to get it to work. Also, it would fail if I did not use cfqueryparam. This just seems like very strange and odd behavior. One would think that ColdFusion could handle the data types much better than this.

BTW... I am using ColdFusion 9 so I don't see this going away anytime soon.

Till next time...

--Dave

ColdFusion VFS Manager released

Now that ColdFusion 9 is released I can release the project I have been working on for a while.

Read all about it here: http://vfsmanager.riaforge.org/

ColdFusion 9 Released

Just in-case you missed the billion tweets and blog entries here is another...

ColdFusion 9

ColdFusion 9 - Deleting all items in VFS

Deleting all files from a directory or the entire directory tree has become very trivial. Especially since ColdFusion 7 when the recurse attribute was added. With VFS (ram drive) you can use all the same tags to manage drive contents as you would with a physical drive.

So... to remove the entire content of the VFS you can use one line of code:

view plain print about
1<cfdirectory action="delete" directory="ram://" recurse="true" >

However, in doing so ColdFusion throws an error:

The specified directory attribute ram:/// cannot be deleted.
You may not have permissions to delete this directory. The exception occurred during a cfdirectory action="delete".

The essence of the error is true. You can't delete the "ram://" root. So, all you have to do is suppress this error using cftry. But be careful this will not only suppress the error we expect but any unexpected errors as well.

Till next time....

--Dave

CFHOUR() Show #15 - Interview with Adam Lehman

Check out our interview with Adam Lehman from Adobe for the CFHour().com podcast.

http://bit.ly/51Av1

ColdFusion 9 - CFScript Examples - QUERY w/ Query Param

So, I did a recent post on an example of query in side cfscript. I was asked if there was a way to do a query param. I did some digging and got an assist from paul kukiel on how to do it.

There was an old blog post that he pointed me to that showed how. I took that code and tried it but it failed. It turned out that the blog post was about 99% accurate. He had one thing wrong that made it not work.

So.... here is an example of how to add a query param using script syntax.

view plain print about
1<CFSCRIPT>
2myQry = new Query(); // new query object     
3
myQry.setSQL("select bookid, title, genre from app.books where bookid = :bookid"); //set query
4
myQry.addParam(name="bookid",value="5",CFSQLTYPE="CF_SQL_INT"); // add query param
5
qryRes = myQry.execute(); // execute query
6
writedump(qryRes.getResult().recordcount, true); // get resultcount
7
writedump(qryRes.getResult(), false); // dump result
8
writeoutput('<BR>');
9
</CFSCRIPT>

So, there ya go. Hope this is helpfull.

Till next time...

--Dave

ColdFusion 9 - CFScript Examples - QUERY

For those that don't know, I am a cfscript nut. I write almost everything in script. I find it much better to read, understand and it just flows better.

So, you can only imagine my excitement when I found out about the ColdFusion 9 enhancements to cfscript. I had been creating my own functions for a while to add functionality to cfscript. But now they have gone and done it for me.

So, here is a first in a series of code examples for cfscript. This example I will focus on running queries. One thing you will notice in the example code that I don't define a datasource. This is because the datasource is defined in application.cfc. This is done by adding "this.datasource = 'cfbookclub';" to your application.cfc.

All these examples use the "cfbookclub" sample datasource that is instlled with CodFusion.

There are multiple ways to run a query inside cfscript.

[More]

ColdFusion 9 - Script based Application.cfc

One of the great new features of ColdFusion 9 is the ability to do script based CFCs. Yep, you are correct, no tags necessary. In fact there isn't even a or a tag.

Without further a due.. here is an example:

view plain print about
1component output="false" {
2    
3    this.name="myApplication";        
4
5    public function onApplicationStart() output="false"{
6    
7        return ;
8    }
9    public boolean function onRequestStart(required string thePage) output="true"{
10        
11        
12        return true;
13    }
14}

That is it, nothing more to it. Granted, this is a very basic example. Just take this code, create a cfc file and drop it all in. This sample code should be the entire content of the cfc.

Now, obviously doing coding in a purely script based sense can cause some other issue. Like, how do i write a query or use an include? All these have been solved as well. The vast majority of ColdFusion tags are now available via a script based syntax. I will have more code samples over the next couple days.

Till next time...

--Dave

Coldfusion 9 / Coldfusion builder public beta

Now available on Adobe labs. Get them while they are hot!!!

ColdFusion 9

ColdFusion Builder

Previous Entries