Dreamweaver MX and Database Connections | Mastering Dreamweaver MX Databases

If you plan to use a database with your web application, you need to create at least one database connection. Without one, your web application won't know where to find the database or how to connect to it. With the exception of ColdFusion web applications, you can create database connections in Dreamweaver MX by providing the information the web application needs to establish contact with a database. When you create a database connection, Dreamweaver MX stores the connection information in a file in the Connections folder of your website. Interestingly, Dreamweaver MX does not actually use that database connection in your web application until you define a recordset on a page. At that point, Dreamweaver inserts an include directive in your page. As the web server executes your web page, the include directive inserts the connection code establishing a database connection.

As we mentioned, before Dreamweaver MX can use any database connection, you must provide the connection's basic requirements, such as the appropriate driver for your database, the database name and location, and the database username and password. Most of this information can be gleaned from the database documentation and installation or through your web server environment. However, before we move into creating database connections, you need to become familiar with the types of database connections. As you may know, Dreamweaver MX can work with a wide variety of database connections-ODBC (Open Database Connectivity), OLE DB (Object Linking and Embedding Database), JDBC (Java DataBase Connectivity), and native drivers. Let's briefly look at each type.

Open Database Connectivity (ODBC)

Microsoft developed ODBC to meet developer needs for an easy way to connect to a variety of databases. To that end, an ODBC connection is actually a connection to a driver whose sole purpose is to connect and translate common instructions (typically SQL) in a way that a specific type of database will understand. For example, an ODBC connection to a Microsoft SQL Server database uses a driver specifically written to translate commands for SQL Server. An ODBC connection to a MySQL database uses a completely different driver written specifically with the MySQL database in mind. In most cases, the driver required to create an ODBC connection to a database is probably included with the operating system you are using. Even if this is not the case, you can usually download the appropriate driver from the websites of the publishers of your chosen database or operating system or even third-party providers. The great thing about using an ODBC connection to link your web application and a database is the simplicity of its creation and the independence between the ODBC connection and your web application.

You can create an ODBC connection on the web server of your web application typically through a control panel provided by the operating system. The only bits of information you need are a name for the connection and the target database name, type, and location. The one word name for the ODBC connection is the data source name (DSN). You'll hear DSN referenced a lot by developers and in documentation. The important thing to remember is that a DSN is how you reference and use the ODBC database connection in your web application.

Just as an ODBC connection exists independently of your web application, your web application also functions independently of the ODBC connection. Your web application doesn't need to know the database name, version, or location. In fact, the database doesn't even have to be on the same computer as your web application. As we mentioned, all your web application requires to use the database connection is the DSN. Knowing this, you can see how useful it is to have such independence between the ODBC connection and your web application. For example, you can switch the source database your web application uses by simply altering the ODBC connection on the web server.

Likewise, you can quickly pack up and move your web application to almost any other web server with an identically named ODBC connection.

Note

ODBC connections are also used to apply a level of security to web applications. Typically a web application has a content presentation side accessed by the public and a content administration side accessed by content administrators. Obviously, the public requires different database access than your web content administrators. Therefore, many developers create two ODBC connections to link to their web application and database. One connection is for the public side of a web application and typically has read-only permission to the database. The second connection is for the content administration side of the web application and has full read, write, and delete permissions.

Although you can create a DSN using Dreamweaver MX, at times you'll need to create a DSN on a web server through the Windows operating system. To create an ODBC connection on a Windows web server, first open the Control Panel, and then locate and click the ODBC Data Source Administrator icon to open the ODBC Data Source Administrator dialog box, as shown in Figure 10.1. (In Windows 2000 and later, you will find the ODBC Data Source Administrator under Administrative Tools.)

click to expand
Figure 10.1: The ODBC Data Source Administrator dialog box

The three basic data connection types are File, Local, and System. A File DSN is an independent file that contains all the information required to connect to a database. Any application with permissions to the file can use the File DSN and move it as easily as moving a file. A Local data connection is stored in the Windows Registry and limits access to the connection to only the creator of the data connection. A System data connection is also stored in the Windows Registry and is available to every application on the server that has the proper access permissions. As such, System data connections are ideal for web applications.

To demonstrate how to create a DSN, let's create a System DSN for a Microsoft Access database version of our Books database. To do so, follow these steps:

In the ODBC Data Source Administrator dialog box, click the System DSN tab. At this point, you will see a list of System Data Sources currently defined on the web server.
To create a new System DSN, click the Add button to open the Create A New Data Source dialog box, as shown in Figure 10.2.

Figure 10.2: The Create New Data Source dialog box
Select the appropriate driver for your database and click Finish. For our example, we are choosing Microsoft Access Driver. (If you cannot find the appropriate driver for your database, you will need to install it on the web server and begin again. Typically you can find the correct ODBC database drivers on the Microsoft website or on the website of the publisher of your database.)
The next few dialog boxes depend on the ODBC driver you choose, but generally they ask you for a one-word DSN, the name and location of the database, and a username and password (which is optional). In our example, enter booktracking for the DSN in the ODBC Setup dialog box.
Next, click the Select button to open the ODBC Microsoft Access Setup dialog box, as shown in Figure 10.3.Locate and select the MDWMX_Books.mdb database, and then click OK to save the booktracking System DSN.

Figure 10.3: The ODBC Microsoft Access Setup dialog box

As you can see in the System DSN tab of the ODBC Data Source Administrator dialog box (see Figure 10.4), a new booktracking System DSN is defined on your server. You can use this System DSN to access the Books Access database using Dreamweaver MX and any development language. What could be simpler?

click to expand
Figure 10.4: The ODBC Data Source Administrator dialog box

Object Linking and Embedding Database (OLE DB)

From a pure performance perspective, using OLE DB is the best way to connect to a database in Windows. In fact, an ODBC connection is actually a wrapper around an OLE DB connection to your database. Consequently, it makes sense that a direct OLE DB connection is faster than an ODBC connection. So why not use OLE DB connections all the time? Although OLE DB is certainly popular and efficient, ODBC remains the easiest to set up and maintain.

Instead of specifying the database and connection criteria in a System DSN as in ODBC, you specify OLE DB connections in the code of your web applications. For an OLE DB connection, you need to supply much of the same information that you supply for an ODBC connection-a provider or driver, the location and name of the target database, and any necessary security information. For example, the following is an OLE DB connection string in an ASP (Active Server Pages) application that connects to SQL Server. Simply replace myWebServer, databasename, username, and password with the appropriate information for your SQL Server.

Provider=SQLOLEDB; Data Source=myWebServer; Initial Catalog=datbasename;  User ID=username; Password=password;

Java Database Connectivity (JDBC)

JDBC is to Java-coded applications what ODBC is to applications written in other languages. JDBC is the standard method for connecting a JSP web application to a database and issuing standard commands that JDBC translates as necessary for the specific database type. Similar to ODBC, JDBC relies on the concept of a DSN to identify a connection to a database.

There are four types of JDBC drivers.

Type 1 The first JDBC method of connection, Type 1 establishes a bridge between JDBC and ODBC so that JDBC can connect to a database. As such, ODBC drivers must also be installed on the server. Although the easiest to set up, this type is the slowest and most unreliable.

Type 2 Also establishes a bridge; however, this bridge connects JDBC directly with the target database, bypassing ODBC. In fact, it doesn't require ODBC to be installed on the server at all. Type 2 may require you to install access software specific to your database choice.

Type 3 Fully independent of ODBC or database-specific connection software, Type 3 is a pure Java driver that translates JDBC into a database-independent protocol that is then translated in native database commands

.Type 4 Also a pure Java driver, Type 4 translates JDBC commands directly into native database commands. Type 4 is best suited for connecting a web application and a database.Dreamweaver MX comes with Type 4 JDBC drivers for IBM DB2, Oracle, MySQL, and Microsoft SQL Server. If you need to connect to some other database, you can use the Sun JDBCODBC Bridge. This type of bridge connection is fine for testing and development, but download and install a Type 4 JDBC driver specific to your database if you plan to move your web application into production. You can usually find Type 4 JDBC drivers on the website of the database publisher or through third-party companies. In addition, Sun maintains a list of available JDBC drivers at their website, http://industry.java.sun.com/products/jdbc/drivers.

Native Drivers

Native drivers connect your web application directly to a database, circumventing the traditional ODBC methods. Native drivers are typically faster and let you use database-specific features. However, to use a native driver, you typically need to install the database client software on your web server. To use a native driver in Dreamweaver MX, you need to consult the native driver documentation to construct a custom connection string.

Database Connections for ASP Developers

In this section we'll show you how to create a database connection for an ASP web application. You will need access to an ASP development environment that includes Internet Information Services (IIS) or Personal Web Server (PWS). Dreamweaver MX provides two default connection methods for an ASP application: Data Source Name (DSN) and Custom Connection String. The Data Source Name (DSN) option lets you connect via a Local or a System DSN. The Custom Connection String lets you craft your own connection using connection types such as ODBC, OLE DB, or a DSN-less connection. To demonstrate how to create a data connection in Dreamweaver MX, we'll create an Access version and a SQL Server version of the Books database and then create a System DSN connection, an OLE DB connection, and a DSN-less connection.

Creating an System DSN Connection

To create a System DSN connection in an ASP application in Dreamweaver MX, follow these steps:

Choose Windows ® Databases to open the Databases panel. Open any ASP page in your site, click the plus (+) sign, and choose Data Source Name (DSN) to open the Data Source Name (DSN) dialog box, as shown in Figure 10.5.

Figure 10.5: The Data Source Name (DSN) dialog box

In this dialog box, you can connect using a System DSN or using a Local DSN. In almost every case, you'll want to connect using a System DSN on the testing server.
Click the Using DSN On Testing Server option to display a slightly altered Data Source Name (DSN) dialog box, as shown in Figure 10.6.The next step is to select a System DSN from the testing server. As you may recall, we created a System DSN to an Access version of our Books application in the "Open Database Connectivity (ODBC)" section. If you happened to skip that step, revisit the section and create that System DSN.

Figure 10.6: The Data Source Name (DSN) dialog box
In the Dreamweaver MX Data Source Name (DSN) dialog box, click the DSN button. Dreamweaver MX queries the testing server and displays a current list of System DSNs in the Select ODBC DSN dialog box, shown in Figure 10.7.Select the booktracking DSN, and click OK to return to the Data Source Name (DSN) dialog box.

Figure 10.7: The Select ODBC DSN dialog box

The last steps are to provide a name for the Dreamweaver MX data connection and test your connection. In our example, we'll use BookTrackingAccess as shown in Figure 10.8. Dreamweaver MX will use the data connection name as the filename of the ASP file that saves the data connection information.

Figure 10.8: Define a Dream- weaver MX data connection name
In the Connection Name box, enter a name for your connection, and then click the Test button to validate the connection string. If the test is successful, all is well. Click the OK button to save your connection as an ASP file in the Connections folder of your site.

Creating an OLE DB Connection

To create an OLE DB connection to SQL Server in an ASP application using Dreamweaver MX, follow these steps:

Choose Windows ® Databases to open the Databases panel. Open any ASP page in your site, click the plus (+) sign, and choose Custom Connection String to open the Custom Connection String dialog box, as shown in Figure 10.9.

Figure 10.9: The Custom Connection String dialog box

In this dialog box, you can enter a name for your Dreamweaver MX data connection and craft your own database connection string.
In the Connection Name field, provide a name for the Dreamweaver MX custom connection string. In our example, we'll use BookTrackingOLEDB, as shown in Figure 10.10.Dream- weaver MX will use the data connection name as the filename of the ASP file that saves the data connection information.

Figure 10.10: OLE DB Connection in the Custom Connection String dialog box
Now you need to enter the connection string to connect to the Books Access database. Enter the following as one line of code in the Connection String text field, adapting the placeholders (identified by [ ]) to connect to your SQL Server development environment.
```
Provider=SQLOLEDB; Data Source=[myWebServer]; Initial  Catalog=[databasename]; User ID=[username]; Password=[password];
```
To create an OLE DB connection to an Access database, you can use the following line instead.
```
Provider=Microsoft.Jet.OLEDB.4.0: Data Source=[c:\databasepath]; User  ID=[]; Password=[];
```
As with creating a System DSN connection, you can connect using a driver on the testing server or using a local driver. In almost every case, you'll want to connect using a driver on the testing server.

Click the Using Driver On Testing Server option.
Before closing the dialog box, click the Test button to verify that the connection string works. If successful, click OK to save the data connection. If the test fails, you'll need to troubleshoot the problem. See "Troubleshooting Data Connections" at the end of this chapter.

Creating a DSN-Less Connection

As an alternative to File, Local, and System DSNs, ASP lets you create a DSN-less connection. A DSN-less connection is simply an ODBC connection defined within your code rather than through the web server operating system. Developers often prefer this type of connection because they can create or alter the connection from within the web application code.

To replace our System DSN connection with a DSN-less connection, follow these steps:

Choose Windows ® Databases to open the Databases panel. Open any ASP page in your site, click the plus (+) sign, and choose Custom Connection String to open the Custom Connection String dialog box.
Enter a name for the Dreamweaver MX data connection. Then enter the following connection string to connect to the Access version of our Books database. The username and password parameters are optional.
```
Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; UID=[ username]; PWD=[=password];
```
Finally, click the Test button to validate the connection string. If the test is successful, all is well. Click the OK button to save your connection as an ASP file in the Connections folder of your site.

Database Connections for ASP.NET Developers

To create data connections for a Microsoft ASP.NET web application, you need access to an ASP.NET development environment that includes IIS or PWS and the .NET Framework. (For more information about creating your development environment in ASP.NET, check out Chapter 14.) There is one connection method for ASP.NET-OLE DB Connection. However, when you installed the .NET Framework, you installed another option-the Managed Data Provider for SQL Server. To demonstrate creating a data connection in Dreamweaver MX, we will create an OLE DB connection to the Access version of the Books database and use the Managed Data Provider for SQL Server to create a connection to the SQL Server version.

OLE DB Connection

To create an OLE DB connection to our Books Access database in an ASP.NET application in Dreamweaver MX, take the following steps:

Choose Windows ®Databases to activate the Databases panel. Open any ASPX page in your site, click the plus (+) sign on the Databases panel, and choose OLE DB Connection to open the OLE DB Connection dialog box, as shown in Figure 10.11.

Figure 10.11: The OLE DB Connection dialog box

In this dialog box, you can build a connection string through a Dreamweaver MX DataLink Properties dialog box or load a Connection String template. Let's begin with building a connection string through the DataLink Properties dialog box.

Using the DataLink Properties Dialog Box

Click Build to open the DataLink Properties dialog box, click the Provider tab, and choose Microsoft Jet OLE 4.0 Provider, as shown in Figure 10.12.

Figure 10.12: The Provider tab of the Data-Link Properties dialog box
Click the Connection tab, which is shown in Figure 10.13.Enter the path to the Books Access database, and enter any username or password you have added to the database.

Figure 10.13: The Connection tab of the Data-Link Properties dialog box
Click the Test Connection button to validate your data connection. If successful, click OK to insert the connection string in the Connection String text field of the OLE DB Connection dialog box.
Click OK to save the data connection in the web.config file in the root folder of your ASP.NET site.

Loading a Connection String Template

As an alternative to the DataLink Properties dialog box, you can load one of the many connection string templates into the Connection String text field of the OLE DB Connection dialog box. By now, even if you don't know what they are, you're probably familiar with the connection string templates. To use the connection string templates to connect to the Access Books database, follow these steps:

In the OLE DB Connection dialog box, click the Templates button to open the Connection String Template dialog box, as shown in Figure 10.14.

Figure 10.14: The Connection String Template dialog box
In the Select Template list, select Microsoft Access 2000 (Microsoft Jet 4.0 Provider), and then click OK to return to the OLE DB Connection dialog box.
As you can see in Figure 10.15, Dreamweaver MX loads the familiar connection string template into the Connection String text field. All you have to do now is replace the placeholders with the appropriate information and click OK to save the data connection in the web.config file in the root folder of your ASP.NET site.

Figure 10.15: The Microsoft Access 2000 connection template loaded in the Connection String text field

Managed Data Provider for SQL Server

If you're creating an ASP.NET web applicaton to connect to SQL Server, the Managed Data Provider for SQL Server is your best option. The Managed Data Provider for SQL Server is optimized for SQL Server and is blazingingly fast. The following steps demonstrate how to use Dreamweaver MX to create a data connection using the Managed Data Provider for SQL Server:

Choose Windows ® Databases to open the Databases panel. Open any ASPX page in your site, click the plus (+) sign and choose SQL Server Connection to open the SQL Server Connection dialog box, as shown in Figure 10.16.

Figure 10.16: The SQL Server Connection dialog
You will see a familiar connection string template in the Connection String text field. Simply replace the placeholder information with your SQL Server and database information.
In the Connection Name text field, enter a Dreamweaver MX data connection name.
Click the Test button to validate the data connection. If the test is successful, click OK to save the data connection in the web.config file in the root folder of your ASP.NET website.

Database Connections for JSP Developers

In this section, we'll show you how to create a database connection for a JSP web application. Before beginning, you need a JSP development environment, which includes a web server, a JavaServer Pages application server (sometimes these are one and the same), and a JSP site defined in Dreamweaver MX. In our example, we are using IIS and Macromedia JRun 4, and we'll connect to the Access and SQL Server versions of the Books database. Further, we'll look at the three data connection methods provided by Dreamweaver MX in a JSP application-a JDBC-ODBC Bridge, Type 4 JDBC drivers, and a Custom JDBC Connection.

JDBC-ODBC Bridge

To connect a JSP web application to a database, you must use a JDBC driver. However, you can use a JDBC driver to connect to an ODBC-compliant database. As you may recall, a JDBC-ODBC bridge allows you to accomplish just this. For example, we can create a Dreamweaver MX data connection in our JSP web application that connects to the System DSN that points to our Access version of the Books database. To establish this connection, follow these steps:

Choose Windows ® Databases to open the Databases panel. Open any JSP page in your JSP web application site, click the plus (+) sign, and choose Sun JDBC_ODBC Driver (ODBC Database) to open the Sun JDBC_ODBC Driver (ODBC Database) dialog box, as shown in Figure 10.17.

Figure 10.17: The Sun JDBC_ODBC Driver (ODBC Database) dialog box
You can specify whether Dreamweaver MX uses a driver on the local machine or a driver on the testing server. Choose Using Driver On Testing Server.
In the Connection Name field, enter a name for the Dreamweaver MX data connection. As we've mentioned, Dreamweaver MX will name the connection JSP page based on the Dreamweaver MX data connection name you provide. Then, in the URL text field, replace [odbc dsn] with the booktracking System DSN you created earlier, as shown in Figure 10.17.
Click the Test button to validate the data connection. If the test succeeds, click OK to save the data connection as a JSP file in the Connections folder of your JSP site.

Type 4 JDBC Drivers

As you may recall, Type 4 JDBC drivers allow a JSP web application to connect directly to a target database. Dreamweaver MX has five Type 4 database drivers you can use to connect directly to your database:

IBM DB2 App Driver (DB2)
IBM DB2 Net Driver (DB2)
MySQL Driver (MySQL)
Oracle Thin Driver (Oracle)
INET Driver (SQL Server)

Each of these drivers requires similar information: the host server name, the port on which the database server communicates, the database name, and the username and password. To demonstrate, we'll use the INET Driver to connect to our SQL Server version of the Books database.

Choose Windows ® Databases to open the Databases panel. Open any JSP page in your JSP web application site, click the plus (+) sign, and choose INET Driver (SQL Server) to open the INET Driver (SQL Server) dialog box, as shown in Figure 10.18.

Figure 10.18: The INET Driver (SQL Server) dialog box
You can specify whether Dreamweaver MX uses a driver on the local machine or a driver on the testing server. Choose Using Driver On Testing Server.
Enter a name for the Dreamweaver MX data connection.
In the URL text field, replace [hostname] with the name of the IP address of your SQL Server, replace [port] with the port to which you've set SQL Server (the default port is 1433), and replace [database] with the database name.
Click the Test button to validate the data connection. If the test succeeds, click OK to save the data connection as a JSP file in the Connections folder of your JSP site.

Custom JDBC Connection

Although Dreamweaver MX comes with five direct drivers that can connect to the most popular database servers, you may find yourself working with a more obscure database. In such a case, you can still use Dreamweaver MX to create a data connection through the Custom JDBC Connection method if you can find a JDBC driver for your database. As you can see in the Custom JDBC Connection dialog box shown in Figure 10.19, this method is wide open to whatever parameters your database demands. Unfortunately, Dreamweaver MX can offer you little assistance in determining these parameters. Your best bet is to install the JDBC driver on the web server and then consult the JDBC driver and your database documentation.

click to expand
Figure 10.19: The Custom JDBC Connection dialog box

Database Connections for ColdFusion Developers

Unlike ASP, ASP.NET, JSP, and PHP, you cannot actually create a data source connection through Dreamweaver MX for Macromedia ColdFusion. Instead, you define the data source connection through the ColdFusion administrator. You can then view and select the data sources from within Dreamweaver MX. However, before Dreamweaver MX can connect to ColdFusion data sources, several requirements must be satisfied.

You must have access to a web server such as IIS, PWS, or Apache Tomcat for Linux.
You must install the ColdFusion application server on the web server.
You must define a ColdFusion site in Dreamweaver MX.

When all these requirements are met, you can see all ColdFusion data sources in Dreamweaver MX. To demonstrate how ColdFusion data sources appear in Dreamweaver MX, we'll use the SQL Server version of our Books database.

Open the ColdFusion Administrator and navigate to the Data Sources section. In the Data Source Name text field, enter a ColdFusion data source name. This is the data source name that Dreamweaver MX displays in the Database window of your ColdFusion website.
Specify a driver to connect to the Books SQL Server database. You can use a ColdFusion- supplied SQL Server driver or select ODBC Socket to choose a System DSN from your web server. For our example, choose the Microsoft SQL Server driver from the Driver drop-down list box, as shown in Figure 10.20, and then click Add to open the Microsoft SQL Server Data Source dialog box

Figure 10.20: The Data Sources section in the ColdFusion Administrator
Enter the Books database name, the server it resides on, and the username and password to connect to the database. Click Submit to create the ColdFusion data connection.
Return to Dreamweaver MX and open your ColdFusion website. Open any CFM page in your site and choose Windows ® Databases to open the Databases panel. As you can see in Figure 10.21, Dreamweaver MX displays the ColdFusion data sources ready for use.

Figure 10.21: The Databases tab in Dreamweaver MX displaying data sources from the ColdFusion Administrator

Database Connections for PHP Developers

Concerning PHP development, Dreamweaver MX supports only the MySQL database. However, PHP is a powerful open-source language that supports many types of database connection methods, including ODBC. Therefore, you can always take matters into your own hands and manually script your database connections. However, for the purposes of this section, we are going to demonstrate how to create a data connection to the MySQL database. To begin, you will need access to a PHP/ MySQL development environment, including a web server and the PHP application server.

To create a data connection to a MySQL database, follow these steps:

Choose Windows ® Databases to open the Databases panel.
Open any PHP page in your site, click the plus (+) sign, and choose MySQL Connection to open the MySQL Connection dialog box, as shown in Figure 10.22.

Figure 10.22: The MySQL Connection dialog box
Enter the name of your Dreamweaver MX data connection, the name of your MySQL Server, the username and password to connect, and the database you want to connect.
Click the Test button to validate the data connection. If successful, click OK to save the data connection as a PHP file in the Connections folder of your PHP site.