Btrieve Data Concepts¶

Handling Variant Records¶

Variant records live up to their name. They can display in various ways, depending on some known flag or tag. In C, this is known as a union and typically has a value in the first part of the record that dictates the appearance of the rest of the record. Before the advent of SQL and relational databases, this kind of storage was popular and Btrieve is quite famous for supporting Variant records.

Let’s examine a simple example in C:

struct Correspondence
{
  char name[30];
  BDATE date;
  int type;  // 1=email, 2=mail, 3=fax
  union {
    char emailAddr[150];
    AddrInfo addressInfo;  //struct size is 300
    char faxNum[15];
        };
}; //Correspondence

EmailAddr, addressInfo, and faxNum share the same space in the record and therefore cannot be defined as separate fields. When Pervasive introduced SQL access to the data, one way to see this data was to define three different tables, all using the same MicroKernel file. All three tables start with name, date, and type. One table also includes emailAddr, the second table includes addressInfo, and the third includes faxNum. When you use SQL to access the data, you must include an extra Where clause. For example, if you want all of the mail records, you might execute SELECT * from Coor_Mail where type=2.

Currently, the Mertech drivers do not provide any special handling to make this functionality operate. If you give the Migration Utility the command to migrate all three tables, then you will have the same records in each of the three tables. The records that do not belong will have garbage in the columns within the union portion. You need to allow this data to be used in a fully-SQL backend and to treat the union portion as binary data.

Think of the record as follows:

struct Correspondence
{
    char name[30];
    BDATE date;
    int type;  // 1=email, 2=mail, 3=fax
    unsigned char unionData[300];
}; //Correspondence

The union is replaced with an array of bytes. This does not need to be done in the C code; it is just another way to view the record. However, you will cause the Pervasive.SQL table to appear as shown in the above example. Note the size of the binary field. It must be at least as large as the largest piece in the union.

Therefore, use syntax similar to this:

CREATE TABLE Correspondence IN DICTIONARY
USING 'correspondence.mkd' (
    name VARCHAR(29) not null,
    deliveryDate DATE not null,
    recType INTEGER not null,
    unionData BINARY(300) not null
)

The IN DICTIONARY Clause¶

When you create DDFs for existing MicroKernel files, you must be careful not to overwrite the existing file or its indexes. As shown above in the variant record example, the IN DICTIONARY clause can be used. This lets the Pervasive.SQL engine update only the DDF files and not create the associated data file.

The same logic applies to indexes. For example:

CREATE INDEX idxRecType IN DICTIONARY ON Correspondence (recType)

Nullable Columns and the True-Null Extra Byte¶

Starting with Pervasive.SQL 2000, a new type of Null Indicator was introduced called True Null. Refer to Key Attributes in the Pervasive.SQL Programmer’s Guide for details. In short, when a table is created with True Nulls, each nullable column is shifted by one byte. This byte is used as a Boolean value and indicates whether the column is null or has a real value.

For example:

// CREATE TABLE SomeNulls ( C1 integer, C2 integer ) needs a C struct as follows:
struct SomeNullsRec {
  bool nullC1; int C1;
  bool nullC2; int C2
};

If you are creating table definitions for existing data files, it is unlikely that they have this extra Boolean byte. Therefore, make sure that you include the not null clause on each column as demonstrated in the variant record example.

CREATE TABLE NoNulls
( C1 integer not null, C2 integer not null )

or:

SET TRUENULLCREATE=OFF;
CREATE TABLE LegacyNulls
( C1 integer, C2 integer )

Restart the Pervasive.SQL Engine After Modifying Table Definitions¶

Some versions of the SQL engine within PSQL may remember older definitions of tables after you have modified them. Some definitions may appear to be inaccurate when you visually examine the data in the Control Center. If you suspect that this is the case, then we recommend that you restart the PSQL engine after each major modification to the table structures.