Home > Articles

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

Project II: Retrieving Data

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

<CFSET>

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

<CFSET firstName = "Mike">
<CFSET lastName = "Fredrick">

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 easily concatenate variables by assigning a variable to equal the value of two or more variables:

<CFSET firstName = "Mike">
<CFSET lastName = "Fredrick">
<CFSET wholeName="#firstName# #lastName#">

Now the variable wholeName would 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 from which you are querying the database.

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 from which to start the output.

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 a variable that must be processed before being displayed to the user's browser. Let's set two variables called firstName and lastName and assign them values:

For example:

<CFSET firstName = "Mike">
<CFSET lastName = "Fredrick">

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

Will output to the screen:

Welcome back firstName lastName!

Not exactly what we were going for. But if you wrap (#) pound signs around the variables firstName and lastName, then ColdFusion will know to process these in a special way, because they are not just ordinary characters.

For example:

<CFSET firstName = "Mike">
<CFSET lastName = "Fredrick">

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

Will output to the screen:

Welcome back Mike Fredrick!

Keeping these basics in mind, we can now grab some data from the Customers 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

CONNECTSTRING="connection string"

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. *Not used when 'DBTYPE=dynamic' or 'DBTYPE=query'.

DBTYPE

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

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

Used to specify a connection not defined in the ColdFusion Administrator. When using 'DYNAMIC', you are required to use the 'CONNECTSTRING' attribute to define your ODBC connection information.

DBSERVER

Used with native drivers to specify the database server machine.

DBNAME

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

CONNECTSTRING

Defines additional connection details of a connection specified in the ColdFusion Administrator. This can also be used to override these settings.

*If using DBTYPE-"dynamic", the CONNECTIONSTRING attribute is required.

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 datasource. <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 datasource 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 Customers 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 in 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 Customers
</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>
</TR>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>

Figure 1-13FIGURE 1–13 Output of all the fields in the Customers table.

  • + Share This
  • 🔖 Save To Your Account