Configuring UltraDev and PHAkT to use an Access Database on PWS

Configuring UltraDev and PHAkT to use an Access Database on PWS

This tutorial assumes the following :-

a). You have PWS installed as your Web Server.
b). You have installed PHP v4.01.
c). You have installed the PHAkT PHP Server model v6.6

Configuring an Access Connection can be a little tricky, due to a bugs in both the PHAkT PHP Server Model, and in PHP.

The bug in the PHAkT Server Model is what applies in this instance, the bug in PHP only applies if you are using a UNIX Webserver, and is beyond the scope of this tutorial. I will explain the bug in PHAkT and provide it's remedy in section 3 of this Tutorial.

Note:
Some lines in this tutorial are very long and should not be wrapped. However because of design issues we had to write them on multiple lines. In this case we use the character ¬ to indicate that the line break should be ingnored. When you are copying-pasting codes form this tutorial be sure to remove the linebreaks and the ¬ character.

A. Configuring a DSN.

1. The first thing that you must do is copy your access database into the relevant directory on your webserver, and then setup a System DSN to your access database. For the sake of this tutorial I am using the Compass Travel database ( compasstravel.mdb ) that ships with UltraDev.
2. First copy the database to C:\Inetpub\wwwroot\compass (the path to your wwwroot directory may be different in your installation).
3. Next, go to Control Panel and open the ODBC Datasources applet.
4. Select System DSN and click 'Add'. Select Microsoft Access Driver (*.mdb) and click 'Finish'.
5. Fill in the form as illustrated in Figure 1.

Figure 1: Configuring a DSN Connection

Notes:

• For the sake of simplicity during this tutorial I have not set a Username or Password for this connection, you on the other hand may want to do so.
• To setup a Username and Password click on 'Advanced' and fill in the relevant details there.

B. Installing ADODB.

The PHAkT PHP Server Model uses a 'wrapper' library called ADODB to provide database connections. ADODB was developed to provide a standardized system to connect, query, modify and maintain databases without having to learn the different commands that each database uses.

To make installing ADODB for use within UltraDev even simpler I created a small command extension that can be downloaded here which will automatically copy all the required files into your working directory.

To install ADODB, click on the Commands Menu in UltraDev, and click Install ADODB. An introductory screen will open. If you now click OK, the ADODB files will be copied to your working directory.

C. Creating a Connection.

Now that you have installed ADODB, we need to create a new connection to the Compass Travel Database.

1. Create a new web page ( or open an existing one ), and open the DataBindings Palette ( Window->DataSources ).
2. Click the Define Connection button. You should see a screen similar to the one in Figure 2.
   
   
   Figure 2: Creating a Connection in UltraDev
   
   It is good practice to prefix all connections with 'conn', as it is also good practice to prefix all recordsets with 'rs' ( more of that later ), for the sake of clarity.
   
   Here are the settings I have used :

   Connection Name: connCompass
   URL: jdbc: odbc:Compass	-	Compass is the name of the DSN created in Step 1.
   User Name:	-	Left blank, if you configured a username for your DSN, enter it here
   Password:	-	Left blank, if you configured a password for your DSN, enter it here
   DB Type: Access
   Host: Localhost
   Database name: Compass	-	Again, the name of the DSN created in Step 1
   User Name:	-	Left blank, if you configured a username for your DSN, enter it here
   Password:	-	Left blank, if you configured a password for your DSN, enter it here

   
   
3. If you now click on 'Test', you should get a message saying 'Connection was made successfully'.
4. Now click OK.

Here is where it gets tricky. As previously mentioned, the way that PHAkT configures Access connections is flawed. The flaw has now been created. If we try to use this connection we will get an error telling us that there is a bad include path, and the connection won't work.

Here is how to resolve that problem.

1. Open the connection file. You will find it in a folder called Connections within your working directory. If you have followed this tutorial precisely, you will see a file called connCompass.php. Open this file and it will look like this :-
   
   
   
   
2. The problem occurs because of the line highlighted in red.
   Change this line to :-
   $connCompass->PConnect($MM_connCompass_DATABASE, ¬
   $MM_connCompass_USERNAME,$MM_connCompass_PASSWORD);
3. Save the file.

The reason that this line doesn't work is that to communicate with a DSN you need to send the information in the format DatabaseName, Username, Password for ODBC Connections. This bug is an issue that has already been reported to Interakt ( the developers who created PHAkT ) and I'm sure this error will be resolved in future versions of the PHP Server Model.

Now that we have installed ADODB, and have created a database connection, we need to upload the two folders 'Connections' and 'adodb' to our webserver. When that is done we can start to use the database :-)

D. Creating a Recordset

1. In UltraDev, open an existing page, or create a new one.
2. In the Data Bindings Palette ( Window -> Data Bindings ) click on the '+' symbol and choose 'Recordset (Query)'.
3. We are going to create a simple recordset containing all of the Employee information, as per Figure 3.
   
   
   
   Figure 3: Creating a Recordset
   
   Call this recordset 'rsEmployees' ( the prefix rs makes it clear that this is a Recordset). Select the connCompass connection, and choose All of the Columns. Now click OK.
4. In your Data Bindings Palette you should now see an entry for 'rsEmployees'. Click on the '+' next to this entry and you will see a list of all of the columns within that table.
5. To test the connection we are just going to drag a couple of these elements onto our page. (see Figure 4). I have chosen to drag FIRSTNAME, LASTNAME and EMAIL onto my page.
6. Select everything you have placed on your page, and click on the the Server Behaviors Tab ( or click Window-> Server Behaviors ) and click on the '+' and click on 'Repeat Region'.
   
   
   Figure 4: Inserting Columns onto a page
   
   In the dialogue box that opens, select 'All Records', and click 'OK'. This will now output all of the records in the Employees table of the Compass Travel database when the page is viewed.
7. To test the page click on the 'Show Live Data View' button and you can see the data within UltraDev.

If you now save this file and upload it to your webserver you will be able to view exactly the same results within your web browser ( see Figure 5 below ).

Figure 5: The finished example page

This same principle can be applied to any Access database that you wish to access in UltraDev, the most important thing to do is to make the change to the Connection file.