Using FTP Server Access for Site Development with UltraDev

by Judy Fontanella

Introduction

Creating Data Driven Web Pages - What Do You Need?

Managing large bodies of information has always been a challenge. Society as a whole, and Web developers in particular, have enthusiastically embraced the use of databases. Web designers are opting more and more to make use of technology that allows them to create dynamically changing pages. Special code on the page interacts with software on the server to retrieve information from a database and send it along to the user. The user always receives the latest, most up-to-date version of the information. Updating the information is as simple as updating the records in a database. No longer is it necessary to redo a Web page every time a piece of information changes.

Until UltraDev, it was necessary to learn a coding language, like ASP, PHP, Cold Fusion or JSP, before you could create these data driven pages. UltraDev has made it possible for Web designers without coding knowledge to create dynamic pages. Web designers who know how to code can work much faster.

When you use UltraDev, placing dynamic data on a page can be accomplished by filling in dialog boxes and selecting what data you need from lists of the tables and fields that are in your database. While you are creating your pages, UltraDev connects to your database and retrieves these lists for you so that you don't have to remember the field and table names yourself. You can just select the ones you want. In order for UltraDev to retrieve this information, you will need a connection to the server where your database (Access or MySQL or other ODBC database) and middleware (ASP, PHP, JSP or Cold Fusion) are running.

Most of the books and tutorials that are written about UltraDev assume that you are running such a server either on your development computer or on your local network. However, there are many Web designers who don't have that kind of development environment available. Those who are designing on Macintosh computers have had very few options available to them. Many Windows users are unable or unwilling to install the server software needed, as well.

Beginning with UltraDev version 4, it is possible to develop your pages by connecting to a remote server via FTP and HTTP. I have written this tutorial to explain this alternate setup procedure since that has been left out of so many books and tutorials. By following these instructions and obtaining an account on a server that runs the necessary software, anyone with UltraDev 4 and an Internet connection can develop dynamic Web sites.

This tutorial is written with directions for using ASP middleware. Hosts offering ASP are probably the easiest and cheapest to find. With just a few changes, you can use this tutorial with JSP or Cold Fusion. There are freely available UltraDev extensions that will allow you to use UltraDev with PHP, too. They are available from the Interakt site at: http://www.interakt.ro/

The Plan of Attack

There are several steps involved in putting dynamic data on your Web pages. These can be somewhat confusing for a newcomer, so I have created an outline of the overall procedure to help you put it all together. Detailed instructions for these steps will be given later in the tutorial.

Things to do once for each site:
- Setup the site folder on your hard drive and place the files that you will need in that folder.
- Define your site in UltraDev's site window. This includes defining the:
  1. Local Info
  2. Remote Info, and
  3. Application Server
Things to do once per database that will be used on the site. (You may use more than one database on a site. If so, you'll need to follow this procedure for each database.)
- Create the database and upload it to the proper folder on the server
- Create a database connection
  1. Open any page in the site and create the database connection.
  2. Test the connection.
Things to do at least once per page that uses database content. Some pages will use more than one recordset. If so, you will do this for each recordset.
- Create a recordset. This is a query that will retrieve just the data that you'll need for that page from the database.
- Place the field placeholders on the page.

Part A - The Site Setup

UltraDev Site Setup

When you create a dynamic site with UltraDev, it is extremely important to get the site setup dialog boxes filled in correctly. UltraDev will use the information you supply to upload your files to the server and to retrieve information from your database throughout development. If you find that you can't retrieve information from your database as you are creating your pages, there is often a problem with your site setup or with the connection to your database.

There are many different ways you can set up your site with UltraDev depending on your server and computer configurations. The method that I will show you uses FTP to connect to a remote server.

Usernames, Passwords and FTP Addresses

Before you begin, be sure that you know the FTP address, username and password that will allow you to login your site on the server using FTP. You will be able to find out this information from your hosting service or server administrator.

Setting up your site in UltraDev

Create a folder on your hard drive and name it something that you will recognize as the main folder that contains your Web site. My site is named "LinksDemo." You will use this folder as the main root folder for your site.
Define your site in UltraDev
1. Open the site window in UltraDev. Use the Site Menu at the top to select "Define Sites."
2. In the Define Sites dialog box, click the "New" button.
3. You will get the Site Definition dialog box.
4. Under the Local Info Category make these changes:
  1. Site Name: Type in a name that you will recognize for this site. It can be anything that you like.
  2. Local Root Folder: Navigate to the main site folder that you just created on your hard drive in part I. If you have chosen the folder correctly, it will be the last folder listed in the "Local Root Folder" text box.
  3. Check "Refresh Local File List Automatically" so that you will see file changes as soon as they are made.
  4. HTTP Address: Type in the URL that you would use to reach the top level folder in your site with your browser.
  5. Check "Enable Cache".

Under the Remote Info Category make these changes:
1. Access: Select "FTP".
2. FTP Host: type in the FTP address of your server. In this case it's "pluto.palomar.edu".
3. Host directory: In many cases, you will leave this text box blank. However, some sites are set up so that you put all of the files that are accessible from a browser in a subfolder named something like html, www, public_html, etc. If that is the case with your site, you will need to add the name of that subdirectory here. Adding a directory name here will cause you to log into that sub directory of your site. If you need to have access to the folders above this folder, you can use another FTP program to do so.
4. Login: Fill in your username.
5. Password: Fill in your password.
6. Leave the next two check boxes unchecked unless you know for sure that you are behind a firewall and will need to bypass it. The Check In/Out option is for use in development team environments. It prevents individuals from overwriting each other's changes.

Under the Application Server Category make these changes if necessary:
1. Server Model: Select ASP 2.0 ( Note: These directions assume that you are using ASP middleware. You may also use Cold Fusion or Java Server pages, if that is the middleware that is available on your server. If you are not using ASP, then make appropriate choices for numbers 2 and 3 below. )
2. Scripting Language: Select VBScript.
3. Page Extension: Select .asp.
4. Access: Select FTP
5. FTP Host: The FTP address of your server. The FTP Host, Host Directory, Login and Password will probably already be filled in correctly for you, because they contain the same information that you entered on the previous screen. Do check to be sure that the information is correct, however.
6. Host Directory: See E3 above for the directions here.
7. Login: Your username.
8. Password: Your password.
9. Leave the next two check boxes unchecked unless you know that you are behind a firewall and need to check them.
10. URL Prefix: Type the URL that would get you to the root folder of your site when you type it into your browser. It is very important to get this right, because UltraDev will use this information to retrieve the table and field information from your database when you are creating your pages. Your address will look something like this: "http://pluto.palomar.edu/Username" or this: "http://www.mydomain.com".

Click OK.
Click Done.

If you click on the Connect button, the one that looks like a plug, you should connect to your site on the server. Now you're ready to continue on to Part B - Connecting to Your Database.

Part B - Connecting to your Database

Preparation

In this section, you will create the connection between your database on the server and your Web page. The connection code that is used to connect to your database is very important. It tells the server important information such as where your database is located and what kind of database it is. That allows the server to know which database driver to use and where to find the files.

There are many different databases and types of connections that you can use. I will show you how to connect to an Access database file that has been uploaded to your server. Many of the books about UltraDev use Access for practice because that eliminates having to rely on more expensive database servers such as MS SQL Server or Oracle. It is also relatively easy and inexpensive to find hosting that supports Access. You can download the Links.mdb Access database file that I have created for this tutorial.

The specific connection type that I will use is a custom connection string, as opposed to a DSN. The connection string that uses the Jet driver for Access has a few important advantages. It's a fast connection, and you can use it without having your host set up a special DSN for you on the server. You can use as many databases as you want without having to wait for service from your host. Under some hosting plans, using the custom connection string may save you money as well as time. Please note that if you are using Cold Fusion or JSP instead of ASP, you will have to use a DSN as your connection type. If you would like to know more about how to set up DSN's or other types of connections for Access or other databases, please visit the Basic UltraDev site at: http://www.basic-ultradev.com/articles/ADOConnections/ .

Before you can create a connection to your database, you have to be sure all of your files are in the right places. Upload your Access file to the server now. Be sure that you put it in a folder that it will stay in for the life of your site, because your connection string will use the exact location of the database on the server. The folder containing your database file will need special permissions set if you plan on writing to your database. Either you or your host will need to put both read and write permissions for the anonymous user (IUSR) on that folder. If the permissions are not set correctly, you will be able to read from your database file, but unable to add or modify records from the Web site. I have set up a folder called "Databases" to house my database files.

Determining the Physical Path to your Database File.

Before you write the connection string to your database file, you will need to know the physical path to your database file. If you are using an ASP server, you can find the physical path to your Databases folder by following the procedure outlined below. You will use this path in the connection string.

Open a new, blank file. Save it as mappath.asp in your Databases folder.
Type the following ASP code into the body section of the CODE VIEW of your document. Do NOT type the code into the design view:

<%= Server.MapPath(Request.ServerVariables("Path_Info")) %>
Save your file and upload it to the Databases folder on your Web site.
Browse to the file with your browser. The URL should be: http://www.mydomain.tld/Databases/mappath.asp. Adjust this URL to reflect the URL of the Databases folder on your site.
When the file opens, you should see the physical path to this mappath.asp file displayed in your browser window. It should say something like: "E:\Inetput\WWWroot\mydomain\Databases\mappath.asp." Since your database files will be in the same folder as this mappath.asp file, you can use this address in your connection string after "Data Source=". Just remember to substitute the name of your database file (whatever.mdb) for the name of the mappath.asp file at the end of the string. So, if the path that displayed in your browser window was "E:\Inetput\WWWroot\mydomain\Databases\mappath.asp", and your database name was "Links.mdb", the physical path to your database file would be: "E:\Inetput\WWWroot\mydomain\Databases\Links.mdb."

Note: If your databases folder is located in a directory above the main folder of your site, you will not be able to place this mappath file in the databases folder and then browse to it. You can't browse to a file above the main root folder of your site. That is good protection for your database file, because no one can download the whole database by typing the address into their browser. It makes this step a little more difficult, however. What I do when faced with this situation is put the mappath.asp file in my main folder. Then, I can browse to it. It may tell me that my mappath file is located at: E:\Inetput\WWWroot\mydomain\html\mappath.asp. If my database file is located in the private folder that is on the same level as the html folder, I will adjust the path to my database file to reflect this. The database path is: E:\Inetput\WWWroot\mydomain\private\whatever.mdb.

Creating Your Connection

Open your Site folder and connect to the server. Make one last check to be sure everything on your site is in the proper folders. Note that I have the database file uploaded to the Databases folder on the server.

Open any page that is in on your site by double clicking it. If you don't have any files, yet, create a new blank file and save it in your site folder.
Go to Modify ->Connections.
Click the New button on the right.
Select Custom Connection String if you are offered a choice between that or a Data Source Name (DSN).
You will be presented with the following dialog box. Fill it out very carefully. You will be substituting the physical path to your database file for the one that is shown. Remember that you found that path by using the mappath.asp file in the section above.

Connection Name: Use a name that you will recognize. It is convention to start the name of a database connection with the "conn" prefix. For the Links database, name your connection connLinks.

Connection String: "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Inetpub\WWWroot\MyDomain\Databases\Links.mdb"
Note: The connection string is all on one line even though it may wrap in your browser window. Replace the part after the "Data Source=" with the physical path to your database file. There are exactly two spaces in the connection string. One is right after the ";" and the other is right after the word "Data". You must have just those two spaces.

Where it says: "UltraDev Should Connect:" select "Using Driver on Application Server".

Click the Test button. You should see the following message:

Now, if you look at the files that make up your site, you should see a new folder called "Connections". This folder holds files with the connection information for each of the databases that you will work with on your site. The files are used as include files. UltraDev places them on each page that uses data from the database. The connection files must reside on the server, as well as your local hard drive. Be sure that the Connection folder, along with its contents, is uploaded to the server before you procede to the next step.

Now you are ready to actually work with your data on your Web pages. All that is left is to create a new recordset and put the data on your page. The next section of the tutorial will show you how to do just that.

Judy Fontanella owns her own Web design company called ArtBytes in San Diego, California. She also teaches both online and face-to-face Web design classes for Palomar Community College in San Marcos, California.