Pervasive PSQL and Embarcadero Data Types
This section details data type mappings.
•
Pervasive PSQL and Embarcadero Data Type Mappings
•
Btrieve and Embarcadero Data Type Mappings
Pervasive PSQL and Embarcadero Data Type Mappings
The following table shows data type mappings from Pervasive PSQL column types to Delphi data types. Types on the left stored in Pervasive PSQL databases will be exposed by PDAC components as the types listed on the right
Table 4
Data Type Mappings from Pervasive PSQL to Embarcadero Delphi
Pervasive PSQL Data Type
Delphi Data Type
BigInt
ftBCD
Binary
ftBytes
Bit
ftBoolean
Char
ftString
Currency
ftCurrency
Date
ftDate
Decimal
ftBCD
Double
ftFloat
Float
ftFloat
Identity
ftAutoInc
Integer
ftInteger
Longvarbinary
ftBlob
Longvarchar
ftMemo
Numeric
ftBCD
Real
ftFloat
Smallidentity
ftAutoInc
Smallint
ftSmallInteger
Time
ftTime
TimeStamp
ftDateTime
Tinyint
ftSmallInteger
Ubigint
ftBCD
Uint
ftInteger
Usmallint
ftWord
Utinyint
ftWord
Varbinary
ftVarBytes
Varchar
ftString
The following table shows data type mapping from Delphi to Pervasive PSQL data types. When new database tables are created using PDAC, columns defined as the field types listed in the left hand column will be stored by Pervasive PSQL using the types listed on the right.
Table 5
Data Type Mappings from Embarcadero Delphi to Pervasive PSQL
Delphi Data Type
Pervasive PSQL 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 Embarcadero Data Type Mappings
The following table shows data type mapping from Btrieve to VCL.
Note:
Binary flags refer to a flag in X$Fields.Xe$Flags
Table 6
Data Type Mappings from Btrieve to VCL
Btrieve Data Types
Binary flag
Length in bytes
VCL Data Types
AUTOINCREMENT (15)
ftAutoInc
BFLOAT (9)
ftFloat
BIT (16)
ftBoolean
BLOB (21)
+
ftBlob
BLOB (21)
-
ftMemo
CURRENCY (19)
ftBCD
DATE (3)
ftDate
DECIMAL (5)
ftBCD
FLOAT (2)
ftFloat
INTEGER (1)
1
ftSmallint
INTEGER (1)
2
ftSmallint
INTEGER (1)
4
ftInteger
INTEGER (1)
8
ftBCD
LOGICAL (7)
1
ftBoolean
LOGICAL (7)
2
ftSmallint
LSTRING (10)
+
ftVarBytes
LSTRING (10)
-
ftString
LVAR (13)
+
ftBCD
LVAR (13)
-
ftMemo
MONEY (6)
ftBCD
NOTE (12)
+
ftBlob
NOTE (12)
-
ftMemo
NUMERIC (8)
ftBCD
NUMERICSA (18)
ftBCD
NUMERICSTS (17)
ftBCD
STRING (0)
+
ftBytes
STRING (0)
-
ftString
TIME (4)
ftTime
TIMESTAMP (20)
ftDateTime
UNSIGNED BINARY (14)
1
ftWord
UNSIGNED BINARY (14)
2
ftWord
UNSIGNED BINARY (14)
4
ftInteger
UNSIGNED BINARY (14)
8
ftBCD
ZSTRING (11)
ftString
The following table shows data type mapping from VCL to Btrieve.
Table 7
Data Type Mappings from VCL to Btrieve
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
Note:
Binary flags refer to a flag in X$Fields.Xe$Flags
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’ – data record manager type. Developer does not need to use this field. It was 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;