The true power behind ColdFusion is its capability to access and manipulate database records. In this chapter learn how to access a database using ColdFusion and basic Structured Querv Language (SQU k1 retrieve information from a database.You will be working with a Microsoft Access 97 database provided on the CD that comes with this book. It is not essential that you have Access 97 in order to continue but it would be very useful.
Setting Up a Database
Before ColdFusion can interact with a database, you must set up a ColdFusion data source in the ColdFusion Administrator. You will have to set up a Cold Fusion data source only once for each database you need to work with. A data source is essentially a pointer to the physical database. In a sense, it is the source path for the data you want to access. You will be working with the ColdFusion Administrator to set up the data source.
A Basic Access Database
You will use the quiz . mdb database for all the exercises in this chapter. It is a simple database with a table containing seven fields. If you have Access 97, you can open the database to view it. You can also see the database in Figure 3.1
You will notice that the quiz database has a table called questions. This is where you will store all the data needed for the exercises during this chapter. If you are viewing the database in Access double-click the table to-open it otherwise look at Figure 3.2
The questions table contains seven columns and ten rows. If you have never worked with databases; you will need a basic understanding of them before you continue. If you have worked with databases! please proceed to the section titled “Using the Administrator to Add a Data Source.”
What is a Database 7
A database provides a way to store categories of information in an organized easily accessible format. In Chapter 2 “Passing Data between ColdFusion Templates” you worked on a quiz that had the following categories of information
• Correct Answer
• Answer 1
• Answer 2
.• Answer 3
• Answer 4
If this information were stored in a database table, these categories would be called fields or columns. Each field would have its own properties (field size, default value, etc.) and would conform ‘to a specific data type (numeric, text, etc.). In Figure 3.3, you can see that the first column of the quest; ons table has been highlighted
In this example each question and all the information tied to it is called a row or a record. Each question in this table has its own correct_answer answer and so on. So 10 questions would constitute 10 rows, or records, in our quiz database. In Figure 3.4 you can see that the second record has been highlighted.
A field that you did not use in the previous chapter has been added to the quest; om table. This field question_; d is the primary key field (see Figure 3.3). A primary key is. field (or combination of fields) that enables a record to be uniquely identified. Normal a numeric value is used for the primary key. No single record in a table can have the same primary key value as any other record. In this way you can easily distinguish, search, and identify records. You will be using the primary key value later-in this chapter to identify questions when you access the database .
Microsoft Access also enables you to view and modify the design of a table. Select View >- Design View or look at Figure 3.5. This is where all the fields are defined. You will notice that the question_ id field (the’primary key) is an auto number field. Auto number means that each time a new question is added to this database table the value for this quest; 0″_id field is automatically generated by Access In this way Access ensures that no two values in this field are the same. You can also see the data types for the other fields.
If you are using Access to view this table, please be careful about making any modifications at this time. Modifications to the table could affect the operation of the code you will write in the rest of the chapter. Of course, if you do make modifications and have difficulties, you car. always copy a fresh version of the database to your hard drive from the accompanying CD.
This explanation 01″ a database is a basic one and will hopefully get you started. You will learn more as you continue reading this book
Using the Administrator to Add a Data Source
Now let’s add the data source in the ColdFusion Administrator. The ColdFusion Administrator is a Web-based tool that you can access by opening the following URL in vour browser
You will be asked for the administrator password. This is the password that you specified during the installation of Cold fusion.
When the administrator loads, please select the ODBC link under Data Sources on the left side (see Figure 3.6). This is the screen where you will add data sources for Open Database Connectivity (ODBC) databases. ODBC is a standard protocol used with many databases to allow for outside access (such as importing and exporting data). Microsoft Access is a database that has an ODBC driver.
First you need to name your data source. It does not have to have the same name as the database file, although this sometimes helps in remembering what database you are referencing. In the Data Source Name field, type quiz. The default ODBC driver selected should be Microsoft Access Driver. If it is not, please select it. Then select Add (see Figure 3.6).
Now you are on the Create ODBC Data Source screen (see Figure 3.7). The data source name is already specified. You do not need to enter a description, but you may if you like. Now you need to specify the path where your qui z. mdb file is located. When you do this, ColdFusion will know where to go when accessing this database. Because the path will vary depending on your Web server root directory, select the Browse Server button .
Browse your hard-drive directory structure to find the quiz. mdb file in the sybex folder of your Web server root directory. Then select OK (see Figure 3.8
Finally, on the Create ODBC Data Source screen, select Create. You will then see the quiz data source listed, along with the example data sources provided by Allaire. To the far right of the screen, under the Verify column, the word verified should appear (see Figure 3.9). This means that the connection to the data source has been successfully established.