Understanding Btrieve and Scaleable SQL4

by Ayodele Dahunsi

Published 1998-01-01    Printer-friendly version

Most likely, by the time you read this article, the Clarion4/SSQL4 - 2 user bundle will be in the hands of Clarioners. Incidentally, a lot of questions have been asked, especially on comp.lang.clarion   on how Scaleable SQL4 is really supposed to function alongside Clarion4.

Some users will probably try Scaleable SQL4 by experimenting, some will go all out to integrate it with their applications, while some will not even bother as a result of their impression of Btrieve. By and large, the most important thing is to understand how Scaleable SQL has been designed to function vis-à-vis Btrieve.

This article will attempt to explain the seemingly complex world of Btrieve/SSQL, and will therefore serve as a foundation for understanding its integration into the Clarion4 development environment.

A Short History

Btrieve was developed in 1982 by Doug and Nancy Woodward, who both founded SoftCraft, Inc. The first version was shipped in September of the same year. Five years later, Novell acquired SoftCraft and Btrieve. On January 26, 1994, Novell transferred the Btrieve database software line comprising of Btrieve, NetWareSQL(which is now ScaleableSQL), Xtrieve, etc. back to the original owners who had now formed a new company, Btrieve Technologies, with some former employees of Novell. However, Novell still retained 15% ownership. The name Btrieve Technologies was later changed to Pervasive Software.

The Microkernel Database Engine (MKDE)

Btrieve, originally developed in Assembly Language was converted to C, and then model independent database functions were separated to form the Microkernel Database Engine(MKDE) which is independent from the full Btrieve product or Scaleable SQL product.

The MKDE consists of a set of background writers (transparent multi-tasked processes), which is responsible for data I/O. For example, in a single-user system, or multi-user/client-server environment, multiple operations which are to be performed on data files are treated together as a "system transaction" before the background writers carry on the task of input and output operations required on these files. When a batch process is being performed, there is a high performance and speed benefit in either a single- or multi-user/client-server environment as a result. In addition, as a safeguard for data integrity, a file involved in a system transaction is prevented from being written to, or read from, the MKDE. The way these background writers perform disk input/output is determined by some settings defined in BTI.INI located in the Windows subdirectory.

The MKDE therefore separates the database model, be it Hierarchical, Network, Relational, or Object-oriented models, because it handles all functions common to the database implementation of any data model. All these functions are managed at the core level to ensure that all access to the database relating to these functions are co-ordinated. The functions include:

• Concurrent transaction processing
• Advanced caching
• Automatic system recovery after failure
• Logging of transactions
• Roll-forward
• Referential integrity
• Performing all disk I/O

By separating the MKDE, the Btrieve Product, therefore, consists of the Btrieve API and the MKDE. The Btrieve API provides the maximum amount of programmatic control from an application over the database. The MKDE makes it possible for you, the user, to access your database in different ways at the same time (concurrently) without compromising data integrity constraints. This is because all security and integrity constraints which are enforced by one data model (say in a Network) cannot be violated by another. Hence, different data models can be loaded and can access the same data.

This is a strong point and advantage of a relational data model like Scaleable SQL over similar SQL engines where deployment means a complete change of the database engine. Scaleable SQL doesn't require a change of the existing data structure/files. As a result, you can leave your well-tested application as it is, and then start to incorporate an application with an entirely different model. Because the MKDE limits itself to core database functions, the design is entirely model independent. It can thus be said that the MKDE does all the work and Btrieve simply is an access module that allows programmers to use the Btrieve API to manipulate data. This API has been skilfully converted into functions we can use within the Clarion environment.

Btrieve Navigational Model

Btrieve is the lowest level interface in Pervasive's MKDE. It is the direct navigational interface to the MKDE architecture. This means that your Clarion application layer has direct responsibility for "navigating" through your database and interpreting the data returned. Btrieve, in a sense, is not really a database since it is not responsible for imposing a structure on the data, nor is it aware of the structure imposed. Therefore, the table and field organization of a Btrieve file is determined entirely by the application that created the file, and so Btrieve cannot differentiate between data and information. It is therefore the responsibility of the application to decipher the data it picks from the files and convert into the necessary information. This presupposes that it is possible to even use a Btrieve file to store information not organized in a table form!

This is why it is possible to define new data types common to other environments like Visual Basic, Magic, etc., but not provided by the Btrieve driver. So long as you know the storage format, and then code how it is going to be stored and interpreted, Btrieve will gladly store it for you without any problem. However, other applications would find it difficult to interpret your new data type because it is only your application that can assign the structure and provide meaning to your records.

All these aspects account for the major reason it is difficult to trace or extract the contents of a Btrieve .dat file, if the structure is not known, or if there are no DDF (Data Dictionary Files) available to show its structures. Data Dictionary files provide your application with information that will enable it to understand Btrieve records on a field level. In essence, your data will be stored in a Btrieve file in exactly the way you instruct the Btrieve API to do the storage.

The following DLLs are usually necessary for any Btrieve application to work, no matter the development platform. These are:

• WBTR32.EXE - Btrieve for Windows Client Engine
• WBTRLOCL.DLL - Client Loader
• WBTRCALL.DLL - Windows Requester
• WBTRVRES.DLL - Resource file

Scaleable SQL (Relational Model)

In Clarion4, a new set of SQL-specific drivers - TopSpeed SQL Accelerator drivers - are featured. Among these is the Scaleable SQL driver.

As explained earlier, the Btrieve interface is separate from the MKDE, so also is Scaleable SQL separate from the MKDE. This makes transition or migration very easy because the developer does not have to change his database engine. What that means is, Scaleable SQL will use your existing data files because the MKDE provides the link to your data. Therefore, it becomes easy to incorporate relational modules into your application at your own pace. However, it must be stated that this is only possible if you are already using Btrieve as your database driver.

Scaleable SQL4 features a complete rewrite of the MKDE (now in version 7.0) and offers a small memory footprint, as little as 4MB (so Pervasive claims). Scaleable SQL has a couple of new enhancements as compared to its predecessor - Scaleable SQL 3. These enhancements are to be expected in a full-fledged relational database management system (RDBMS). They include:

• ANSI SQL and IBM SAA standards
• Close conformity to Microsoft ODBC
• SQL triggers and stored procedures
• Bi-directional and scrollable cursors which can be updated
• Declarative referential integrity
• Nested transactions and transaction logging
• Cursor-based statements
• TCP/IP support.

Triggers are common in RDBMS; they are data dictionary objects which initiate an action for a Database Management System (DBMS) to execute when it encounters an SQL command like INSERT, UPDATE, or DELETE, on the table that has an associated trigger. Triggers will provide you with a means of defining and enforcing various kinds of data integrity constraints.

Stored procedures allow grouping of logically associated programming steps to form a procedure, which can then be executed with one statement. You can also pass parameters to affect the variable used in the execution. External procedures, which have been programmed and stored on disk, can also be executed.

Pervasive has bundled Inscribe, which is a scripting tool, with Scaleable SQL4. Inscribe is a Visual Basic compatible scripting tool that can be used to execute stored procedures. The stored procedures can be executed from other procedures, or even activated by triggers. With Inscribe, you can define business rules separate from your application and bundle these to, say, a server for processing the rules. Inscribe also allows you to access other applications from within your database. These actions performed on these applications can be initiated by the use of triggers! As an example, using Inscribe, you can automatically send an email to a prospective client or customer once he places an order, say over the Internet. An Inscribe script can be written to handle this without any modification to your application software. So your database will react at your site to any update actions defined by the trigger!

Data Dictionary Files (DDFs)

Users of Clarion4 are going to come across a new set of files as they start using the Scaleable SQL database driver. These files (with the extension .ddf), which have been with the Btrieve engine for a very long time, are needed to fully document the structure of Btrieve data files. DDFs (which are also Btrieve files themselves) have a fixed table format and are a compromise for Btrieve's lack of understanding on how the fields of data are defined. Many commercial add-ons/packages use DDFs to understand the positioning of fields in a Btrieve data file. DDFs also define the components of your database as well as the relations. This would include field names, data types, indexes, etc. Nevertheless, it must be stated here that DDFs can be encrypted as well to prevent the end-user from knowing the structure of the database system.

Though a Clarion application that is developed using the Btrieve driver does not need these DDFs, they are required by the Scaleable SQL driver for ODBC access. They are also required in order to allow access to your data files by other applications like Crystal Reports, Microsoft Access, etc.

In the past, data file structures were usually hard-coded into applications, therefore the DDF standard was implemented to enforce and provide access to the SQL relational model specifically for Btrieve. If there was a way to externally recognize where the fields in a Btrieve data file were located, then DDFs would not have been necessary. Nevertheless, a Btrieve file that has fields defined as index segments can provide more information in making a guess about the likely structure of a Btrieve file although, depending on the number of fields in this particular file, this might just be a shot in the dark.

The standard set of DDFs consists of three files. These files are:

• FILE.DDF - Stores Names and Location of data files.
• FIELD.DDF - Stores Field Names, and Data types.
• INDEX.DDF - Stores Index definitions and Positions within each file.

This is the minimum number of DDFs required by any Btrieve application or utility. They can be created in various ways:

1. Using the Scaleable SQL Import Wizard in Clarion4
2. Using SQLScope supplied with Scaleable SQL that allows you to update DDFs with simple SQL commands. The command below will create these three standard DDFs in the \CW4\DATA subdirectory:
3. Using 3rd Party tools like Smithware DDF Builder, BtSearch, etc.
4. Using direct Btrieve API calls. This method is discouraged, since an improperly created DDF usually results in Status 6 errors.

The third file, INDEX.DDF is usually left out by some applications since it is possible to determine the index by making use of Btrieve's Status operation. However, there would be a problem when the data file is to be created for the first time.

An extra set of eight DDFs is also created specifically for SQL functionality. This set is created as soon as you add extra SQL characteristics to your application or your scripting. These files are:

• ATTRIB.DDF - Stores Field and Column display formats.
• VIEW.DDF - Stores definitions of relational views.
• PROC.DDF - Keeps information on stored procedures.
• TRIGGER.DDF - Stores trigger definitions.
• DEPEND.DDF - Stores trigger dependencies.
• RELATE.DDF - Stores referential integrity information.
• USER.DDF - Stores Users, Groups, and Password information.
• RIGHTS.DDF - Stores User and Group access rights.

DDF is available as a proprietary format; therefore, it is possible to create additional DDFs in order to enhance the functionality of your application. For example, Smithware Inc., created these additional DDFs:

• COMMENTS.DDF - Stores comments on each field defined in the DDF.
• FIELDEXT.DDF - Stores information on unsupported index data types (see discussion under Btrieve Navigational Model). These data types include VB Currency, Binary Intel, COBOL Decimal Comp-6, MagicPC Real/Extended, Pascal Real, and so many others. Your development environment has to support these data type for you to be able to use them. Unfortunately, Pervasive-sw does not support these additional DDFs so Scaleable SQL will not even recognise them.

Conclusion

This article has provided the background needed to get you started in your attempt to delve into Btrieve/Scaleable SQL4. Future articles will be specific solutions/ideas on common issues in the Btrieve development environment.

Printer-friendly version

Copyright © 1999-2009 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942