ColdFusion and SQL Server

If you've recently been charged with setting up a Web server to interact with SQL Server, your choice of development environments probably comes down to Active Server Pages (ASP) or Allaire ColdFusion. A SQL Server Magazine Instant Poll showed that more SQL Server developers use ASP than ColdFusion (you can find the results at http://www.sqlmag.com/Poll/Main.cfm?QID= 99&Action=PreviousPoll). But ColdFusion is a powerful tool that offers a viable alternative to ASP, as I'll demonstrate in this article by using examples from my experience as Web technology manager for SQL Server Magazine and Windows 2000 Magazine.

The ColdFusion Application Server runs as a multi- threaded process that provides a runtime environment for application logic and dynamic page generation. You usually develop ColdFusion applications as a package of scripted pages that might have other components such as COM. When a browser requests a page in a ColdFusion application, ColdFusion Server processes the scripting in the page, interacts with other components, connects with back-end systems, and dynamically generates the HTML page that is returned to the browser.

In general, ColdFusion does what ASP does. But you might find among ColdFusion's specific features and functionality some compelling reasons to choose it. The people who use database servers as Web site tools often include software programmers, database administrators, and graphic designers or other people with no programming or development experience. You need a diverse set of tools to serve this diverse group of people. ColdFusion lets you create powerful Web applications, even if you're not a hard-core programmer. If you are an experienced programmer, you'll appreciate ColdFusion's power and flexibility.

If you want to use a Web server other than Microsoft Internet Information Server (IIS) or run an OS other than Windows NT or Windows 2000 (Win2K), ColdFusion is a good option because you can run it on different Web servers and you can run it on other OSs, including Solaris, HP-UX, and Linux. And except for a few OS-specific features, such as case sensitivity for UNIX, your ColdFusion code will run on all these OSs. To test this notion, I downloaded the Linux version of ColdFusion and installed it on a Red Hat Linux 6.1 box running the Apache Web server, and the code I developed for our NT-based Web servers ran correctly on that box.

ColdFusion comes in three versions: Express, Professional, and Enterprise. The Express and Professional versions are for Windows and Linux OSs, and the Enterprise version is for Windows, Linux, Solaris, and HP-UX. Express is a free, limited-functionality version of ColdFusion. It's a great place to start if you're interested in trying the product. The Professional version is the complete solution and supports full-text searching and connectivity to email, files, and distributed objects. The Enterprise version includes the features the Professional version contains, plus it supports clustering, load balancing, server failover, native database drivers, and other high-end features for enterprise and e-commerce applications.

ColdFusion supports native integration with various technologies, including ODBC-compliant databases, OLE DB, email through POP and SMTP, Lightweight Directory Access Protocol (LDAP), file servers through native file system support and FTP, and distributed objects through COM and Enterprise JavaBeans (EJB). The Enterprise version of ColdFusion includes Common Object Resource Broker Architecture (CORBA) and native database drivers.

How ColdFusion Works

Listing 1 demonstrates how to use ColdFusion to complete a simple task—getting a customer list from various companies and grouping the output according to company name. The code outputs the results you see in Listing 2. This example shows how simple CFML is to use. You use a straightforward tag to query the database and an equally straightforward grouping of tags to get the desired output.

What ColdFusion Does

• Query caching. In some cases, using query caching can yield a bigger performance gain than using stored procedures. Stored procedures are an efficient way to set up and use queries. But if you want to maintain a dynamic area of your site, query caching is the perfect tool. For example, we use query caching on the Windows 2000 Magazine home page to check for changes or new content based on the time frame we determine. Listing 3 shows an example of a query we use to pull recent news stories from the database. The query retrieves the most recent three news stories and ensures that they weren't displayed in another spot on the home page. The query makes calls to the SQL Server machine at 10-minute intervals. The CACHEDWITHIN parameter lets you set the time interval to days, hours, minutes, and seconds. Until the designated expiration time, ColdFusion maintains the query results in memory.
• Web Distributed Data Exchange (WDDX). Exchanging data among servers with different programming environments is a challenge, no matter how simple the data structure is. Even if you were to use Extensible Markup Language (XML), the people on both ends of the data exchange would need to learn XML and create and agree on a common Document Type Definition (DTD). But by using WDDX on both ends, each side can convert the data to a common format. WDDX uses an XML vocabulary for describing complex data structures such as arrays and record sets in a basic way so you can move the data among different application server platforms by using only HTTP. Target programming environments for WDDX include ColdFusion, ASP, JavaScript, and Perl.
  
  Listing 4 shows how you can use WDDX to serialize into XML an array created in ColdFusion and send the array to an ASP server that will then deserialize it from XML into a VBScript array object with all the types natively converted. Using WDDX makes the conversion process between languages relatively transparent.
• CFMAIL. You can use the CFMAIL tag to create email messages in which the recipients' names and addresses and the message are generated dynamically. In other words, you can query a database for recipients' email addresses or other information and create an email message on the fly. ColdFusion supports connectivity to email servers with SMTP through the CFMAIL tag. With the example in Listing 5, I can notify someone in another department by email when certain company accounts are going to expire. I set up a scheduled task, and the recipient gets daily notifications.
• CFHTTP. You can use CFHTTP to download a file, document, or Web page from any site and store its contents in a ColdFusion variable so that a ColdFusion application can manipulate the data or store it as a file on the server. For example, we use CFHTTP to pull Web pages from the Monster Board Web site. We then parse through the pages for the data we want and display it on our site.
• Custom Tags. Using custom tags is an efficient way to reuse code. Custom tags let you call a page built in CFML and add its functionality to any ColdFusion application. You can write the tags or obtain them from ColdFusion resources, including the Allaire Web site (http://www.allaire.com). Let's say you repeatedly add functionality to your site that displays only when certain values are passed to it. Rather than rewriting the code each time you need to use it, you could write a custom tag and send variables to it. We use the following tag on the Windows 2000 Magazine Web site to tell users whether they need subscriber access to view a particular article.
  
  CF_AccessImage AccessLevel="#AccessLevel#"> The CF_ before AccessImage tells ColdFusion to look in the current directory, then the Custom Tag directory, for a file called AccessImage.cfm. If we need to change the default settings of the tag, we simply pass those attributes. By default, we show the small image. But in some circumstances, we might need to show the big image, which the code in Listing 6 does.
  
  Another great use of custom tags is to deploy code written by someone who's willing to let you use it. For example, the DirectoryList_Enhanced tag in Listing 7, which I downloaded from Allaire's site, makes a list of all the files in a directory and its subdirectories with the file extensions you specify in the filter parameter. Because someone else had already written the 100-plus lines of code I needed, and I needed to use the code in only one place, using the tag made sense.
• Debugging. One especially developer-friendly feature of ColdFusion is its debugging capability. If debugging is turned on, ColdFusion delivers an enormous amount of information with each page to any person with an approved IP address. Because ColdFusion administration is Web-based, from any computer with Internet access I can enter the IP address of the machine I'm on and instantly receive de- bugging information. Screen 1 shows a sample of information the debugger gives you. This information can help you tweak a page for better performance or identify the source of a problem. ColdFusion 4.5 includes the capability to determine the time it takes to process each component that makes up the delivered page. This feature helps you isolate the specific component that's causing a problem.
  
  The debugging tool also shows how the query performed. You receive a count of records returned and how long it took the query to run. In Listing 8, you can see that the query Question returned 1 record and took 1 millisecond (ms) to run. The stored procedure instaNTQuestion returned 1 question and took 0ms to run. But the query getNews shows that it returned cached results because the time is listed as Cached Query. The last pieces of debugging information, URL Parameters and Form Fields, show what values are being passed from one page to another.

Web-Based Administration

• Data-source management. You can create, edit, delete, or verify data sources on the Web server, as Screen 2 shows.
• Page scheduling. The ColdFusion Administrator's scheduling facility lets you schedule the execution of ColdFusion pages and generate static HTML pages. Page scheduling is useful for a page that needs to remain dynamic but whose content changes only every few hours. For example, the Windows 2000 Magazine home page is dynamic because it draws information from a database, but the information doesn't change every minute, and no parameters are passed that require the page to vary. I can schedule ColdFusion to run the dynamic page, save it as an HTML-only page that doesn't require a SQL Server request, and serve the page. Screen 3 shows the scheduled task.
• Caching. ColdFusion offers page and database connection caching. ColdFusion caches pages into memory across multiple user requests, giving you a noticeable performance advantage over having a page built each time a user calls it. ColdFusion also caches and pools database connections for use across multiple client requests. Database connection caching improves a Web site's performance by eliminating ODBC connection times and by having multiple page requests share the same connection.
• Automatic server recovery. You have the option to configure automatic server recovery. For example, you can tell ColdFusion to restart the ColdFusion service after the occurrence of a certain number of failed requests that execute component code such as an ODBC driver or a CFX tag. ColdFusion 4.5 includes the ability to restart the ColdFusion service when requests terminate abnormally. If you enable this option, the engine will track requests that encounter abnormal termination conditions.
• Verity Search Engine. ColdFusion's Verity Search Engine lets you use a directory structure to index any textual data stored in relational databases and standard text and document files. To index static files, you first create a new collection. Then you assign a path and the extensions of the files you want to index. Next, click Update, and you're finished. Indexing ODBC data sources requires you to take a few more steps, but it's also a simple and flexible process.

ColdFusion for All?

In contrast, I've observed programmers with little or no ColdFusion experience create dynamic pages within one week after seeing ColdFusion for the first time. Within a few months, they were creating extensive, complex Web applications. Because of its ease of use and its flexibility, ColdFusion might be the right choice for your Web development environment