A tutorial in Creating Web-enabled databases with Inmagic DB/TextWorks through ODBC

Breeding, Marshall

Abstract

Learn to create Web-enabled databases through this step-by-step tutorial. The combination of Inmagic's DB/TextWorks, ODBC Drivers, and Perl operating on in 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 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, search-and-retrieval engines, and other features inherent in databases to provide more sophisticated access to content. Web-enabled databases also separate raw information from it's 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 Web-enabled 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 on the DB/Text WebPublisher product, I have now 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 product for traditional library management applications such as OPAC, serials control, and acquisitions, but also find it to be an excellent tool for managing full-text information. DB/TextWorks has proven to be particularly adept at providing access to textual information, has excellent search and retrieval abilities, and has a relatively easy 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 possible 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 that possible with 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: staffweb.library.vanderbilt.edu/breeding/inmagic.html) Yet in the process of creating these applications, I have run into several limitations that have prevented me from creating some features I wanted.

One of the reasons for these limitations lies in the way that DB/Text Web Publisher takes full control of the head of an HTML document. This precludes the ability to use Cascading Style Sheets, which is an important aspect of creating 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 have been superceded and that may not work consistently among the different Web browsers. It is usually the case that the Web database needs to fit within a context of a larger set of Web pages. If the overall Web site makes use of style sheets, it is quite a limitation to not be able to take advantage of these style sheets for the presentation of information from the database. As we at Vanderbilt recently updated our Web pages, we used a style sheet to create a common look of the new Web site. It then became critical to be able to make the database-driven pages to also link in the style sheet. This was not possible with DB/Text Web publisher, making it necessary to find another approach.

The Web pages generated by DB/Text Web Publisher make extensive use of JavaScript. The navigational buttons and other features of these pages will not function with browsers without JavaScript capability.

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 an simple approach for publishing a database on the Web for those without much knowledge of HTML. But once one gets involved in creating specific HTML commands that go before and after each field, the graphical user interface of DB/Text Web Publisher seems to be more in the way than it is of assistance. There comes a point a pure programming approach offers more flexibility and sophistication than can be accomplished in a graphical environment.

For Web developers that want to have complete control over the creation of a Web-enabled database and are competent with a programming language, Inmagic offers a utility that allows access to a DB/TextWorks database through the Open Database Connectivity model, or ODBC. These Inmagic ODBC drivers allow DB/TextWorks to be accessed through any programming language that has ODBC support.

An Introduction to ODBC and SQL

Open Database Connectivity model, or ODBC, was created by Microsoft Corporation as a method to access information from a database in a standard way. ODBC allows information to be easily shared from one database to another. It also allows the same program code to access information from different database applications, so long as each has an ODBC driver available.

The standard approach to providing access to information through ODBC on the Web would involve making a connection to the database through ODBC, selecting records through Structured Query Language (SQL), and then using the Common Gateway Interface (CGI) to insert any needed HTML coding around each data element to display the results through the user's Web browser. Through ODBC, applications can be created that select records from a database through a query, retrieve the contents of records, sort results, or even update records or add new ones.

SQL has become a very common approach for working with databases. Through ODBC, non-SQL languages such as DB/TextWorks can be accessed through this standard. DB/TextWorks is organized into textbases, each textbase has records with multiple fields, and that fields can have multiple variable-length entries. It allows arbitrarily long fields, and relies heavily on the "Contains" search operator. SQL treats databases much 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 must reshape itself into the SQL mold. Its field types must be mapped into the pre-defined SQL data types, and its operators can be incorporated into SQL syntax.

ODBC is an applications programming interface (API) that allows different kinds of software work together through a common communications layer. Since Inmagic offers an ODBC driver for DB/TextWorks and there is an ODBC interface available for Perl, then it is possible for these two applications 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 applications, 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 that will work with these diverse systems. Perl is used very commonly for creating CGI (Common Gateway Interface) applications. Processing information submitted from Web forms is a task that is easily handled through a Perl script. 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. As 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.

The version of Perl that I have use with DB/TextWorks is ActivePerl version 5.6.0 for Windows NT from ActiveState Tool Corporation of Vancouver Canada. ActiveState offers its version of Perl as freeware. It sells complete application development tools for those that require a more sophisticated environment than that available in the free version. ActivePerl is one of the few implementations of Perl that operates under Windows NT. In addition to the Windows NT version, ActivePerl is available for Linux and Solaris. For more information about ActivePerl see www.activestate.com.

DB/TextWorks: A Windows NT Based system

One of the main considerations involved in choosing components to compliment DB/TextWorks is that Inmagic offers this product only for Windows NT. It does not operate under any flavor of Unix. Therefore, the other components must also operate under Windows NT.

In order to create a complete Web publishing environment with DB/TextWorks through ODBC, several components need to be installed. These components include:

Microsoft Windows NT Server V.4 (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 personally have not tested it with this operating system.

Preparing the Server

While creating the server environment for ODBC access to DB/TextWorks is not especially complex, 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 interface with the Inmagic ODBC driver.

Inmagic offers no direct support for this environment. While Inmagic sells and documents the ODBC Driver, it does not include support for how to develop programs in this environment. Inmagic will 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 the specific set of installation instructions that have proven to work in my environment. Having just rebuilt an NT Server from scratch, these steps have been recently tested to be successful. Other approaches to installing and configuring an NT server will likely work equally well.

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.
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.
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. Do not install the gopher server. You'll just have to remove it later. I performed this 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.
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 need to restart the server before going on to the next step.
Install Service Pack 4. I installed this upgrade from the CD mentioned above. You will need to restart the server before going on to the next step.
Install Service Pack 6a. At press time, 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/sp6/). You will need to restart Windows NT yet again, but you will then have it configured with the latest version of all the Microsoft-supplied components.
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.
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 witch DB/TextWorks resides.
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.
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 is used by default by IIS.
Using the Microsoft Management Console create a Virtual Directory called /cgi-bin/ linked to the physical directory just created.
Set this directory to allow execution of programs, 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

Start IIS if it is not already running through the Microsoft Management Console.
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.
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. Figure 3 and Figure 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

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.
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.
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:

c:\>perl -v This is perl, v5.6.0 built for MSWin32-x86-multi-thread (with 1 registered patch, see perl -V for more detail) Copyright 1987-2000, Larry Wall Binary build 613 provided by ActiveState Tool Corp. http://www.ActiveState.com Built 12:36:25 Mar 24 2000 Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5.0 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page.

Install Win32::ODBC Module. This is the interface that allows Perl programs to communicate with ODBC data sources. While ActiveState comes with some ODBC support, you will need to obtain the latest Beta version from Roth Consulting. I tried several versions, and it is the only one that worked with the Inmagic ODBC Drivers.
- Go to the Roth Consulting Web site (http://www.roth.net/perl/odbc/)
- Select the link to go to the "Perl Package Repository" page
- Right click on the Win32:ODBC Beta link
- Choose option to "Save Target As..." and save the Win32-ODBCBETA.ppd file in a temporary directory
- Open a command window and change directories to the C:\perl\bin directory
- ssue the command:
  E:\Perl\bin>ppm install c:\temp\win32-ODBCBETA.ppd
- Verify installation:
  PPM> query ODBC* Win32-ODBCbeta [0.1999.12.21] The Win32::ODBC BETA extension for Win32 X86. Interface for ODBC databases.

If you have success with each of these steps, then your server environment is complete. It should take a hour or so to complete all 20 steps, providing that you do not run into complications. You are now ready to start building your applications.

You will also need to create and populate a DB/TextWorks database. While you can use ODBC and Perl to create a database and load information into it, I recommend that you start off with a database that you have created through the Windows-based DB/TextWorks interface and either key-in or import at least a starter set of records.

Building the Application

Once all the prerequisite software modules have been installed, you are finally ready to start writing the HTML forms and the programs in Perl needed to create a Web-enabled database. Let's assume that you have already created and populated the database in Inmagic DB/TextWorks. For the purposes of this article, I will use examples taken from my "lib-web-cats" database. See http://staffweb.library.vanderbilt.edu/breeding/libwebcats.html to see the full version of this application. The examples that I show in this article are simplified versions of the programs that power the live version.

Enabling the database for ODBC

In order for the database to be used with ODBC, it must reside in the directory you specified when you created the SystemDSN as you configured the Inmagic ODBC driver.

One step that you will need to take to use this database with the ODBC approach will be to create an INI file. This file tells the ODBC driver the fields that are in the database and describes how to treat them in SQL queries. The data types available in DB/TextWorks are slightly different than those of other relational databases and some mapping is needed to tell the ODBC driver how to treat each field. To create this INI file, one must first dump the contents of the database to a file through the Export utility. From the File menu choose the Export option, give it a file name, and then export all the records as "Inmagic Tagged Format." You will then need to Execute the Inmagic DBStats program. When you launch DBStats, as illustrated by Figure 5 you will need to browse to the name of the file with the database dump you just created. Click on the "Display Report" button and it will analyze the file and open a window that shows information about the database. Click on the "Modify INI..." button and it will prompt you to enter a name in which to save the file. The name of the INI file must correspond to the name of the database. You normally will not need to make further modifications of this INI file, but in some cases you will need to change the field types to get certain types of SQL queries to operate correctly.

HTML Query Form

You will next need to build a query form. This form will prompt the user for the information that is to be retrieved from the database. The HTML form passes its request to the Perl script that in turn passes the request to the ODBC driver, which in turn communicates with the database.

Figure 6 shows our sample HTML query form. It is basically a stripped down version of the lib-web-cats basic search page.

And is based on the following HTML coding:

lib-web-cats: A directory of libraries and online catalogs on the Web [by Marshall Breeding] lib-web-cats:
library Web pages, online catalogs, and profiles.
a directory of over 5,000 libraries worldwide.

Enter the name of the institution associated with the library:

(hint: for public libraries, enter city or county)

The part of the HTML document that of interest are the ones that define the form itself. Looking only at the form, we are left with:

institution:

The

statement specifies that the form will deliver its output to the server lib10.library.vanderbilt.edu, which has its Web server running on TCP port 8080. The script that it expects to handle its output is named "lwc-simple.pl" and is in a virtual directory called cgi-bin. As noted above, this directory must be configured to allow the execution of programs. For security reasons it should be read only and not allow browsing of the file names. The text that will be used to search the database specified by the input value called "searchstring." The form closes with the usual "Submit" and "Reset" buttons.

When the user enters a string such as "University of California" and presses "Submit" the following string is passed to the script:

http://lib10.library.vanderbilt.edu:8080/cgi-bin/lwc-simple.pl? searchstring=University+of+California

Our goal is to produce a listing of libraries, that will include the name of its associated institution, the library's name, its geographic location, and a link to its online catalog. If all works correctly, the results should appear as shown in Figure 7.

The Perl Script

The script for our example is designed to accept the input from the form, parse it according to the standard Common Gateway Interface (CGI) conventions, send an SQL command to the ODBC interface, and then present a list of results. In this example we will be searching the lib-web-cats database by both institutional and library name, and will present a listing that includes the library name, the URL for the library's Web page, the city, state, and country of the library, its online catalog's URL, and the type of library automation system it uses.

The Perl script itself is as follows:

# Created December 7, 2000 by Marshall Breeding # Last modified December 7, 2000 # # first we need to accept the raw string from the form $temp=$ENV{'QUERY_STRING'}; # get the ip address while we're at it # Unpack the contents into an asociative array. # We've got to change the plus signs back into spaces and # deal with the control charactors. @pairs=split(/&/,$temp); foreach $item(@pairs) { ($key,$content)=split (/=/,$item,2); $content=~tr/+/ /; $content=~ s/%(..)/pack("c",hex($1))/ge; $fields{$key}=$content; } print "Content-type: text/html\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "lib-web-cats: Search Results\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; use Win32::ODBC; my($db) = new Win32::ODBC('MMBDATA'); $SqlStatement="SELECT * FROM libwebcats WHERE (Institution CT '$fields{'searchstring'}') or (LibraryName CT '$fields{'searchstring'}') ORDER BY Institution"; $searchdisplay = "the Library name or its institution is $fields{'searchstring'}"; if ($db->Sql($SqlStatement)) { print "SQL failed.
\n"; print "Error: " . $db->Error() . "
\n"; print "SQL Statement was: $SqlStatement
\n\n"; $db->Close(); exit; } print "
lib-web-cats
\n\n"; print "\n\n"; print "
\n\n"; while ($db->FetchRow()) { %data = $db->DataHash(); print "$data{'Institution'}"; if (length($data{'LibraryWeb'}) > 0) { print " $data{'LibraryName'}"; } else { print " $data{'LibraryName'}"; } print " ("; if (length($data{'City'}) > 0) { print " $data{'City'},"; } if (length($data{'State'}) > 0) { print " $data{'State'}"; } if (length($data{'Country'}) > 0) { print " $data{'Country'}"; } print ")\n\n"; if (length($data{'OnlineCatalog'}) > 0) { print " OPAC\n\n"; } if (length($data{'ILS'}) > 0) { print " [$data{'ILS'}]\n\n"; } print "
\n"; } $db->Close(); print "\n\n\n\n";

Cleaning up Errors

If you are more careful, talented, and lucky, than I am your script will work correctly the first time, and you will see the results returned on your Web browser when you type in a query string and press the submit button. You may, unfortunately have errors in your script. Something like the following indicates that you made a simple syntax error in your Perl script (in this case, not ending a statement properly):

CGI Error The specified CGI application misbehaved by not returning a complete set of HTTP headers. The headers it did return are: syntax error at C:\InetPub\cgi-bin\docdisplay.pl line 51, near "print" BEGIN not safe after errors--compilation aborted at C:\InetPub\cgi-bin\docdisplay.pl line 54.

You may see something like the following, which indicates a problem with the SQL statement itself:

SQL failed. Error: [0] [145849520] "[Inmagic, Inc.][DB/Text ODBC Driver] Expected lexical element not found: " [37000]

Analyzing the script

It is not as complicated as it looks. We will consider the script one section at a time. First consider the segment:

$temp=$ENV{'QUERY_STRING'}; # get the ip address while we're at it # Unpack the contents into an asociative array. # We've got to change the plus signs back into spaces and # deal with the control charactors. @pairs=split(/&/,$temp); foreach $item(@pairs) { ($key,$content)=split (/=/,$item,2); $content=~tr/+/ /; $content=~ s/%(..)/pack("c",hex($1))/ge; $fields{$key}=$content; }

This part of the Perl script is a fairly standard approach for simply transferring the data passed from the HTML form into the program. The data is held in an environment called QUERY_STRING, which is transferred into a variable called $temp. This part of the script separates the data into value pairs, converts the plus signs back into spaces, and unpacks any special characters. As a result of this part of the script, each of the values passed to the script can be accessed later on through variables such as $fields{'searchstring'}. If you plan to do further CGI programming, study this segment carefully since it is one of its basic building blocks of this approach.

print "Content-type: text/html\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "lib-web-cats: Search Results\n\n"; print "\n\n"; print "\n\n"; print "\n\n";

The print statements above simply start writing HTML code to the user's Web browser. The first statement is critical. Without this line, the browser will not recognize the script's output as HTML code and will prompt the user for an application to use to view the code. The second line specifies the version of HTML to use. While not essential, it is considered good style to indicate what version of HTML with which the page can be validated. Next, we see the usual , , ,and <body> tags. Note also the <link rel=stylesheet …> directive, which connects the Web page generated by the script with an external stylesheet. Most of the formatting characteristics can now be determined by the stylesheet and we will not have to use explicit formatting coding as we build the HTML page in our scripts.</p> <table border><tr><td> <blockquote><b><pre> use Win32::ODBC; my($db) = new Win32::ODBC('MMBDATA'); </pre></b></blockquote> </td></tr></table> <p>Now we begin to delve into the part of the script that invokes the ODBC components. The "use Win32::ODBC" simply tells the Perl script to go ahead and link in the ODBC module. The second line of this fragment opens a connection to the SystemDSN named "MMBDATA" that we defined when we installed the ODBC driver. Remember that this SystemDSN corresponds to the directory where our textbases reside.</p> <table border><tr><td> <blockquote><b><pre> $SqlStatement="SELECT * FROM libwebcats WHERE (Institution CT '$fields{'searchstring'}') or (LibraryName CT '$fields{'searchstring'}') ORDER BY Institution"; $searchdisplay = "the Library name or its institution is <b>$fields{'searchstring'}</b>"; </pre></b></blockquote> </td></tr></table> <p>The next task involves creating an SQL statement that will be issued to the textbase through ODBC. The text of the SQL statement will be stored in a variable called $SqlStatement for use later. SELECT is one of the most basic SQL commands. The asterisk is an indication to get all the data from each row that matches the query. "FROM libwebcats" indicates the table or textbase that will be used for the query. After WHERE we see the criteria for the search. The $fields{'searchstring'} is the text passed from the HTML Form that for which we will be searching. The command specifies that the search string can be in either the Institution or the LibraryName columns of the table. The "CT" operator is special to the Inmagic ODBC environment. The DB/TextWorks search engine uses the CT operator in its native query language. One can use the standard SQL "=" when specifying a query, it will search through the textbase sequentially, and will take longer to process. The "CT" operator makes use of the DB/TextWorks indexes, resulting in significantly faster response to queries. The ORDER BY part of the SQL command tells ODBC how to sort the results of the query before they are presented to the user.</p> <table border><tr><td> <blockquote><b><pre> if ($db->Sql($SqlStatement)) { print "<font face=\"arial\" color=\"red\"><b>SQL failed.</b><br>\n"; print "Error: " . $db->Error() . "<br>\n"; print "SQL Statement was: $SqlStatement<br></font>\n\n"; $db->Close(); exit; } </pre></b></blockquote> </td></tr></table> <p>The fragment shown above represents the commands to execute the SQL statement formed earlier. If the command is successful, it passes the results into the $db variable. If it encounters an error, the three print statements present an error message to the user and the "$db->Close();" closes the database connection, and the script terminates.</p> <table border><tr><td> <blockquote><b><pre> while ($db->FetchRow()) { %data = $db->DataHash(); print "<b>$data{'Institution'}</b>"; if (length($$data{'LibraryWeb'}) > 0) { print " <a href=\"$data{'LibraryWeb'}\">$data{'LibraryName'}</a>"; } else { print " $$data{'LibraryName'}"; } print " ("; if (length($data{'City'}) > 0) { print " $data{'City'},"; } if (length($data{'State'}) > 0) { print " $data{'State'}"; } if (length($data{'Country'}) > 0) { print " $data{'Country'}"; } print ")\n\n"; if (length($data{'OnlineCatalog'}) > 0) { print " <a href=\"$data{'OnlineCatalog'}\">OPAC</a>\n\n"; } if (length($data{'ILS'}) > 0) { print " <font color=\"maroon\">[$data{'ILS'}]</font>\n\n"; } } </pre></b></blockquote> </td></tr></table> <p>We are now ready to start requesting results from the textbase and presenting data to the user. while The "($db->FetchRow())" command gets one row from the textbase returned by our SQL statement. The "while" statement tells the program to loop until all the results have been retrieved. The "%data = $db->DataHash();" stashes the information from each table row into convenient variables that correspond to the field names in DB/TextWorks. For each record, the content of each field is represented by $data{'fieldname'}. For required fields, its contents can be presented, surrounded with any HTML coding necessary to identify and spruce up the text. For fields that may or may not contain any information, it is necessary to check for a non-zero length. In most cases you want to completely omit blank fields rather than presenting a label for the field with no content. Note especially the following fragment:</p> <table border><tr><td> <blockquote><b><pre> if (length($$data{'LibraryWeb'}) > 0) { print " <a href=\"$data{'LibraryWeb'}\">", $data{'LibraryName'}</a>"; } else { print " $$data{'LibraryName'}"; } </pre></b></blockquote> </td></tr></table> <p>Study of these lines of code will reveal that it tests for information the field LibraryWeb, which will be the URL of the library's web page. The field LibraryName is required, so we can assume that it is populated. If the database contains a URL, it builds an active link from the library's name, otherwise the library name is presented as regular text. </p> <table border><tr><td> <blockquote><b><pre> $db->Close(); print "</body>\n\n</html>\n\n"; </pre></b></blockquote> </td></tr></table> <p>Finally, once the results have all been processed, we close the database with the command "$db->Close();" and finish the web page with the usual </body></html> coding. </p> <p>Now that we have created a simple listing, we can now start adding additional features to our application. We might, for example, want to provide a link that will display additional information about each library. This would be accomplished by adding coding in our Perl script that will create an active link that invokes our script with additional parameters, and add another section to the script for presenting this full-record display. The following Perl fragment will be added to our script at the end of the section that processes each row fetched from the table:</p> <table border><tr><td> <blockquote><b><pre> print " <a href=\"http://lib10.library.vanderbilt.edu:8080/cgi-bin/lwcsearch.pl? searchstring=$rowdata[$i]{'RecordNumber'}&action=detail\" ", " onMouseOver =\"self.status=\'View detailed information", " about this library\'; return true\"", " onClick =\"self.status=\'View detailed information ", " about this library\'; return true\"", " onMouseOut =\"self.status=\' \'; return true\"", ">Details</a>.<br>\n\n"; </pre></b></blockquote> </td></tr></table> <p>The following new section will create the detailed display that presents all the information available for the selected record:</p> <table border><tr><td> <blockquote><b><pre> if ($fields{'action'} eq "detail") { $RecordNumber=$fields{'searchstring'}; $SqlStatement="SELECT * FROM libwebcats WHERE RecordNumber = '$RecordNumber'"; if ($db->Sql($SqlStatement)) { print "<font face=\"arial\" color=\"red\"><b>SQL failed.</b><br>\n"; print "Error: " . $db->Error() . "</font><br>\n"; $db->Close(); exit; } while ($db->FetchRow()) { my(%data) = $db->DataHash(); print "<h1> $data{'Institution'}</h1>\n\n"; print "<h2> $data{'LibraryName'}</h2>\n\n"; print "<hr color=\"maroon\">Detailed information from <b>lib-web-cats</b>:", " <hr color=\"maroon\">\n\n"; print "<table cellspacing=5>\n\n"; print "<tr><td width=30%><b>Postal Address / location:</b></td><td>"; if (length ($data{'Address'}) > 0 ){ print "$data{'Address'}"; } if (length ($data{'City'}) > 0 ){ print " $data{'City'}, "; } if (length ($data{'State'}) > 0 ){ print "$data{'State'}"; } if (length ($data{'Country'}) > 0 ){ print " $data{'Country'}"; } print "</td></tr>\n\n"; print "<tr><td><b>Library Type& #60;/b></td><td> $data{'Type'}<td></tr>\n\n"; if (length($data{'LibraryWeb'}) > 0 ){ print "<tr><td><b>Library Web Page: </b></td><td>", "<a href=\"$goto$data{'LibraryWeb'}", "&RC=$data{'RecordNumber'}\"", " onMouseOver =\"self.status=\'$data{'LibraryWeb'}\'; return true\"", " onClick =\"self.status=\'$data{'LibraryWeb'}\'; return true\"", " onMouseOut =\"self.status=\' \'; return true\"", ">$data{'LibraryWeb'}</a>", "</td></tr>\n\n"; } if (length($data{'OnlineCatalog'}) > 0 ){ print "<tr><td><b>Link to Online Catalog: </b></td><td>", "<a href=\"$goto$data{'OnlineCatalog'}", "&RC=$data{'RecordNumber'}\"", " onMouseOver =\"self.status=\'$data{'OnlineCatalog'}\'; return true\"", " onClick =\"self.status=\'$data{'OnlineCatalog'}\'; return true\"", " onMouseOut =\"self.status=\' \'; return true\"", ">$data{'OnlineCatalog'}</a>", "</td></tr>\n\n"; } if (length ($data{'ILS'}) > 0 ) { print "<tr><td><b>Library Automation System: </b></td><td>$data{'ILS'}"; if (length ($data{'Vendor'}) > 0) { print " from $data{'Vendor'}"; } if (length ($data{'InstallDate'}) > 0) { print ", implemented in $data{'InstallDate'}"; } print ".</td></tr>"; } if (length ($data{'PreviousILS'}) > 0 ) { print "<tr><td><b>Previous Library Automation System: </b></td ><td>$data{'PreviousILS'}"; } if (length ($data{'PrevInstallDate'}) > 0) { print ", implemented in $data{'PrevInstallDate'}"; } print ".</td></tr>"; } if (length ($data{'CollectionSize'}) > 0 ) { print "<tr><td> <b>Number of Volumes in Collection: </b></td ><td>$data{'CollectionSize'}"; } if (length ($data{'AnnualCirculation'}) > 0 ) { print "<tr><td><b >Annual circulation transactions: </b></td ><td>$data{'AnnualCirculation'}"; } if (length ($data{'OCLC'}) > 0 ) { print "<tr><td> <b>OCLC Symbol: </b></td> <td>$data{'OCLC'}"; } if (length ($data{'Affiliation'}) > 0 ) { print "<tr><td> <b>Affiliations: </b></td ><td>$data{'Affiliation'}"; } if (length ($data{'Branches'}) > 0 ) { print "<tr><td valign=\"top\"><b>Branches: </b></td> <td>$data{'Branches'}"; } if (length ($data{'Notes'}) > 0 ) { print "<tr><td><b>Additional information: </b></td> <td>$data{'Notes'}"; } print "<tr><td><b >Listing added: </b ></td><td>$data{'RecordCreated'} </td></tr>\n\n"; print "<tr><td><b>Last Updated: </b ></td><td>$data{'RecordUpdated'} </td></tr>\n\n"; print "<tr><td><b>LWC Record Number: </b ></td> <td>$data{'RecordNumber'} </td></tr>\n\n"; if (length ($data{'Counter'}) > 0 ){ print "<tr><td> <b><nobr> Times Accessed through lib-web-cats: </nobr></b>", "</td><td>$data{'Counter'} </td></tr>\n\n"; } else { print "<tr><td><b> <nobr>Times Accessed through lib-web-cats: </nobr></b>", "</td><td>0 </td></tr>\n\n"; } print "</table><hr color=\"maroon\">\n\n"; print "Maintained by <a href=\"http://staffweb.library.vanderbilt.edu/breeding/\">", "Marshall Breeding</a>. Please report any errors or broken links.\n\n"; } $db->Close(); } </pre></b></blockquote> </td></tr></table> <p>The results of this script are illustrated by <a href="http://staffweb.library.vanderbilt.edu/breeding/ltgimages/LWC-details-results.gif" target="_blank">figure 8</a>. This represents a very common approach to Web-based databases where the results of a query are first listed in a brief form, giving the user just enough information to identify interesting items. From this brief listing, links or buttons are provided that invoke a detailed view of a single record.</p> <p>Another feature that we might add would involve keeping track of the number of times any given library is accessed through our interface. To make this happen we will add a field to the database record structure that will be used as an access counter. Consider the following code fragment:</p> <table border><tr><td> <blockquote><b><pre> $goto="http://lib10.library.vanderbilt.edu:8080/cgi-bin/go.pl?URL="; if (length($data{'LibraryWeb'}) > 0 ){ print "<tr><td><b> Library Web Page:</b></td><td>", "<a href=\"$goto$data{'LibraryWeb'}", "&RC=$data{'RecordNumber'}\"", " onMouseOver =\"self.status=\'$data{'LibraryWeb'}\'; return true\"", " onClick =\"self.status=\'$data{'LibraryWeb'}\'; return true\"", " onMouseOut =\"self.status=\' \'; return true\"", ">$data{'LibraryWeb'}</a>", "</td></tr>\n\n"; } </pre></b></blockquote> </td></tr></table> <p>You will see that we are creating a link to the URL of the library's web site. But rather than going directly to the URL, we are adding coding that takes the user to another script, represented by the variable $goto, which prepends the URL with the string "http://lib10.library.vanderbilt.edu:8080/cgi-bin/go.pl?URL=". We are making this action less obvious to the user by adding some Javascript coding that causes the native URL to display on the browser's status line when the cursor moves over it rather than the expanded URL that includes the supplemental script.</p> <p>Let's also look at the script that increments the counter in each library's record each time it is accessed through lib-web-cats. The script, called go.pl is as follows:</p> <table border><tr><td> <blockquote><b><pre> @pairs=split(/&/,$temp); foreach $item(@pairs) { ($key,$content)=split (/=/,$item,2); $content=~tr/+/ /; $content=~ s/%(..)/pack("c",hex($1))/ge; $fields{$key}=$content; } # Print the header of the HTML to be returned to the Browser print "Location: $fields{'URL'}\n\n"; use Win32::ODBC; my($db) = new Win32::ODBC('MMBDATA'); $SqlStatement="SELECT * FROM libwebcats WHERE RecordNumber = \'$fields{'RC'}\'"; if ($db->Sql($SqlStatement)) { print "Content-type: text/html\n\n"; print "<font face=\"arial\" color=\"red\"> <b>SQL failed.</b> <br>\n"; print "Error: " . $db->Error() . "</font><br>\n"; $db->Close(); exit; } while ($db->FetchRow()) { my(%data) = $db->DataHash(); if (length($data{'Counter'}) > 0) { $counternow=$data{'Counter'}; } else { $counternow=0; } } $newcounter = $counternow+1; $SqlStatement="UPDATE libwebcats SET Counter = $newcounter WHERE RecordNumber = '$fields{'RC'}'"; if ($db->Sql($SqlStatement)) { print "Content-type: text/html\n\n"; print "<br><font color=maroon size=-1><b> SQL request failed.</b><br>\n"; print "<b>Error:</b> " . $db->Error() . "<br>\n\n"; print "<b>SqlStatement:</b> $SqlStatement</font><br>\n\n"; $db->Close(); exit; } $db->Close(); </pre></b></blockquote> </td></tr></table> <p>The only information passed to the script is the record number of the row in the table that corresponds to the library and the URL for the library. You will see ODBC coding similar to that used in our main script. In this case, we fetch a single record from the table, based on a unique record key. The program examines the field that holds the counter. If this library has never been accessed before through lib-web-cats, this field will be blank, and we will assume that the current value of the counter to be zero. The program then increments the value of the counter by one, and then performs another SQL command to update the counter field in the record, replacing the original value with the incremented value. Notice that $SqlStatement uses UPDATE instead of SELECT and that it uses the SET directive to write information to the database. Also note that the directory in which the textbase resides will need to have its NTFS access permissions set to allow write access by the anonymous Internet user account. </p> <p>The main work of the script is to take the user to the URL of the library, which is accomplished through the one line:</p> <table border><tr><td> <blockquote><b><pre> print "Location: $fields{'URL'}\n\n"; </pre></b></blockquote> </td></tr></table> <p>This code is the standard way to perform redirection in Perl from one URL to another. The "Location:" qualification indicates that the text is to be written to the Address or URL window of the browser and not presented as text within the browser. It is essential that this be the first and only text written to the browser by the script. Redirects are the one function where you should not use "print "Content-type: text/html\n\n"" as the first text passed to the browser.</p> <p>This concludes our tutorial on using Perl, ODBC, and DB/TextWorks to create a dynamic Web environment. I hope that I have been successful in providing both conceptual explanations and practical examples. Good luck in creating your own creative applications!</p> <h2>Pricing and Packaging of the Inmagic ODBC Driver</h2> <p>Inmagic includes the ODBC Driver that we have been discussing as part of their "Interactive Module" suite of applications. It is not possible to purchase the ODBC driver separately. This Interactive Module also includes the DB/Text Power Pack and DB/Text Web form. This package, with a list price of $5,000 is not inexpensive. </p> <p>The PowerPack includes utilities that allow you to update and manage DB/TextWorks databases completely through Web interfaces. With these utilities, it is possible to eliminate the need to provide access to the DB/TextWorks Windows software to those that will need to maintain databases. Adding and updating records in a DB/TextWorks database can be accomplished through a Web browser. While more work is involved in creating and customizing the Web interfaces involved, it avoids the cost of purchasing additional licenses of DB/TextWorks and makes it easier to distribute responsibility for updating a database across a large group of users. </p> <p>The DB/Text PowerPack approach differs significantly from the ODBC-based method described in this article. You create an HTML form that prompts a user for the information and you write a script to convert this data to a file in delimited format. The script deposits the data file into a specified directory. The Importer module, which runs as an NT service, scans the directory every few seconds, and automatically imports the file into the database. The PowerPack approach involves a considerable amount of work to set up, but the amount of programming involved is limited compared to the ODBC approach. </p> <p>I find the choices that Inmagic made on how to package and price their ODBC Drivers to be very frustrating. It is not possible to obtain the ODBC Drivers without purchasing the entire Interactive Module package. Yet, if you have the ODBC Drivers you do not need any of the other components of the Interactive Module package. Their use is largely mutually exclusive. The HTML forms created with WebForm will not work with the ODBC approach, and the ODBC drivers allow you to add and update records in DB/TextWorks in a more efficient way than the Importer service of PowerPack. The high price of the Interactive Module is a very steep amount if all you need is the ODBC Drivers. I encourage Inmagic to reconsider their policy on not selling the ODBC Drivers separately. </p> <h2>Conclusion</h2> <p>In general, I am satisfied with DB/TextWorks, Perl, and ODBC as an effective environment for constructing Web database applications. DB/TextWorks offers a number of search and retrieval features that are not available in a plain relational database. Having the DB/TextWorks indexes available through the ODBC Drivers offers a significant performance boost compared to an approach that requires a more sequential pass through the database to retrieve results. I find that this environment performs well, even under heavy volume of transactions. I continue to be impressed by the overall quality of software created by Inmagic. Their applications tend to be rich in features, fast in performance, yet lean in the computer resources they consume. I commend Inmagic for not allowing DB/TextWorks evolve into bloated and inefficient applications, as have many of their competitors. While the costs of their software is high, so is the quality. The Inmagic products work on a number of levels. Non-technical users will find the graphical interface of WebPublisher to be accessible, while those willing to take a more technical approach can develop more complex applications using methods such as those described in this article. </p> </div>  <table class="DetailsTable" > <tr><th>Permalink:</th><td> <a href="https://librarytechnology.org/document/8928/"><img src="https://librarytechnology.org/images/link.jpg" title="Persistent URL for this listing"></a> <a href="https://librarytechnology.org/document/8928/" itemprop="url">https://librarytechnology.org/document/8928/</a></td></tr> <tr><th>View Citation</th><td><input type="button" value="View Citation" onclick="newWindow('/repository/citation.pl?id=8928','window2')" style="font-size: 80%; font-family: sans-serif; font-weight: normal; background-color: #ccccff; text-align: left; padding-left: 10pt;" > </td></tr> <tr><th>Publication Year:</th><td>2001</td></tr> <tr><th>Type of Material:</th><td>Article</td></tr> <tr><th>Language</th><td><span itemprop="inLanguage"> English</span> </td></tr> <tr><th>Published in: </th><td><em> <a href="https://librarytechnology.org/repository/search.pl?Source=Library+Computing" title="Execute a Source search for Library Computing" >Library Computing</a><br> </em></td></tr> <tr><th>Publication Info:</th><td><a href="https://librarytechnology.org/repository/search.pl?Source=Library Computing&Volume=19&Number=01">Volume 19 Number 01</a></td></tr> <tr><th>Issue:</th><td>2001</td></tr> <tr><th>Publisher:</th><td>MCB University Press</td></tr> <tr><th>Place of Publication:</th><td>Bradford, England</td></tr> <tr><th>Company:</th><td> <a href="https://librarytechnology.org/repository/search.pl?Company=Inmagic,+Inc." title="Execute a Company search for Inmagic, Inc." >Inmagic, Inc.</a><br> </td></tr> <div itemscope="itemscope" itemtype="https://schema.org/Product"> <meta itemprop="name" content="DB/TextWorks" > </div>  <tr><th>Products:</th><td> <a href="https://librarytechnology.org/repository/search.pl?Product=DB/TextWorks" title="Execute a Product search for DB/TextWorks" >DB/TextWorks</a><br> </td></tr> <tr><th>Subject:</th><td> <a href="https://librarytechnology.org/repository/search.pl?Subject=Web-enabled+databases" title="Execute a Subject search for Web-enabled databases" >Web-enabled databases</a><br> <a href="https://librarytechnology.org/repository/search.pl?Subject=Perl" title="Execute a Subject search for Perl" >Perl</a><br> <a href="https://librarytechnology.org/repository/search.pl?Subject=ODBC" title="Execute a Subject search for ODBC" >ODBC</a><br> </td></tr> <tr><th>Record Number:</th><td>8928</td></tr> <tr><th>Last Update:</th><td>2023-12-05 23:52:22</td></tr> <tr><th>Date Created:</th><td>0000-00-00 00:00:00</td></tr> <tr><th>Views:</th><td>4332 </td></tr> </table> </div>  </div>  <table><tr><td> <div style="display: inline;"> <form style="display: inline; margin: 0;" action="https://librarytechnology.org/repository/" method="post" > <input type="hidden" name="SID" value="2023121098130832" > <input type="hidden" name="code" value="bib" > <label accesskey="s" for="search"> <input type="submit" name="submit" value="New Search" id="search" style="font-size: 80%; font-family: sans-serif; font-weight: normal; background-color: #ccccff; text-align: left; padding-left: 10pt;" > </label> </form> </div> </td><td> <div style="display: inline;"> <form style="display: inline; margin: 0;" action="https://librarytechnology.org/index.pl" method="post" > <input type="hidden" name="SID" value="2023121098130832" > <input type="hidden" name="code" value="bib" > <input type="submit" name="Home" value="Library Technology Guides" id="home" style="font-size: 80%; font-family: sans-serif; font-weight: normal; background-color: #ccccff; text-align: left; padding-left: 10pt;" > </form> </div> </td></tr></table> <hr> <table><tr><td> <div style="display: inline;"> <form style="display: inline; margin: 0;" action="https://librarytechnology.org/repository/" method="post" > <input type="hidden" name="SID" value="2023121098130832" > <input type="hidden" name="code" value="bib" > <label accesskey="s" for="search"> <input type="submit" name="submit" value="New Search" id="search" style="font-size: 80%; font-family: sans-serif; font-weight: normal; background-color: #ccccff; text-align: left; padding-left: 10pt;" > </label> </form> </div> </td><td> <div style="display: inline;"> <form style="display: inline; margin: 0;" action="https://librarytechnology.org/index.pl" method="post" > <input type="hidden" name="SID" value="2023121098130832" > <input type="hidden" name="code" value="bib" > <input type="submit" name="Home" value="Library Technology Guides" id="home" style="font-size: 80%; font-family: sans-serif; font-weight: normal; background-color: #ccccff; text-align: left; padding-left: 10pt;" > </form> </div> </td></tr></table> </div>  <div id="pagefooter"> <p>Maintained by <a href="https://librarytechnology.org/marshallbreeding/"><strong>Marshall Breeding</strong></a></p> <p>Copyright 1994-2022</p> </div>  </div>  </body> </html>

Library Technology Guides

Document Repository