At the Developer's Conference a few months back I was fortunate enough to introduce and first demonstrate the next major version of ColdFusion - CF5. However, time constraints prevented me from going into as much detail as I would have liked. As this issue of CFDJ is all about databases, I thought I'd take the opportunity to tell you about two very important and exciting database-related features that will make their debut in CF5.

Of course, I must point out that at the time of this writing CF5 Beta 1 has yet to be released, so some of the details discussed here could change between now and the time the product ships. Having said that, here goesŠ.

Query-of-Queries
"Query-of-queries" (another name being tossed around is "CFSQL") is a mechanism by which ColdFusion developers can execute SQL queries against previous queries. What does this actually mean? Here are a few examples of how you'd use this feature:

• E-commerce sites often load entire product catalogs into queries within the APPLICATION scope to improve the speed of browsing catalogs and to reduce database access. This technique works well when the entire result set is needed, but accessing a specific subset requires complex looping and comparisons (often requiring going back to the database anyway). In CF5 developers will be able to execute SQL queries against that APPLICATION scope query, returning a new query result without having to hit the databases again.
• Full text searches (using the Verity engine) usually require at least two queries - one against Verity and another against the databases (using the data returned from Verity). This process needs to be repeated for each full text search; the results of one search cannot be used for a subsequent search (unless the searches were identical). Using CF5, developers would be able to perform queries against both Verity and the database and then JOIN the results allowing more powerful searches and re-sults reuse.
• <CFPOP> is used to retrieve e-mail from a POP mailbox. is used to retrieve information from directory services. Both tags return results as queries, and those queries can be JOINed to access e-mail, along with any known information about senders and recipients.

As you can see, one of the most intriguing aspects of this new feature is that it can be used for any ColdFusion queries, not just database queries.

Here's what the code might look like for that first example. First the complete catalog is retrieved:

<!--- Read catalog --->
<CFIF NOT IsDefined("APPLICATION.catalog")>
<!--- Lock the scope --->
<CFLOCK SCOPE="APPLICATION" TYPE="EXCLUSIVE" TIMEOUT="30">
<!--- And execute the query --->
<CFQUERY DATASOURCE="dsn" NAME="APPLICATION.catalog">
SELECT prod_id, prod_desc, prod_price, prod_image
FROM products
ORDER BY prod_desc
</CFQUERY>
</CFLOCK>
</CFIF>

<!--- Read-only lock in case being updated --->
<CFLOCK SCOPE="APPLICATION" TYPE="READONLY" TIMEOUT="30">
<!--- Execute the query --->
<CFQUERY DBTYPE="query" NAME="search">
SELECT *
FROM APPLICATION.catalog
WHERE prod_desc LIKE '%#FORM.search#%'
</CFQUERY>
</CFLOCK>

In this second query the DBTYPE is specified as "query", telling Cold-Fusion that you're going to query a query. The SQL code then does a SELECT against the existing query using the query name as the table name. And that's all there is to it.

There are a few limitations in the SQL support - but all the basic language features (sorting, grouping, filtering, aggregate functions, aliases, and some JOIN types) are supported.

Why would you want to use this feature? It could improve performance (although not always; after all ColdFusion is not a full DBMS nor will it become one), it definitely can help alleviate server load, and it can also allow the types of data access and manipulation that aren't possible even with straight database queries.

DSNless Connections
Another exciting and much requested feature (and one not announced at the Developer's Conference) is the ability to use databases without having data sources defined. Until now ColdFusion has supported database access via data sources (ODBC, native database drivers, or OLE-DB). Database access was impossible unless that data source had been defined. As of CF5 this changes. It's now achieved by passing complete database connection strings along with the database query. Here are a few examples of when you'd want to use this feature:

• Developers working on servers that they don't have administrative access to will be able to access databases without having to wait for someone to create data sources for them.
• ISPs or IT departments can allow developers to access databases as needed without having to define data sources.
• Temporary access to databases (perhaps for an import or export operation) can be accomplished easily and efficiently.
• If you work with multiple databases within a single database you'd no longer need a data source defined for each. Instead you'd be able to use a single data source and provide the databases to be used as part of the connection string.
• Many databases support the passing of additional connection data that can't be specified in the data source configuration. This information can now be passed as part of the connection string.

Using DNSless connections re-quires that the DBTYPE be specified as "__DYNAMIC__" (with two under-scores before and after the text). Here's an example using Microsoft SQL Server:

<!--- Connect to database using a connect string ---> <CFQUERY NAME="data_import" DATASOURCE= "__dynamic__" CONNECTSTRING="DRIVER={SQL SERVER};SERVER=(local);uid=#SESSION. user#;PWD=#SESSION.password#;DATABASE= data_import"> SELECT * FROM import </CFQUERY>

• <CFGRIDUPDATE>
• <CFINSERT>
• <CFQUERY>
• <CFSTOREDPROC>
• <CFUPDATE>

And finally, queries against DNS-less connections can be used like any other queries. They can be looped over, cached, have additional queries executed against them, and more.

Other Features
CF5 boasts several other important features that, while not directly database related, are worth mentioning:

• is a new tag that al-lows the dynamic generation of business graphs and charts (pie charts, bar charts, etc.). can take a query as an attribute so as to generate database-driven queries.
• The new ColdFusion Adminis-trator features sophisticated log file analysis that can make looking for database- or SQL-related problems much easier.
• The ColdFusion Administrator features extensive integrated context-sensitive help, which should make managing database settings easier.