Pervasive.SQL and Borland Data Types

This section details data type mappings.

Pervasive.SQL and Borland

The following table shows data type mappings from Pervasive.SQL column types to Delphi data types. Types on the left stored in Pervasive.SQL databases will be exposed by PDAC components as the types listed on the right


Pervasive.SQL Data Type	Delphi Data Type
Binary	ftBytes
Varbinary	ftVarBytes
Longvarbinary	ftBlob
Char	ftString
Varchar	ftString
Longvarchar	ftMemo
Bigint	ftBCD
Numeric	ftBCD
Decimal	ftBCD
Bit	ftBoolean
Integer	ftInteger
Smallint	ftSmallInteger
Tinyint	ftSmallInteger
Uint	ftInteger
Usmallint	ftWord
Utinyint	ftWord
Ubigint	ftBCD
Real	ftFloat
Double	ftFloat
Float	ftFloat
Currency	ftCurrency
Identity	ftAutoInc
Smallidentity	ftAutoInc
TimeStamp	ftDateTime
Date	ftDate
Time	ftTime

The following table shows data type mapping from Delphi to Pervasive.SQL data types. When new database tables are created using PDAC, columns defined as the field types listed in the lefthand column will be stored by Pervasive.SQL using the types listed on the right.


Delphi Data Type	Pervasive.SQL Data Type
ftAutoInc	Identity
ftBCD	Numeric
ftBlob,	Longvarbinary
ftBoolean	Bit
ftBytes	Binary
ftCurrency	Currency
ftDate	Date
ftDateTime	DateTime
ftFixedChar	Char
ftFloat	Double
ftFmtMemo	LongVarChar
ftGraphic	Blob
ftInteger	Integer
ftLargeInt	BigInt
ftMemo	Longvarchar
ftSmallInteger	Smallint
ftString	Varchar
ftTime	Time
ftTypedBinary	Binary
ftVarBytes	VarChar
ftWord	Smallint

Btrieve and Borland

The following table shows data type mapping from Btrieve to VCL.

Note
Binary flags refer to a flag in X$Fields.Xe$Flags


Btrieve Data Types	Binary flag	Length in bytes	VCL Data Types
STRING (0)	+		ftBytes
STRING (0)	-		ftString
INTEGER (1)		1	ftSmallint
INTEGER (1)		2	ftSmallint
INTEGER (1)		4	ftInteger
INTEGER (1)		8	ftBCD
FLOAT (2)			ftFloat
DATE (3)			ftDate
TIME (4)			ftTime
DECIMAL (5)			ftBCD
MONEY (6)			ftBCD
LOGICAL (7)		1	ftBoolean
LOGICAL (7)		2	ftSmallint
NUMERIC (8)			ftBCD
BFLOAT (9)			ftFloat
LSTRING (10)	+		ftVarBytes
LSTRING (10)	-		ftString
ZSTRING (11)			ftString
NOTE (12)	+		ftBlob
NOTE (12)	-		ftMemo
LVAR (13)	+		ftBCD
LVAR (13)	-		ftMemo
UNSIGNED BINARY (14)		1	ftWord
UNSIGNED BINARY (14)		2	ftWord
UNSIGNED BINARY (14)		4	ftInteger
UNSIGNED BINARY (14)		8	ftBCD
AUTOINCREMENT (15)			ftAutoInc
BIT (16)			ftBoolean
NUMERICSTS (17)			ftBCD
NUMERICSA (18)			ftBCD
CURRENCY (19)			ftBCD
TIMESTAMP (20)			ftDateTime
BLOB (21)	+		ftBlob
BLOB (21)	-		ftMemo

Additional Information Regarding Table Creation with TPvTable

Call the CreateTable method at runtime to create a table using this dataset's current definition.

If the FieldDefs property contains values, these values are used to create field definitions. Otherwise the Fields property is used. One or both of these properties must contain values in order to recreate a dataset.

Use the Add method to assign field properties

procedure Add(const Name: string; DataType: TFieldType; 
Size: Word; Required: Boolean);

Add is provided for backward compatibility. The recommended way to add new field definitions to the Items property array is using the AddFieldDef method. You should also use it to specify precision and scale for the ftBCD data type.

Add uses the values passed in the Name, DataType, Size, and Required parameters and assigns them to the respective properties of the new field definition object.

Note
Set "Required" to False if field is nullable. To activate the autoincrement property for AutoInc field, you have to create a unique index on this field.

Delphi Example:

PvTable1.DatabaseName := 'TestData'; 
PvTable1.TableName := 'TestData1'; 
with PvTable1.FieldDefs do 
begin 
    Clear; 
  Add('F_autoinc',   ftAutoInc, 0, True); 
  Add('F_currency', ftCurrency, 0, False); 
  Add('F_integer',    ftInteger, 0, False); 
  Add('F_word',       ftWord, 0, False); 
  Add('F_fixchar',    ftFixedchar, 30, False); 
  Add('F_varbin',     ftString, 25, False); 
  Add('F_blob',        ftBlob, 60, False); 
end; 
with PvTable1.FieldDefs.AddFieldDef do 
begin 
  Name := 'F_BCD'; 
  DataType := ftBCD; 
  Size:=2;     //Scale 
  Precision := 10;  //Precision 
  Required := false; 
end; 
with PvTable1.IndexDefs do 
begin 
  Clear; 
  Add('Index1', 'F_autoinc', [ixPrimary, ixUnique]); 
  Add('Index2', 'F_integer', [ixCaseInsensitive]); 
end; 
PvTable1.CreateTable;

C++ Builder Example:

PvTable1->DatabaseName="TestData"; 
PvTable1->TableName="Test1"; 
PvTable1->FieldDefs->Clear(); 
PvTable1->FieldDefs->Add("F_autoinc", ftAutoInc, 0, 
True); 
PvTable1->FieldDefs->Add("F_integer", ftInteger, 0, 
False); 
PvTable1->FieldDefs->Add("F_Curr", ftCurrency, 0, 
False); 
PvTable1->FieldDefs->Add("F_Word", ftWord, 0, False); 
PvTable1->FieldDefs->Add("F_fixchar", ftFixedChar, 0, 
False); 
PvTable1->FieldDefs->Add("F_String", ftString, 20, 
False); 
PvTable1->FieldDefs->Add("F_blob", ftBlob, 60, False); 
TFieldDef *FieldDef = PvTable1->FieldDefs-
>AddFieldDef(); 
FieldDef->Name="F_BCD"; 
 FieldDef->DataType=ftBCD; 
FieldDef->Size=2; 
FieldDef->Precision=10; 
FieldDef->Required=False; 
PvTable1->IndexDefs->Clear(); 
PvTable1->IndexDefs-> 
Add("Index1","F_autoinc",TIndexOptions() 
<<ixPrimary << ixUnique); 
PvTable1->CreateTable();

The following table shows data type mapping from VCL to Btrieve.

Note
Binary flags refer to a flag in X$Fields.Xe$Flags


VCL Data Types	Btrieve Type	Binary flag	Length, bytes
ftAutoInc	AUTOINCREMENT (15)		4
ftBCD	NUMERIC (8)
ftBlob	BLOB (21)	+	FieldDefs[].Size
ftBoolean	LOGICAL (7)		1
ftBytes	STRING (0)	+	FieldDefs[].Size
ftCurrency	CURRENCY (19)
ftDate	DATE (3)
ftDateTime	TIMESTAMP (20)
ftFixedChar	STRING (0)	-	FieldDefs[].Size
ftFloat	FLOAT (2)		8
ftFmtMemo	BLOB (21)	-	FieldDefs[].Size
ftGraphic	BLOB (21)	+	FieldDefs[].Size
ftInteger	INTEGER (1)		4
ftLargeint	INTEGER (1)		8
ftMemo	BLOB (21)	-	FieldDefs[].Size
ftSmallint	INTEGER (1)		2
ftString	ZSTRING (11)	-	FieldDefs[].Size
ftTime	TIME (4)
ftTypedBynary	STRING (0)	+	FieldDefs[].Size
ftVarBytes	LSTRING (10)	+	FieldDefs[].Size
ftWord	UNSIGNED BINARY (14)		2

Additional Field Type Information

There is a Pervasive-specific table create method (TPvTable.PvCreateTable) that allows you to tune additional parameters related to field types. It has the following definition:

Procedure PvCreateTable(PvFieldDefs: TPvFieldDefs)

In PvFieldDefs you can adjust several parameters:

TPvFieldDef = class(TCollectionItem) 
public 
  FieldNum: integer; 
  BtrType: integer; 
  DrmType: word; 
  ColumnSize: integer; 
  DefaultValue: string; 
  IsColumnCaseInsensitive: boolean; 
  ACS_FileName: string; 
  ACS_Name: string; 
  ACS_ID: string; 
end;

Where:

`FieldNum' - field number in primary FieldDefs.

`BtrType' - Btrieve type.

`DrmType' - DRM type. Developer does not need to use this field. It added for use in TPvBatchMove only.

`ColumnSize' - Column size in bytes.

`DefaultValue' - Default value for column in string format.

`IsColumnCaseInsensitive' - Set it in true if indexes for field are case insensitive.

`ACS_FileName' - Set file name with ACS data without .alt extension.

`ACS_Name' - Set name of ACS data.

`ACS_ID' - Set ID of ACS data.

If you do not want to set a particular field, it can be set to 0 (DrmType, ColumnSize), -1 (BtrType), false (IsColumnCaseInsensitive) or `' (all string fields). In this case, default values will be used. Field `FieldNum' is required.

Fields `IsColumnCaseInsensitive', `ACS_FileName', `ACS_Name', `ACS_ID' are mutually exclusive. That is, you can set only one of them.

To match Btrieve behavior, index option `ixCaseInsensitive' is ignored

The following is an example of using the PvCreateTable method:

with PvTable1.FieldDefs do 
begin 
  Clear; 
  Add('F_AutoInc', ftAutoInc, 0, true); 
  Add('F_Bytes', ftBytes, 10, False); 
end; 
PvFieldDefs := TPvFieldDefs.Create(TPvFieldDef); 
try 
  PvFieldDef := PvFieldDefs.Add(); 
  PvFieldDef.FieldNum := 1; // F_Bytes 
  PvFieldDef.BtrType := 10; 
  PvFieldDef.DrmType := DRM_coltypVarText; 
  PvFieldDef.ColumnSize := 20; 
  PvFieldDef.IsColumnCaseInsensitive := true; 
  PvTable1.PvCreateTable(PvFieldDefs); 
finally 
  PvFieldDefs.Free(); 
end;