Before You Begin¶

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.

DDF Validator command-line syntax¶

Select Mertech’s ISDBC Drivers for Btrieve | DDF Validator from the Windows Start menu. Since this is a command-line utility, a Command Prompt is automatically opened.

Run the DDFValidator script by typing the following at the command prompt:

DDFValidator [-dblocation <DDF Folder>] [-log <log file name>]
[-table <table name>] [-max-warnings n] [-max-rows n]
[-ignore <error list>] [-?]

-dblocation: The full path to the Pervasive.SQL DDFs that define the database schema. If this is not provided, the current directory is assumed.

-log: Send the report (XML format) to the specified file. When -log is not included, the XML is sent to stdout. When -log is provided, the XML is sent to the file and only the error messages display so you can see that the tool is working and not hung.

‑table: By default, all tables are tested. The ‑table parameter allows you to narrow the tests by specifying one or more tables. Use semicolons to separate the list; or include the ‑table parameter multiple times.

-max‑warnings: Stops processing after <n> errors.

-max‑rows: Allows you to narrow the testing time by reducing the number of rows checked. If your table has 10 million rows, it is likely that after approximately the first million, the rest of the rows will validate the same way.

-ignore: Error codes to ignore - separated by semicolons. If you receive many of the same warning messages and you have verified that it is not really a problem for your database, then you can filter it out to avoid littering the output.

Parameters with brackets [] are optional. Surround names with quotes if needed (space in the name).

Example: Test the Pervasive.SQL Demodata database; send report to XML file:

DDFValidator -dblocation "c:\pvsw\demodata" -log DemoDataTest.xml

DDF Validator errors¶

Database

• (4000) More than one table defined for the same Btrieve file.

• (4001) Btrieve file found in database folder that is not defined in the DDFs.

Table

• (3000) Number of rows read did not match the number reported by B_STAT.

• (3001) Table, Column, or Index name is invalid for SQL.

• (3002) Obsolete.

• (3003) Not all of the record is defined by fields.

• (3004) Record length is too large or small.

• (3005) ACS / ISR is not supported by the Mertech drivers at this time.

Columns

• (2000) Column defined outside record length.

• (2001) NumericSTS data type is not currently support by the ISDBC drivers.

• (2002) Length of field is too large or small for the data type.

• (2003) Two or more fields are overlapping.

Indexes

• (5000) A column appears more than once in an index definition.

• (5001) A Key is defined in the Btrieve file but not in the DDFs.

• (5002) An index is defined in the DDFs but not in the Btrieve file.

Rows

• (1000) Field defined as ZString/String/LString but appears to have binary data.

• (1001) Value not recognizable for the defined data type.

• (1002) BIT columns should only be 0 or 1 (normally).

• (1003) Field defined as ZString but did not have a null terminator.

• (1004) Characters beyond the terminator character of a string. May be bytes from adjacent field due to alignment problem.

• (1005) Value for Null-Indicator-Segment (NIS) should normally be either 0 or 1.

• (1006) Length byte for LString is longer than defined field length.

• (1007) Timestamp value looks suspicious. While almost any value is “valid”, a really old or in the future timestamp may mean trouble.

General errors

• (9000) Unexpected Btrieve error.

• (9001) This tool does not support … (a data type, etc.).

• (9002) Btrieve returned an unexpected data length value.

Sample output from the DDF Validator tool¶

<code>
<report>
  <timestamp time="Mon Jan 02 13:27:17 2006" />
    <database location="D:\DDFVALIDATOR\TESTDATA\">
      <warning severity="1" code="4001">
        "Btrieve file 'NotATable.btr' found in database location
        but is not defined in the table list."
      <suggestion> "Should this file have a table definition?"
 </suggestion>
    </warning>
      <warning severity="2" code="4000">
        "Table T1 and T2 are both defined as Btrieve file
        D:\DDFVALIDATOR\TESTDATA\t1.mkd. This is illegal."
          <suggestion> "Remove one of the table
          definitions." </suggestion>
    </warning>
      <table name="T10" recordSize="42" flags="64" numRecords="1">
         <location path="D:\DDFVALIDATOR\TESTDATA\T10.mkd" />
         <column name="In va#l*id" type="4-TIME" offset="1"
 size="4" flags="4">
              <warning severity="1" code="3001" column="In va#l*id">
              "Column name is not a valid SQL identifier. This may
              cause problems with SQL queries later."
                <suggestion> "Names should only contain a space,
 underscore, or alpha-numeric characters" </suggestion>
           </warning>
         </column>
         <rows>
            <warning
 severity="3" code="1001" recordNum="1" column="In va#l\*id">
              "Error reading data - Bad time [145:01:145.1]."
              <suggestion> "Check the offset and length of the
 field." </suggestion>
            </warning>
            <warning
 severity="2" code="1000" recordNum="1" column="C1">
              "Field has binary data.  '1' found at offset 1 in
 the field."
              <suggestion>
                "This may mean the field should be defined as
 Binary instead of Text."
               </suggestion>
            </warning>
            <warning
 severity="3" code="1006" recordNum="1" column="C2">
                "Length byte indicates value (103) which is larger
 than defined field size of 42"
                <suggestion> "Either the data is corrupt or the
 field is misaligned." </suggestion>
            </warning>
         </rows>
         <results recordsTested="1" validRecords="0" />
      </table>
      <table name="T4" recordSize="0" flags="0" numRecords="0">
         <location path="D:\DDFVALIDATOR\TESTDATA\t4.mkd" />
         <column name="c1" type="1-INTEGER" offset="1" size="4"
 flags="4">
            <warning severity="3" code="2000" column="c1">
               "Column defined beyond Btrieve record size."
               <suggestion> "Columns must fit within defined record
 length." </suggestion>
            </warning>
         </column>
         <column name="field_0" type="0-STRING" offset="0"
 size="10" flags="0">
            <warning severity="3" code="2003" column="field_0">
              "Column overlaps with 'field_1'.  This is illegal
 for SQL access."
            </warning>
         </column>
         <warning severity="2" code="3003">
              "Some portions of the record are not defined by a
 column."
              <suggestion> "Ensure fields are defined for the entire
 Btrieve record." </suggestion>
         </warning>
         <results recordsTested="0" validRecords="0" />
      </table>
   </database>
   <results tablesTested="16" recordsTested="30" validRecords="13"
 />
</report>