Further Understanding Btrieve - and Scaleable SQL4

by Ayodele Dahunsi

Published 1998-05-01    Printer-friendly version

In my previous article - Understanding Btrieve and Scaleable SQL4 (which I will advise you to read first if you haven't before proceeding with this), I gave an introduction on the Btrieve/Scaleable SQL, highlighting what is necessary to understand the workings of these systems. I will further expand on this by concentrating on Client-Serving, deployment concerns, and scalability of applications.

One thing about Client-Server deployment is that you don't really know it until you use it, and you don't really use it until you are asked for it. What you have been calling a Client-Server might just be a multi-user system, but I assure you that before you are halfway through this article, you will be able to re-define your system by yourself.

Now note that we can't go too far in discussing Client-Server without mentioning Structured Query Language (SQL) which is a language with English-like commands used for maintaining and querying large databases which are relational in nature - Relational Database Management Systems (RDBMS). However, this does not mean that it is only SQL that is used in the Client-Server environment. The only shortfall is that it usually takes about twice the effort using a language that is not SQL for a Client-Server.

It is worth mentioning that, while this article is not strictly about Client-Server technology, it is necessary to throw some light on the basic requirements, and thus provide a better understanding and appreciation on whether or not you have to even bother installing your 2-User Scaleable SQL4 bundle. Most importantly too, it might be wrong to assume that every subscriber to Clarion Online fully understands the basics of Client-Server Technology. It is also obvious that this is a complex area that includes aspects many of us never encounter.

Some Client-Server Definitions

What is Client-server Computing?

Client/server computing is the logical extension of modular programming. Modular programming has as its fundamental assumption that separation of a large piece of software into its constituent parts ("modules") creates the possibility for easier development and better maintainability. Client/server computing takes this a step farther by recognizing that those modules need not all be executed within the same memory space. With this architecture, the calling module becomes the "client" (that which requests a service), and the called module becomes the "server" (that which provides the service).

The logical extension of this is to have clients and servers running on the appropriate hardware and software platforms for their functions. For example, database management system servers running on platforms specially designed and configured to perform queries, or file servers running on platforms with special elements for managing files. It is this latter perspective that has created the widely believed myth that client/server has something to do with PCs or Unix machines. - Steve Hultquist

What is a Client process

The client is a process (program) that sends a message to a server process (program), requesting that the server perform a task (service). Client programs usually manage the user-interface portion of the application, validate data entered by the user, dispatch requests to server programs, and sometimes execute business logic. The client-based process is the front- end of the application that the user sees and interacts with. The client process contains solution-specific logic and provides the interface between the user and the rest of the application system. The client process also manages the local resources that the user interacts with such as the monitor, keyboard, workstation, CPU and peripherals. One of the key elements of a client workstation is the graphical user interface (GUI). Normally a part of operating system i.e. the window manager detects user actions, manages the windows on the display and displays the data in the windows. - Ravi Kalakota

What is a Server process?

A server process (program) fulfils the client request by performing the task requested. Server programs generally receive requests from client programs, execute database retrieval and updates, manage data integrity and dispatch responses to client requests. Sometimes server programs execute common or complex business logic. The server-based process "may" run on another machine on the network. This server could be the host operating system or network file server; the server is then provided both file system services and application services. Or in some cases, another desktop machine provides the application services. The server process acts as a software engine that manages shared resources such as databases, printers, communication links, or high powered-processors. The server process performs the back-end tasks that are common to similar applications. - Ravi Kalakota

Are we therefore seeing something here? A Client-Server setup will reduce the traffic along the network because only queries are sent to the server, and the client software is not responsible for fetching the records.

In my days of Clipper programming, I replaced my database of .dbf files with Btrieve files using a function library called RASQL. When I send queries to the NetWare Server using the dreadful "SET FILTER" command, you will realise that the server is the only equipment doing the filtering, and within seconds, you get results on the workstation, even on a slow 386SX. During testing, if you use CTRL-C to terminate the Clipper program, the Server still continues the processing based on the earlier commands sent! While not possessing all the characteristics of Client-Server, I want to use this only as an illustration of the basic logic.

Having established some basic principles of Client-Serving, is Scaleable SQL for everybody? Let's move on and see.

Is Structured Query Language (SQL) for you?

Edgar Codd (an IBM researcher in the 1970s), while working with his colleagues in a bid to build prototypes of relational databases, saw the need for a query language that could function with the databases they were trying to build - hence SQL was born. SQL consists of English-Like commands that make it possible to query, retrieve, and update many records in one go.

When data is stored in the same physical area, data retrieval is not usually a difficult task, and coding to handle this is not complex. However, where you have large amounts of data expected to be accessed, processed, and reported on regularly, then it gets more complex. Hence, the need to access this tabular information in form of columns and rows.

There are many SQL applications/environments available in the market today. Talking about the likes of Oracle, MicrosoftSQL Server, SQLAnywhere, SolidServer, our new friend - ScaleableSQL4, and a host of others. It will shock you that, despite some of the advantages of SQL, it doesn't really follow that all your packages should be re-written using SQL, and in some cases the end-result just might not be worth the energy expended inthe conversion.

Navigational as against Relational access

Many of you might have come across Btrieve being referred to as a "Navigational" data access engine, and Scaleable SQL as a "Relational" data access engine. What does this really mean?

In a Navigational data access engine, the front-end language/application is able to position itself at the beginning, end, or any point in the database. This is usually made possible by the keys defined within it. Therefore the front-end is able to provide quick and speedy access to any part of the database for updates to occur on the located record instantly. A common example can be in real time banking applications where instant update might be required for a customer's account. Hence, the front-end "navigates" through the database. The front-end in this case is responsible for the Referential Integrity (RI) between the related data files. In Clarion, the RI is defined in the dictionary while using the Btrieve driver, and then code is generated by the template to maintain this.

The file definition in the Clarion dictionary provides information on how data is stored in Btrieve files. Btrieve consists only of an interface, i.e. the API that has other sub-functions. This Btrieve Application Interface is what allows you to have access to the file structure you have defined and these calls are made through only one procedure that has seven parameters. Therefore, depending on the parameters you pass, Btrieve would return the expected result.

For instance, the C language code below demonstrates this:

  Status = BTRV(B_CREATE, posBlock, &FileCreateBuf, &dataLen, KeyBuf, KeyNum);

Will create a Btrieve file, while;

  Status = BTRV(B_INSERT, posBlock, &nameRecord, &dataLen, KeyBuf, KeyNum);

Will insert a record into a Btrieve file.

However, the Btrieve driver in Clarion provides transparent access to these functions.

In a Relational data access system, access to data is done based on keys and relations between the files (Tables) in the system, thereby relying on three basic principles which are :

1. Normalization - Method used in databases to eliminate redundancies in Fields and complex record structures within all files when treated as one global unit.
2. Foreign Keys - Item for establishing and determining interfile relationships.
3. Joins - Mechanism that enables files to be linked together based on relationships by issuing simple statements.

Manipulation, processing, and updating tables in an application is therefore left to the SQL Engine. In our case this is the Scaleable SQL4 Engine, and it is responsible for maintaining the RI and other functions dealing with updates, and passing SQL statements to the engine. This is made possible with the Data Dictionary Files (DDFs) which have been explained in my previous article.

Unlike the Btrieve Engine, which has only one API, Scaleable SQL has over 30 individual APIs with their respective parameters.

It is therefore clear that most of your applications should benefit from a mix of Btrieve and Scaleable SQL since there would be instances where Navigational access is actually required and not Relational. This must have been Pervasive Software's thinking when they recently merged Btrieve and SSQL4 into one single product called Pervasive.SQL.

New Data Access Methods

In my last article, I explained the major advantage of the MicroKernel Database Engine (MKDE), which is that different access methods can be employed to access your data simultaneously.

To refresh our memory, below is a simple diagram:

This is a basic representation of the access methods at the time Scaleable SQL was in version 3 (SSQL3). However, these access methods were not clearly defined in operation as represented in the diagram. This was because the MKDE was within the Btrieve engine itself as an integral part. As a result, it was imperative to always supply components of Btrieve itself together with a copy of SSQL3. The Btrieve Engine was always supplied together with the SSQL3 Server Engine, even though you wouldn't be able to access the Btrieve Engine from your workstation.

In reality, the diagram below is more appropriate:

With the advent of Scaleable SQL4 (SSQL4), the MKDE was vitally separated from Btrieve and completely re-written thereby making all present and future access models completely independent of one another. With the recent introduction of Pervasive.SQL, we now have:

Note that Btrieve was upgraded from v6.15 to v6.3. This version 6.3 does not have the MKDE components.

Unlike other Database Engines, you use the same file format for all your access methods, either Navigational or Relational.

Pervasive.SQL

Pervasive.SQL is a hybrid between a relational database access system and a navigational database access system. This can be expected since Pervasive's Navigational Engine - Btrieve and their Relational Engine - SSQL4 both share the same access layer, that is the MicroKernel Database Engine.

How does Pervasive.SQL fit into Clarion's existing array of database drivers, when a lot of people are still trying to get a grasp of the Scaleable SQL4 driver?

At the moment, TopSpeed has not come out with a Pervasive.SQL driver. The updated driver released last month as an upgrade to Clarion 4a allows you to access files created with Btrieve 7.0 and 6.15.

In conclusion, Pervasive.SQL makes it possible for you eat your cake and have it. With the speed of Navigational data access having been integrated with Relational access which is solid and robust, this is a considerable advantage over other systems that provide different data formats for the access methods.

Pervasive is providing a free 2-month trial pack (60MB download size) which makes it possible to try it out before you make a switch. For now, you will still have to make do with your Btrieve driver and your Scaleable SQL driver, even though TopSpeed has provided a driver upgrade to make your applications compatible with Pervasive.SQL's files.

This download is available at www.pervasive.com/products/download/index.html

Printer-friendly version

Copyright © 1999-2008 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