Using ColdFusion for Database Operations


Although defining and displaying custom variables might enhance the functionality of your website, the more powerful tool that ColdFusion offers is the capability to communicate with databases and display and interact with content stored within them.

Imagine if your client, Retro's Cycles, had 100 motorcycles in inventory for sale. It wouldn't be reasonable to consider creating an individual web page for each of the 100 motorcycles. It would, however, be a better idea to store the information about the motorcycles in a database and then create a small set of pages that interacts with the database to display information about what is in inventory. Imagine being able to build a search page, a results page, and a details page that enable you to display the content details for each of the 100 motorcycles. That's 3 pages that now have taken the place of 102 pages. Talk about a timesaver!

ColdFusion is able to interact with a database in several different ways. It can retrieve data, update data currently stored in the database, insert new data, or delete existing data.

Retrieving Data

The most common database interaction is simply drawing data out of the database for display on a page. To do this, however, you need to have established a connection to the database and then create a recordset. Think of a recordset as a container that holds all the database records that you have requested. For instance, if you asked the database to retrieve all records where the Make field is equal to "Honda", the recordset would contain only the records for Honda bikes.

To request this data, you need to combine the <CFQUERY> tag with a SQL query that contains the proper syntax for returning the correct records. 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 Inventory


This statement says, "Retrieve all columns (fields) of all rows (records) from the Inventory table." The asterisk is a wildcard 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 item's unique ID, make, model, year, list price, and so on.

If you want to limit the records your recordset contains, you can use a WHERE clause to select only specified rows. For instance, the following statement retrieves only records where the Make is equal to "Honda":

SELECT * FROM Inventory WHERE Make = 'Honda'


To use this SQL statement in ColdFusion, enclose it in a <CFQUERY> tag. In the following example, the <CFQUERY> tag specifies the retroscycle database, and assigns the name rsHondas to the recordset that is created:

<CFQUERY name="rsHondas" datasource="retroscycles"> SELECT * FROM Inventory WHERE Make = 'Honda' </CFQUERY>


Inserting Records

Adding content to your database is almost as simple as retrieving data. Inserting a record usually requires completion of a form and form fields: You enter the data into the form and have it added after you submit it. The form then passes the data to a page (or code on the same page) that processes a SQL command similar to this:

INSERT INTO Inventory (Year, Make, Model) VALUES (#FORM.Year#, #FORM.Make#, #FORM.Model#)


Basically, this SQL query takes the contents of the Year, Make, and Model form fields and adds them to the Year, Make, and Model fields in a new record in the database.

Therefore, when you create your full CFML code, it would look something like this:

<CFQUERY name="rsHondas" datasource="retroscycles"> INSERT INTO Inventory (Year, Make, Model) VALUES (#FORM.Year#, #FORM.Make#, #FORM.Model#) </CFQUERY>


Updating Records

Another common database interaction is updating a record that already exists in the database. Like the Insert process, this requires two different elements: a form that displays the existing data and allows you to change it, and an action that replaces the existing data with the updated information after the form is submitted.

The form is created with a standard HTML form, or you can use a form created in CMFL by using the <CFFORM> tag. The update action uses the <CFQUERY> tag, along with the SQL update statement that looks something like this:

UPDATE Inventory SET Make='Kawasaki' WHERE Make='Honda'


This update statement modifies all records in the database where the Make is currently Honda and changes the Make field to Kawasaki. Although this is a global change to the entire table, you can limit the update to a single record by further refining the WHERE clause to look something like this:

UPDATE Inventory SET Make='Kawasaki' WHERE Make='Honda' AND InventoryID = '00011'


This update statement would affect only the record that has an InventoryID of 00011.

Because it's likely you'll be making the updates via a form, the form variables that are submitted can also be used in the SQL statement. For example, look at the following:

UPDATE Inventory SET Make=#FORM.Make# WHERE Make='Honda' AND InventoryID = #FORM.InventoryID#


This grabs whatever value is stored in the Make form field and enters it into the Make field in the database. Note that this affects only the record that is passed in the InventoryID form field.

Note

Fields present in the database but not in the UPDATE statement remain unchanged.


Deleting Records

Although deleting records is certainly an important part of database maintenance, it is important to remember that deleting permanently and irrevocably removes records from the database. There is no Edit, Undo command, and you can't restore the data without resorting to a known, good backup such as a tape backup or SQL backup file. So use the command deletion process wisely and cautiously.

When deleting records, the more detail about which record to delete, the better. For instance, consider this simple statement:

DELETE * FROM Inventory


Because it puts no limits on what to delete, it totally wipes out all the records in the Inventory database. Gone...wiped...kaput.

Therefore, it's a wise idea to restrict your delete commands so that they affect only the record you want. For instance, consider the following command:

DELETE FROM Inventory WHERE InventoryID = '00001'


If the InventoryID field is a primary key that increments automatically, you can be sure that there will only ever be one record with an InventoryID of 000001. This means that you have deleted only one record and you hope it's the correct one.

Keep in mind that relationships between tables can cause delete operations to fail. For example, if you had a Sold table that depended on the Inventory table and you tried to execute a DELETE FROM Sold (delete all records in the Sold table), you would receive an error stating The record cannot be deleted or changed because table 'Inventory' includes related records.



Special Edition Using Macromedia Studio 8
Special Edition Using Macromedia Studio 8
ISBN: 0789733854
EAN: 2147483647
Year: 2003
Pages: 337

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net