If you've found that Microsoft Access doesn't meet your needs, but you aren't ready to leap to SQL Server yet, there is a middle ground. Visual FoxPro 6.0 is a Webriffic database package that hasn't gotten the attention it deserves.

You could call Visual FoxPro® the Rodney Dangerfield of Visual Studio®; its omission from introductory articles on Visual Studio 6.0 (including MIND's October cover story, Building a Web Knowledge Base with Visual Studio 6.0 Enterprise Edition) reflects the difficulty Visual FoxPro has in getting respect outside of its own development community. Visual FoxPro 6.0 offers a full-featured application development system, geared to developing data-oriented business applications. Visual FoxPro is both wide and deep, and sports all of the workspaces you would expect from a sophisticated development tool and then some, as well as a powerful database engine with a rich set of data connection and manipulation functions.     Notable among its work spaces are object-oriented visual form and class designers, a tightly integrated report writer, and a database manager. Microsoft® has built an object-oriented development language that provides sophisticated inheritance and encapsulation capabilities. The syntax has evolved from the days when FoxBASE was a dBASE clone. In association with its native database engine, Visual FoxPro blends extended Xbase data management syntax with SQL and a powerful cursor management architecture. With version 6.0, Microsoft has beefed up Visual FoxPro's ability to create COM components that can be deployed on the server, and has added the ability to create forms as Active Documents. Designing Forms and Classes Visually     The Visual FoxPro form design system delivers object-oriented programming capabilities. The visual Class Designer looks very much like the Form Designer, but is oriented to designing objects that you store as classes. Visual FoxPro presents the controls that will populate a form as classes so that individual controls on a specific form become instances and inherit the basic characteristics of their class. This approach enables you to create libraries of reusable custom controls. The major drawback to Visual FoxPro's control model is that it uses controls native to Visual FoxPro rather than standard Windows®controls.

Figure 1: Creating a Class

The visual form design surface is complemented by the visual class design surface. For the past three years, developers working with Visual FoxPro have been creating visual class libraries for several different types of interface components. Very low-level components include form objects (spinners, comboboxes, phone number entry fields, currency entry fields, and entry fields for other common data elements) and toolbar icons. Mid-level components like entry panels for addresses, payment terms, and line items are used in a variety of places throughout a suite of applications. High-level components like form templates serve a variety of needs, from simple containers that encapsulate standard form management logic to classes of business forms that encapsulate an organization's business rules.     There are two ways to add objects to a visual class library. If you are setting out explicitly to create a class, you choose New Class from the File menu, select the built-in class that you want to use as the base class, and identify the visual class Figure 2: Tool Palette library in which you want to store the class. Visual FoxPro then presents a design surface that consists of a layout area and a property sheet where you can set properties for the new class (see Figure 1). Once you are satisfied with your work, save the class. Now, when you want to use the class on a form, open the form, click the Choose Class icon, and open the visual class that you created. The tool palette (see Figure 2) changes to show the controls that you created in place of the stock controls that were there when you started Visual FoxPro. Now you can select your custom class and drop it on the form you are working on.     Once you have created an instance of the class this way, you may want to further refine its appearance and behavior for use on a certain category of forms. Let's say you modify the object's properties and perhaps even add custom properties or methods to it, then decide that you later want to make the modified object available for reuse, as well as the custom class from which you derived it. Here's where the second approach to adding objects to a visual class library comes into play. With the modified object selected in the Form Designer, you choose the Save as Class command from the File menu and save the class that you derived from your initial class into the visual class library. This is a very powerful concept: any time you create a form, part of a form, or a custom object on a form, Visual FoxPro offers an extremely straightforward way to make that object part of your stock class library. The Visual FoxPro Database Engine     The Visual FoxPro local database engine and metadata repository are other key components. Visual FoxPro stores application data in freestanding files (using a .dbf extension), each representing a single logical table. The native structure of these files is derived from the original dBASE format and has been enhanced to support memo and binary fields. In addition to data files, Visual FoxPro maintains indexes for each table in separate files that have a .cdx extension. There can be as many (or more) keys associated with a table as there are fields. Visual FoxPro earned a reputation for speed owing to the index-based Rushmore™ the optimization technology introduced several versions ago. As a result of this technology, queries against large databases in Visual FoxPro can perform as fast or faster than against similar-sized SQL Server databases on the same platform.     Visual FoxPro maintains information about database structures in a database container file (with a .dbc extension), which is really just a specially designed Visual FoxPro table file. You can maintain and specify database rules through language functions such as DBSetProp (which sets the properties of fields, views, and connections) and through the SQL-based CREATE/ALTER TABLE syntax for creating and modifying table structures and indexes.     In Visual FoxPro, there is a visual equivalent for most of the design-oriented aspects of the command language, and the database is no exception. The Database Designer initially presents a blank canvas to which you can add existing tables or create new tables. When you create or modify a table, Visual FoxPro presents you with the Table Designer (see Figure 3), where you can specify field characteristics, including basic data type and size, display format, default caption, validation rule, validation text, default value, and the visual class that should be used to display the field on forms and reports.

Figure 3: Table Designer

Clicking on the Table tab brings up a page where you specify a record validation rule, which is tested when a record is added to a table. You can also insert, delete, and update triggers. Rules, triggers, and index expressions can call stored procedures that are defined and stored in the database container as well.     Clicking on the Indexes tab in the Table Designer dialog reveals a form where you can define indexes for the table. Indexes can be set as Primary, Candidate, Unique, or Regular. While Primary and Candidate indexes are RDBMS-standard, in Visual FoxPro the Unique and Regular indexes are a little different. Where an index contains multiple records with a common index value, a Unique index stores a pointer to only the first physical record that contains the index value; the index simply ignores the second or later occurrence of a nonunique value. a Regular index is simply an index that is not Unique, Primary, or Candidate. It can be used for ordering and seeking records, and as the target index for the many side of a one-to-many persistent relationship.     To support programmatic data administration, Visual FoxPro supports fairly standard SQL data definition language (DDL), providing a command set that includes the following

ALTER TABLE TableName ALTER COLUMN FieldName ; SET DEFAULT default-expression ; SET CHECK logical-expression

for establishing a default value and validation rule for a field, and

ALTER TABLE TableName ALTER COLUMN FieldName ; SET CHECK logical-expression ERROR message-text ; ADD PRIMARY KEY key-expression TAG index-tag-name ; ADD UNIQUE key-expression TAG index-tag-name ; ADD FOREIGN KEY key-expression TAG index-tag-name ; REFERENCES foreign-table-name

for establishing candidate and foreign keys, table-level validation rules, and relationships. Connecting to Database Servers     This brings us to the third strategic leg of Visual FoxPro 6.0: its support for managing client/server connections. The first level of client server support is the portability of data and metadata from the Visual FoxPro environment to client/server platforms. By enabling the migration of both data and metadata, Visual FoxPro enables software companies like SBT Accounting Systems to develop versions of their product that run against either Visual FoxPro tables or SQL Server, and enables their customers to migrate from one to the other when they are ready. It's also enabling companies to have Java/Oracle applications coexist with Visual FoxPro applications; our company's Visual FoxPro applications read from and write to the Oracle database (see Figure 4), and our Java language-based applications update Visual FoxPro tables via JDBC!     With Visual FoxPro, developers can access server-based data via either persistent application-level connections, which are stored as remote views in the FoxPro database container, or through transient connections established at execution time. Configurable defaults for persistent remote views include whether to use the current connection for new views, the maximum number or rows to be returned to a view, how many rows to return in a single fetch, whether to fetch memo fields with their records or wait until display of the memo data is requested, whether to compare key fields or key fields plus updateable fields to determine whether server data needs to be updated, and whether to perform updates using UPDATE or DELETE and INSERT. Configurable defaults for connections include whether to enable asynchronous or batch processing and the amount of time to wait before timing out in several connection scenarios. In fact, if you compare the Visual FoxPro Connection and View properties with the ADO Connection and Recordset objects, you will notice some striking similarities.     In the interactive View Designer (see Figure 5), you first select the tables to include in the view and their relationships, then specify selection criteria, fields, ordering, grouping, and update criteria. The update criteria lets you specify which columns can be updated, which columns should be treated as keys for matching buffered rows back to rows on the server, and whether you want to override the defaults for selection and update of rows whose values have been changed.

Figure 5: View Designer

The mechanics and syntax of establishing a data view and the capabilities the view provides are identical whether the underlying tables are FoxPro tables or server-based tables. Once a view is defined, you can manipulate it via either SQL or Xbase commands. It's handy to be able to create a multitable view against a SQL Server database, and then issue a sequence of Xbase commands like these:

use 'titles by author' index on au_id tag au_id of tbya_cdx seek '672' replace royaltyper with royaltyper + 12

These commands create a local cursor based on the view specification, build a FoxPro index on author ID, perform an indexed search of the cursor for the author whose ID starts with 672, and increase the royalty period by 12. Visual FoxPro and the Web     Visual FoxPro offers a variety of ways to access data and application components via the Web. The most straightforward is to use the new Web Publishing wizard that automatically generates HTML output based on information in a database. Given the open architecture of Visual FoxPro, it's not difficult to use the output of the wizard as the basis for pages that Visual FoxPro can build in real time.     At the other extreme, Visual FoxPro employs the isapi model to create a system of on-demand generation of HTML pages that represent entire applications. The building blocks for an isapi application are packaged as foxisapi.dll and the isapi class library. This class library includes a number of user interface classes designed to generate HTML code representing each object. The Genhtml method actually does the work of generating the HTML code for the object. Figure 6 shows the Genhtml method from the htmlgrid object.)     To demonstrate this feature, we created a form with a label and a grid that lists the results of a query. The query reports on the delivery status of EDI transactions bound for EDI Value Added Networks. By creating our label as a subclass of the htmlLabel class, creating our grid as a subclass of the htmlGrid class, and creating the form as a subclass of the isform class, we were able to take advantage of the code in the isapi.vcx class library that generates the Dynamic HTML.     Foxisapi.dll is actually a fairly general-purpose API for invoking COM servers via isapi. The HTML request for a Foxisapi-based application must invoke the foxisapi.dll and the automation server that you built from your class (for example, http://yourserver/scripts/foxisapi.dll/ecisapi.gateway). When somebody requests the page, foxisapi.dll launches an instance of the Visual FoxPro automation server that you created, and calls one of that server's methods to return the HTML. It hands the HTML back to the Web server through isapi, which in turn passes it back to the user's browser. Visual FoxPro 6.0 provides a new version of foxisapi.dll that includes support for pool management of Visual FoxPro Automation servers as well as improved debugging capabilities.     Because the new foxisapi.dll is freethreaded, it can now pool multiple Visual FoxPro isapi Automation servers, improving the scalability of Web applications created this way. Pooled isapi Automation servers allow a free isapi Automation server to service a request when other isapi Automation servers are busy. To take advantage of isapi Automation server pooling, each instance of the Automation server should be made persistent by setting the nPersistInstance parameter passed by foxisapi.dll to 0, which indicates that the Automation server should remain in memory after completing the method call.     The number of isapi Automation servers available to service requests is determined by settings in the foxisapi.ini initialization file. To create a pool of multiple Automation servers, include a heading entry in square brackets with the name of the Automation server for which a pool is created. Follow this heading with a list of Automation servers that comprise the pool, and with a numeric value that specifies the maximum number of instances of each Automation server that can be created.

[ecisapi.gateway] ecisapi1.gateway=4 ecisapi2.gateway=3

Creating Active Documents with Visual FoxPro 6.0     Version 6.0 of Visual FoxPro introduces Active Document technology to the product. An Active Document is a type of OLE embeddable document that can be hosted within a special container, like Microsoft® Internet Explorer 4.0. When you open an Active Document, the application that created it runs and presents its user interface in the entire client area of the active document container. If you have ever opened a Word document within Internet Explorer 4.0, you have encountered the Active Document experience.     Unfortunately, Internet Explorer 4.0 is currently the only browser we know of that hosts Active Documents, and Active Documents require that the Visual FoxPro runtime engine be available for execution on the client's workstation. While there is no runtime license fee associated with Visual FoxPro-based applications, you will need to ensure that your users get the runtime installed before trying to open your Active Document, which adds overhead. Visual FoxPro does include a reasonably straightforward setup wizard that automates the creation of downloadable applications.     The ActiveDoc class is one of the new base classes included with Visual FoxPro. The most notable difference between Active Document applications and typical Visual FoxPro applications is that whereas the main file for most applications is a program or a form, the main file for an Active Document must be set to a class based on the ActiveDoc base class. You can use the visual Class Designer to create a class based on the ActiveDoc base class, or you can write the equivalent code.     The ActiveDoc base class provides properties, events, and methods for an Active Document. You also use it for communication with the Active Document host. For instance, the CommandTargetExec event provides a means for Visual FoxPro to respond to events such as menu selections and navigation requests registered by Internet Explorer 4.0. By adding code to the CommandTargetExec event, you can define custom actions to take when a user makes a menu choice while your application is running. The ContainerRelease event occurs when the host releases the Active Document; this can be a good place to close any tables or files that may have been opened by the Visual FoxPro session.     ActiveDoc applications are usually going to be form-intensive; after all, the principal application is to broadly distribute user interface components. Insofar as most Visual FoxPro developers have built extensive libraries of forms for their applications, we anticipate a good deal of interest in reusing these forms as Active Documents. It's straightforward to load a Visual FoxPro form within an ActiveDoc application using the calling syntax shown in Figure 7. You will want to make some relatively minor changes to the form, as illustrated by the code in Figure 8. Creating Server-Side COM Controls     a final way to reuse Visual FoxPro code to support Web-based application development is by packaging it as middle-tier components. Creating COM DLLs from Visual FoxPro classes is as easy as checking the OLE Public checkbox on the Class Info dialog and Build COM DLL on the Build Options dialog when you build your project. You can achieve the same result by programmatically creating the class with the OLEPUBLIC keyword

DEFINE CLASS vfpactivex AS custom OLEPUBLIC

BUILD DLL vfpactivex.dll FROM vfpactivex.pjx 

Of course, Microsoft has made Visual FoxPro a more viable choice for building server-side components in Version 6. The most obvious improvement is that any public properties and methods that you create now appear in the Automation server's type library and are available when you use the IntelliSense® feature in Visual Basic® or Visual InterDev™. Less visibly, but more significantly, Microsoft has reworked the execution model to allow COM DLLs built with Visual FoxPro to support apartment model threading as Visual Basic 5.0 does (each apartment manages a single thread of execution, and in-process DLLs may not include any user interface functions). In addition, Microsoft has changed the runtime model so that each in-process dll now uses a separate instance of the vfp6r.dll runtime.

Figure 9: Visual FoxPro Class Browser

To demonstrate an application built from an existing codebase, we reworked some code from the SBT Accounting Systems Professional Series payroll product (with the vendor's permission, of course). Figure 9 displays the Visual FoxPro class browser with our class loaded. Figure 10 lists the class code itself. Figure 11 displays the Web forms that we used to submit the request and to display the results of the calculation. We called our Visual FoxPro-based COM server to calculate the payroll from an ASP script (see Figure 12). In addition to showing how you can effectively repackage existing logic, the VFP COM class code demonstrates the separation of the class's public and private interfaces via the use of the HIDDEN keyword as well as the new property assign methods introduced in Visual FoxPro 6.0.

Figure 11: Submitting a Payroll Request and Displaying the Results

We've spent a lot of time building software products with Visual FoxPro, and earlier with FoxPro 2.6. We've enjoyed working with the product, especially the powerful data access tools and its truly innovative object-oriented language. But we're frustrated by the lack of acceptance of Visual FoxPro as the top-tier development tool it is. With Visual FoxPro 6.0, the product's separate status has become more apparent even while it's become capable of participating in the Microsoft DNa architecture. For instance, Visual FoxPro has added its own component gallery with components stored in its own structures, distinct from the Visual Component Manager available to other Visual Studio tools that's based on Microsoft's repository. While it's nice to have a component organizing tool, not being able to share components with other tools hurts Visual FoxPro when it's time to choose tools for building components. Then there's the lack of IntelliSense technology in Visual FoxPro. With the expanding use of third-party components, the ability to list all of a component's properties and methods in the code editor becomes a necessity. Since native Visual FoxPro components lack a type library, the IDE cannot support IntelliSense.     To overcome this shortcoming, Visual FoxPro sports a highly evolved class browser. This is a great tool, but again, it's different from the object browser familiar throughout the balance of Visual Studio. If you're an existing Visual FoxPro user, you'll find that version 6.0 is a great upgrade. If you're currently using another package, you'll need to weigh Visual FoxPro's many strengths against some of its incompatibilities to decide whether it suits your needs.

For related articles, see http://msdn.microsoft.com/vfoxpro and http://support.microsoft.com/support /kb/ARTICLES/Q182/5/79.asp.

From the February 1999 issue of Microsoft Internet Developer