Content area
A tutorial on how to create Web-enabled databases is presented. The combination of Inmagic's DB/TextWorks, ODBC Drivers, and Perl operating on a Windows NT server work together to form an effective database-driven Web environment. This article describes the process for installing all the components of this system. Example HTML pages and Perl scripts taken from a lib-web-cats database illustrate the basics of CGI programming and the process of delivering information from a database to Web users.
Full text
Keywords
Databases, Computer programming, Information retrieval, Internet, Information resources management
Abstract
A tutorial on how to create Web-enabled databases. The combination of Inmagic's DB/TextWorks, ODBC Drivers, and Perl operating on a Windows NT server work together to form an effective database-driven Web environment. Describes the process for installing all the components of this system. Example HTML pages and Perl scripts taken from the author's lib-web-cats database illustrate the basics of CGI programming and the process of delivering information from a database to Web users.
Introduction
One of the key ways to energizing a Web site involves tapping into the power of information held in database systems. Presenting lists of information on a static HTML page simply does not work as an effective means of providing access to large amounts of information content. A Web-enabled database connects an organization's users to its information resources. This method takes advantage of record structures, indexes, searchand-retrieval engines, and other features inherent in databases to provide more sophisticated access to content. Web-enabled databases also separate raw information from its presentation, making it easy to create a new look and feel for a Web site without recreating its underlying content.
This article outlines a roadmap for the construction of Web-enabled databases. While the examples that I give are specific to Inmagic's DB/TextWorks and OBDC Drivers, the general approach can be applied to other database applications. With only a few changes, the Perl programs described in this article can be used with other databases such as MySQL. As long as the database manager runs under Windows NT and has an ODBC interface, then the programming models described here should apply fairly closely. Along the way, we'll cover the basics of CGI (Common Gateway Interface) which is the basis of programming for a variety of Web applications.
Inmagic's WebPublisher vs ODBC
Inmagic's DB/Text WebPublisher product offers one approach to the creation of Webenabled databases that I have been using for quite some time. While a generally functional and easy-to-use product, it is one with some limitations. In order to continue to leverage the ability power of DB/TextWorks to deal with large quantities of textual data, and to make use of databases that I have created over the years in DB/TextWorks, I have recently started using a new approach. Rather than rely solely upon the DB/Text WebPublisher product, I have started using Perl scripts in combination with ODBC drivers available from Inmagic to provide Web access to DB/TextWorks.
Inmagic's DB/TextWorks has long been a popular text-oriented database management application for special libraries. Corporate and other types of special libraries have found DB/ TextWorks to be not only an excellent approach to traditional library management applications such as OPAC, serials control, and acquisitions, but also to be an excellent tool for managing full-text information. DB/TextWorks has proven particularly adept at providing access to textual information, has excellent search and retrieval abilities, and presents a relatively clean and simple user interface. Many libraries use Inmagic's DB/Text for Libraries as their primary library automation system, while others create custom databases for specialized purposes. Inmagic offers a program called DB/ Text Web Publisher that provides an easy approach for making these databases available through the Web. DB/Text Web Publisher allows one to create Web-enabled databases through a graphical interface, making it simple to create a dynamic Web environment without expertise in HTML or programming languages. DB/Text Web Publisher does allow you to specify HTML coding in order to create customized displays beyond those possible using the graphical interface alone. Figures 1 and 2 illustrate the graphical interface of DB/ Text WebPublisher. Figure 1 shows the Web Publisher Form Designer window that allows you to add HTML coding around the database fields. While you can use a purely graphical approach to select the fonts and colors of text, it also allows you to specify HTML coding directly, as shown in the example. Figure 2 shows the panel used to control each aspect of the presentation of the Web pages generated by WebPublisher.
It is possible to create relatively sophisticated Web-enabled databases with the combination of DB/TextWorks and DB/Text Web Publisher. I have created dozens of applications in this way, and consider myself a very satisfied user of this product. (For a list of these applications see: http://staffweb.library.vanderbilt.edu/ breeding/inmagic.html) Yet in the process of creating these applications, I ran into several limitations that prevented me from creating some features I wanted.
One of the reasons for these limitations is that DB/Text Web Publisher takes full control of the "header" parameters of generated HTML documents. This eliminates the ability to use Cascading Style Sheets. This is an important aspect of implementing a standards based Web environment with sophisticated formatting. Without the ability to link in a style sheet, any sophisticated formatting must be accomplished through HTML tags that may have been superseded or that may not work consistently among different Web browsers. It is usually the case that a Web database presentation needs to fit within the context of the larger set of Web pages within a site. If the overall Web site uses style sheets, it is quite a limitation to not be able to take advantage of these style sheets for presenting of database information. For example, at Vanderbilt we recently updated our Web pages, using a style sheet to create a common look for the new Web site. It became critical to be able to make the database-driven pages to follow the style sheet format. This was not possible using DB/Text Web publisher, making it necessary to develop another approach.
The graphical interface of DB/Text Web Publisher does not provide an especially friendly environment for editing complex HTML coding. DB/Text Web Publisher is great at providing a simple approach for publishing a database on the Web for those without much knowledge of HTML. But if you need specific HTML commands to define field presentation, the graphical user interface of DB/ Text Web Publisher actually gets in the way more than it helps. You'll find there comes a point with this product when a pure or manual programming approach will simply offer more flexibility and sophistication than can be accomplished in an "automatic transmission" presentation engine environment. One example of the limits of the "out of the box" approach is that the Web pages generated by DB/Text Web Publisher depend upon the use of JavaScript. Unfortunately, one result is that the navigational buttons and other features of these pages will not function with browsers without JavaScript capability. Ergo, you may need another page design solution approach.
Fortunately, for Web developers who want complete control over the appearance of a Web-enabled database and who are competent with a programming language, Inmagic offers a utility giving access to a DB/TextWorks
database through the Open Database Connectivity model (ODBC). Inmagic ODBC drivers allow DB/TextWorks to be accessed through any programming language that offers ODBC support.
An introduction to ODBC and SQL
The Open Database Connectivity model, or ODBC, is Microsoft Corporation's standardized access protocol for passing information to compliant database products. ODBC allows information to be easily shared between database applications. It also allows the same program code to access information from different database applications, so long as each has an ODBC driver available.
A standardized approach to providing ODBC access to information on the Web may thus follow the pattern of:
(1) connecting to the database through ODBC;
(2) selecting records using Structured Query Language (SQL); and finally,
(3) using Common Gateway Interface (CGI) scripting or programming to insert HTML coding necessary to properly display data elements in a Web browser interface.
Using ODBC interface, it is thus possible to create applications that can select records from a database through a query, retrieve the contents of records, sort results, or even update or add new records.
SQL is another common protocol for working with multiple database products. Also, using ODBC, non-SQL languages such as DB/ TextWorks may be accessed. For example, DB/ TextWorks is organized into textbases, each containing records with multiple fields, and characteristically with fields having multiple and variable-length entries. DB/TextWorks provides extremely long fields, to handle text, and the information acccess relies heavily on using the "Contains" search operator. SQL treats databases very differently, organizing information according to tables and rows. It assumes a specific set of data types and search operators. In order for DB/TextWorks to be accessed through ODBC and SQL, it has to "reshape itself' to fit into the SQL mold. Its field types must be mapped into the pre-defined SQL data types, and its operators must be expressed in SQL syntax.
The ODBC applications programming interface (API) standard allows different kinds of software to work together through a common communications layer. Since Inmagic offers an ODBC driver for DB/TextWorks, and there is also an ODBC interface available for the Perl scripting language, it is thus possible for the two to work together.
Perl: a programming language for the Web
There are a number of programming languages that can be used to create an ODBC-based application. The languages commonly used in this way include C++, Java, Visual Basic, and Perl. Perl is especially well suited for developing Web-based applications. As an Open Source application, it operates with a large variety of computing environments, including all forms of Unix, Linux, Windows NT, and even Novell NetWare. Since I use all these environments in my work, I find Perl to be a great programming language for creating utilities to work across these diverse systems. Perl is commonly used to create CGI (Common Gateway Interface) applications for Web presentation. Perl is an interpreted language, meaning that Perl scripts are processed line-by-line on the host computer, and not compiled into a binary executable program. Since it is an interpreted language, Perl is a little slower than compiled languages, but scripts are quite portable from one operating system to another, and generally offer more than adequate performance on all but the most heavily loaded Web servers. Perl scripts are also frequently used to process or manipulate information submitted via Web forms. These form-input applications are very familiar to experienced Web surfers.
The version of Perl I use with DB/TextWorks is ActivePerl version 5.6.0 for Windows NT from ActiveState Tool Corporation of Vancouver Canada. ActivePerl is one of few implementations of Perl which operate under Windows NT. ActiveState offers its version of Perl as freeware. It sells complete application development tools for those who require a more sophisticated environment than that available in the free version. In addition to the Windows NT version, ActivePerl is also available for Linux and Solaris. For more information about ActivePerl see http://www.activestate.com
DB/TextWorks: a Windows NT based system
One of the main considerations involved in choosing components to complement DB/ TextWorks is that Inmagic offers this product only for Windows NT. It does not operate under any flavor of Unix. Therefore, any other database application components must also operate under Windows NT.
In order to create a complete Web publishing environment with DB/TextWorks through ODBC, you must install numerous components. My "toolkit" includes:
* Microsoft Windows NT Server VA (Service Pack 6);
* Microsoft Internet Information Server;
* Inmagic DB/TextWorks Version 4.03;
* Inmagic DB/TextWorks ODBC Driver Version 1. 1;
* ActiveState ActivePerl 5.6.0 from ActiveState;
* Roth Consulting Perl Win32;;ODBC module;
* User-created HTML Query Forms;
* User-created Perl scripts.
The environment that I describe here is based on Windows NT 4.0. It is my understanding that DB/TextWorks is well supported under Windows 2000, but I have not personally tested it with this operating system.
Preparing the server
Creating the server environment for ODBC access to DB/TextWorks is not especially complex, but it does involve a number of steps. It is important to have fairly up-to-date versions of each software component. I found, for example, that only the latest Beta version of the Win32;ODBC mod for Perl would properly interface with the Inmagic ODBC driver. Do let your component documentation guide you in this area.
Inmagic offers no direct support for this environment. While Inmagic sells and documents the ODBC Driver, it does not include support development of applications in this environment. Inmagic does sell consulting services to aid in programming ODBC applications, but it appeared to me that they do not have particular expertise with Perl. Given the lack of clear instructions or documentation from Inmagic on how to interface Perl with the Inmagic ODBC drivers, I will describe a specific set of installation instructions that have worked successfully in my environment. I recently rebuilt an NT Server from scratch, and was successful in using this approach. I'm sure this isn't an "only way" answer; other pragmatic approaches to installing and configuring NT servers will likely work equally well.
Configuring your ODBC development environment (in excruciating detail)
(1) Install Windows NT 4.0 (from CD). In most cases, you will probably have an NT server already established. But in case you are starting from scratch, it is generally easiest to install the Windows NT Server 4.0 from the three start-up diskettes and the CD provided by Microsoft. You will need to partition drives, install network drivers, and all the usual configuration routines associated with building an NT server. I recommend that all drives be formatted with NTFS rather than FAT to ensure greater flexibility, more security, and to accommodate larger disk volumes.
(2) Install Service Pack 3. This Service pack is needed for the later installation of the Windows NT Option Pack. It takes only a few minutes to unpack and load, and requires a restart of the server.
(3) Install Windows NT Option Pack, including Internet Information Server.
The version of Microsoft Internet Information Server included in the Option Pack is old, but it is easier to go ahead and get it installed and upgrade it through the later Service Pack than to perform a fresh install of the latest version. (However, do not install the gopher server. You'll just have to remove it later.) I performed the Option Pack installation from a 2-CD set obtained from Microsoft which included the Windows NT 4.0 Option Pack, Service Pack 4, and Internet Explorer 4.0.
(4) Install Internet Explorer 4.0. Again, you will need to upgrade it to a newer version later on, but you cannot install Service Pack 4 without this version of the browser. You will again need to restart the server before going on to the next step.
(5) Install Service Pack 4. I installed this upgrade from the CD mentioned above. You will need to restart the server, etc.
(6) Install Service Pack 6a. At time of writing, this is the latest update to Windows 4.0 available from the Microsoft Support Web site (see http://www.microsoft.com/ ntserver/nts/downloads/recommended/ sp6n You will need to restart Windows NT yet again, but it will finally be configured with the latest version of all the Microsoft-supplied components.
(7) Install DB/TextWorks 4.0 from CD. Run the Setup.exe file, enter your name, institution, and license key when prompted, and let it install the application to its default locations.
(8) Install DB/TextWorks Patch to 4.04 (available from Inmagic's Web site). You will need to update DB/TextWorks to the current version. 4.04 is available now, and 4.1 will soon be available. Most of the new features announced for version 4.1 are not relevant if you are using ODBC. Inmagic makes the product patches available on its Web site at http://www.inmagic.com/ patches.htm The patch comes as a .ZIP file which you will need to unpack with a utility such as WinZip. Once you have decompressed the files into a utility, you will update DB/TextWorks by going to a command prompt, changing to that directory, and issuing the command "Patch" followed by the directory in which DB/TextWorks resides.
(9) Configure Microsoft Internet Information Server (IIS). Microsoft IIS comes with a number of sample Web pages, applications, and scripts. For security reasons, you should remove all directories and programs that you will not be using. If you make use of ASP, then you will have to be somewhat selective on what you remove.
(10) Create a directory for your Perl scripts. The common practice is to name it "cgi-- bin", but you can name it anything you want. My practice is to place it at the root level of the volume where Windows NT resides, but you might also want to put it within the "InetPub" directory that IIS uses by default.
(11) Using the Microsoft Management Console create a Virtual Directory called /cgi-bin/ linked to the physical directory just created.
(12) Set this directory to allow execution of the contained program files, following these steps:
* right click on directory;
* select Properties;
* choose Virtual Directory tab;
* check access rights for "Read" and "Log Access";
* uncheck Content Control option for "index this directory";
* check permissions for Execute.
(13) Start IIS if it is not already running through the Microsoft Management Console.
(14) Install DB/TextWorks ODBC Driver from the CD supplied by Inmagic. You will need to supply your name, institution, and license key. Once the files are copied, it will automatically launch Microsoft's ODBC configuration control panel.
(15) Create an ODBC DSN for DB/ TextWorks. This is the name that the server will use to open a connection between ODBC and your DB/TextWorks databases. Figures 3 and 4 illustrate these Control Panel. Keep in mind that the System DSN corresponds to the directory in which the DB/TextWorks databases reside, and that each table name corresponds to an individual textbase.
* From Control Panel, launch ODBC Data sources.
* Go to the System DSN panel.
* Click Add.
* Select the "Inmagic DB/Text driver (*.tba)"
* Assign a name. For example: "DBTEXT".
* Enter the directory in which the DB/ TextWorks databases reside. For example, D:/dbtext.
(16) Give IIS access to the textbase. Use My Computer to navigate to the directory in which the DB/TextWorks databases reside. Right click on the directory, go to the Security tab, press the Permissions button and give the Internet Guest Account (usually IUSR_SERVERNAME) read-only access if you will only be querying databases, and read/write if you plan to allow updating through ODBC.
(17) Install Windows Installer Version 1.1. This does not automatically come with Windows NT and its various service packs, but you will need it to install ActivePerl. You can obtain it from the ActiveState Web site.
(18) Install ActivePerl. Available from ActiveState's Web site http://www. activestate.com. You can also obtain the prerequisite Windows Installer Version 1.1. from this site. The installation proceeds quickly and automatically. Once installed, you should be able to issue the following command from a command prompt and see these results:
Marshall Breeding
The author
Marshall Breeding is Library Technology Officer at Vanderbilt University, Nashville, Tennessee, USA.
Copyright MCB UP Limited (MCB) 2000