Databases provide us with a structured and organized manner in which to store data. One of the most popular types of database models is the relational database. Relational databases use tables as a way of representing relationships between entities. SQL is the language used to communicate with relational databases. Although you do not need to be a SQL expert to develop ColdFusion applications, you do need a basic understanding of the SQL language.
Our discussion of SQL should get you started; but if you are not familiar or comfortable with SQL, we strongly recommend that you invest in any one of the excellent texts on the market that cover the topic in detail. Keep in mind that most DBMSs have added proprietary extensions to the basic SQL, so it is strongly suggested you purchase a text that focuses on the database system with which you are working.
Stored procedures offer a way to encapsulate repetitive and complex tasks that are normally accomplished with a series of SQL statements. The use of stored procedures offers many advantages, including improved performance. In ColdFusion, we can execute stored procedures using either the CFSTOREDPROC or CFQUERY tags. You normally want to execute stored procedures with the CFSTOREDPROC tag because of the limitations of the CFQUERY tag.
Many databases support the concept of transactions. Transactions enable us to treat a group of queries as one, meaning that if one fails, the results of the preceding queries are rolled back. Transactions also provide a mechanism to prevent multiple users from editing the same data at the same time. We use the CFTRANSACTION tag to identify the queries we desire to treat as a single transaction.
In the Chapter 24, "Advanced Database Interaction," we discuss how to take our database interactions to the next level by writing dynamic SQL statements and queries of queries (CFSQL). We also examine how to improve performance in applications by using query caching.