This white paper is intended for UNIX based ISPs who want to use and support database access with their implementation of Chili!Soft ASP. Special attention is paid to methods for transitioning ASP Web sites that have been built using Microsoft Access.
In this white paper:
· Chili!Soft ASP Database Connectivity Primer
· DSN Decisions
System DSN
File-based DSN
DSN-less Connection String
· Database Connectivity
PC Client Access
Command-line/Web Server Access
· What about Microsoft Access?
Scenario 1: Using dBase
Scenario 2: Using Access to Export Data Directly
Scenario 3: Using MySQL
Scenario 4: Using Oracle or Sybase
· Conclusion
· Company and Product Overview
About ASP
About Chili!Soft ASP
About Chili!Soft
· Appendix A
Using Microsoft Access as a Data Mining Tool
One of the benefits of the Active Server Pages (ASP) platform is the ease in which data from databases may be accessed and published to the Web. This white paper presents the many options an ISP has in making a database generally available to ASP developers when hosting Chili!Soft ASP on UNIX. It also offers recommendations on methods for transitioning a Microsoft Access-driven ASP Web site to a UNIX based database-driven Web site.
Database access and publishing are primary features of the ASP framework. In order to facilitate this on UNIX, Chili!Soft ASP ships with a fully compliant ActiveX Data Objects (ADO) Control. Within the ASP framework, using ADO typically involves writing VBScript or JavaScript procedures that issue a query or insert to a database, then formatting the results with the requisite HTML tags. Database connectivity is handled via ODBC. Chili!Soft ASP ships ODBC drivers for the following database products:
· Oracle
· Sybase
· Informix
· OpenIngres
· DB2
· MySQL
· dBase
· Microsoft Access and Microsoft SQL Server (via connectivity to an NT server)
Given the ADO database access model is regular and understood, many of today’s ASP tools ship with wizards and pre-built scripts to further ease Web development. For example, Microsoft FrontPage 2000 has a built-in Database Results Wizard that enables a user having no SQL or ASP skills to access and then publish a database to a Web site. For more information, see "Using FrontPage Database Features" in "Chapter 4: Building a Chili!Soft ASP Application."
Before an ISP makes a database generally available for use by ASP Web developers, key decisions about how to deploy DSNs must be made. A DSN, or Data Source Name, is used by the Chili!Soft ASP ADO and ODBC implementation to identify the database connection, as well as any database specific parameters that are needed. An example DSN for Oracle 7 follows:
[Oracle7]
Driver=/opt/odbc/lib/ivor712.so
Description=Oracle7
ServerName=OracleTNSName
LogonID=odbc01
Password=odbc01
EnableScrollableCursors=1
Parameters such as username/password access can be stored with the DSN or provided when the connection is made from the ASP script. Each ASP Web site that makes a connection to a database needs a DSN. There are three different ways DSNs can be deployed using Chili!Soft ASP and each provides its own benefits and liabilities. It is important to note that in a virtual hosting environment, Chili!Soft ASP can be configured such that each Web server has its own configuration of the software. This allows the Web site owners to make their own decisions about DSN deployment. The three possibilities for DSN deployment are as follows:
Each instance of Chili!Soft ASP has a configuration file installed with it called ODBC.INI. This file is a listing of DSN entries. When an ASP Web site makes a database request to a DSN, the ASP engine looks in this file to get the full parameters of the connection. This is equivalent to using the ODBC Manager under Windows NT to store and manage database connections.
Advantages:
· The file is secured such that only the owner of the Chili!Soft ASP engine can make modifications.
· Provides one central place for an ISP administrator or customer owner to manage database access.
· Global changes to data access can be made in one file.
Disadvantages:
· In a shared hosting environment where a large number of ASP Web sites are sharing the same instance of Chili!Soft ASP this file could become extremely large and unmanageable.
· In the shared hosting environment, an ISP admin must touch this file for each ASP Web site that needs database access.
· Rogue developers can attempt to guess DSNs in the file, looking for a database that might have weak username/password security.
An individual file, separate from the ODBC.INI file, contains the DSN information. This file is stored in a directory that the ASP Web site can access. When the ASP engine gets the database request, it looks in this file instead of the ODBC.INI file.
Advantages:
· ISP administration is reduced as each Web site developer can create his or her own DSN.
· Security is enhanced since the file can be stored in a directory that can only be accessed by the Web site owner.
Disadvantages:
· Global database changes (i.e., database is moved to another machine) require changes be pushed out to all Web site owners in order to maintain site functionality.
· Does not work well with file-based (dBase and text) databases.
Rather than store the DSN externally, the connection is fully described in the ASP script that is making the database request.
Advantages:
· ISP administration is reduced because each Web site developer manages his or her connection information.
Disadvantages:
· Global database changes (i.e., database is moved to another machine) require changes be pushed out to all Web site owners in order to maintain site functionality.
· Database connection information can be stored in individual ASP files and any changes to it would have to be replicated throughout all the files.
Once a DSN deployment decision has been made, the ISP must also decide how to offer database access to the Web developer. There are two ways to provide this access:
Most Windows-based ASP Web development tools have a database interface that relies on the client machine’s ability to connect to the database. In an ISP hosting configuration, this means exposing the database interface so that a PC client can connect to the database over the ISP network. The PC client would be given the database information and would configure the ODBC driver and ASP development software to talk directly to the database.
Advantages:
· A rich set of point-and-click database development tools are made available to even the casual user. Users can use these tools to be their own DBA, importing and exporting data, adding new tables, etc.
· Users that are not SQL savvy can use the database wizards in ASP tools like FrontPage 2000 and Elemental Drumbeat to create their database driven Web pages.
Disadvantages:
· Database is exposed via a network connection and provides a means for network based hacking.
· Connection to network and database is required to create database driven Web pages.
In this case, the only way to talk to the database is through the computer server that hosts the database. This is done via a terminal session or indirectly through ASP Web pages. Development of the ASP Web site happens off-line and is then uploaded and tested against the ISP database. A Web developer can duplicate the ISP hosting environment (install local copies of the requisite database and Web server) and then change the DSN information before the ASP Web site is uploaded.
Advantages:
· Development is off-line and there is no need to connect to the ISP except to upload and test the application.
· The ISP database is more secure with one less access path.
Disadvantages:
· Casual users without SQL skills can have difficulty creating and managing data in a database.
· Some tools, such as FrontPage 2000, expect a live database connection will not work.
Once an ISP has developed a process for creating and delivering DSN information as well as for providing database access, new development of database driven ASP Web sites is ready to go. However, some work can be required when an ASP developer creates their Web site somewhere else, or for some other purpose and then brings it to the ISP to host. In many cases, given ASP’s roots in the Microsoft environment, the database used for this off-site development is Microsoft Access. Microsoft Access only runs in a Windows environment, so to host these Web sites on UNIX means the database access portion of the site must be transitioned to a new database. This is work that should be encouraged anyway, as even Microsoft does not recommend the hosting of Access-based Web sites in the high stress, high concurrency, 24x7 environment that most ISPs provide. Access can still be used as a data-mining tool once a Web site has been transitioned to another database. For more information, see "Appendix A: Using Access as a Data Mining Tool" in this white paper.
Chili!Soft has put together a number of possible database transition scenarios for an Access database driven Web site. Software required and a step-by-step process are included in each scenario.
Chili!Soft ASP includes the drivers and environment for supporting dBase. dBase can provide a transition for an ISP that does not provide a server based database or very simple database-driven Web sites. dBase is a file-based database, and therefore does not support many of the advanced features of today’s server-based SQL databases.
Windows 95/98/NT all come with ODBC drivers for dBase and developers can test this configuration before they upload it.
PC Client software required: None
ISP software required: Chili!Soft ASP
Process:
1. Developer uses Microsoft Access to export individual tables to dBase files.
· In the Database window, click the name of the table or query to export and then on the File menu, click Export.
· In the Save As Type box, click dBase IV. Click the arrow to the right of the Save In box and select the drive or folder to export to.
· In the File Name box, enter the file name and click Save.
· Two files will be produced: filename.dbf (the database file) and filename.mdx (the index file).
2. Repeat these tasks with each of the tables in the database.
3. Upload all files to the Web host and place them in a directory that the Web server has access to.
What must be changed in the ASP application:
· Old DSN information must be replaced with new dBase DSN information. For more information, see "Configuring Data Source Names (DSNs)" in "Chapter 3: Managing Chili!Soft ASP."
· Access table names are cropped to eight characters with no spaces and ASP code referencing these tables must be modified.
· Access field names are cropped to ten characters with no spaces and capitalized and ASP code referencing these tables must be modified.
· Table joins are not supported.
Microsoft Access supports the ability to export table data directly to another ODBC database. This does not work for all databases, so this should be thoroughly tested before making this user recommendation. PC client access to the hosted database is required for this to work.
PC Client software required: ODBC driver and client software for ISP hosted database
ISP software required: Database with ODBC driver
Process:
1. ISP/customer owner creates database.
2. Developer adds DSN for this database to the ODBC Manager of their PC client machine.
3. Developer uses Microsoft Access to export individual tables to tables in the ISP hosted database (instructions from Microsoft Access 2000 documentation):
· In the Database window, click the name of the table or query (Microsoft Access database only) to export, and then on the File menu, click Export.
· In the Export dialog box, in the Save As Type box, click ODBC Databases ().
· In the Export dialog box, enter a name for the file (or use the suggested name), and then click OK.
· The Select Data Source dialog box is displayed; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the Machine Data Source tab, and then double-click the ODBC data source that you want to export to.
· Particular ODBC data sources have different requirements for accessing their data. For example, most require you to enter a logon ID and password. You might need to get this information from your system administrator. Enter the appropriate information in the dialog box, and then click OK.
· Microsoft Access connects to the ODBC data source and creates the new table.
· Repeat these tasks with each of the tables in the database.
What must be changed in ASP application:
· Old DSN information must be replaced. For more information, see "Configuring Data Source Names (DSNs)" in "Chapter 3: Managing Chili!Soft ASP."
· In some cases, Access table and column names must be modified to adhere to ISP database conventions. This will mean that ASP code that references these items must also be changed.
Many ISPs use the database MySQL (http://www.mysql.com) as their choice for providing database access for a broad range of Web site developers. A nice feature of this database is its availability on a wide range of platforms, including Windows NT, Solaris and AIX. This scenario will assume that the developer is going to use the hosted version of MySQL for their work, and therefore needs to get the Access data into this database. This scenario uses a public domain script for doing its work.
It is also possible that a developer can load a Windows version of MySQL on their PC and do the work themselves to convert their Access database to MySQL.
PC Client software required: Script for exporting Access data to MySQL http://www.cynergi.net/exportsql/
ISP Software: MySQL
Process:
4. Developer downloads script and follows instructions to load script into Access database.
5. Developer runs script, end result is two files: esql_add.txt and esql_dl.txt.
6. Files are uploaded to ISP Web server.
7. ISP admin/customer owner imports data to MySQL with command: > mysql databasename < esql_add.txt
What must be changed in ASP application:
· Old DSN information must be replaced. For more information, see "Configuring Data Source Names (DSNs)" in "Chapter 3: Managing Chili!Soft ASP."
· In some cases, Access table and column names must be modified to adhere to MySQL conventions. This will mean that ASP code that references these items must also be changed.
Both Oracle and Sybase offer programs that allow Microsoft Access databases and their data to be directly imported into the respective databases. PC client access to hosted database is required for these applications to work.
Rather than go into detail on using these programs, it is recommended that an ISP who is hosting one of these databases download the programs and read the included documentation:
Oracle (works with Oracle 7 and 8):
Sybase (works with Sybase v11):
Active Server Pages offers a powerful model for connecting databases to the Web. Chili!Soft extends this model to the UNIX platform and marries this powerful model with a wide range of Unix databases. With proper planning, UNIX-based ISPs can use this combination to create a database oriented ASP hosting service that expands current customer options while attracting new ASP oriented customers.
ASP is an open, compile-free Web application environment that combines scripting, HTML custom server components, and robust database publishing to create dynamic Web-based business applications. With ASP, developers can build browser-independent Web solutions and publish legacy databases to the Web using tools such as Microsoft’s Visual InterDev™, Macromedia™ Dream Weaver, Elemental™ Drumbeat, or Sybase™ PowerSite. ASP is widely known for accommodating developers of varying skill sets and expertise, and for allowing corporate IT managers to more effectively allocate scarce developer resources. There are more than 500,000 ASP developers, over 500 companies producing ASP components and applications, and approximately 25,000 public Web sites using ASP.
Chili!Soft ASP is the functional equivalent of Microsoft’s ASP. Microsoft ASP is available exclusively on Microsoft’s Internet Information Server (IIS) Web server on Windows NT. Chili!Soft ASP enables ASP on other leading Web servers and operating systems, making it the first open, cross-platform Web application server based on Active Server Pages (ASP) architecture. Chili!Soft ASP is supported by industry leading Web development tools such as Microsoft Visual InterDev, Elemental Drumbeat™, NetObjects ScriptBuilder™, and Sybase PowerSite™, and provides ASP functionality to Netscape, Lotus, IBM and other Web servers.
Chili!Soft, Inc., a wholly owned subsidiary of Sun Microsystems, Inc. (NASDAQ: SUNW), is a provider of enabling technologies and applications for Active Server Pages. Chili!Soft's award-winning flagship product, Chili!Soft ASP, extends the ASP framework to major Web servers and operating systems including Microsoft Windows NT and Windows 2000, Sun Solaris, IBM AIX, Hewlett Packard HP-UX, and Linux. Chili!Soft was incorporated in 1997 with headquarters in Bellevue, Washington. For more information about the company, please visit the Chili!Soft Web site at http://www.chilisoft.com or call (425) 957-1122.
Once a Microsoft Access-based ASP Web site has been transitioned to use a UNIX database, Access can then be re-introduced as a way to analyze and tabulate the data being acquired by the Web. This is done by using the Access table-linking feature and requires that the ISP database be exposed to the network. The following instructions are from the Microsoft Access 2000 on-line guide.
Note
You can link a table only in a Microsoft Access database, not a Microsoft Access project.
Important
You cannot import or link an SQL or other ODBC data source unless you have installed the appropriate driver.
· Open a database, or switch to the Database window for the open database.
· To link tables, on the File menu, point to Get External Data, and then click Link Tables.
· In the Link dialog box, in the Files of Type box, select ODBC Databases ().
· Important: If you link to a file on a local area network, make sure to use a universal naming convention (UNC) path, instead of relying on the drive letter of a mapped network drive in Windows Explorer. A drive letter can vary on a computer or may not always be defined, whereas a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table.
· The Select Data Source dialog box lists the defined data sources for any ODBC drivers that are installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the ODBC data source that you want to import. To define a new data source for any installed ODBC driver, click New, and then follow the instructions in the Create New Data Source dialog box and the dialog boxes that follow it before continuing.
· If the ODBC data source that you selected requires you to log on, enter your logon ID and password (additional information might also be required), and then click OK.
· Microsoft Access connects to the ODBC data source and displays the list of tables that you can import or link.
· If you're linking a table, select the Save The Login ID And Password check box to save the information for the table in the current database so that users won't have to enter it. If you leave the check box cleared, all users must enter the logon ID and password every time they open the table in each new session with Microsoft Access. Your SQL Database administrator can also choose to disable this check box, requiring all users to enter the logon ID and password each time they connect to the SQL database.
· Click each table that you want to import or link, and then click OK. If you're linking a table and it doesn't have an index that uniquely identifies each record, then Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that will uniquely identify each record, and then click OK.
Notes
After importing or linking an SQL database table, you can set field properties for the table. If you import a table, you might want to set a primary key for the table.
If you encounter an error while importing, linking, or using an SQL database table, there might be a problem with your account on the SQL database server or with the database itself. If you can't access an SQL database table, contact the SQL database administrator.
To edit a linked SQL database table, usually the table must contain a unique index on the server. If you want to edit a table that doesn't have a unique index, or if you want to edit an SQL view, then you can create an index within Microsoft Access that the SQL database isn't aware of. You do that by creating a data-definition query, using the Create Index statement. Be aware, however, that the index must be created on a field or combination of fields in which each value is unique. If the field contains any duplicate values, all updates to the table will fail. To delete the index, use another data-definition query.
If the structure of an SQL database table changes after you link it, use the Linked Table Manager to refresh the link.
Copyright 2001 Sun Microsystems, Inc. All rights reserved. Legal Notice.