The test application I used was a basic forms and reports program to manage parts of a neighborhood association's Web site. The Web site lets you list, create, and edit member records; and list, create, and edit contractors' information, including members' contractor references.
I wrote the program to handle data using Microsoft Access and Microsoft SQL Server, and all database interaction was via SQL statements, specifically SELECT, INSERT, UPDATE, and DELETE. A few places in the program changed SQL statements based on which database the program used. For example, I tested for true values in a SQL Server bit field by testing for a value of 1, and I tested for true values in an Access Yes/No field by testing for a value of -1.
I wrote an ASP version of this application using ADO to talk to the database and a Perl version using the Win32::ODBC module to talk to the database. Each version has a subdirectory called private that contains the administrative pages. During my testing, I attempted to create an Administrator account and restrict the private Web pages' rights to permit access to only that user. This task proved to be the most difficult test for the Web hosting services I tested. I wanted to use Windows NT's built-in security capabilities; however, most ISPs don't let you create users and restrict directories, or the ISPs require you to call support staff to make these changes for you. I might have had better luck creating a user database, building the code to manage it, and logging on users myself. (Interland was the exception because it let me create an Administrator account and set permissions easily.)
The standard posting process is to FTP data to the site and post the files in a directory. This process was easy using all the services I tested. Next, I created the ODBC Data Source Name (DSN). Some services let me create the ODBC DSN on a Web page, some services required me to call customer service to create the ODBC DSN, and other services allotted me a DSN as part of my contract.
To post Access databases to your Web site, FTP the database to the site. Most hosting services provide a special data or databases directory for this purpose, and their DSNs search these directories for file-based databases. You don't want these directories in Web namespace because a clever user might gain unauthorized access to parts of the site.
To post SQL Server data, each vendor gave me its SQL Server system's DNS name or IP address and a login to a database on that server (the vendors usually used the same login information as the FTP account information they assigned me). I used SQL Server Enterprise Manager to import data from my local database to their database. However, three of the vendors were running SQL Server 6.5, and my SQL Server 7.0 Enterprise Manager couldn't talk to the vendors' servers. To overcome this problem, I created SQL scripts to create the tables in a database and INSERT the dummy data into the vendors' databases.
