TOOLS OF THE TRADE:

---

Start Your ENGINES

---

Data here, data there, data everywhere. Do you develop any applications these days that don't use data at all, anything less trivial than a calculator utility? I'll bet most of the time you're working with applications that gather, process, or distribute the masses of data stressing out the world. How do you handle those piles of data? What tools can you use to extract every penny of value from them?

Visual Basic has been the premier Windows database development tool since version 3.0 hit the streets a few years back. Just as Microsoft. From the humble beginnings of the data control and first version of data-access objects, the tools have blossomed into myriad options and even a third edition of Visual Basic, the Enterprise Edition, that's dedicated to managing an enterprise's data. Microsoft provides two primary database engines that it would love you to use.

The Microsoft Jet engine is Visual Basic's native data-access tool, letting you access Microsoft Access files, other desktop databases, and the rest of the world through ODBC. If Jet doesn't scale well for you, join the big leagues and load up Microsoft SQL Server on your favorite NT server machine and do almost anything with any data anywhere. (Yes, I know Microsoft also sells FoxPro, but it really isn't a central part of its strategy.) Soup to nuts: Now that Microsoft has dominated the desktop and mid-range databases, it's clearly looking to climb the food chain. But maybe its database options aren't the right ones for you.

FIGHT OR FLIGHT?

Microsoft spent a few bajillion of its precious dollars and years of its talented workforce's time in developing the Jet database engine and SQL Server, so why would it ever make sense to switch away from it? Besides, Jet is included free-once you've paid your hundreds for Visual Basic, that is-right in the Visual Basic box, but plenty of developers seem to think there are several good reasons to switch, and the vendors of the products reviewed here aren't going to disagree.

The biggest reason for looking elsewhere for database access is performance. Depending on whom you listen to, once you start getting beyond 10 to 40 concurrent users and about 100 MB database files, Jet disappoints. Queries start taking forever; the 2K page locks tie up too much data and keep too many users from access; corruption seems to increase; and administration becomes a nightmare. Microsoft's response, of course, was to upgrade to SQL Server, a back-end database that provides plenty of oomph when it's run on Windows NT Server. You'll have to spend more time administering the system, but when you start getting into that many concurrent users and larger databases, you'll probably have someone around full time to keep the system tuned anyway.

One obstacle in moving from Jet to SQL Server is the cost. Jet is free and freely distributable without royalties, but you'll pay dearly for SQL Server. The server and five user licenses will run you $1,300 on the street, and each additional five-user license pack is $730. You get price breaks the more you buy, of course. If you're an independent developer, every client will have to have at least one copy of SQL Server plus the user licenses. That can start adding some serious dollars to the cost of your custom solution.

Another reason for changing to another database engine is if you need to support a specific database file format, using tools optimized for that format. Jet lets you get at data in basically three ways: Native Jet databases directly, ISAM databases more or less directly, or any database through ODBC. But each of these methods has some significant overhead associated with it, since everything passes through the Jet engine, adding a layer of code to slow things down. Even using ODBCDirect requires use of an ODBC driver (although Microsoft has worked hard to speed up ODBC). But if you need to work with a specific file format, such as xBase, or you don't care about the format and so can use a custom format, some of the alternative engines can significantly enhance the applications' performance and ease coding.

If you moved to Visual Basic from another database development system, third-party engines can let you keep one foot firmly in the familiar while taking advantage of Visual Basic development tools. Two of the products in this review, Luxent Software's Artemis and Sequiter Software, Inc.'s CodeBase, support xBase files by letting you manipulate them in ways similar to what you're used to. For example, Artemis has sx_Zap, sx_Alias, sx_Close, sx_Go, sx_Reindex, and sx_SetFilter functions that any xBase programmer immediately will know how to use.

My own switch years ago from Clipper (Back in the Nantucket days) to Visual Basic was hard, but back then Visual Basic really didn't have much in the way of database support anyway. By the time Visual Basic 3.0 and its database support came along, I was ready to learn the new way of doing things.

Yet another reason to switch, although relatively less important, is you might need some features not available in Jet or SQL Server. For example, Artemis has a Fast Text Search that can make searching for text blindingly fast. I doubt you would select any of these products with that as the primary criterion, but it might just be enough to sway your decision on.

One important consideration you should weigh carefully, is: How eager are you to learn a whole new way of doing things? Some of the products, particularly those that support ODBC, will let you use most of Visual Basic's native data-access tools. Others have extensions that could be confusing. For example, Artemis mirrors xBase programming techniques, so it helps to have a strong xBase background.

DISTINGUISHING FEATURES

As I worked and played with these products-it was mostly work, as you'll see-it became clear there were several features that distinguished them from each other. All these features complicate the decision of which to select, except that most products are radically distinctive from the others and intended for different kinds of applications.

Despite all of Microsoft's work, the world is not yet clearly 32-bit, so if you have to develop or support 16-bit applications, the consideration will be a major factor in your move to a database engine. All the products support 32-bit programming, but a couple of them support only 32-bit.

Visual Basic has expanded the ways you can use ODBC in your applications, with tools like remote data objects and ODBCDirect. Once upon a time ODBC was a complex API that could sterilize frogs at 50 yards (well, it still is and can), but RDOs make it far easier to use. If the database engine includes an ODBC driver, you can use these native Visual Basic coding techniques in the Enterprise Edition, which are reasonably fast and fairly consistent no matter which back-end database you use. It also makes it slightly less painful to change database engines, as long as the table and record structures remain the same. Most of the products don't' support ODBC at all, although Microrim Inc.'s Oterro v. 1.0 provides access only through ODBC.

With the advent of application delivery on CD-ROMs, distributable file size is less of a concern than in the days of 1.44MB floppies, but it's still important. And if you deliver your applications over a network, it's definitely a concern. Third-party Visual Basic development tools are notoriously evasive when it comes to finding out what you can and need to distribute. Some products are lightweight powerhouses, while others are heavyweight and feature-rich.

Initially I was a bit surprised that not all products support transaction processing; this has become a necessity in my mind. But I suppose that for the products that lack it, it does make sense. The underlying xBase database doesn't support transactions, so Artemis kept things simple and left it out, while CodeBase, a more complex product, worked it in. VideoSoft's VSData is designed to be small and fast, so VideoSoft left it out. That's likely to be a make-or-break issue for some developers, particularly if you develop robust client-server applications.

Some of the products include custom reporting tools. For those that don't, it probably isn't a big loss since Visual Basic includes Crystal Reports, if you can tolerate its foibles. VideoSoft has a separate product for database reporting. Some of the products include tools for manually creating and maintaining database files, particularly important for custom formats.

If you've read my past reviews, you know that I give a log of weight to a product's documentation. I've seen too many otherwise fine products made worthless by poor documentation, and too many whose worth I was unable to discover because of little or no documentation. A product doesn't necessarily have to have a printed manual, but I sure prefer it. I want to conserve the earth's resources as much as anyone else, but there's nothing like a printed manual to learn a product, letting me make marks in the margins, take with me to study when I have to stand in a line somewhere, and fill with sticky notes. The best situation is to have both online and printed documentation, with the same contents in each. An important part of the documentation is plenty of sample applications. Even liberal use of code snippets in the manual doesn't substitute for applications that show you how to use the product's features in different ways and with differing levels of complexity.

Even though performance is one of the major reasons to make a switch to another database engine, I haven't included benchmark figures for these products. I just couldn't figure out a way to make benchmarks fair. These products are useful in different types of applications, and comparing one designed for many concurrent users with another that isn't just won't provide you with useful information. Besides, my experience with such benchmarks is that what runs faster on my network, using my database and its data, with Venus and Mars aligned just so, and with this Service Pack installed, won't match your results. Beware of vendor benchmarks too; they have the luxury of setting up the tests to make their products look good. Test the products under your conditions if performance is important to you and your users.

CODEBASE (Windows Version)

CodeBase is the other xBase-compatible database engine in this review, one that's far more powerful than Artemis. This product has been around for years, giving Sequiter the opportunity to fine-tune the back-end database even as the various xBase file formats changed. CodeBase also is better supported with tools, including CodeReporter for database reports, CodeControls you can use on forms for data management, and plenty of other goodies.

CodeBase includes versions for C/C++, Delphi, Visual Basic in every box. The product implements the different xBase formats-FoxPro, dBase, and Clipper data files in a way that I find inconvenient. A single application can support only one of the three formats, not unreasonably, but the DLL function has to have the name c4dll.dll. So to run an application using Clipper files, you have to make sure any prior versions of the DLL are unloaded from memory, copy the c4cli.dll to someplace that windows can find it, rename it to c4dll.dll, and run your application. Even running the sample applications requires multiple steps of copying files to the right place. To make matters worse, you have to run the full CodeBase installation program three times if you want to install full support for all three database formats. I suppose if you were interested in only one of the formats, it would be reasonable, but my life has never been that simple.

Once you get beyond these annoyances, CodeBase has some powerful database features. Its query optimization technology is fast, and I found it to be relatively easy to use, particularly if you have recent xBase programming experience. (Mine is getting old, thank heavens.) It supports transaction processing, update logging, and client-server support. The only network protocol supported for the client-server version is TCP/IP. (It used to also support IPX/SPX.) With both 16- and 32-bit support you can create applications for any version of Windows.

CodeReporter is a comprehensive relational report designer similar to other visual designers like Crystal Reports, and ReportSmith, although without as many features. The ties between the data files used in the report are made using simple dBase expressions. One nice feature is lookahead totals so you can include a subtotal in advance of the list of amounts in the report.

The package includes utilities to back up, analyze, and restore databases. CodeBase records all the changes made to a database in a log file. If the database becomes damaged, the supplied utilities can use the log to restore the data. It also includes a CodeControls VBX (how quaint) with edit, listbox, combo-box, and data controls.

The CodeBase documentation generally is good with plenty of support for each development environment. A printed manual, Getting Started, covers installation and the intricacies of the product's files for all environments. Each environment also has a comprehensive reference guide in PDF format and one version in printed form as well as a user guide in PDF form only. I found this a good scheme to be a good compromise between no manuals and providing too many that go unused.

The people who put the sample applications together were more concerned with their own convenience than the users'. The good news is they include many examples you can use to learn the product. The bad news is that instead of a set of ready-to-run sample applications, you have to copy various files from different places-renaming them-before you can run any example. Then you have to repeat the process for the next sample. The product ships on a CD-Rom, so there's no reason not to set this up better for the user. It does include a batch file to automate the process, requiring you to type the very intuitive command, Getex 16 ex20.bas, from the command line in a DOS box, after navigating to a deeply nested directory, to run example 20 as a 16-bit application. That's great-I need to use the command line to run a batch file in Windows NT.

In any event, Sequiter provides plenty of tools to learn the product, but you have to work harder than you should. If you need dedicated xBase format support, CodeBase is a comprehensive and fast product that will let you accomplish almost everything you need. You'll have to get past some of the annoying packaging decisions that Sequiter made and tolerate tools that sorely need a major update to take full advantage of 32-bit Windows.

MAKING THE RIGHT CHOICE

Much as I'd like to conclude with a bold and convincing statement that Brand X is the brand for you, there just isn't any way to do that. More than any other product category I've reviewed, selecting an alternative database engine is one of the most complex decisions you'll face, assuming of course you're convinced you have to leave behind the tools that come free with Visual Basic. I haven't even been able to come up with any kind of hierarchical scheme for making the decision. I suspect that it's a rare software developer who will use any one database system all the time, unless you're a corporate developer who develops exclusively for the corporate mainframe database.

So you're left with the job of sifting through the various features and perhaps resorting to the least evil choice. Sort through the options to find the ones the work for you and your users. The good news is all the vendors have evaluation versions available you can put through the paces with the data you'll be throwing at the system. Let me know what you decide and why.

Don Kiely is director of technology for SkyFire Group, developing Windows and Internet applications for businesses in rural Alaska, and he writes regularly about Windows programming.