The DataFlex Pervasive.SQL Driver enables DataFlex programs to store and retrieve data using Pervasive.SQL Database Engines. More technically, it enables DataFlex programs running on a DataFlex Database API-enabled runtime (e.g., DataFlex or DataFlex Console Mode) to access data stored in a Pervasive.SQL database.
Pervasive.SQL is a Client/Server based database management system built around Pervasive Software's MicroKernel Database Engine.
In general, most existing DataFlex applications using the DataFlex Embedded Database will be able to use Pervasive.SQL for data storage, without changing the application. In some cases application changes may be necessary to achieve better performance.
The driver can be used to convert existing DataFlex data to the Pervasive.SQL format, and can also be used to access existing Pervasive.SQL data generated by other applications such as any of the popular accounting packages that use Pervasive.SQL as their database management system. In addition, the driver supports restructuring capabilities that enable Pervasive.SQL to be used as the database system in a DataFlex development environment.
Pervasive.SQL is a reliable, low-maintenance, high-performance; Client/Server based database management system (DBMS). Pervasive.SQL is built around the Micro Kernel Database Engine (MKDE), historically also know as the Btrieve engine.
The Pervasive.SQL engine consists of two database sub-engines:
MicroKernel Database Engine (MKDE) or transactional engine, which provides Btrieve and MicroKernel API support for Pervasive.SQL applications.
SQL Relational Database Engine (SRDE) or relational engine, which provides support for SQL-based applications.
The DataFlex Pervasive.SQL Driver uses the transactional interface to access the Pervasive.SQL data tables. The transactional (or Btrieve) interface provides the fastest way to access Pervasive.SQL data.
The driver creates and maintains tables in such a way that data created by the driver (through the transactional interface) can also be accessed by the relational interface. This makes it possible to access this data with any SQL (or ODBC) oriented application. The driver is also capable of connecting to any data that is created by external (SQL based) applications.
Pervasive.SQL is a Client/Server database management system. In a Client/Server database environment, the following two processes communicate:
The database client
The database server
The Client/Server architecture allows all data requests of a client to be executed by one or several servers. Thus, data manipulation is carried out by the client, and the server carries out data management/recording. This offers greater integrity and speed in the processing of information.
Pervasive.SQL servers can run on Microsoft Windows servers, Netware or Linux. Pervasive.SQL clients are available for Microsoft Windows and for Linux. The DataFlex Pervasive.SQL Driver can only be used with Windows clients.
In Pervasive.SQL V8.5 new security features were added that enable access to secured databases through the transactional engine. The driver fully supports these new security features and is capable of accessing Pervasive.SQL secured databases.
The Data Access Database API expects database drivers to support a certain set of attributes for a table. These attributes will describe the structure of a table. For example: which fields and indexes make up the table and how they are defined.
Attributes required by the API may or may not be available in the supported back-end. If an attribute is not available in the back-end it needs to be stored somewhere outside of the back-end database. The API uses so called “intermediate files” to store such information. The contents of an intermediate file are partly API defined and partly driver specific.
The DataFlex Pervasive.SQL Driver will use the Pervasive.SQL back-end for its attributes whenever possible. Some information can only be stored in the intermediate file because Pervasive.SQL does not support the attribute that is essential to DataFlex. In general, an intermediate file must be present for every table that is accessed through the driver. In some specific circumstances an intermediate file can be omitted.
Most attributes needed by the database API define structural information about the tables that are to be accessed by the driver. This structural information about files, fields and indexes is stored in the Pervasive.SQL metadata tables. These metadata tables have a DDF extension and are also referred to as the DDF tables.
Most Pervasive.SQL tools, including the SQL interface, use the DDF tables to provide the structure information about a Pervasive.SQL table. The DataFlex Pervasive.SQL Driver is fully based on these DDF tables.
There are three DDF tables: FILE.DDF, FIELD.DDF, and INDEX.DDF. These tables are just data tables like any other Pervasive.SQL table. Their format has been predefined so that any tool can use these tables to read and write information about a Pervasive.SQL table to it.
FILE.DDF contains information about Pervasive.SQL tables at the table level and has one record for every table. It describes the table’s name, location, and optionally extra flags.
FIELD.DDF contains information about the tables’ fields. Every field needs to be defined in this table before it can be accessed by one of the tools. The information stored about a field includes the table it belongs to, the offset the field starts in the record, its length, the number of decimals (if it’s a number) and extra flags.
INDEX.DDF contains information about the defined indexes for the tables. There is one record per index segment. Each segment describes to which table the segment belongs, its index number, its segment number and extra flags specifying more information about the segment.
Configuration of the Pervasive.SQL Server and Client components can be done from the Pervasive.SQL Control Center. Configuration settings can be changed in the ‘configuration’ branch in the tree view for every Pervasive.SQL engine.
The configuration parameters will be auto configured after initial installation and, in general, the driver will function with these initial settings. Most configuration parameters will re-configure themselves dynamically and don’t have to be changed after the initial installation. Some settings may need to be adjusted to get better performance.
Refer to the Pervasive.SQL documentation for further information on specific configuration settings.
The Pervasive Client Cache was introduced in Pervasive.SQL V8.0. Earlier versions don’t have a client cache. Client caching is by default turned on.
It is recommended to switch off the client cache when using the Pervasive.SQL Driver!
When using the client cache, updates of a record from one workstation may not immediately be visible on other workstations. There can be a considerable delay (up to 5 seconds) before a change is noted. This delay can cause data corruption.
When not updating the data, you may consider leaving the client cache on. For example, when running heavy reporting programs. This may increase performance significantly.
In Pervasive.SQL (earlier than V8.5), the client cache can be turned off with a setting of the client configuration.
In the Control Center select:
Configuration
Client
Performance tuning
Use Cache Engine
Note: You must turn the client cache off on all workstations!
In Pervasive.SQL V8.5 or later the client cache can be turned off with a setting at the server. In the Control Center select:
Configuration
Server
Access
Allow Cache Engine Connections
When using this server setting, you don’t have to switch off the client cache at the workstations.
Understanding How Connectivity Works