Connecting through an ODBC driver

Although JSP applications must communicate with databases through JDBC drivers, they can communicate through ODBC drivers if you have a JDBC-ODBC bridge driver. The bridge driver acts as an interpreter between your JSP application, which "speaks" JDBC, and your ODBC driver, which "speaks" ODBC. This channel of communication lets your JSP application communicate with the database.

This kind of connection offers two advantages for Windows users. First, you can use the free ODBC drivers from Microsoft. Second, you can use a DSN to simplify the task of creating the connection.

You must meet the following requirements to connect through an ODBC driver:

• Your JSP application server must be running on a Windows computer.
• An ODBC driver for your database must be installed on the Windows computer running the application server. For more information, see Checking for the ODBC driver.
• A JDBC-ODBC bridge driver must be installed on the Windows computer running the application server. For more information, see Installing the Sun JDBC-ODBC Bridge driver.

If you meet these requirements, you can connect through an ODBC driver. For instructions, see Creating an ODBC connection.

Checking for the ODBC driver

Make sure an ODBC driver for your database is installed on the Windows computer running the JSP application server. To find out whether an ODBC driver is installed, see Viewing the ODBC drivers installed on a Windows system. If an appropriate driver is not installed, you can download and install the Microsoft Data Access Components (MDAC) 2.5 and 2.7 packages on the computer running the JSP application server. You can download MDAC for free from the Microsoft website at http://msdn.microsoft.com/data/mdac/downloads/. These packages contain the latest ODBC drivers from Microsoft.

If you have an ODBC driver for your database, you can install a JDBC-ODBC driver next. For instructions, see Installing the Sun JDBC-ODBC Bridge driver.

Installing the Sun JDBC-ODBC Bridge driver

To connect through an ODBC driver, you must install the Sun JDBC-ODBC Bridge driver on the Windows computer running the JSP application server. The driver comes with the Sun Java 2 SDK, Standard Edition, for Windows.

To find out if you already have the Java 2 SDK with the driver, check your hard disk for any of the following folders: jdk1.2, jdk1.3, or j2sdk1.4.

If you don’t have the SDK, you can download it from the Sun website at http://java.sun.com/j2se/ and install it. The driver installs automatically when you install the SDK.

Although it is adequate for development use with lower-end database systems such as Microsoft Access, the Sun JDBC-ODBC Bridge driver is not intended for production use. For example, it lets only one JSP page connect to the database at a time (it does not support concurrent use by multiple threads). For more information on the driver’s limitations, see TechNote 17392 on the Macromedia support center at www.macromedia.com/go/17392.

After you install the bridge driver, you can create the database connection next. For instructions, see Creating an ODBC connection.

Creating an ODBC connection

Before connecting through an ODBC driver, make sure the appropriate ODBC driver and the Sun JDBC-ODBC Bridge driver are installed on the Windows computer running the JSP application server (see Checking for the ODBC driver and Installing the Sun JDBC-ODBC Bridge driver).

To connect through an ODBC driver in JSP:

1. Define a DSN on the Windows system hosting your application server.

   For instructions, see the following articles on the Microsoft website:

   • Windows 2000 users, see Microsoft Knowledge Base Article 300596 at http://support.microsoft.com/default.aspx?scid=kb;en-us;300596
   • Windows XP users, see Microsoft Knowledge Base Article 305599 at http://support.microsoft.com/default.aspx?scid=kb;en-us;305599
2. Open a JSP page in Dreamweaver, then open the Databases panel (Window > Databases).

   The panel displays the connections defined for that site.

3. Click the Plus (+) button on the panel and select Sun JDBC-ODBC Driver (ODBC Database) from the pop-up menu.

   The Sun JDBC-ODBC Driver (ODBC Database) dialog box appears.

4. Enter a name for the new connection.

   NOTE

   Do not use any spaces or special characters in the name.

5. Replace the [odbc dsn] placeholder in the URL box with the DSN you defined in step 1.

   The URL box should look like this:

   jdbc:odbc:myDSN
   

6. Specify the user name and password to access the database.

   If you don’t need a user name or password, leave the boxes blank. For example, if your DSN is called Acme and you don’t need a user name or password to access the database, enter the following parameter values:

   Driver: sun.jdbc.odbc.JdbcOdbcDriver

   URL: jdbc:odbc:Acme

   Username:

   Password:

7. Specify the location of the JDBC-ODBC Bridge driver.
   • If the driver is installed on the same computer as Dreamweaver, select the Using Driver On This Machine option.
   • If the driver is not installed on the same computer as Dreamweaver, select the Using Driver On Testing Server option.

   Macintosh users can ignore this step because all database connections use the application server.

8. Click Test.

   Dreamweaver attempts to connect to the database. If the connection fails, double-check the DSN and the other connection parameters. If the connection still fails, check the settings for the folder Dreamweaver uses to process dynamic pages (see Specifying where dynamic pages can be processed).

9. Click OK.

   The new connection appears in the Databases panel.

Related topics

• Troubleshooting Database Connections