If you want details about how storage devices are performing on Redhat/Centos/Fedora use the vmstat and iostat commands.

After installing Centos 5.3 the iostat command is not available. To install it use yum:

# yum install sysstat

In this article Chris Mavin, explains and details how to use the Recital Database Server with the Open Source Servlet Container Apache Tomcat.

Overview

PHP has exploded on the Internet, but its not the only way to create web applications and dynamic websites. Using Java Servlets, JavaServer Pages and Apache Tomcat you can develop web applications in a more powerful full featured Object Oriented Language, that is easier to debug, maintain, and improve.

Tomcat Installation

There are a number of popular Java application servers such as IBM Web Sphere and BEA WebLogic but today we will be talking about the use of Apache Tomcat 5, the Open Source implementation of the Java Servlet and JavaServer Pages technologies developed at the Apache Software Foundation. The Tomcat Servlet engine is the official reference implementation for both the Servlet and JSP specifications, which are developed by Sun under the Java Community Process. What this means is that the Tomcat Server implements the Servlet and JSP specifications as well or better than most commercial application servers.

Apache Tomcat is available for free but offers many of the same features that commercially available Web application containers boast.

Tomcat 5 supports the latest Servlet and JSP specifications, Servlet 2.4, and JSP 2.0, along with features such as:

• Tomcat can run as a standalone webserver or a Servlet/JSP engine for other Web Servers.

• Multiple connectors - for enabling multiple protocol handlers to access the same Servlet engine.

• JNDI - The Java Naming and Domain Interface is supported.

• Realms - Databases of usernames and passwords that identify valid users of a web application.

• Virtual hosts - a single server can host applications for multiple domain names. You need to edit server.xml to configure virtual hosts.

• Valve chains.

• JDBC - Tomcat can be configured to use any JDBC driver.

• DBCP - Tomcat can use the Apache commons DBCP for connection pooling.

• Servlet reloading (Tomcat monitors any changes to the classes deployed within that web server.)

• HTTP functionality - Tomcat functions as a fully featured Web Server.

• JMX, JSP and Struts-based administration.

Tomcat Installation

In this next two sections we will walk through the install and setup of Tomcat for use with the Recital database server.

To download Tomcat visit the Apache Tomcat web site is at http://jakarta.apache.org/tomcat.
Follow the download links to the binary for the hardware and operating system you require.

For Tomcat to function fully you need a full Java Development Kit (JDK). If you intend to simply run pre compiled JavaServer pages you can do so using just the Java Runtime Environment(JRE).

The JDK 1.5 is the preferred Java install to work with Tomcat 5, although it is possible to run Tomcat 5 with JDK 1.4 but you will have to download and install the compat archive available from the Tomcat website.

For the purpose of this article we will be downloading and using Tomcat 5 for Linux and JDK 5.0, 
you can download the JDK at http://java.sun.com/javase/downloads/index.jsp.

Now we have the JDK, if the JAVA_HOME environment variable isn't set we need to set it to refer to the base JDK install directory.

Linux/Unix:

$ JAVA_HOME= /usr/lib/j2se/1.4/
$ EXPORT $JAVA_HOME

Windows NT/2000/XP:

Follow the following steps:

1. Open Control Panel.
2. Click the System icon.
3. Go to the Advanced tab.
4. Click the Environment Variables button.
5. Add the JAVA_HOME variable into the system environment variables.

The directory structure of a Tomcat installation comprises of the following:

/bin 			- Contains startup, shutdown and other scripts. 
	/common  	- Common classes that the container and web applications can use.
	/conf 		- Contains Tomcat XML configuration files XML files.
	/logs 		- Serlvet container and application logs.
	/server 		- Classes used only by the Container.
	/shared 		- Classes shared by all web application.
	/webapps 	- Directory containing the web applications.
	/work 		- Temporary directory for files and directories.

The important files that you should know about are the following:

• server.xml

The Tomcat Server main configuration file is the [tomcat install path]\conf\server.xml file. This file is mostly setup correctly for general use. It is within this file where you specify the port you wish to be running the server on. Later in this article I show you how to change the default port used from 8080 to port 80.

• web.xml

The web.xml file provides the configuration for your web applications. There are two locations where the web.xml file is used, 
web-inf\web.xml provides individual web application configurations and [tomcat install path]conf\web.xml contains the server wide configuration.

Setting up Tomcat for use

We'll start by changing the port that Tomcat will be listening on to 80.

To do this we need to edit [tomcat install path]/conf/server.xml and change the port attribute of the connector element from 8080 to 80.

After you have made the alteration the entry should read as:

<!-- Define a non-SSL HTTP/1.1 Connector on port 8080 -->
<Connector port="80" maxHttpHeaderSize="8192"

Next we want to turn on Servlet reloading, this will cause the web application to be recompiled each time it is accessed, allowing us to make changes to the files without having to worry about if the page is being recompiled or not.

To enable this you need to edit [tomcat install path]/conf/context.xml and change <Context> element to <Context reloadable="true">.

After you have made the alteration the entry should read as:

<Context reloadable="true">
<WatchedResource>WEB-INF/web.xml</WatchedResource>
</Context>

Next we want to enable the invoker Servlet.

The "invoker" Servlet executes anonymous Servlet classes that have not been defined in a web.xml file.  Traditionally, this Servlet is mapped to the URL pattern "/servlet/*", but you can map it to other patterns as well.  The extra path info portion of such a request must be the fully qualified class name of a Java class that implements Servlet, or the Servlet name of an existing Servlet definition.

To enable the invoker Servlet you need to edit the to [tomcat install path]/conf/web.xml and uncomment the Servlet and Servlet-mapping elements that map the invoker /servlet/*.

After you have made the alteration the entry should read as:

<servlet>
<servlet-name>invoker</servlet-name>
<servlet-class>org.apache.catalina.servlets.InvokerServlet</servlet-class>
<init-param>
<param-name>debug</param-name>
<param-value>0</param-value>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>

<servlet-mapping>
<servlet-name>invoker</servlet-name>
<url-pattern>/servlet/*</url-pattern>
</servlet-mapping>

If you are you not interested in setting up your own install of Tomcat there are prebuilt versions Tomcat that has all of the above changes already made, and has the test HTML, JSP, and Servlet files already bundled. Just unzip the file, set your JAVA_HOME

Next we will give Tomcat and your web applications access to the Recital JDBC driver.

For the purposes of this article we are going to install the Recital JDBC driver in the /[tomcat install path]/common/lib/ this gives Tomcat and your web applications access to the Recital JDBC driver. The driver can be installed in a number of places in the Tomcat tree, giving access to the driver to specific application or just to the web application and not the container. For more information refer to the Tomcat documentation.

Copy the recitalJDBC.jar which is located at /[recital install path]/drivers/recitalJDBC.jar to the /[tomcat install path]/common/lib/ directory.

Linux:

$cp /[recital install path]/drivers/recitalJDBC.jar /[tomcat install path]/common/lib/

Once you have completed all the steps detailed above, fire up the server using the script used by your platform's Tomcat installation.

Linux/Unix:

[tomcat install path]/bin/startup.sh

Windows:

[tomcat install path]/bin/startup

If you are having problems configuring your Tomcat Installation or would like more detail visit the online documentation a the Apache Tomcat site.

Example and Links

Now we have setup our Tomcat installation, lets get down to it with a JSP example which uses the Recital JDBC driver to access the demonstration database (southwind) shipped with the Recital Database Server.

The example provided below is a basic JDBC web application, where the user simply selects a supplier from the listbox and requests the products supplied by that supplier.

To run the example download and extract the tar archive or simple save each of the two jsp pages individually into /[tomcat install path]/webapps/ROOT/ on your server.

By enabling the invoker Servlet earlier we have removed the need to set the example up as a web application in the Tomcat configuration files.

You can now access the example web application at http://[Server Name]/supplier.jsp if the page doesn't display, check you have followed all the Tomcat installation steps detailed earlier in this article and then make sure both Tomcat and a licensed Recital UAS are running.

Downloads:
Archive: jspExample.tar

Right click and save as individual files and rename as .jsp files:
supplier.txt details.txt

Further Reading on JSP and JDBC can be found at http://www-128.ibm.com/developerworks/java/library/j-webdata/

Final Thoughts

Recital and Apache tomcat are a powerful combination, using Java Servlet technology you can separate application logic and the presentation extremely well. Tomcat, JSP, Java Servlets and the Recital database server form a robust platform independent, easily maintained and administered solution with which to unlock the power of your Recital, Foxpro, Foxbase, Clipper, RMS and C-SAM data.

Recital is a dynamic programming language particularly suited to the development of database applications. While Recital still supports standard procedural programming, new extensions to the language give you the power and flexibility of object-oriented programming. Object-oriented design and object-oriented programming represent a change in focus from standard procedural programming. This short primer will give you a good understanding of how to program object-oriented Recital.

This article examines the advantages of using databases and looks at how to create databases for new and existing applications.

Overview

file which contains information, including full path details, about all the files that belong to that particular database. Opening a database gives an application access to all that database's files - operating system and data dictionary protection and security permitting - in whichever directory on the system they reside and database commands can target the files as a single unit.

NOTE: The word 'database' has often been used in Xbase products to refer to an individual '.dbf' file. In this article these are referred to as 'tables' and a database may contain many tables.

Advantages

Recital has always offered developers the maximum in flexibility in the design and deployment of their applications. The SET DEFAULT and SET PATH commands along with the ability to access files via their full Operating System path name has allowed data and code to be created and stored in any required location. Such flexibility does however put the onus on the developer to manage all elements of the application and ensure that maintenance tasks cover all files. The introduction of the database commands retains the established developer-driven design, but provides functionality to facilitate the access and maintenance of the components of that design. The functionality offered is in three main areas:

• Facilitate data access
• Facilitate the storage and extraction of information about the files in an application
• Facilitate the maintenance of the files in an application

These three areas are looked at in more detail in the sections below.

Data Access

The immediate effect of using a database is that all the database's tables and associated files (indexes, dictionaries, memo files) are accessible with the issue of a single command, the OPEN DATABASE command.

// Open the database
> open database southwind
// List the database tables
> list tables

Tables in Database southwind:
 Name                Source
 categories		/usr/recital/data/southwind/categories.dbf
 customers		/usr/recital/data/southwind/customers.dbf
 employees		/usr/recital/data/southwind/employees.dbf
 example		/usr/recital/data/southwind/example.dbf
 order_details	/usr/recital/data/southwind/order_details.dbf
 orders			/usr/recital/data/southwind/orders.dbf
 productsbyname	/usr/recital/data/southwind/productsbyname.dbf
 products		/usr/recital/data/southwind/products.dbf
 shippers		/usr/recital/data/southwind/shippers.dbf
 suppliers		/usr/recital/data/southwind/suppliers.dbf
 cisamdemo		/usr/recital/data/southwind/cisamdemo.dbf

> use example

In the case of the sample southwind database that ships with Recital products on all platforms and is shown above, this effect can be achieved in other ways (SET PATH or SET DEFAULT), since its files all reside in the southwind directory. The database advantage comes when the database catalog contains files from multiple directories, e.g.

// Open the database
> open database myapp
// List the database tables
> list tables

Tables in Database myapp:
  Name                Source
  zipcodes		/usr/myapp/data/lookups/zipcodes.dbf
  customers		/usr/myapp/data/current/customers.dbf
  archive03		/usr/myapp/data/archive/archive03.dbf
  archive04		/usr/myapp/data/archive/archive04.dbf
  users			/usr/allapps/data/users.dbf
  menus		/usr/myapp/data/system/menus.dbf

// Open a table
> use users

The OPEN DATABASE command requires only the database name to be specified, not the complete path of a directory as with SET PATH or SET DEFAULT. Databases are searched for in the sub-directories of the directory defined in the DB_DATADIR environment variable or symbol. DB_DATADIR is set in the system wide configuration file.

#---------------------------------------------------
# location of directories and important files
#---------------------------------------------------
DB_DATADIR="${ROI_ROOT}data/"     ;export DB_DATADIR

NOTE: DB_DATADIR is read from these files at startup to determine the home directory for databases. Updates to DB_DATADIR once a Recital process is running do not change this setting. The OPEN DATABASE command and certain other database commands allow a '?' to be specified in place of a database name. In this case the 'Select a file' dialog is displayed, listing the available files in the DB_DATADIR directory.

Click image to display full size

Fig 1: Recital Terminal Developer OPEN DATABASE ? command.

This functionality is also supported by the PACK DATABASE, REBUILD DATABASE and REINDEX DATABASE commands.

Databases can also simplify data access for Recital Client Drivers using the Recital Database Server: instead of specifying a starting directory, only a database name is required. The database can handle files from multiple directories and associate tables with their single index files.

Recital Universal .NET Data Provider

/////////////////////////////////////////
// include the references below
using System.Data;
using Recital.Data;

/////////////////////////////////////////
// sample code to call a Stored Procedure
public int CallStoredProcedure()
{
    string source = "Data Source=localhost;" +
	                "Database=southwind;" +
			"uid=?;"+
			"pwd=?";
						
	RecitalConnection conn = new RecitalConnection(source);
	...

Recital Universal JDBC Driver

import java.sql.*;
import java.io.*;
import java.net.URL;
import java.math.BigDecimal;
import Recital.sql.*;

public class jdbc_test {

  public static void main(String argv[]) {
    int i;
    ResultSet rs;
    ResultSetMetaData rsmd;

    System.out.println("Recital JDBC driver verification.");
	
    for (int ii=0; ii<1; ++ii) {
      try {
        //----------------------------------------------------------
        //-- Load the Client Driver for the
        //-- Recital Universal Application Server
        //----------------------------------------------------------
        System.out.println("Loading Recital JDBC driver.");
        new RecitalDriver();
        //-----------------------------------------------------------
        //-- The standard format of the connection URL is as follows:
        //-----------------------------------------------------------
        String url = "jdbc:Recital:" +
                     "SERVERNAME=?;" +
                     "DATABASE=jdbc_test;" +
                     "USERNAME=?;" +
                     "PASSWORD=?;" +
                     "ENCRYPTION=false";
...

Windows Recital Universal ODBC Driver

Click image to display full size

Fig 2: Recital Universal ODBC Driver DSN setup using a database.

Database Procedure Libraries

A database can have an associated procedure library, which is automatically opened when the database is opened. This way, any procedures required by the database's data files are always available. The procedure library should reside in the database's directory and be named dbc_xxx_library.prg, where 'xxx' is the name of the database. When the OPEN DATABASE command is issued, a check is made for the database procedure library and a SET PROCEDURE TO dbc_xxx_library.prg ADDITIVE issued automatically. The procedure library is in turn closed when the CLOSE DATABASES command is issued. This is particularly convenient for client/server database access.

Database Events

Issuing the OPEN DATABASE or CLOSE DATABASES command also fires a database event. Database events, like triggers for tables and forms, can have programs associated with them. The OPEN DATABASE command fires the DBC_OPENDATA event and will run a program called db_opendata.prg if one exists in the database's directory. Similarly, the CLOSE DATABASES command fires the DBC_CLOSEDATA event and runs the db_closedata.prg program. Both of these events also allow the current open or close operation to be abandoned if the associated program returns a logical false (.F.).

Information

Databases, specifically database catalogs, are an excellent place to store information about the files required by a particular application. Instead of having to search through application code to determine which tables are being used and what index keys they have, this information can be viewed in the database catalog. Database catalogs are themselves Recital tables and can be viewed and/or updated in the same way as any other Recital table. There is also a series of commands to provide information about the currently open database. DISPLAY/LIST DATABASE The DISPLAY DATABASE and LIST DATABASE commands display the database name and path, followed by the equivalent of LIST STRUCTURE INDEX and LIST DICTIONARY for each table in the database, e.g.

> open database southwind
> display database

Database Name:  southwind
Database Path:  /usr/recital-9.0/data/southwind/

Structure for database : categories.dbf
Number of data records : 8
Date of creation : 05/12/2004
Date of last update : 05/12/2004
Database encrypted : False


Field	Field Name		Type			Width	Dec	Description
    1	CATEGORYID	Numeric       	10			Category ID
    2	CATEGORYNAME	Character     	15			Category Name
    3	DESCRIPTION	Memo           	8			Description
    4	PICTURE		Memo           	8			Picture
** Total **                       42
Production DBX file: categories.dbx
  Master Index TAG: CATEGORYID
  Key: categoryid
  Type: Binary
  Len: 8
...

DISPLAY DATABASE shows the data one screen at a time, whereas LIST DATABASE is ideal for output to a file.

> open database southwind
> list database to file info.txt

DISPLAY/LIST TABLES

LIST TABLES, as we have seen above, lists all the tables from the database, giving each table's name and path. DISPLAY TABLES shows the information one screen at a time.

Maintenance

Using a database can simplify the maintenance of an application's programs, tables and indexes. Multiple files, possibly in different directories, can be targeted by a single database command.

COMPILE DATABASE

The COMPILE DATABASE command can be used to issue a COMPILE command for all program files listed in the database catalog.

// Open the database
open database myapp

// Compile all the database's programs
compile database

NOTE: Program files are added to a database using the SQL CREATE PROCEDURE command.

PACK DATABASE

The PACK DATABASE command issues a PACK command for every table in the database catalog. The PACK command is used to permanently remove any records that have been marked for deletion using the DELETE command.

// Open the database
open database southwind
// Pack all the database's tables
pack database

REINDEX DATABASE

The REINDEX DATABASE command rebuilds all the tag and single indexes in the catalog. This is the equivalent of an INDEX ON <key> TO | TAG <index> for each catalogued index key.

// Open the database
open database southwind
// Rebuild all the database's indexes
reindex database

NOTE: Using a database helps protect against a table being opened without its associated single index files also being opened. Any single index files that are included in the database catalog will automatically be opened when their table is opened via the database. If a single index appears in the database catalog, but the physical file no longer exists, it will be removed from the catalog when its associated table is next opened. All indexes, tagged indexes or single indexes, created while the database is open, are added automatically to the database catalog.

REBUILD DATABASE

The REBUILD DATABASE command is used to PACK, rebuild the index tags and refresh the contents of the database catalog file.

// Rebuild the database
rebuild database southwind

Creating and populating a database

Databases are created using the CREATE DATABASE command.

// Create new database
create database newdb

The CREATE DATABASE creates a sub-directory in the DB_DATADIR and initializes the new database's catalog file. The catalog file is given the same basename as the database and is a Recital table with a '.cat' file extension. It has a production index file with a '.cax' file extension and a memo file with a '.cam' file extension.

// Open the database
open database newdb

With the database open, the catalog file table is opened in the highest available workarea and is given an alias name of its basename preceded by an underscore, e.g. '_newdb'. Any new tables or indexes that are created will be automatically added into the catalog and form part of the database. 'Free' tables can also be manually added into a database using the SQL ADD TABLE command.

// config.db
set sql on
set sql to vfp
// end of config.db

// Create a 'free' table, with no database open
create table free1 (field1 char(10), field2 date)
// Open the database
open database newdb
add table free1

As the application runs, 'myapp' in the example above, each table that is opened is added to the database catalog. Indexes that are opened are added in the same way. Using SET AUTOCATALOG OFF, inclusion in the catalog can be restricted. Once all required areas of the application have been catalogued, the application can be updated to make use of the database commands.

Converting an existing application

The AUTOCATALOG commands can be used to automatically create a database catalog based on an existing application. The SET AUTOCATALOG TO >database< command will create the database if it does not already exist.

// Database must be closed during autocatalog process
close databases
// Specify the database
set autocatalog to myappdb
// Start the autocatalog process
set autocatalog on
do myapp
// The autocatalog process can be toggled off to exclude 
// certain parts of the application if required
// set autocatalog off

Exporting a database

The BACKUP DATABASE and RESTORE DATABASE commands have been added to simplify the process of moving applications between binary incompatible platforms. Binary files such as tables, memo files and data dictionaries must be exported to ASCII format to allow them to be transferred from one platform to another where the platforms have different binary ordering. For example, this is the case when moving files between a SUN Sparc Solaris machine and an Intel Linux machine. The BACKUP DATABASE and RESTORE DATABASE commands are extensions of the BUILD and INSTALL commands: where BUILD and INSTALL operate on a specified list of tables, BACKUP DATABASE and RESTORE DATABASE automatically handle an entire database.

// Open the database
open database southwind
// Export the database to ASCII format
backup database

The BACKUP DATABASE command goes through the database catalog, exporting each file into an ASCII format that can be handled by the RESTORE DATABASE command. The files are created in a directory with the same name as the database. This directory is a sub-directory of the directory specified in the environment variable DB_BACKUPDIR and is created automatically. By default, DB_BACKUPDIR is the 'backup' sub-directory of the Recital home directory.

// Query the DB_BACKUPDIR environment variable setting
> ? getenv([DB_BACKUPDIR])
/usr/recital/backup

Like DB_DATADIR, DB_BACKUPDIR is set in the system-wide configuration file and is read at startup to determine the home directory for database backups. Updates to DB_BACKUPDIR once a Recital process is running do not change this setting.

# profile.db/uas extract
#---------------------------------------------------
# location of directories and important files
#---------------------------------------------------
DB_BACKUPDIR="${ROI_ROOT}backup/"     ;export DB_BACKUPDIR

Once the BACKUP DATABASE command has completed, the files can be transferred to another platform, for example from Intel SCO OpenServer to IBM AIX and the RESTORE DATABASE command used to recreate the database.

// Export the database to ASCII format
// Note: the BACKUP DATABASE command operates
// on the active or specified database
$ db
> backup database southwind
> quit

// 'tar' up the files for transfer
$ cd /usr/recital/backup
$ tar cvf southwind.tar ./southwind

// Transfer the tar archive to DB_BAKUPDIR on the 
// target machine, then extract the files
$ pwd
/usr/recital/backup
$ tar xvf southwind.tar

// Rebuild the database on the target platform
// The database is automatically created 
// as a sub-directory of DB_DATADIR
$ db
> restore database southwind