This Issue:

Dynamic SQL Queries By Mike Chorost
Listing Session and Application Variables by Tad Murphy
Using Structures to Delete a Session Variable by Charles Arehart
Joining Multiple Tables: The Correct Syntax by Charles Arehart
Emulating a "Words" Function by Charles Arehart

Let's say you want to create a dynamic SQL query -- that is, a CFQUERY that looks up different fields depending on circumstances. Why would this be useful? Let's say you're creating an employee roster application. You want it to show full information to employees (such as first name, last name, phone number, email address, etc.) but only partial information to outsiders (such as the above fields minus phone.) Such a thing is simple enough to code when you have only four fields and two levels of access, but what if you have two dozen fields and six levels of access? Then coding separate CFQUERY solutions for each possible combination becomes a nightmare.

The solution is to create a CFQUERY where the SELECT line is generated dynamically.

Let's start with what non-dynamic code would look like. Here is a simple CFQUERY which looks up fields in the way we all know and love:

<CFQUERY Datasource = "xyz" Name = "GetRosterData">
SELECT FirstName, LastName, Phone, Email
FROM Employees
</CFQUERY>

Dynamic code, on the other hand, would look like this:

<CFQUERY Datasource = "xyz" Name = "GetRosterData">
SELECT #evaluate(de(QueryString))#
FROM Employees
</CFQUERY>

QueryString is a list variable, e.g. "FirstName, LastName, Phone, Email." The code #evaluate(de(QueryString))# evaluates QueryString in such a way that ColdFusion thinks it's getting QueryString itself. It's a bloomin' miracle!

You can easily set QueryString to list whatever fields you need for a particular circumstance. You can, of course, set it manually:

<CFSET QueryString = "FirstName, LastName, Phone, Email, Address1, Address2, City, State, Zip">

Even better, you can generate QueryString dynamically. Let's say you've got a table named "PermittedFields" that says which access levels get to see which fields. Then you can generate QueryString in a two-step operation. You run a query that returns what fields the user gets to see:

<CFQUERY Datasource = "xyz" Name = "GetPermittedFields">
SELECT FieldName FROM PermittedFields
WHERE AccessLevel=#Session.AccessLevel#
</CFQUERY>

Then you initialize QueryString and CFLOOP the field names into it:

<CFSET QueryString = "">
<CFLOOP Query = "GetPermittedFields">
<CFSET QueryString = ListAppend(QueryString, "#FieldName#",",") >
</CFLOOP>

And there you have QueryString freshly minted and ready to go.

This is a simple but powerful solution to the problem of needing CFQUERY code that covers a multitude of possible cases.

In Cold Fusion 4.0, Session and Application variables are registered as a structure. This means that you can use any of the Struct functions to gain access to the names and values of these variables. Make sure you are using CFLOCK around all your session and application variable accesses, as these are shared requests. If we fail to use CFLOCK, some other template could update the variables while we are attempting to read them. The CFLOCK will prevent that.

Below is an example of using CFLOOP to access the Session Variables structure, followed by the runtime results of the code. NOTE: This code will not work unless Session management has been "enabled" in the Cold Fusion Administrator and "turned on" with a CFAPPLICATION tag in the page or the application.

First we set our Session Variables

<CFSET Session.test1 = "testvalue1">
<CFSET Session.test2 = "testvalue2">
<CFSET Session.test3 = "testvalue3">
<CFSET Session.test4 = "testvalue4">

Then we loop over our "session" collection

Session Variables
<CFLOCK Timeout = "30" Name = "getSessionVars">
<CFOUTPUT>
<CFLOOP Item = "name" Collection = #session#>
#name# = #session[name]#
</CFLOOP>
</CFOUTPUT>
</CFLOCK>

Runtime Results

Session Variables
TEST1 = testvalue1
TEST2 = testvalue2
TEST3 = testvalue3
TEST4 = testvalue4

In addition to making it easy to refer to session and application variables, using structures also provides a simple and straightforward means to delete such a variable. The StructDelete function does the trick.

The specific format of the StructDelete would be to name the variable type (session or application) as the first parameter, without quotes, and to name the specific variable in the second parameter, in quotes. For example, the following deletes the session variable "login":

<CFSET Foo = StructDelete(session,'login')>>

This isn't too obvious from the reference manual, because it doesn't actually explain what should and shouldn't be quoted, and it speaks in terms of structures and their keys. In our example, session is the structure and "login" is the key.

If you've tried to join more than one table in SQL, you may have encountered a challenge in figuring out the correct syntax. Here is a three-table join in the two different forms of join syntax: the first uses the JOIN keywords in the FROM clause while the second defines the joins in the WHERE clause.

Either approach will work in either Access or SQL Server.

SELECT *
FROM (tablea INNER JOIN tableb ON tablea.columna = tableb.columna)
INNER JOIN tablec ON tableb.columnb = tablec.columnb

Note that the challenge is in arranging the parentheses and where you put things relative to them. When using the JOIN clause, the first join is enclosed in parentheses and lists the first table name before its INNER JOIN keyword. The second join has no parentheses and DOES NOT list a table before the INNER JOIN. Instead, the first join can be thought of as the "table" on which the second join is made.

Using the simpler syntax, the join would be:

SELECT *
FROM tablea, tableb, tablec
WHERE tablea.columna = tableb.columna AND tableb.columnb = tablec.columnb

You could join more tables, but the performance impact should be seriously considered.

Though CF provides many string functions, one common one that it doesn't offer is a "words" function, to count the number of words in a string.

This can be easily emulated, with:

<CFSET words = ListLen ("string to count", " ")>

For the above CFSET, "words" would be set to 3. Kind of nifty.