Understanding Data Sources

As explained in Chapter 5, "Building the Databases," a database is a collection of tables that store related data. Databases are generally used in one of two ways:

• Directly within a DBMS application such as Microsoft Access or SQL Server's Enterprise Manager. These applications tend to be very database specific (they are usually designed by the database vendor for use with specific databases).

• Via third-party applications, commercial or custom, that know how to interact with existing external databases.

ColdFusion is in the second group. It isn't a database product, but it let you write applications that interact with databases.

How do third-party applications interact with databases, which are usually created by other vendors? That's where data sources come in to the picture. But first, we need to look at the database driver. Almost every database out there has available database driversspecial bits of software that provide access to the database. Each database product requires its own driver (the Oracle driver, for example, won't work for SQL Server), although a single driver can support multiple databases (the same SQL Server driver can access many different SQL Server installations).

There are two primary standards for databases drivers:

• ODBC has been around for a long time, and is one of the most widely used database driver standards. ODBC is primarily used on Windows, although other platforms are supported, too.

• JDBC is Java's database driver implementation, and is supported on all platforms and environments running Java.

NOTE

ColdFusion 5 and earlier used ODBC database drivers. ColdFusion MX and later, which are Java based, primarily use JDBC instead.

Regardless of the database driver or standard used, the purpose of the driver is the sameto hide databases differences and provide simplified access to databases. For example, the internal workings of Microsoft Access and Oracle are very different, but when accessed via a database driver they look the same (or at least more alike). This allows the same application to interact with all sorts of databases, without needing to be customized or modified for each one. Database drivers are very database specific, so access to databases need not be database specific at all.

Of course, different database drivers need different information. For example, the Microsoft Access driver simply needs to know the name and location of the MDB file to use, whereas the Oracle and SQL Server database drivers require server information, and an account login and password.

This driver-specific information could be provided each time it's needed, or a data source could be created. A data source is simply a driver plus any related information stored for future use. Client applications, like ColdFusion, use data sources to interact with databases.