European Central Crop Databases (ECCDB)

European Central Crop Databases (ECCDB) On-line Databases Training Workshop. 8-10 June 1997, Bonn, Germany. ECPGR & ZADI Options and approaches to providing on-line access to databases. Part III. FoxPro on-line databases on an institution server

Helmut Knüpffer
Genebank, Institut für Pflanzengenetik und Kulturpflanzenforschung (IPK)
D-06466 Gatersleben, Germany
http:\\www.ipk-gatersleben.de
knupffer@ipk-gatersleben.de
http:\\fox-serv.ipk-gatersleben.de

The Genebank of IPK has a local area network including about 20 PCs. This LAN is part of IPK’s LAN that is connected with the Internet by a Unix Server. However, there is no database management system available in IPK which would be suitable to build up WWW-searchable databases. Therefore, copies of some databases of IPK’s genebank were sent to ZADI in order to make them searchable via the Internet.

For a large institution like IPK it is desirable to have the possibility to prepare on-line searchable databases on its own server. Therefore, the genebank was very glad when the University of Leipzig sent a request to IPK to define topics for computer science students with specialization in databases for a 2 month practicum. The following topic was defined: "Providing Access to Passport Data of the Genebank in the Internet". The aim of this work was to make the existing local passport database on-line searchable. It was known that a "Visual FoxPro WWW Search Page Wizard" existed which enables users to easily prepare WWW-searchable versions of existing FoxPro databases.

Two computer science students from Leipzig, namely Uwe Neubert and Thomas Nowotka, were interested in this work. It was decided to include several related tasks. Since some of these tasks were novel for IPK, it could not be estimated in advance how far the students would be able to proceed in 2 months. It could be expected that they would encounter certain hardware or software constraints which would have to be overcome by acquiring new products. Therefore we requested that a detailed report be written at the end of the time, giving the problems still to be solved, and the work remaining to be done. If the goal of establishing an Internet-searchable database could be reached in the 2 months, the steps leading to the solution were to be documented in a way that would allow them to be adapted to create similar solutions for other IPK databases.

In detail, the work included the following steps:

analysis of existing hardware and software situation at IPK
literature study with respect to relevant topics
investigation of the possible integration of the existing Novell 4.1 network and of IPK’s Unix Server with respect to the creation of on-line searchable databases on the basis of existing FoxPro databases
development of different solutions
analysis of applicability of the "Visual FoxPro WWW Search Page Wizard" available as alpha version (Microsoft) from the Internet
selection of one preferred solution, and preparation of its realization, using a test database
definition of a test database with passport data (subset from the complete database)
realisation of a WWW-searchable sub-database with passport data
writing final report (see specifications above).

According to these specifications, a prototype of a searchable database was created, based on a PC-server under Windows 3.11 and Visual FoxPro 3.0b. It contains a home page (http:/fox-serv.ipk-gatersleben.de) giving contact details for the genebank and three menu entry points covering typical kinds of search requests: (1) for accession numbers; (2) for scientific names and/or countries of origin; (3) flexible search - combined search for up to four descriptors to be chosen from a total of 15 searchable descriptors, with the possibility of using wildcards for search strings. All pages contain the same information in German and English.

The output format of the database server was designed to be as compact as possible, giving the requested information from the database preceded by field explanations in German and English. Only those fields where information exists are displayed for a particular accession. Up to 33 accessions are shown on one return page. The total number of hits is shown on the top. On the bottom there is a window which allows one to select another portion of the search result. Depending on the predefined sorting order of output (by accession numbers or by scientific names), this window shows the range of the portions with respect to the sorting criteria.

Microsoft’s "Visual FoxPro WWW Search Page Wizard" appeared to be an alpha version with very limited usability. Large parts of it had to be re-programmed, and freed from bugs. It was necessary to develop novel solutions for communication between IPK’s Unix server and the PC database server under Windows 3.11. Errors in entering searches should not lead to an unforeseeable behaviour of the server. Some necessary compilers and programme components had to be sought in the Internet, downloaded and adapted.

During a second period of work, one month in February/March 1997, the performance of the server was greatly improved (Knüpffer et al. 1997). A "normal" search takes between 5 and 15 seconds now. The server system was transferred to Windows NT Server, in order to allow for simultaneous processing of several requests without errors. Some improvements were also implemented, based on feedback from both satisfied and dissatisfied users. A log file was built in to enable tracing of frequency of access and contents of requests (SQL statements generated from search requests). The server’s administrator is allowed to change some parameters, such as the number of hits shown per page, the timeout, or the maximum number of accessions in downloadable ZIP files. The transfer from Windows 3.11 to Windows NT required a compiler which was able to generate 32-bit scripts - such a shareware compiler was identified on the Internet. Both the 3.11 and the NT server exist in parallel. After electricity cuts, the 3.11 server loads up automatically (for the NT server it is necessary to enter the user password). Special attention was paid to safety aspects. Addresses, field names and other information that would enable an unauthorized user to enter the database in other ways than supposed, are hidden from the user. Therefore, it is not possible to establish links from external databases directly to accessions.

Other special features are:

correct and consistent handling of non-ASCII characters, e.g. a search for the string "urb" or "ürb" or "úrb" as part of the accession name would yield in names such as "Kürbis" (German: pumpkin), "Lasurblau" and "Hurbanovská". The German "ß" is handled in the same way as "ss".
possibility of downloading search results as a DBF file (compressed by PKZIP), with up to approx. 20,000 accessions downloadable from one search (a quarter of the whole database).

According to the log file, and according to the number of requests for seeds from the genebank referring to accession numbers found in the on-line database, the server is being used more and more frequently. This leads to a better accessibility and utilization of the genetic resources material. Some users informed us by fax when they found that the connection was down. Some users sent feedback about the performance and the appearance of the server - they ranged from enthusiastic to negative. It has to be taken into account that it is a pilot project which cannot easily be modified by people not having been involved in the development of the server.

In conclusion, the following can be stated. The work carried out at IPK by two very enthusiastic and skilled computer science students shows that it is possible to establish a PC-based on-line database, based on Visual Foxpro and Windows. It shows also that great flexibility and broad knowledge of different programming environments and skills are necessary to establish such a solution. In general, the work of students can be recommended to establish such pilot solutions. University programmes often require periods of practical work as parts of the study. It is necessary to circumscribe the scope of the work in a way that allows the work to be completed in reasonable time.
The response time of such a PC-based server is reasonably low. It is even possible to prepare downloadable ZIP files „on the fly". This is especially interesting when the search result is large, or has to be incorporated in other databases.
The on-line availability of passport data of the genebank collection led to increased requests for genebank material. IPK now belongs to the few genebanks worldwide with searchable databases.
Copies of the Reports about the WWW server (in German) are available in electronic form (WinWord 6.0 documents) from the author of the present contribution.

References (titles translated from German)

Knüpffer, H., T. Nowotka and U. Neubert, 1997: Searching FoxPro Databases via the Internet. Pilot Solution for the Passport Data of the Genebank of IPK Gatersleben. Final Report for the period 17 Feb. to 21 March 1997. IPK Gatersleben. 7 pp. Unpublished.
Neubert, U., T. Nowotka and H. Knüpffer, 1996: Installation and Operation Manual for the FoxPro WWW Database Server developed in the frame of the work „Providing Access to Passport Data of the Genebank in the Internet", 12 Aug. to 11 Oct. 1996. IPK Gatersleben. 15 pp. Unpublished.
owotka, T., U. Neubert and H. Knüpffer, 1996: Searching FoxPro Databases via the Internet. Pilot Solution for the Passport Data of the Genebank of IPK Gatersleben. Final Report for the period 12 Aug. to 11 Oct. 1996. IPK Gatersleben. 39 pp. Unpublished.