This chapter is from the book
This chapter is from the book
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:
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:
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.
Using the SQL statement SELECT, you will select ALL the fields using the * wildcard character.
Using the SQL statement FROM, you will SELECT * FROM the Customers table.
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–13
Output of all the fields in the Customers
table.