Paginating data is one of those commonly required tasks. In previous version of MS SQL Server it's been tricky to handle, but made easy via the use of stored procedures that handle the pagination logic for you. However, if you're using SQL Server 2005 there's a much easier way to handle pagination and that's to use a Common Expression Table (CTE.)

Microsoft created the CTE syntax to make solving complicated tasks easier (such as returning a query of hierarchical tree data.) When you create a CTE, you're essentially creating a virtual table you can query against. CTEs generally perform very well and can often replace the need for creating temp tables.

Let's look at an example of query out records 11-20 from a Employee database:

-- create the Common Table Expression, which is a table called "pagination"
with pagination as
(
    -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration
    select
        row_number() over (order by department, employee) as rowNo,
        -- a list of the column you want to retrieve
        employeeId, employee, department 
    from 
        Employee
    where 
        disabled = 0
)
-- we now query the CTE table
select 
    -- add an additional column which contains the total number of records in the query
    *, (select count(*) from pagination) as totalResults
from
    pagination
where 
    RowNo between 11 and 20     
order by
    rowNo

The first part of the query creates the table expression we'll actually query against when we grab the actual results from the database. In the CTE you're going to write the SQL you'd normally write to grab all the records with one exception—you use the row_number() function to generate a virtual column that orders your result rows for you. The actual "order by" clause you want to use will actually go in the row_number() declaration—this makes SQL Server assign the correct row number for each record.

It's important to remember that a CTE can be as complex as you need it to be. You can do joins, pivots, etc—whatever you need.

The next step is to actually query against the CTE you created. This query will virtually be the same for all pagination queries you write. You are simply grabbing all of the columns and then limiting the returned data to just the rows you want to display (in this case rows 11 - 20.)

In this example, I added an additional column that contains the total results that were returned from the CTE. This is not needed, but it's handy if you need to do something like "Results 11 - 20 of 4,567 records." There are many ways you could generate the total records, this is just one method.

If you've upgraded to SQL Server 2005 and haven't started using CTEs yet, you really need to start getting familiar with them as they really simplify many tasks in SQL Server that previously were very difficult to solve.

I was working on another SVN hook today and only wanted to run some code if a particular folder was modified, just to save some processing time from unnecessarily running an SVN update process. Here's the little snippet I came up with to run in my post-commit.bat file:

The idea behind this is simple. We check the log for the revision that was just checked in to see if it contains a specific string—which in this case is a folder path. If the log does contain the string, we update our local copy. If it doesn't exist, we skip that step and just output a message that says no updates found.

Make sure to check out my other posts on using SVN hooks in Windows for more tips and tricks.

Raymond Camden blogged about a question someone had about getting the folder path for the current template. While Raymond addressed how to get the OS path, how would you get the URL path? So, if you had your user was on the URL http://www.example.com/some/folder/and/file.cfm, how would you go about getting the "/some/folder/and/" path?

While there are always many ways to solve a problem, I've tried to come up with a solution that should work for any version of ColdFusion from 6.0 and above. I wanted to avoid using CGI variables (since those vary by webserver,) so I went with using getPageContext() instead.

Here's the solution I wiped up:

If you just call getWebPath() it will return the current web folder path for the current base template.

We use a regular expression to strip out additional path info information that can sometimes be present for people using SEO-friendly URLs. For example, the URLs on my site appear like: http://blog.pengoworks.com/index.cfm/2006/9/27/CFMX-UDF-Parsing-a-URI-into-a-struct which returns a path of /index.cfm/2006/9/27/CFMX-UDF-Parsing-a-URI-into-a-struct. We need the regex to find the last period in the string and assume everything else is additional path info. The default regex should work in the vast majority of cases, but you can adjust it for the corner cases.

You can also manually supply a path such as: #getWebPath('/index.cfm/2006/9/27/CFMX-UDF-Parsing-a-URI-into-a-struct')#. This would return "/" as the web path.

Anyway, hopefully some of you will find this little UDF useful.

I had the need to convert a ColdFusion date/time stamp to a JS Date Object. I thought serializeJSON() function would handle this, but it turns out it treats CF date/time variables as strings. The toScript() function will convert CF variables to JS Date Objects—provided that the date/time variable is in ODBC format (i.e. {ts '2008-05-02 13:32:16'}.)

However, I wanted something that would work for anything that ColdFusion saw as a Date object, so I just whipped out this little 4 line helper function:

If ColdFusion doesn't see the date as a date object, then it'll set the date/time to "null". To use this function you just do:

This would then generate the following:

Obviously this is pretty straightforward, but it's saved me a lot of repetitive typing today and simplified the readability of my code.

I'm working on a project where I'm trying to create thumbnails for documents the user uploads. Since CF8 has introduced the <cfpdf /> tag, I thought it would be pretty straightforward to turn page 1 of a PDF into a thumbnail image—turns out I was wrong.

While the <cfpdf /> does work, it was causing me to jump through some various hoops some of which I could easily overcome. The issues I had were:

I've been working on a Java applet that can get images from the user's clipboard or take screenshots of the user's desktop and upload them to a server via HTTP. The last piece of the puzzle was to make sure I could access the task methods via JavaScript, as the interface will most likely be driven by HTML.

I've been using a self-signed cert to sign the Java Applet so that I can access the user's clipboard and take the screenshot. All of this was tested and working extremely well from if I used the Applet UI. However, as soon as I would try to invoke one of the sandboxed functions from JavaScript the applet started throwing the error:

AccessControlException is thrown: access denied (java.awt.AWTPermission accessClipboard)

I tried a number of things to get around this problem. I thought it was something I was doing wrong when I was signing the applet (since it was working fine from within the Applet's built-in UI controls.) After playing around with the cert signing process and getting no where, I finally came across the post JavaScripting in Applets - Getting Out of the Sandbox where a comment from Stéphane Bury pointed me to a solution.

public static void doSpecialWork(String param1){
    final String fParam1 = param1;
    java.security.AccessController.doPrivileged(new java.security.PrivilegedAction() {
        public Object run() {
            // put the code of your method here
            ...
            ...
            return "";
        }
    });
}

My final solution was to write a command() method which I can use to trigger off the internal privileged methods:

/**
 * The method to invoke from JS to perform the privileged methods--which throw
 * security errors if you try to access them directly.
 * 
 * @param command - the command you want to perform (clipboard, screenshot, upload)
 */
public void command(String command){
    final String cmd = command;

    java.security.AccessController.doPrivileged(
        new java.security.PrivilegedAction(){
            public Object run() {
                // execute the privileged command
                executeCommand(cmd);
                // we must return an object, so we'll return an empty string
                return "";
            }
        }
    );
}

As you can see the method is very basic, it just passes the command it received to a private method which actually executes the command. Now I can access any of the privileged methods in my applet with this little helper method.

[UPDATED: Saturday, March 08, 2008 at 10:26:56 AM]

I've been playing around with e - TextEditor this weekend to see if maybe it'll replace Textpad for me. I've always liked the speed of Textpad and it does a really good job on large files. I use Eclipse as my main IDE, so when it comes to a Notepad replacement my two main requirements are fast load times and the ability to handle large text files (for reading logs.)

However I keep seeing some of the really cool things that TextMate can do, so I've been keeping an eye out on e - TextEditor. It has a couple of features that would make quick editing of HTML files extremely easy. I really like the ability to selected a bunch of words or tags in a document and easily replace them.

One of the key features of e is it's TextMate bundle support—which really allows you to extend the functionality of the program. While surfing the e forums, I came across a Bundle command which allows you to open the file you have selected in the document (or the file where the caret is positioned.) This script makes it really easy to open files being loading from <script />, <style /> or any other tag you may use for loading files.

The original script, posted by tanguyr, I found either needed you to add e.exe to the Windows PATH environment or you needed to hard code the path to e.exe. I fixed the problem by using one of e's environmental variables. The $TM_SUPPORT_PATH variable points to a sub-directory above where the e.exe file exists, so I just use a relative path to get to the e.exe executable.

To add this command to e, go to the Bundles > Edit Bundles > Show Bundle Editor. I added this script to the Source bundle, as it seemed to make the most sense to me. Select the Source folder and click the + button and select the "New Command" option. I decided to call the command "Open Target Document", if you don't like the name choice something you like better.

Paste the following Bash script into the source area.

Someone asked me this morning how they could determine distinct relationships. They wanted to be able to retrieve all the distinct rows where someone had defined another user as a friend or any users who had defined them as a friend. The table schema looked like this:

As you can see friend 1 is pretty popular amongst other users. However, he only has 1 person defined as friend of his—friend 10. What we need to do is to find all distinct cross-column relationships in the table—which means only one row should be returned for the relationship between friend 1 and friend 10.

Phill Nacelli blogged about a great tool for monitoring SVN repositories called CommitMonitor from Stefan's Tools. Just last week I set up a post-commit script to e-mail me when updates were commited to a few of my SVN repositories. When you do all your work via telecommuting, staying in touch with updates to your repositories can be somewhat problematic—especially when you have lots of repositories.

I like being proactive and knowing when other developers are adding new committing new code. Some of our repositories aren't frequently updated, so proactive monitoring really helps me stay up-to-date. Up to now my choices were to either configure a post-commit script to e-mail changes or to check the repository logs for changes.

That's all changed with CommitMonitor. It's basically an RSS reader for your SVN repositories. You can set it up to monitor any number of repositories and you can configure how frequently updates are checked for. When CommitMonitor checks the repository any changes will be shown to you via a small pop-up window in the lower right hand corner.

CommitMonitor is a single executable that you can download in a zip file. I've always favored applications that allow you to just unzip and run—it makes it very easy to remove the application if don't like it.

From my brief experience with CommitMonitor I see this tool as being extremely helpful to my workflow.  Go download it now and see how it helps you!

I've been using Windows Live Writer for posting all my blogs. Overall it's a terrific program. It's got a very simple, easy to use interface and it drastically eases the process of blogging.

However, the one complaint I've had with the program is there's no easy way to "add" common HTML tags that you commonly use to the interface. I use the <samp> tag quite frequently when showing inline sample code and for variable names (although I suppose I should use the <var> tag instead.) To use these tags I've had to drop to the HTML Code view and manually add the tags. This is a pain to do—especially on longer posts where I need to add the tag frequently.

To solve my dilemma, I've been periodically searching for a Live Writer Plug-in that might allow me to add some custom HTML snippets. Well today I just happened to stumble on a great little plug-in called Dynamic Template Plugin for Windows Live Writer.

You can use this plug-in for doing something as inserting static text or for modifying the selected text. The real power comes from the fact that the plug-in allows you to use C# code snippets. You can even have the template prompt you for input.

To get you started, here are a couple of templates I just wrote to help me out with a few tasks:

As I mentioned early I've been working on a SVN post-commit script. We've got a SVN repository that will be modified by several remote developers and I really need to keep an eye on this repository and I need to closely monitor changes to this repository.

There are two major functions that I needed in my post-commit script:

1. I needed to update the working copy on my server
2. I needed to e-mail the changes to myself, so I know when developers are making changes

There are an abundant of examples showing off how to do this in various *nix flavors, but I couldn't find any good Windows-based solutions that didn't require Perl to be installed on the server. That led me to create the following post-commit.bat script.

I've been working on a post-commit hook for our Subversion install and was running in to a number of issues. The post-commit.bat file would run fine from command line, but I just could get things to work as I expected from SVN. After much debugging and scouring Google for answers, I've found a few tips that will hopefully help you to troubleshoot your own SVN repository hooks.

1) Subversion executes all hook programs with an empty environment

This was the biggest issue I was running in to, because I was expecting the my script to be able to find any programs in my %PATH% statement. That's the main reason my scripts were working fine from command line, but were breaking when executing from my SVN hook.

Every now and again I have the need to test some code against against some live data. The reasons vary from simply trying to recreate a bug, testing a piece of code for performance or for testing a UI widget.

In the past the quick and dirty way I've done was to move a template into production that would perform the query and then convert the query object to WDDX. This would allow me to create a "copy" of the live data that I could port to development. The problem with this method is it makes you move temporary code to a production environment that you then have to remove.

I recently posted about a new open source Java project called AntiSamy—which allows you to protect your websites from XSS hacks. I also promised that I'd soon show you some code examples that show you how you can use AntiSamy within ColdFusion.

I've only tested this code under ColdFusion 8. It should theoretically work on any ColdFusion installation, provided you're using a JDK version that supports the compiled version of the AntiSamy code (which is compiled to Java v1.5.)

Before you can actually use AntiSamy, there are a few quick steps you need to make.

Scott Stroz (who's always up for a round of golf) today asked How Do You Set Up Your Development Environment? In his article, he has an excellent tip for restarting your local install of Apache using an Ant script. This is a great tip and something I quite frankly never thought to do.

But what if you're using Windows and want to stop a service on remote server?