Chapter 5: Accessing Databases using ColdFusion MX

ColdFusion MX applications can access back-end data by accessing databases. To access databases through ColdFusion applications, you need to create data sources. ColdFusion provides various tags that enable you to access databases and perform database operations.

In this chapter, you'll be introduced to the overview of SQL. You'll learn how to manipulate back-end data using various ColdFusion tags.

Introducing Databases and Data Sources

A database stores information, which is organized in tables. A table is a collection of related pieces of information arranged in a grid of columns and rows. For example, let's say you want to create a table to organize employees' identification numbers, names, and salaries. The first column contains employee identification numbers, the second has employee names, and the third has their salaries. Columns are known as fields. Each row constitutes one data record—data for a single employee. Each row is unique because it pertains to one employee. You can organize data in multiple tables. Details of each entity in the application can be stored in a different table.

The employee number, which identifies each employee, is unique. This unique identifier can be used to identify and refer to each record in a table in the database and is called the primary key. The data in each column must be consistent for each record. To maintain consistency, the foreign key concept is used. For example, to store the details about the purchase order in a table, you need not store all the details about the supplier. You should only save the primary key value of the Supplier table in the Purchase Order table. The primary key of the Supplier table in the Purchase Order table is called a foreign key. This forms the basis of a relational database.

In ColdFusion, a data source is a connection between ColdFusion and an information source. Data sources added to your ColdFusion server allow you to connect to the databases from your ColdFusion applications. You can connect to a database from a ColdFusion application using a standard interface known as Open Database Connectivity (ODBC). Applications that use ODBC must have an ODBC driver installed and configured for each of the data sources.

To view ODBC drivers on Windows 2000, check your system's installed drivers by opening the ODBC Data Source Manager in the Windows Control Panel. To do so, choose Start, Settings, Control Panel, Administrative tools, Data sources.

The installed set of ColdFusion ODBC drivers include

• Delimited text files

• Microsoft Excel, Microsoft Access, and SQL Server

• RDBMS, such as Informix, Oracle, and Sybase

• Centura SQLBase databases

After a database has been created, you can access the data source by adding data sources in ColdFusion Administrator.