One of ColdFusion’s greatest strengths is its ability to retrieve and manipulate data stored in a database. Once you learn how to access a database through the Web, a completely new world of development will open up to you. You will be able to store, track, and manipulate the following types of data:
- User Data (interface preferences, bookmarks, personal information, login data, etc.)
- Human Resources Information (employee data, salaries, resumes, etc.)
- Product Catalogues (product descriptions, sales information, customer information, etc.)
- Financial Reports (sales information, billing, balances, credit card info/etc.)
- Educational Materials (lessons, quizzes, tests, scores, etc.)
- Any other information that can be stored in text format.
In order to begin using ColdFusion’s powerful database connectivity features, there are. a few things you need to be familiar with first:
- How to design and create a database
- Setting up a ColdFusion Datasource
- Using Structured Query Language (SQL, pronounced ‘es-que-el’ —-or ‘sequel’)
This book provides an excellent chapter on how to design and create a basic database. If you have never worked with databases before, now would be a good time to go back and read Chapter 9, “Creating Databases and Tables.” While that chapter focuses mainly on developing a database using Microsoft Access 97, the concepts provided . apply to development with all database applications. In Chapter 3, the section “Setting up a Database” also walks you through creating a simple database with one table.
Once you have created your database, you need to inform the ColdFusion Application Server that it exists. The section, “Using the Administrator to Add a Data Source” in Chapter 3 walks you through adding a ColdFusion data source. A data source is a pointer for Coldfusion. It explains where to find the database and what settings should be applied to it. For more detailed instructions on setting up a data source, refer to Chapter 35, “ColdFusion Administration.” .
After your database is created and your ColdFusion data source is defined, you are ready to start accessing your data using the ColdFusion tag CFQUERY in conjunction with SQL. SQL is the industry-standard language used to create, manipulate, and control data with all the top database management systems (DBMS). ColdFusion enables you to interact with these database systems via SQL because ColdFusion is an Open Database Connectivity (ODBC) client. ColdFusion uses ODBC as its database interface. You can also connect directly to a DBMS through native drivers. .
To become a great ColdFusion developer, you really need to also be a great SQL programmer. This chapter will cover the basics of SQL and how to do the most common database interactions. You may also want to review the first half of Chapter 3, which introduces some basic SQL commands. If you want to become a SQL expert, you should buy a complete book on SQL. Some SQL book recommendations can be found in the Experience/Knowledge section of the Introduction. f)
The CFQUERY Tag and SQL
ColdFusion allows you to connect to a variety of database applications, including (but not limited to) MS Access, SQL Server, Oracle, Sybase, Informix, and DB2. In order to connect your template to a database using ColdFusion, you need to use the CFQUERY tag. The CFQUERY tag has an opening and closing tag, takes several attributes, and is written as the following:
The CFQUERY tag attributes are described Table 10.1.
When connecting to a database there are several types of interactions that can be done:
- Data can be retrieved from a database.
- Data can be inserted into a database.
- Data can be updated in a database.
- Data can be deleted from a database.
- Tables and fields can be created in an existing database. (Discussed in Chapter 11, Using Advanced Query Techniques.”)
In all cases, you will use the CFQUERY tag to create the connection with the database .
The type of interaction you make with the database depends on your SQL statement. The SQL statement is inserted between the opening and closing CFQUERY tags. A working example of connecting to a database using ColdFusion’s CFQUERY tag and a simple SQL statement is shown in the following line of code:
Tit:! database tables used in this chapter come from a scaled-down version of a database used for the Cosby Speakers Bureau. These tables are Stcled in a database file (sybex.mdb) provided on the enclosed CD-ROM. To use the examples in this chapter, you need to install sybex .mdb as an OOBC data source named sybex.
This Specific tables you Will be looking at in this chapter are the following:
Stores information about speakers ‘Iisted with the Cosby Speakers Bureau. This thecentral table for the database. ‘
.title “Stores speaker job titles. This table is tied to the speakers table through ,the- speaker _ i d field.
Peak books Stores information about books written by the speakers in the speakers table. 1h; table is tied to the speakers table through the speaker _i d field.
‘speaker.;category Stores the category 10’s associated with each speaker. This table is tied to thespeakers table through the- speaker _i d field and to the category_desc table through the speaker _category_id field.