Inside Coldfusion MX
| ColdFusion is known as a great tool for interacting with databases. It enables application developers to read data from and write data to local or remote databases. In this section, we look at how to use ColdFusion to read information in the database and output that information to the browser. We'll see how ColdFusion enables us to interact with the database in the following ways:
Before we begin learning how ColdFusion allows us to interact with a database, let's make sure you have an understanding of datasources. Understanding Datasources
One of ColdFusion's strengths is its capability to work with sources of data. ColdFusion developers usually connect to a database as the source of their data. For ColdFusion to communicate with any database, you must create a connection called a datasource to point to that database. A datasource is a named connection that serves as an entry point for all database operations. You can create and configure a ColdFusion datasource in the Data Sources page of the ColdFusion Administrator. There are a few simple rules that you should follow when establishing a datasource connection:
After logging in to the ColdFusion Administrator, you should click the Data Sources link found in the left navigation bar. Upon arriving at the Data Sources page, you'll see the names of any datasources that are already connected. From this page, you can edit, verify, or delete any existing datasources connections or you can create a new one. Let's create a new datasource. The first thing that you need to know is the type of datasource to which you're connecting. This information helps you to choose the proper database connection driver. For our purposes, let's set up a connection to a Microsoft Access database.
ColdFusion creates the datasource and verifies that it can connect to it. We skipped over several fields because they were not required for us to set up this datasource connection. In fact, there is an entire set of advanced datasource settings that we skipped over. Again, these settings vary according to the driver type that you selected. If you need to know the details related to any datasource connection option, refer to your ColdFusion MX documentation. You'll most commonly reference your datasource when you are making a CFQUERY tag call. Using the CFQUERY tag, you can make a call to your datasource using structured query language (SQL). The SQL that you use determines how the CFQUERY tag interacts with the datasource. Check out the following simple example of a CFQUERY call: <cfquery name="getUsers" datasource="ICFMX_Data"> SQL statements </cfquery> There are other instances where you'll reference a datasource, such as when calling stored procedures using the CFSTOREDPROC tag. We'll take a more in-depth look at using SQL and at calling stored procedures in ColdFusion and interacting with databases in Chapter 23, "Working with Databases." Selecting and Displaying Data
What would a data-driven application be without a hardy backend database? Let me say up front that we're not going to get into a lengthy discussion about SQL at this point, and neither are we going to argue the strengths and weaknesses of any database system. Let's keep it simple and take one step at a time. The capability to select data from a database table and to display that data in the browser is the heart of a database-driven application. We've seen in a few code examples so far that we can query the database for records. In ColdFusion, we accomplish this task by using the CFQUERY tag. The CFQUERY tag does exactly what you might think it does; it queries the database. We'll use it to select, insert, update, and delete information from the database. We'll also take a look at some ColdFusion tags that have similar functionality built in. First, let's take a look at a simple query to get information from our Members table. Listing 4.15 Select Query
<cfquery name="getMembers" datasource="ICFMX"> SELECT * FROM Members </cfquery> Pretty simple query, huh? Well, this query would be terrible to run on a table that contained thousands of records, but we'll use it for now anyway. Let's take a look at our Members table so that we know the tables with which we're working (see Figure 4.6). Figure 4.6. Members table.
When we run the query in Listing 4.16, we have a result set with all columns for each record present in the table. From the group of columns in our query, we could select any column present to output to the screen. Listing 4.16 Select Query with Record Output
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Displaying Records</title> </head> <body> <cfquery name="getMembers" datasource="ICFMX"> SELECT * FROM Members </cfquery> <cfoutput query="getMembers"> #getMembers.MemberFirstName# #getMembers.MemberLastName# - #getMembers.MemberEmail#<br> </cfoutput> </body> </html> We can also pick and choose which columns to select in our query and to ultimately output to the screen. Our modified CFQUERY might look something like this: <cfquery name="getMembers" datasource="ICFMX"> SELECT MemberFirstName, MemberLastName, MemberEmail FROM Members </cfquery> Because ColdFusion works so well with HTML, you might choose to display the specified column values within a table. Listing 4.17 Displaying Record Output in HTML Tables
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Displaying Records with HTML Tables</title> </head> <body> <cfquery name="getMembers" datasource="ICFMX"> SELECT MemberFirstName, MemberLastName, MemberEmail FROM Members </cfquery> <table border="1"> <tr> <td>Current Members:</td> </tr> <cfoutput query="getMembers"> <tr> <td>#getMembers.MemberFirstName# #getMembers.MemberLastName# -#getMembers. Remember that CFOUTPUT with the query attribute reproduces all the code that occurs within it, just as the CFLOOP did. With the code in Listing 4.17, a new table row and contained cells is produced for each record that is returned from the query. The resulting output looks something like Figure 4.7. Figure 4.7. Members table output.
Notice that several rows now exist in our table. Inserting Records CFINSERT
Let's put aside the CFQUERY tag for a moment and take a look at some of the built-in tags to which ColdFusion gives us access. Interaction with the database is a two-way street, so ColdFusion helps us to insert new records into the database with the CFINSERT tag. The CFINSERT tag looks like this: <cfinsert datasource="icfmx_data" tablename="Members"> CFINSERT is a very simple tag with a very simple purpose: to insert a single record into a single table in your database. It is a great tag for beginners who do not yet understand SQL syntax. Along with simplicity, however, comes functional and performance limitations. The CFINSERT tag is much slower than a standard SQL INSERT statement. You can insert values into one table at a time and your form variable names must match the names of the columns in the table. If you do not want all form variables to be inserted, you must use specify which ones to insert in the FORMFIELDS attribute. The CFINSERT tag requires that you specify two attributes: a datasource, which is the name of the datasource that contains the table into which the record is inserted; and a tablename, which is the name of the database table into which to insert the record. The CFINSERT tag must be used in conjunction with form variables. These most often come from an HTML or CFML form. The CFINSERT tag tries to insert the values from every field in the form into the table. If a field is found in the form that does not match a field in the table, an error occurs. In Listing 4.18, we're creating the form values just above the CFINSERT tag so that you can see all the code. Listing 4.18 Using CFINSERT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>CFINSERT</title> </head> <body> <cfset form=StructNew()> <cfset form.MemberEmail="roger@codesweeper.com"> <cfset form.MemberFirstName="Roger"> <cfset form.MemberLastName="Ross"> <cfinsert datasource="icfmx" tablename="Members"> <cfquery name="getMembers" datasource="ICFMX"> SELECT * FROM Members </cfquery> <table border="1"> <tr> <td>Current Members:</td> <cfoutput query="getMembers"> </tr> <tr> <td>#getMembers.MemberFirstName# #getMembers.MemberLastName# -#getMembers. Later in the book, we take a close look at the SQL statements needed to insert records using the CFQUERY tag. It's not difficult to do at all. Updating Records CFUPDATE
ColdFusion also provides developers with the CFUPDATE tag to help speed the process of updating values in a table. The CFUPDATE tag works much like the CFINSERT tag does and it also has the same limitations. The CFUPDATE tag requires that you specify a datasource and table name. The CFUPDATE tag updates the row that corresponds to the table's primary key value. The CFUPDATE tag looks like this: <cfupdate datasource="icfmx_data" tablename="Members"> Deleting Records
There is not a magical ColdFusion tag for deleting records from a table. For this operation, we must use a simple SQL statement. Remember that we'll look later at more complex SQL statements. At its most basic, the delete operation requires only that you specify the table name from which to delete records: <cfquery datasource="icfmx"> DELETE FROM Members </cfquery> This is actually very scary code. If executed, this operation would delete all records in the Members table. It is irreversible and I'm sure that some poor developer somewhere has lost a job over just such an operation. When deleting records from a table, you should specify which records to delete. The most reliable way to do this is to specify the primary key value of the record that you want to delete. You specify this value in a WHERE clause that is positioned within the CFQUERY tag call: <cfquery datasource="icfmx"> DELETE FROM Members WHERE MemberID = 6 </cfquery> If you need to delete multiple records, you can delete them one at a time or you can create a statement similar to the following: <cfquery datasource="icfmx_data"> DELETE FROM Members WHERE MemberID = 6 OR MemberID = 5 </cfquery> |