CPCUG Monitor

Creating dynamic websites with ColdFusion

Part 2: Displaying data with SQL

by Michael Smith

TeraTech http://www.teratech.com/

What is ColdFusion

In this article we continue to look at what ColdFusion is and how you can use it for dynamic website creation. We cover displaying data from any database and testing and debugging techniques. We also mention where you can learn more about ColdFusion, including the free CPCUG ColdFusion Conference in June.

 

In case you missed the last article that introduced ColdFusion, let me explain what it is. ColdFusion is a programming language based on standard HTML (Hyper Text Meta Language) that is used to write dynamic webpages. It lets you create pages on the fly that differ depending on user input, database lookups, time of day or what ever other criteria you dream up! ColdFusion pages consist of standard HTML tags such as <FONT SIZE=”+2”> together with CFML (ColdFusion Meta Language) tags such as <CFQUERY>, <CFIF> and <CFLOOP>.  ColdFusion was introduced by Allaire in 1996 and is currently on version 4.0

 

Creating a Page to Select and Display Data

To select and output data from a database, you create a ColdFusion application page that uses the CFQUERY tag, plus any HTML formatting you want. The database query is done with SQL (Structured Query Language) inside the CFQUERY tag.

 

To illustrate, here is an example from a database called Company associated with an ODBC (Open DataBase Connectivity) data source named CompanyDB.

 

To create a select query called EmployeeList that retrieves all of the records in the Employees table, use this syntax:

 

<CFQUERY NAME="EmployeeList" DATASOURCE="CompanyDB">

SELECT FirstName, LastName, PhoneNumber, Email

FROM Employees

ORDER BY LastName

</CFQUERY>

 

EmployeeList.cfm

SQL and ODBC

SQL is an industry standard language used to interact with relational databases. While the advanced features of SQL are extremely powerful, learning the basics of SQL is relatively simple. You can learn the SQL you need to know to use ColdFusion productively in a few minutes.

The basic SQL statements are:

Action	SQL syntax
Read records	SELECT FieldsList FROM TableName WHERE conditions ORDER BY FieldsList
Add records	INSERT INTO TableName (FieldsList) VALUES (ValuesList)
Delete records	DELETE * FROM TableName WHERE conditions
Change records	UPDATE TableName SET field = value WHERE condition

 

In practice, you rarely need to write SQL statements by hand since there are so many high-quality visual SQL tools available. Visual SQL tools include the Microsoft Access query builder or the Microsoft Query applet bundled with Microsoft Office. These tools allow you to visually define and preview queries and then copy and paste the resulting SQL statement into other applications such as ColdFusion. This will save you a lot of cryptic ODBC error messages and SQL syntax errors too!

 

By the way, ODBC is a way for programs such as ColdFusion to use the same SQL with different backend databases without having to change any code. The ODBC driver handles all the details of how to talk to the particular database used and you are free to spend more time on other issues such as the user interface. ODBC drivers are available for most databases including:

·         Access  SQL server

·         Excel

·         Comma delimited text

·         HTML tables

·         FoxPro

·         Paradox

·         Oracle

·         Sybase

·         DB2

Displaying the Results of a Selection

Once you’ve created a CFQUERY in your application page file, you can then reference its results within other CFML tags. The query results can be used to dynamically create an HTML page.

 

As you learn to use CFML tags in application page files, keep in mind that you can also use any HTML tags and text in application page files. Wherever you use standard HTML tags and text inside your application page, ColdFusion simply passes the tags and text directly back to the client browser.

 

The most flexible way to display data retrieved from a CFQUERY is to define a CFML output section in your application page file using the CFOUTPUT tag. Output sections can be linked to a specific query or contain content from multiple queries.

 

 Basic output sections have the following syntax:

 

     <CFOUTPUT QUERY="queryname" MAXROWS=n STARTROW=s>

        Literal text, HTML tags, and

        dynamic field references (e.g. #FirstName#)

     </CFOUTPUT>

 

Note the use of the pound signs (#) to enclose the naked variable name FirstName. A CFOUTPUT tag can contain:

 

·         Literal text

·         HTML tags

·         References to query columns

·         References to dynamic parameters like form fields

·         Functions

 Example

If you execute a CFQUERY called EmployeeList and you want to use it to display the first name, last name, and email address of each employee (separated by a horizontal rule), use the following CFOUTPUT section:

 

     <CFOUTPUT QUERY="EmployeeList">

        <HR>

        #FirstName# #LastName#

        (Email: <A HREF="mailto:#Email#">#Email#</A>) <BR>

     </CFOUTPUT>

EmployeeList2.cfm

If there were three records in the query result set, the HTML generated by this CFOUTPUT section would look like this:

 

        <HR>

        Deborah Jones

        (Email: <A HREF="mailto:[email protected]">[email protected]</A>)<BR>

        <HR>

        John Smith

        (Email: <A HREF="mailto:[email protected]">[email protected]</A>)<BR>

        <HR>

        Frank Wilson

        (Email: <A HREF="mailto:[email protected]">[email protected]</A>)<BR>

 

Notice the use of the standard HTML <A HREF> tag with the mailto: URL to give a live email link on the page.

Presenting Query Output in a Table

Presenting the results of queries using CFOUTPUT sections is usually adequate if the number of records returned is small. However, you might need a more compact and structured display of query results. Because the CFOUTPUT tag can include any HTML, you can use standard HTML table tags to build a table dynamically. The result is a clear, concise rendering of your query results.

 

Let’s change our above example to display in table:

 

<TABLE>

<TR>

<TD>First Name</TD>

<TD>Last Name</TD>

<TD>Email</TD>

</TR>

<CFOUTPUT QUERY="EmployeeList" MAXROWS=10>

      <TR>

     <TD>#FirstName#</TD>

<TD>#LastName#</TD>

<TD><A HREF=”mailto:#Email#”>#Email#</A></TD>

</TR>

     </CFOUTPUT>

     </TABLE>

EmployeeList3.cfm

 

This code creates a table with three columns labeled "FirstName," "Last Name" and "Email”. The table draws its data from the CFQUERY named "EmployeeList" and shows no more than 10 rows.

 

<TABLE>

<TR>

<TD>First Name</TD>

<TD>Last Name</TD>

<TD>Email</TD>

</TR>

<TR>

<TD>John</TD>

<TD>Smith</TD>

<TD><TD><A HREF=”mailto:[email protected]” [email protected]</A></TD>

<TR>

…

</TABLE>

 

In your browser you should see something like:

First Name	Last Name	Email
Deborah	Jones	[email protected]
John	Smith	[email protected]
Frank	Wilson	[email protected]

 

Using Dynamic Parameters in SQL Statements

You can harness the real power of the CFQUERY tag when you dynamically customize the contents of the SQL attribute by using parameters passed to the application page. The SQL statement is customized  by embedding dynamic parameters within the SQL text. Dynamic parameters (also called variables) normally include form entries, parameters passed in the URL, and CGI environment information.

 

The convention for including a dynamic parameter inside a SQL statement is to enclose it in pound (#) signs (e.g., #:LastName#). Whenever ColdFusion sees text enclosed by # signs, it searches through all Form, URL, cookies, client, and CGI variables looking for one that matches the specified name. When it finds the name, it substitutes the appropriate value for the parameter reference. If you specify the variable type (Form) in our example below then less searching has to be done and your code will run a bit faster.

Example of dynamic SQL

If you created a form to allow end users to search for employees by last name, you could use the following SQL statement with dynamic parameters:

<CFQUERY NAME="EmployeeList" DATASOURCE="CompanyDB">

                SELECT * FROM Employees

WHERE LastName = '#Form.LastName#'

</CFQUERY>

 

     If the user entered "Rucker" for LastName, the SQL statement sent to the database would be:

 

     SELECT * FROM Employees

  WHERE LastName = 'Rucker'

 

 Sources for dynamic parameters

The following table summarizes the primary sources from which you can draw dynamic parameters for use in your SQL queries:

  Variable Types in CF

Field	Description
Form fields	The most common way of passing parameters to an application page. When a user enters data in a form field, a parameter bearing the name of the form field (#Form.formfield#) is passed to the application page.
URL parameters	Parameters that are embedded on the end of a URL  (such as, /input.cfm?name=adam).
Server	A variable that remains available to all application pages until the ColdFusion application server terminates.
CGI environment	An environment variable interpreted by the browser.  Every request sent to an application page has several environment variables sent to it that relate to the context in which it was sent. The variables available depend on the browser and server software in use for a given request.
Query objects	Query columns you can reference once a query has been executed. Once a query has been run, its results can be used as dynamic parameters in other queries. For example, a query that returns a column called UserID can be Referenced in the following form:  queryname.UserID
Cookies	General mechanism for storing and retrieving information About the Web client (browser).
Client variables	Used to store persistent client variables in the system Registry on the Web server. These variables are specific to an individual browser accessing your ColdFusion application.
Session variables	Variables available only for an individual session. Session Variables are tied to an individual client and persist for as long as that Client ID maintains a session.
Application variables	Variables available only for an individual application. Application names are defined in the CFAPPLICATION tag, which is typically used in the application.cfm file.

 

To Learn More

If you are interesting in learning more about ColdFusion CPCUG and TeraTech are holding a free ColdFusion User Conference on Saturday 6/26/99 at the Masur Auditorium from 9am to 6pm. The morning will contain introductory sessions and the afternoon advanced ones. You can sign up for the conference at http://www.teratech.com/cfconf/ or call 301-424-3903.

You can download a free 30 day-evaluation version of ColdFusion from Allaire or request a free eval CD-ROM from

the Allaire website http://www.allaire.com/ 

 

Allaire Corporation

1 Alewife Center

Cambridge, MA 02140

 

Tel: 617.761.2000 voice

Fax: 617.761.2001 fax

Toll Free: 888.939.2545

Email: [email protected]

Web: www.allaire.com

 

CPCUG Discount

ColdFusion costs $1295 direct from Allaire. TeraTech is offering a 10% discount for all CPCUG members and their organizations. Call 301-424-3903 for this

SQL Resources

SQL Help and Tutorial - http://www.ilook.fsnet.co.uk/ora_sql/sqlmain.htm

Tutorial - Using Microsoft Access-SQL - http://www.cs.jcu.edu.au/Subjects/cp1500/1999/tutes/AccessSqlEx4-2.html

SQL Reference - http://nscpcw.physics.upenn.edu/db2_docs/db2s0/db2s003.htm

SQL Reference (Microsoft) - http://msdn.microsoft.com/library/wcedoc/vbce/sql.htm

Sams Teach Yourself Sql in 10 Minutes by Ben Forta - http://www.amazon.com/exec/obidos/ASIN/0672316641/

SQLServer 7.0 Books Online available for download from the following Microsoft Web site: http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe

ColdFusion Resources

Allaire also maintain an extensive knowledge basis and tech support forums on their website.

CPCUG and TeraTech ColdFusion Conference http://www.cfconf.org

TeraTech maintains a ColdFusion code cuttings called ColdCuts at http://www.teratech.com/ColdCuts/. This page also has links to about a dozen ColdFusion white papers in the CF Info Center.

The Maryland ColdFusion User Group meets the second Tuesday of each month at Backstreets Cafe, 12352 Wilkins Avenue, Rockville. See http://www.cfug-md.org/ for details and directions.

The DC ColdFusion User Group meets the first Wednesday each month at Figleaf , 16^th and P St NW, Washington DC. See the DCCFUG page on http://www.figleaf.com/ for details and directions.

Summary

In this article we have looked at how to search a database using ColdFusion and SQL SELECT and how to display the results in a table. We also looked at dynamic queries and ColdFusion variable types that you can use in your pages.

Bio

Michael Smith is president of TeraTech, a ten year old Rockville Maryland based consulting company that specializes in ColdFusion, Database and Visual Basic development. You can reach Michael at [email protected] or 301-424-3903.