Home > Articles

  • Print
  • + Share This
This chapter is from the book

Project II: Retrieving Data

Now that you have your database set up, it's time to pull some data from those tables. Here are a few basic elements of ColdFusion you need to know in order to make this happen. These will help you understand how ColdFusion works and give you some hands-on experience right up front.

<CFSET>

The <CFSET> tag allows you to set a value to a ColdFusion variable. For example:

Now that you have set the variables firstName and lastName, you can reference them anywhere in the ColdFusion template and they will hold these values. You will learn other ways you can use this as you are introduced to more functions.

Concatenating Variables

You can also very easily concatenate variables by assigning a variable to equal the value of two or more variables:

Now the variable wholeName will be: Mike Fredrick

<CFOUTPUT>

<CFOUTPUT

 

 

QUERY="name of the query"

Optional

 

GROUP="query column"

Optional

 

GROUPCASESENSITIVE="yes | no"

Optional

 

STARTROW="row of query to start from"

Optional

 

MAXROWS="maximum number of rows to return"

Optional

</CFOUTPUT >

 


The <CFOUTPUT> tag will allow you to output ColdFusion content requests to your browser, such as the variables that were set above.

TABLE 1–4 <CFOUTPUT> Attributes

Attribute

Description

QUERY

The name set in the CFQUERY in which you are querying the database from.

GROUP

Defines the column in the query to use when sets of records are grouped.

GROUPCASESENSITIVE

Defines whether to group by case sensitivity. Default value is set to YES.

STARTROW

Defines the row of the recordset to start the output from.

MAXROWS

Defines the number of rows from the recordset you want returned.


#—The pound (#) sign

Whenever you are going to be displaying a variable to the browser you must enclose the variable within pound characters. This lets the ColdFusion server know at runtime that this is not just ordinary text but it is a variable that must be processed before being displayed to the user's browser.

For example:

<CFOUTPUT>Welcome back firstName lastName!</CFOUTPUT>

Will output to the screen:

Welcome back firstName lastName!

If you wrap pound (#) signs around the variables FirstName and LastName, then ColdFusion will know to process these since they are not just ordinary characters.

For example:

<CFOUTPUT> Welcome back #firstName# #lastName#!</CFOUTPUT>

Will output to the screen:

Welcome back Mike Fredrick!

Keeping these basics in mind, we can now grab data from the Customer and Products table from the Shelley Catalog we built earlier in this chapter.

<CFQUERY>

<CFQUERY

 

 

NAME="name of query"

Required

 

DATASOURCE="datasource name"

Required

 

DBTYPE="database type"

Optional

 

DBSERVER="database server"

Optional

 

DBNAME="database name"

Optional

 

USERNAME="username"

Optional

 

PASSWORD="password"

Optional

 

MAXROWS="maximum number of rows"

Optional

 

BLOCKFACTOR="block-size"

Optional

 

TIMEOUT="time in milliseconds"

Optional

 

CACHEDAFTER="date"

Optional

 

CACHEDWITHIN="time-span"

Optional

 

PROVIDER="COM provider"

Optional

 

PROVIDERDSN="datasource name"

Optional

 

DEBUG="Yes | No"

Optional

</CFQUERY>

 


TABLE 1–5 <CFQUERY> Attributes

Attribute

Description

NAME

Defines what you would like to name the query.

DATASOURCE

Defines the name of the data source you set up in your ODBC connection.

DBTYPE

Defines the database driver type. By default, ODBC is set which is perfectly fine for most cases. If using other types of connections like DB2, Informix, OLEDB, Oracle, or Sybase use the following accordingly:

  • DB2
  • Informix73
  • OLEDB
  • Oracle73
  • Oracle80
  • Sybase11

DBSERVER

Used with native drivers to specify the database server machine.

DBNAME

SQLOLEDB and Sybase drivers specific; specifies the name of the database.

USERNAME

Defines the username set on the data source.

PASSWORD

Defines the password set on the data source.

MAXROWS

Defines the maximum number of rows to return from the recordset.

BLOCKFACTOR

Used to set a maximum number of records an Oracle or ODBC driver will return from a query.

TIMEOUT

Defines the time in milliseconds to timeout from query.

CACHEDAFTER

Returns cached data from the same query that was executed previously. SQL statement and data source must be the same as the original to retrieve cached data.

CACHEDWITHIN

Retrieves the cached data if the query was run within the parameter supplied here. Use the ColdFusion function CreateTimeSpan to create a valid entry.

PROVIDER

Defines the COM provider and is used for OLE-DB only.

PROVIDERDSN

Defines the name of the COM provider and is used for OLE-DB only.

DEBUG

Used for debugging queries.


The <CFQUERY> tag is the method of communication to the ColdFusion application server, which allows you to retrieve data from a data source. <CFQUERY> uses SQL to extract the data you are looking for. For example, let's start the application with a statement to retrieve all the customers.

  1. <CFQUERY NAME="getCustomers" DATASOURCE="shelleyCatalog">

  2. SELECT *

  3. FROM Customer

  4. </CFQUERY>

How this works

  1. NAME defines the name used to reference this query statement. DATASOURCE is the name of the data source you set up in your ODBC connection. In this case we are using shelleyCatalog.

  2. Using the SQL statement SELECT you will select ALL the fields using the * wildcard character.

  3. Using the SQL statement FROM you will SELECT * FROM the Customer table.

  4. Close the <CFQUERY> statement with </CFQUERY>.

Now that you have made your request to the database for the information, you can display it to the browser with <CFOUTPUT>.

The <CFOUTPUT> now has the QUERY attribute attached to it and the name of the query that you specified with <CFQUERY> as the NAME attribute. <CFOUTPUT> will now loop through all the records pulled from the <CFQUERY> and display them. We will throw this into a table to produce a nicely formatted output.

Script 1-1 firstQuery.cfm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>My First QUERY!</TITLE>
</HEAD>
<BODY>
<CFQUERY NAME="getCustomers" DATASOURCE="shelleyCatalog">
SELECT *
FROM Customer
</CFQUERY>
<H2>Customers of Shelley Biotechnologies, Inc.</H2>
<TABLE BORDER="0">
<CFOUTPUT QUERY="getCustomers">
      <TR>
      <TD>#customerID#</TD>
      <TD>#firstName#</TD>
      <TD>#lastName#</TD>
      <TD>#address#</TD>
      <TD>#city#</TD>
      <TD>#state#</TD>
      <TD>#zip#</TD>
      <TD>#country#</TD>
      <TD>#phone#</TD>
      <TD>#email#</TD>
      </TD>
</TR>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>

FIGURE 1–13 Output of all the fields in the Customer table.

  • + Share This
  • 🔖 Save To Your Account