Connecting Dreamweaver to MySQL & Chilisoft ASP

Preface

There have been a lot of questions over the years about whether or not you can connect Dreamweaver to Sun One Active Server Pages (formerly Chilisoft ASP). I would guess that the reason for this is the popularity of the Sun Cobalt RaQ web servers and the fact that Sun now bundles the ASP engine with their servers.

As did many developers, when we started hosting our own web sites, we began migrating our Microsoft ASP applications over to Sun/Chilisoft and found that the engine does not directly support Access databases and to support MSSQL, we had to use a software bridge to connect to a real windows machine. We were looking for an all-in-one solution at the time and our next best bet was MySQL which is also pre-installed on the RaQ servers, so this is the course we decided to take.

Our configuration for this tutorial:

Development Computer:
Windows 2000/XP Professional (any system with access to the Data Sources (ODBC) control panel will do)
Dreamweaver MX (We were using this technique with Ultradev 4 as well)

Web Server
Sun Cobalt RaQ4
Sun Chilisoft 3.6.2
MySQL 3.23.54

Step 1 - Setup

First I suggest you learn about MySQL. Get yourself a book, go to a class, whatever you need to do to get familiar with it. You'll be happy that you did...there are differences between Access and SQL in general that you are going to need to know, too many to go into depth here. Also, make sure you download a recent version and get it installed on your server. The pre-installed versions are very old. If you are using a RaQ, you can download a package file from PackageMaster.

Next, move your database to or create your database in MySQL and set up a user that will be allowed to access it. We often do some preliminary work in Access and then convert the file using Access-to-MySQL from Intelligent Converters.

Step 2 - The Chilisoft DSN

We typically start by creating the Sun/Chilisoft DSN first because the settings you make here MUST be entered the same in windows. Linux is very particular about case-sensitivity, where there are cases where windows is not.

I went to the ASP control panel, clicked on Databases and chose New Data Source. The image to the right is an example of what a typical setup screen looks like. Make sure you write down the exact DSN name and Database that you type here including the case-sensitivity of them. DSN: The name of your data source as you will refer to it in your programming efforts. Description: A short description of what this database is for. This will ONLY appear in the Chilisoft control panel. Database Type: The type of database we are connecting to. In this case, MySQL. Driver: The name of the driver file being used to connect to this database. Server: The name or IP address of the server we are connecting to. Port The port number that the MySQL server is listening to. The default is 3306. Database: The name of the database to which we are connecting. User* The username for connecting to this database Password* The corresponding password for this user. *Only if you don't plan to include it when programming later. After clicking save, you should see the Data Source Names list including your new DSN.

Step 3 - the Windows DSN

Now for the windows side of things. The people at MySQL distribute a driver for Windows ODBC that allows you to connect to MySQL databases using a standard DSN. Download and install the proper version for your OS. They have versions for 95/98/ME versions as well as NT/2000/XP. After installing the driver (and rebooting your computer in some cases), you'll need to open the Data Sources (ODBC) control panel. Under 95/98/ME, you should be able to find it under the control panel, NT/2000/XP users will find it in the control panel under administrative tools.

Select the System DSN tab and then click Add to create a new data source. You will be presented with a dialog similar to the one shown here. Select the MySQL driver and click Finish.

The mysql Driver default configuration screen should appear. Windows DSN: Corresponds to the DSN name you specified for Chilisoft. MySQL Host: The name or IP address of the server where the database resides. MySQL Database: From Chilisoft configuration. User: From Chilisoft configuration. Password: From Chilisoft configuration. Port From Chilisoft configuration. If you have any questions about the rest of these parameters, there is documentation online at the MySQL web site.