Connecting to Your Database
Server-side data
A Web application page is different from a client-side Web page because you can pull data directly from a database, which resides on the server. This means that you can select data and display it, but also that you can insert, delete or update information on the fly from a Web page. The database component for ColdFusion is called the datasource.
To access a database, you need to use:
- ColdFusion datasources
- The CFQUERY tag
- SQL commands
You have some review material in your handout, and a link to the full manual and this database introduction from Adobe on the Schedule page.
Databases are organized in tables, which are collections of related items. For example, a table might contain the names, street addresses, and phone numbers of individuals. Think of a table as a grid of columns and rows. In this case, one column contains names, a second column contains street addresses, and the third column contains phone numbers. Each row constitutes one data record. In this case, each row is unique because it applies to one individual. Rows are also referred to as records. Columns are referred to as fields. Each table has a field called the primary key that assigns a unique numerical identifier to each record.
You can organize data in multiple tables. This type of data structure is known as a relational database and is the type used for all but the simplest data sets. The field that links the two tables is called the key, or the foreign key. In our case it is the Department ID field (called fldDep_id in the database, staffDir.mdb), the field that is also the primary key in each table.
Suggestions:
-
The field names are not case-sensitive, but it's a good idea to be consistent anyway. To make them redable you can use caps (fldEmail) or underscore (fld_email).
- The fields have datatypes defined for them (data/numeric, character string/text, logical/true-false, etc.) so that data needs to be entered consistent with the datatype.
As said, the database file that we are using (staffDir.mdb) is referred to by ColdFusion as the datasource in the CFML tags such as <CFQUERY datasource="fred">. You will use the CFAdministrator to add data sources to your ColdFusion Server so that you can connect to the databases from your ColdFusion files. This puts all the information about a ColdFusion Server's database connections in a single, easy-to-manage location.
Be careful that there is not another datasource with the same name as yours, or you might end up connecting to someone else's database.
About Open Database Connectivity
Open Database Connectivity (ODBC) is a standard interface for connecting to a database from an application. For Windows or Unix/Linux server (in the case of the DArt server), there are drivers for many common database formats such as
-
Microsoft SQL Server
-
Microsoft Access and FoxPro databases and many others...
To add (bind to) a datasource:
- FTP the database (staffDir.mdb) to your folder on the DArt server.
- Start the ColdFusion Administrator. http://139.84.48.107/cfide/administrator/index.cfm
The Administrator prompts you for a password. - The Administrator password on the DArt server is CFMX-0924.
- If you're not in the datasources page, on the left-hand menu go to Data & Services > Datasources.
- For the driver type, with ColdFusion MX - select
Microsoft Access driver.
- Name the data source, for example, CompanyInfo, or Fred.
- Select Microsoft Access Driver (*.mdb) from the drop-down box to describe the ODBC driver.
- Click Add.
- In the Database File field, enter the full path of the database. (You can
also use the Browse button to locate the file in the server.). Note you will probably have to browse to the file on the DArt server, using your folder: at
E:\Users\Dartstudents\yourname\staffDir.mdb - Click Create to create the CompanyInfo (Fred) data source. The data source is added to the data source list.
- Locate CompanyInfo (Fred) in the data source list.
- Select Verify to run the verification test on the data source. If the data source was created, you should see this message: The connection to the data source was verified successfully.
- Click Go Back to return to the data sources list.
- Logout.
You can query databases to retrieve data on the fly. The retrieved data, called the result set, is stored on that page as a query object. When retrieving data from a database, you perform the following tasks:
-
Use the CFQUERY tag on a page to tell ColdFusion how to connect to a database.
-
Write SQL commands inside the CFQUERY block to specify the data that you want to retrieve from the database.
-
Later on the page, reference the query object and use its data values in any tag that presents data, such as CFOUTPUT.
The CFQUERY tag. Note that tags are not case-sensitive, as with HTML. Uppercase is used mostly for redability, and you'll see that Dreamweaver writes them as lowercase <cfquery>.
The CFQUERY tag is one of the most frequently used CFML tags. You use it in conjunction with the CFOUTPUT tag so that you can retrieve and reference the data returned from a query.
When ColdFusion encounters a CFQUERY tag on a page, it does the following:
-
Connects to the specified data source.
-
Performs SQL commands that are enclosed within the block.
-
Returns result set values to the page in a special kind of variable called a query object. You specify the query object’s name in the CFQUERY tag’s name attribute. Often, people refer to the query object simply as "the query".
The CFQUERY tag syntax
<CFQUERY name="EmpList" datasource="Fred">
SQL statements here
</CFQUERY>
In this example, the query code tells ColdFusion to:
-
Use the Fred data source to connect to the staffDir.mdb database.
-
Store the retrieved data in the query object EmpList.
Follow these rules when creating a CFQUERY tag:
-
The CFQUERY tag is a non-empty tag, that is, it has an opening <CFQUERY> and ending
-
</CFQUERY> tag.
-
Use the name attribute to name the query object so that you can reference it later on the page.
-
Use the datasource attribute to name an existing data source that should be used to connect to a specific database. (You can also do this in the tag without having done the connection (binding) in the Administrator.)
-
Always surround attribute values with double quotes (").
-
Place SQL statements inside the CFQUERY block to tell the database what to process during the query.
-
When referencing text strings in SQL, use single quotes (’). For example,
SELECT *
FROM someTable
WHERE FirstName=’Fred’selects every record (because of the wildcard *) from someTable in which the value of the FirstName field is Fred.
In between the begin and end CFQUERY tags, write the SQL that you want the database to execute.
For example, to retrieve data from a database:
-
Write a SELECT statement that lists the fields or columns that you want to select for the query.
-
Follow the SELECT statement with a FROM clause that specifies the database tables that contain the columns.
Tip You can reference that data set by name using the CFOUTPUT tag later on the page.
Basic SQL syntax elements
The following sections present brief descriptions of the main SQL command elements.
Statements
A SQL statement always begins with a SQL verb. The following keywords identify commonly used SQL verbs:
|
Keyword |
Description |
|
SELECT |
Retrieves the specified records |
|
INSERT |
Adds a new row |
|
UPDATE |
Changes values in the specified rows |
|
DELETE |
Removes the specified rows |
Statement clauses
Use the following keywords to refine SQL statements:
|
Keyword |
Description |
|
FROM |
Names the data tables for the operation |
|
WHERE |
Sets one or more conditions for the operation |
|
ORDER BY |
Sorts the result set in the specified order |
|
GROUP BY |
Groups the result set by the specified select list items |
Operators
The following basic operators specify conditions and perform logical and numeric functions:
|
Operator |
Description |
|
AND |
Both conditions must be met |
|
OR |
At least one condition must be met |
|
NOT |
Exclude the condition following |
|
LIKE |
Matches with a pattern |
|
IN |
Matches with a list of values |
|
BETWEEN |
Matches with a range of values |
|
= |
Equal to |
|
<> |
Not equal to |
|
< |
Less than |
|
> |
Greater than |
|
<= |
Less than or equal to |
|
>= |
Greater than or equal to |
|
+ |
Addition |
|
- |
Subtraction |
|
/ |
Division |
|
* |
Multiplication |
As discussed, you build queries using the CFQUERY tag and SQL.
To query the table:
-
Create a new file in Notepad or Dreamweaver
-
Edit the page so that it appears as follows:
<html>
<head>|
<title>Employee List</title>
</head>
<body>
<h1>Employee List</h1>
<CFQUERY name="EmpList" datasource="Fred">
SELECT FirstName,
LastName,
Salary,
Contract
FROM Employee;
</CFQUERY>
</body>
</html> - It's good practice to add the trailing semi-colon, although the Adobe manual doesn't always do that.
-
Save the page as emplist.cfm in xx430 and FTP it to the Dart server.
-
Return to your browser and enter the following URL to view empList.cfm: http://csc-srv1.lasalle.edu/yourID/xx430/emplist.cfm (OR http://localhost:8501/jb430/emplist.cfm if we were doing local emulation)
-
View the source in the browser.
The ColdFusion Server creates the EmpList data set, but only HTML and text is sent back to the browser so you just see the heading "Employee List." To display the dataset on the page, you must code tags and variables to output the data.
Reviewing the code
The query you just created retrieves data from the StaffDir database. The following table describes the code and its function:
|
Code |
Description |
|
<CFQUERY name="EmpList" datasource="Fred"> |
Queries the database specified in the Fred data source |
|
SELECT FirstName, LastName, Salary, Contract FROM Employee |
Gets information from the FirstName, LastName, Salary, and Contract fields in the Employee table |
|
</CFQUERY> |
Ends the CFQUERY |
Things to remember
When creating queries to retrieve data, keep the following guidelines in mind:
-
Enter the query name and datasource attributes in the opening CFQUERY tag.
-
Surround attribute values with double quotes when there is a space in the character string; best practice is to do that always.
-
Make sure that a datasource exists in the ColdFusion Administrator before you call it in a CFQUERY tag.
-
The SQL that you write is sent to the database and performs the actual data retrieval.
-
Columns and tables that you refer to in your SQL statement must exist, otherwise the query will fail.
-
Reference the query data by naming the query in one of the presentation tags, such as CFOUTPUT later on the page.
After you define a query on a page, you can use the CFOUTPUT tag with the query attribute to specify the query object that contains the data you want to output to a page. When you use the query attribute:
-
ColdFusion loops over all the code contained within the CFOUTPUT block, once for each row returned from a database.
-
You must reference specific column names within the CFOUTPUT block to output the data to the page.
-
You can place text, CFML tags, and HTML tags inside or surrounding the CFOUTPUT block to format the data on the page.
-
You do not have to specify the query object name (the table name) when you refer to a query column. For example, if you specify the Emplist query in your CFOUTPUT tag, you can refer to the Firstname column in the Emplist query as either Emplist.Firstname or just Firstname.
The CFOUTPUT tag accepts a variety of optional attributes but, ordinarily, you use the query attribute to define the name of an existing query.
To output query data on your page:
-
Return to empList.cfm in Dreamweaver or your text editor.
-
Edit the file so that it appears as follows:
<html>
<head>
<title>Employee List</title>
</head>
<body>
<h1>Employee List</h1>
<CFQUERY name="EmpList" datasource="Fred">
SELECT FirstName, LastName, Salary, Contract
FROM Employee;
</CFQUERY><CFOUTPUT query="EmpList">
#FirstName#, #LastName#, #Salary#, #Contract#<br>
</CFOUTPUT></body>
</html>
-
Save the file as emplist.cfm.
-
View the page in a browser. A list of employees appears in the browser, with each line displaying one row of data.
Reviewing the code
You now display the results of the query on the page. The following table describes the code and its function:
| Code | Description |
| <cfoutput query="EmpList"> | Display information retrieved in the EmpList query. Display information for each record in the query, until you run out of records. |
| #FirstName#, #LastName#, #Salary#, #Contract# | Display the value of the FirstName, LastName, Salary, Contract fields of each record, separated by commas and spaces. |
| <br /> | Insert a line break (go to the next line) after each record. |
| </cfoutput> | End the cfoutput block. |
Query output notes and considerations
When outputting query results, keep the following guidelines in mind:
- A cfquery must precede the cfoutput that references its results. Both must be on the same page (unless you use the cfinclude tag).
- It is a good idea to run all queries before all output blocks.
- To output data from all the records of a query, specify the query name by using the query attribute in the cfoutput tag.
- Columns must exist and be retrieved to the application in order to output their values.
- Inside a cfoutput block that uses a cfquery attribute you can optionally prefix the query variables with the name of the query, for example Emplist.FirstName.
- As with other attributes, surround the query attribute value with double quotes (").
- As with any variables that you reference for output, surround column names with pound signs (#) to tell ColdFusion to output the column’s current values.
- Add a <br /> tag to the end of the variable references so that ColdFusion starts a new line for each row that is returned from the query.