Section 20.5. Connecting to a Database

20.5. Connecting to a Database

Dreamweaver works with a variety of different databases, from simple Microsoft Access files to corporate mainstays like Oracle 10g. Depending on the server model you've chosen , Dreamweaver connects to those databases in a variety of ways. For the PHP server model, for instance, Dreamweaver works using only the MySQL database and must connect directly to the MySQL server. For this tutorial, you'll use the Access database you downloaded earlier.

---

Note: The online PHP/MySQL tutorial at www.sawmac.com/dw8 shows you how to make a MySQL connection.

---

Databases, like server models, come in many shapes and sizes. Access is different from SQL Server, which is different from Oracle, FoxPro, or MySQL. Fortunately, there's a common language that lets operating systems and programs communicate with all of these databases and morea protocol called ODBC ( Open Database Connectivity). Each database company creates its own ODBC driver software.

The ODBC drivers for most popular databases are preinstalled on most Windows machines. To further simplify the process, Windows computers let you assign a data source name , a "nickname" that points to a particular database through a specific ODBC driver. A data source name (DSN) is one of the most common methods of connecting to a database on Windows. Instead of having to remember a long, complex series of codes to access your data, you simply use a short namethe DSN. Many Web hosting companies that provide Windows-based Web hosting services let you set up DSNs on their computers, making database connections easy.

However, DSN connections tend to be slower than the other option available in the ASP server model: connection strings. A connection string is simply a line of information that informs the Web server how to connect to the database and where the database is located on the system. It's a bit trickier to set up than a DSN connection, since each database requires its own set of instructions for the connection, but it's more reliable and quicker than a DSN connection.

---

Note: Note to Mac users : Follow these steps on a networked Windows machine. Copy the database file nationalEx.mdb onto the Windows server and use the following instructions to create a DSN. You can then connect to it from your Mac. Or follow the PHP version of this tutorial located at www.sawmac.com/dw8/.

---

1. Open the Databases panel by choosing Window Databases .

   The Application panel group opens.

2. Click the + sign button at the top right of the panel. From the pop-up menu, choose the appropriate database connection .

   The options you see depend on which server model you've selected. For example, if you're using ColdFusion, you get a list of all data sources (databases) defined in the ColdFusion server. If you're using JSP, you see a long list of different database options. In PHP, you see only one listing: MySQL Connection.

   For the purposes of this tutorial using ASP, your options are Custom Connection String and Data Source Name (DSN). Select Custom Connection String. The window shown in Figure 20-8 opens.

   Figure 20-8. A custom connection string specifies where the database file is and how the Web server should connect to that database. In this example, the file is located on the C:\ drive in a folder named DWDynamic.

   

   ---

   Tip: Dreamweaver won't let you set up a connection to a database unless a dynamic page is already open. You need to either open an existing page ( index.asp , for example) or create a new dynamic page, as described on Section 20.4.

   ---

3. In the Connection Name box, type connNationalEx .

   You can use any name you want, as long as it doesn't start with a number and doesn't contain any characters other than letters , numbers , and the underscore character. In this case, conn is a helpful indicator that this is a database connection and makes identifying it easier if you ever need to look into the underlying code of the page.

   Next , you'll add the connection string that tells the Web server where the database is and how to connect to it.

4. In the connection string box, type Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DWDynamic\nationalEx.mdb .

   This should all be typed on a single linedon't press Return, for example. This piece of information breaks down into two parts . The first partProvider=Microsoft.Jet.OLEDB.4.0;explains how to connect to the database (in this case, using a Microsoft method known as OLE DB). The second partData Source=C:\DWDynamic\nationalEx.mdbidentifies the location of the Access database file on your computer. For this to work properly, make sure the nationalEx.mdb file is in fact inside the DWDynamic folder and located directly on your C:\ drive. If it's not, you need to change this filepath so that it matches your setup. For example, if your computer's main drive is D:\ and you've put the nationalEx.mdb file in your Documents folder, you would change C:\DWDynamic\nationalEx.mdb to D:\Documents and Settings\Your Name\My Documents\nationalEx.mdb .

5. Make sure the Using Driver on this Machine button is selected, and then click the Test button .

   If all goes well, a message reading "Connection made successfully" appears.

6. Click OK to close the dialog box .

   Behind the scenes, Dreamweaver creates a small file in the selected server model's programming language, stores it in a folder called Connections in your site's root folder, and adds it to every page on your site that communicates with the database. (The file's name reflects the connection name you typed in step 3here it's connNationalEx.asp .)

---

Note: Don't delete the Connections folder. This folder holds scripts that let your pages connect to one or more databases. If, while cleaning your site, you throw this folder away, you break the database connection for all pages.

---

You're not limited to a single database connection. You can repeat the steps above to connect to as many databases as you like. Once you've created a database connection, Dreamweaver attaches it to all the pages within your site; you don't have to recreate the connection for each page.

20.5.1. Exploring the Databases Panel

The Databases panel (Figure 20-9) lets you do more than just connect databases to your site. It also lets you explore a database's structure and data. By clicking the + sign buttons (flippy triangles on Macs), you can view any of three lists:

• Tables . Lists all the tables in the database (see Section 20.3). Expanding a table displays all the columns for that table (as shown way back in Figure 20-6). You'll use this option most often.

• Views . Lists all views stored in the database. A view is a selection of data in the databasea slice of its data. Unless you've created views using the database systems tools, this list is empty.

• Stored Procedures . Lists programs that access and manipulate information in the database. Since they're stored right in the database, they run faster than similar code in a Web page. (Some database systemsAccess and MySQL, for exampledon't recognize this feature.)

---

Tip: To get a quick peek at the data in a database table, right-click (Control-click) the table's name in the Databases panel. From the shortcut menu, choose View Data. A window appears, displaying a table of data extracted directly from the database!

---

Figure 20-9. The Application panel group contains four panels for working with dynamic database-driven Web sites. The Components tab contains advanced features for use with ColdFusion, JSP, and ASP.NET Web sites. (It doesn't have any effect for the ASP or PHP server models.)

In this chapter, you've laid the foundation for a dynamic Web site. In the next chapter, you'll start adding data from a database to the page you created in the preceding tutorialand building a real, dynamic Web application.

FREQUENTLY ASKED QUESTION
Parenthetical Puzzler

In the Databases panel, I see some weird information in parentheses next to the column namesInteger 4 Required, for example. What's that about?

You're rightthere's a notation next to each column name. For example, Figure 20-9 shows a column called prodID , which is followed by ( Integer 4 Required ).

The information in parentheses denotes the type of data in that column. In this instance, it's an integer (a whole number like 1, 3, or 5), it's 4 bytes of data long (meaning it can be a very, very large number), and it's required (meaning that every new record must have a value stored in this field). Within each of these categories, there can be subtypes like time stamp, decimal number, and so on. Different databases recognize different data types, so there's quite a long list of possible data types for all the server models Dreamweaver supports.

These notations may appear cryptic, but they can come in handy. For example, if you're creating a form for updating or inserting a record in a database (as described in the next chapter), the data type and length can help you figure out what kind of information you're looking for and how long it should be.

For example, the prodName column pictured in Figure 20-9 contains text (that's what "Char" stands for, as in "characters") and is only 50 characters long. So if you're creating a form element, you'll probably want to create a text field that accepts at most 50 characters (see Section 10.3.2 for details on form elements).