| The main purpose of ColdFusion is to provide a high-level programming interface for data-enabling Web sites. Thus, the kernel of ColdFusion functionality focuses on the four basic database operations: retrieve, update, insert, and delete. One tag, cfquery , handles all four operations. The database functionality is expressed in a Structured Query Language (SQL) statement contained between the start and end tags. You can greatly enhance your ability to interact with databases in ColdFusion by learning some beyond-the-basics SQL. On the other hand, all the examples provided in this section were auto-generated with Dreamweaver, as described in Chapter 13. You may find it faster and easier to generate basic SQL with Dreamweaver. Even if you do not use Dreamweaver, the material in this section will help you understand the code that Dreamweaver generates, in case you want to modify it.  For more on database operations and SQL, see "Database Operations," page 290 , in Chapter 13, "Developing ColdFusion Applications in Dreamweaver."
Retrieving Data Retrieving data creates a recordset , an object containing zero or more records, which you can access and manipulate with CFML. You retrieve data by enclosing an SQL SELECT statement within a ColdFusion cfquery tag. NOTE SELECT is an SQL keyword , a word reserved by SQL for its own use. (Although not a requirement, SQL keywords are often represented in all caps, to make them easier to distinguish from other elements of the SQL statement.)
For a full listing of SQL reserved words, see SQLreservedWords.htm on the CD accompanying this book.
The <cfquery> tag specifies the database from which to retrieve data. The SELECT statement specifies the table(s) within the database, and which records in the tables to get. The simplest SELECT statement looks like this: SELECT * FROM Products
This statement says, "Retrieve all columns (fields) of all rows (records) from the Products table." The asterisk is a wild-card meaning "all columns." All rows are retrieved, because the statement contains no limiting clause for selecting only a subset of rows. In this case, each row is a different product, and the columns are the attributes of the product, such as the product's unique ID, category, description, name , list price, and so on. To avoid possible problems with database management systems that automatically create columns, it is best to explicitly name each column you want to retrieve, like this: SELECT ProductID, Category, ProductName, Description, ProductURLID, ListPrice, Weight, ProductSize, ProductApplication, AlternativeTo, Brand, SmallImageName, MediumImageName, LargeImageName, WhenEntered, Owner FROM Products
You can use a WHERE clause to select only specified rows. For instance, the following statement retrieves only records with the Category code 13001010000 (the category code for books in the greendept.mdb database): SELECT ProductID, Category, ProductName, Description, ProductURLID, ListPrice, Weight, ProductSize, ProductApplication, AlternativeTo, Brand, SmallImageName, MediumImageName, LargeImageName, WhenEntered, Owner FROM Products WHERE Category = '13001010000'
To use this SQL statement in ColdFusion, enclose it in a cfquery tag. In the following example, the cfquery tag specifies the greendept database, and assigns the name qProducts to the Recordset that is created: <cfquery name="qProducts" datasource="greendept"> SELECT ProductID, Category, ProductName, Description, ProductURLID, ListPrice, Weight, ProductSize, ProductApplication, AlternativeTo, Brand, SmallImageName, MediumImageName, LargeImageName, WhenEntered, Owner FROM Products WHERE Category = '13001010000' </cfquery>
qProducts.cfm , qProducts2.cfm , qProducts2b.cfm , and qProducts_form.cfm , on the CD accompanying this book, all use some version of the qProducts recordset. These sample .cfm files are discussed in "Retrieving Data," page 293 , in Chapter 13. qProducts.cfc also uses a recordset named qProducts that is similar to this one but retrieves only four attributes. qProducts.cfc is discussed under "Web Services," page 672 (Chapter 23).
Updating Records Updating changes the values stored in fields in an existing record or records. Conceptually, you use two ColdFusion pages to update data: an update form and an update action page. In practice, the two pages can be, and often are, combined on one page. You create an update form with an HTML form tag or a cfform tag. On the action page, you can use either a cfquery tag (containing an SQL UPDATE statement) or a cfupdate tag. The update action page may also contain a "success" message, so that the user knows that the record was successfully updated, or it may redirect the user to a page containing such a message. The following is a basic UPDATE statement in SQL. As it stands, because no limit is put on which records to update, it sets the Category field to all zeros for all products in the database. UPDATE Products SET Category='00000000000'
Multiple fields can be updated with a single UPDATE statement. The assignment statements (for example, Category='00000000000' ) for the different fields are separated by commas. For example: UPDATE Products SET Category='00000000000', ProductName='Seeking Sanctuary'
A WHERE clause limits the update to specified records: UPDATE Products SET Category='00000000000', ProductName='Seeking Sanctuary' WHERE ProductID = 1
(Note that an SQL statement can be written on multiple lines for readability.) Form variables provide the values in the assignment statements. Thus, the user's input on the form determines the values used in the update operation. For example: UPDATE Products SET Category='#FORM.Category#', ProductName='#FORM.ProductName#' WHERE ProductID = #FORM.ProductID#
In addition, you should use a cfif tag to check that the form variables actually exist (for example, cfif IsDefined("FORM.Category") ) and that they are not blank (for example, AND #FORM.Category# NEQ "" ). ( NEQ is "not equal to.") If the form variable doesn't exist or it is empty, one solution is to update the database with a NULL value. Here's an example, with the cfquery tag added: <cfquery datasource="greendept"> UPDATE Products SET Category= <cfif IsDefined("FORM.Category") AND #FORM.Category# NEQ ""> '#FORM.Category#' <cfelse> NULL </cfif> , ProductName= <cfif IsDefined("FORM.ProductName") AND #FORM.ProductName# NEQ ""> '#FORM.ProductName#' <cfelse> NULL </cfif> WHERE ProductID = #FORM.ProductID# </cfquery>
Fields present in the database but not in the UPDATE statement remain unchanged. Attempting to update with a NULL value generates an error if the database has been configured not to accept the NULL value for that field. In that case, you may be able to substitute a more appropriate value for NULL. For instance, the Category field in the greendept database is a required field and does not accept a NULL, but there is a category code for Miscellaneous (00000000010) that might be appropriate: <cfif IsDefined("FORM.Category") AND #FORM.Category# NEQ ""> '#FORM.Category#' <cfelse> '00000000010' </cfif>
If the same default value is always appropriate, you can configure it in the database. If a default value has been configured in the database, the database provides that value when you try to update with a NULL. Relationships between tables can also generate errors when you try to update. For instance, in the greendept database, the Products table has an Owner field, which can contain only a valid user ID from the UserID field in the Users table. Any other value would generate an error if there were no default set in the database (or if a default were set that didn't correspond to a value in the UserID field in the Users table). To handle this situation, a "No User" record has been created in the Users table, with the UserID equal to 1. With the default Owner in Products set to 1, there is a match between Owner and UserID , and no error is generated. Even where the database accepts a NULL, it may be preferable to insert a dash or some other character indicating an empty field. Dynamic tables may format better with such a character. For instance, the following example uses a dash instead of NULL to indicate "no value": <cfif IsDefined("FORM.ProductName") AND #FORM.ProductName# NEQ ""> '#FORM.ProductName#' <cfelse> '-' </cfif>
For an illustration of updating, see update1.cfm , which was auto-generated with Dreamweaver, on the CD accompanying this book.
Inserting Records Inserting adds a new record to the database. As with updating, there is conceptually a form page and an action page for inserting, and the two pages are commonly combined. Here's a simple INSERT statement, providing three values for three fields: INSERT INTO Products (ProductID, Category, ProductName) VALUES (1, '00000000000', 'Seeking Sanctuary')
Fields present in the database but not in the INSERT statement are filled in according to the rules of the database. Typically, a NULL is inserted if the database permits it and no default value has been defined in the database for that field. Using form variables, substituting NULL for nonexistent or blank variables, and adding the cfquery tag, the statement looks like this: <cfquery datasource="greendept"> INSERT INTO Products (ProductID, Category, ProductName) VALUES ( <cfif IsDefined("FORM.ProductID") AND #FORM.ProductID# NEQ ""> #FORM.ProductID# <cfelse> NULL </cfif> , <cfif IsDefined("FORM.Category") AND #FORM.Category# NEQ ""> '#FORM.Category#' <cfelse> NULL </cfif> , <cfif IsDefined("FORM.ProductName") AND #FORM.ProductName# NEQ ""> '#FORM.ProductName#' <cfelse> NULL </cfif> ) </cfquery>
The same problems that arise in attempting to update with NULL can arise when attempting to insert a NULL value with the cfelse clause, and the same solutions also apply. For an illustration of inserting, see insert1.cfm , which was auto-generated with Dreamweaver, on the CD accompanying this book.
Deleting Records Deleting permanently and irrevocably removes records from the database. This simple statement, because it puts no limits on what to delete, totally wipes out an entire table: DELETE FROM Products
Relationships can cause delete operations to fail. For example, if you try DELETE FROM Users (delete all records in the Users table) in greendept.mdb, it has no effect, because each UserID in Users is required to maintain a relationship with the Products table. You get an error, The record cannot be deleted or changed because table 'Products' includes related records. You can apply delete operations to specific records if you use a WHERE clause: <cfquery datasource="greendept"> DELETE FROM Products WHERE ProductID = 1 </cfquery>
| 