Connecting to Data Sources

With a well-designed data source at the ready, you're prepared to establish a connection to it from your Web application. You can achieve a connection in several ways, depending on the application server in use. Typically, application servers offer several alternative routes to establishing a connection. One method might be easier to set up but not quite as efficient, whereas another might be more difficult to create but more robust. It's good to know what the options are so that you can decide which type of connection is best in a given situation.

Making ASP Connections

Although there are some variations, ASP connections occur in one of two ways: either through a Data Source Name (DSN) or through a connection string. Although the DSN connection is easier to set up, it does affect the performance of the application server somewhat. Connection strings are more robust in general but also require more detailed information, such as a precise path to the data source on the server.

Note

Because only ASP, PHP, and ColdFusion applications are used in the recipes, details on creating connections are covered for only those server models. See Dreamweaver Help for information on creating connections for other application technologies.

When you create an ASP connection, whether through a DSN or a connection string, Dreamweaver stores the information in a file saved in the Connections folder of your site. Every time you create a recordset using that connection, the file is incorporated into your pages by using an <include> tag, like this:

<!--#include file="../Connections/Recipes.asp" -->

Dreamweaver regards this as a dependent file; when you upload your .asp files to the server via FTP, you'll see an option to include dependent files. Clicking OK will automatically transfer the connection file, and you'll be good to go.

DSN Connections

A DSN is similar to a domain name in that it too is an alias that represents the file path of the data source. As with domains, the mapping from DSN to file location is handled on the server side. DSN connections contain other vital information, such as the type of data source driver required and any security information, such as username and password.

On a remote testing server, DSNs are established by a system administrator. On a development system, a DSN is defined in a Windows program: the ODBC Data Source Administrator. ODBC stands for Open Database Connectivity; it is a system of drivers that allows the same language, SQL, to communicate with a variety of data sources.

Note

If you intend to follow along with the recipes, be sure to copy the recipes.mdb file from the CD-ROM to your system as instructed in the read-me file.

Here's how a local DSN is established through the ODBC Data Source Administrator:

1.	Run the appropriate ODBC management program for your system: Windows 95, Windows 98, and Windows ME: Select ODBC Data Source (32 bit) from the Control Panel folder. Windows 2000 and Windows XP Professional: Choose Data Sources (ODBC) from the Administrative Tools folder.
2.	In the ODBC Data Source Administrator dialog, select the System DSN tab. All the previously defined DSNs are listed on the System DSN panel.
3.	Select Add to open the Create New Data Source dialog.
4.	Select the driver that is appropriate for your data source, and click Finish when you're done. Select Microsoft Access Driver (*.mdb). The Setup dialog for the chosen driver is displayed.
5.	Enter an appropriate name in the Data Source Name that properly identifies the data source. Enter Recipes.
6.	Choose Select and locate your data source. Locate the file recipes.mdb copied to your system from the Web site.
7.	If a login name and password are necessary, select Advanced and enter them there.
8.	Verify your choices and, when finished, select OK to close any open dialogs.

Although you can launch the ODBC Data Source Administrator independently of Dreamweaver, you can also open it from within Dreamweaver as you are defining the DSN connection. Here's how:

1.	From the Databases panel, choose Add (+) and select Data Source Name (DSN).
2.	The Data Source Name (DSN) dialog appears [c1-4]. c1-4.
3.	For local development connections, choose the Using Local DSN option. If the connection you are creating is to a remote DSN, select the Dreamweaver Should Connect Using DSN On Testing Server option. Macintosh users do not have a local option.
4.	Enter an identifying name in the Connection Name field. To create a connection to use with the recipes in this book, enter Recipes.
5.	If the local DSN has been previously defined, you can select its name from the drop-down list.
6.	To set up a new local DSN, choose Define. The ODBC Data Source Administrator will be displayed. Set up the DSN as described in the previous series of steps. When finished, close the ODBC Data Source Administrator; your new DSN should appear in the drop-down list.
7.	To define a remote DSN connection, enter the name in the Data Source Name (DSN) field. You also have the option of selecting the DSN button. Dreamweaver will then contact the testing server and gather a list of assigned DSNs that you can select from.
8.	Enter your username and password if required.
9.	Make sure your connection is working by selecting Test. Dreamweaver lets you know if the connection was successful or if it encountered problems. Click OK when you're done.

Your new connection is listed in the Databases panel.

Connection Strings

A connection string, unlike a DSN connection, does not use an alias; rather, it specifies an exact path locationas well as the ODBC driver or OLE DB provider and any other necessary information such as username and password. All this information is put into one rather lengthy text string. As noted, you can include the ODBC driver in a connection string to make what is referred to as a DSN-less connection, but it's not as robust as a direct OLE DB connection string. ODBC acts as a translator, facilitating communications from the data source to the underlying engineand that engine, or provider, is OLE DB.

Each data source type has a particular OLE DB provider, which naturally affects the connection string. For example, if you were using an Access database called thebook.mdb as your data source, and the file was stored in the databases folder on your D: drive, the OLE DB connection string would look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\databases\thebook.mdb;

If, on the other hand, you were using a SQL Server database called BigBook found on a server called TheShelf, the OLE DB connection string would look like this:

Provider=SQLOLEDB;Server=TheShelf;Database=BigBook;UID=jlowery;PWD=starfish

Notice that this OLE DB connection string also contains username and password information.

Dreamweaver's Custom Connection Strings feature makes it possible to connect directly to the OLE DB provider. Here's how:

1.	From the Databases panel, choose Add (+) and select Custom Connection String.
2.	In the Custom Connection String dialog [c1-5], enter the label for your connection. c1-5. Enter Recipes in the Connection Name field.
3.	Enter the full connection string in the Connection String field. Enter the following string, substituting the actual path to your copy of the recipes.mdb for the variable yourDriveLetter:\yourPath\: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourDriveLetter:\yourPath\recipes.mdb;
4.	Select Test to make sure that the connection is working properly. If you encounter problems, check your connection string for typos; if you're successful, click OK to exit the dialog.

Setting Up ColdFusion Connections

The best, most flexible way to establish a data source connection in ColdFusion takes three steps:

1.	Assign your data source in the ColdFusion Administrator.
2.	Create and save an Application.cfm file, which includes a variable reference to your data source. The Application.cfm file should be stored at the site or application root.
3.	In Dreamweaver, add a Data Source Name variable to the Bindings panel.

Although there are other ways to establish a data source that do not require as many steps, the other methods are less modular and far more difficult to adapt if a data source changes.

Declaring a Data Source

The ColdFusion Administrator acts as a central clearinghouse for all data sources, among many other things. The process for setting up a new data source is similar in ColdFusion MX and ColdFusion 5, but slightly different. Here's how you set up a data source in ColdFusion MX and MX 7:

Note

If you intend to follow along with the recipes, be sure to copy the recipes.mdb file from the CD-ROM to your system as instructed in the read-me file.

1.	Run the ColdFusion Administrator by choosing Administrator in your Macromedia ColdFusion MX program group. You might need to log in, if you haven't done so in the current session.
2.	From the main Administrator page, select Data Sources, under the Data & Services category.
3.	In the Add New Data Source dialog, enter the name for your data source. Enter Recipes in the Data Source Name field.
4.	Select the driver for your particular data source from the list. Select Microsoft Access from the Drive drop-down list.
5.	Choose Add to move to the next screen.
6.	Enter the necessary details for your driver and data source. If the data source is file-based, enter the path in the Database File field; otherwise, choose System Database File. If necessary, select Show Advanced Settings to enter a different ColdFusion username or other security settings. Select Browse Server next to the Database File field and locate the copy of recipes.mdb copied to your system from the CD-ROM.
7.	When you've completed all the settings, choose Submit; the new data source should be added to the list.

It is possible at this point to go into Dreamweaver and select this data source to build a recordset. If you do, however, the data source name will be encoded directly into the <cfquery> tag. The technique we are following applies a variable to the <cfquery> tag rather than a name, which makes the recordset far easier to update across an application.

Setting Up the Application.cfm File

The Application.cfm file is a marvelous time- and code-saving aspect of ColdFusion. The code found within an Application.cfm file, if placed at the site root, is automatically included into every ColdFusion page within the site, regardless of where it is located in the file structure. This facility allows us to define a variable pointing to the data source name set up in the ColdFusion Administrator, which will in turn allow us to declare a Data Source Name variable in Dreamweaver. A site can contain numerous Application.cfm files; additional files are stored in the root folders of one or more applications.

The basic tag to create a variable is the <cfset> tag. If you wanted to assign a variable named theData to a data source you had created called CorporateData, you would use code like this:

<cfset theData="CorporateData">

Let's build an Application.cfm file together.

1.	In Dreamweaver, select File > New to open the New Document dialog.
2.	Choose Dynamic Page in the first column and ColdFusion in the second; click Create when you're ready. By selecting a ColdFusion page, Dreamweaver automatically appends the .cfm extension when the page is saved.
3.	In the new page, enter Code view and delete all the standard code inserted.
4.	Insert the <cfset> tag that assigns the desired variable name to your defined data source. Enter this code: <cfset Recipes="Recipes">
5.	Save the file as Application.cfm at the root of your site.

Defining the Data Source Name Variable

For our final step in the process, we move into Dreamweaver and use a new feature introduced in Dreamweaver MX: the Data Source Name variable. After declaring a Data Source Name variable, Dreamweaver includes the variable as an option in the appropriate drop-down lists and dialogs. Choosing such a variable while creating a recordset creates code like this:

Note

For maximum cross-platform implementation, be sure to save your Application.cfm with an uppercase A. Although application.cfm is acceptable on a Windows platform, it would fail on any Unix system.

<cfquery name="MyQuery" datasource="#DataSourceName#">

Note that the value for the datasource attribute is a ColdFusion variable rather than a direct reference to a data source. Here's how a Data Source Name variable is set up in Dreamweaver:

1.	In the Bindings panel, choose Add (+) and select Data Source Name Variable.
2.	In the Data Source Name Variable dialog, enter the name for the variable. Enter Recipes in the Variable Name field.
3.	Select the data source from the list. Choose Recipes from the Data Source list.
4.	Verify your choices and click OK to add the variable to the Bindings panel [c1-6]. c1-6.

Connecting to a MySQL Data Source for PHP

As noted earlier, MySQL is a server-based data source. Dreamweaver provides a direct method for creating a connection to a MySQL data source. Once the connection is established, you'll be able to refer to the connection for all the relevant applications.

Inserting a Connection

Connections are created and displayed in Dreamweaver's Databases panel. Before you can create a connection, MySQL must be running, and you should have a username and password established.

Note

The following procedure assumes that you've already installed MySQL on your development system. To learn how to install MySQL, visit www.mysql.org.

To use the data source created for the recipes, follow the instructions in the read-me file of the CD-ROM for setting up the provided data source files.

To create a connection, follow these steps:

1.	From the Databases panel, choose Add (+) and select MySQL Connection from the drop-down list.
2.	In the MySQL Connections dialog displayed, enter a name for your connection. Enter Recipes in the Connection Name field.
3.	Insert the server address hosting MySQL. The MySQL server address is either an IP address or, if the server is located on your development system, localhost.
4.	Enter your username and password in their respective fields.
5.	Specify the database. Choose Select and click Recipes in the Select Databases dialog. Click OK to confirm your choice and close that dialog [c1-7]. c1-7.
6.	Select Test to verify that the connection is working.
7.	After the connection is confirmed, click OK to close the dialog and create the connection file.

The newly created connection is displayed in the Databases panel. Expand the main connection node to reveal the available tables. The actual connection file is stored in the Connections folder and can be opened and examined in Dreamweaver. Dreamweaver writes a standard PHP file, like this one:

<?php # FileName="Connection_php_mysql.htm" # Type="MYSQL" # HTTP="true" $hostname_Recipes = "localhost"; $database_Recipes = "recipes"; $username_Recipes = "root"; $password_Recipes = "mypassword"; $Recipes = mysql_pconnect($hostname_Recipes, $username_Recipes, $password_Recipes) or trigger_error(mysql_error(),E_USER_ERROR); ?>

When you create a recordset in Dreamweaver, the connection file is included through the use of a require_once() function. This function is written into your PHP page at the top above the <html> tag, like the following code:

<?php require_once('Connections/Recipes.php'); ?>

The connection file is considered a dependent file by Dreamweaver. As such, it can be transferred automatically from the local to remote site with your application page when using Dreamweaver's built-in FTP feature to transfer files. Dreamweaver asks whether you'd like to transfer dependent files as well as the primary file. Answering yes ensures that the connection file is uploaded as well as additional system files necessary for the connection to be completed.