Pervasive.SQL Conversion Schema
This topic contains information about how the DataFlex Pervasive.SQL Driver converts existing data to Pervasive.SQL and which decisions it makes when creating or restructuring Pervasive.SQL tables through this driver.
DataFlex-to-Pervasive.SQL Conversion
When the driver converts a field, it selects the Pervasive.SQL field type that comes closest to the DataFlex field type. Pervasive.SQL only needs to know the field type of a piece of data when it is being used in an index so that it knows how to sort the index. Pervasive.SQL does not care about the type of the information being put into the field itself.
Other applications using the DDF tables maintained by the driver do care about a field’s format and contents.
The conversion function of the driver uses the following table to convert DataFlex field types to Pervasive.SQL field types.
| DataFlex Field Type | Pervasive.SQL Field Type |
|---|---|
| ASCII | STRING |
| NUMERIC | DECIMAL |
| DATE | DATE |
| OVERLAP | STRING |
| TEXT | LONGVARCHAR |
| BINARY | LONGVARBINARY |
ASCII Fields
A field of type ASCII in DataFlex will be converted to Pervasive.SQL’s STRING type. This type works exactly the way the DataFlex ASCII field type works.
NUMERIC or BCD Fields
A Numeric field in DataFlex will be converted to the Pervasive.SQL type DECIMAL. This is a COMP-3-compliant format. Fields in COMP-3 format take 1 nibble to store the sign. Therefore, the size of the field after it has been converted is somewhat different when compared to DataFlex. For example, when in DataFlex a field size has been set to 2, it will take 1 byte to store it. When this field is converted to Pervasive.SQL, it needs 1 nibble for the sign and 2 nibbles to store the digits. In Pervasive.SQL, this field will have a length of 2 bytes. Because 2-byte COMP-3 fields have room for 3 digits, it will report to the DataFlex API that the size is 3.
DATE Fields
Date fields will be stored in Pervasive.SQL’s native DATE format, which takes 1 byte to store the day, 1 byte to store the month, and 2 bytes to store the year.
Pervasive.SQL (applications) expects the dates to have 4-digit year values. When a field’s data is converted from DataFlex to Pervasive.SQL, it takes the value straight from the DataFlex field. If the value of the DataFlex date uses 2-digit years, it will also have 2-digit years in Pervasive.SQL. If it uses 4-digit years, it will have 4-digit years in Pervasive.SQL.
TEXT Fields
Text fields will be converted to the Pervasive.SQL LONGVARCHAR type. LONGVARCHAR is a variable length field type. The amount of data stored is the actual length of the data. So if a text field with a maximum length of 1000 characters contains 10 characters, only 10 characters will be stored.
When a table has LONGVARCHAR type fields, it will automatically have variable length records. The actual contents of the field will be stored in the variable part of every record. The fixed part will contain an 8-byte field that contains an offset (pointer) to the variable data and the length of the variable data.
There can be multiple text fields in a record/table.
NOTE
Earlier versions of the driver used different Pervasive.SQL types for text fields. Text fields that were the last field of the record would be converted to NOTE type, while text fields that are not the last field of the record would be converted to ZSTRING type.
For backward compatibility reasons, these types can still be generated during conversion. Specify:
TEXT_TYPE NOTE
in DFBTRDRV.INT to use the NOTE/ZSTRING types.
Note that NOTE and ZSTRING types are no longer supported in Pervasive.SQL.
BINARY Fields
Binary fields will be converted to the Pervasive.SQL LONGVARBINARY type. LONGVARBINARY is a variable length field type. The amount of data stored is the actual length of the data. So if a binary field with a maximum length of 1000 characters contains 10 characters, only 10 characters will be stored.
When a table has LONGVARBINARY type fields, it will automatically have variable length records. The actual contents of the field will be stored in the variable part of every record. The fixed part will contain an 8-byte field that contains an offset (pointer) to the variable data and the length of the variable data.
There can be multiple binary fields in a record/table.
NOTE
Earlier versions of the driver used the Pervasive.SQL STRING type for binary fields.
For backward compatibility reasons, this type can still be generated during conversion. Specify:
TEXT_TYPE NOTE
in DFBTRDRV.INT to use the STRING type.
OVERLAP Fields
Obsolete
Overlap columns were replaced by multi-segment relationships in DataFlex 11.0 and are now considered obsolete.
Overlap fields will be converted to the Pervasive.SQL STRING type. This means that in the DDF tables, where the fields are defined, there is more than one field occupying the same space in the record. There may be applications that cannot handle this kind of field. Currently, we don’t know of such an application, but you should know that an Overlap field is not a commonly used field type in Pervasive.SQL.
When the driver encounters an Overlap field, it will try to detect at what position the overlap field starts and at what position it ends. Based on that information, it will try to create the right overlap for it.
Overlap fields that overlap a number of fields exactly will also overlap those fields being converted to Pervasive.SQL. If the underlying fields acquired different sizes when converted to Pervasive.SQL, the driver will calculate the difference and modify the length of the overlap field automatically.
If an Overlap field does not start at the beginning of a field or end at the end of a field and the offending fields were ASCII fields in DataFlex, then the driver will automatically adjust the offset and/or offset of the overlap field.
If an Overlap field does not start at the beginning of a field or end at the end of a field and the offending fields were not ASCII fields in DataFlex, then the driver will create the overlap to cover the complete field(s). You will get a warning that it has done this. After this, you can adjust the overlap yourself using the DataFlex Database Builder.
Pervasive.SQL-to-DataFlex Conversion
The driver supports reading, writing, and restructuring tables that have been created or came with other applications that use Pervasive.SQL. Pervasive.SQL supports more field types than DataFlex, and therefore, we need to map the data from a certain Pervasive.SQL field type to a DataFlex field type.
The only field types currently not supported are BFLOAT and LVAR.
The following table lists the known Pervasive.SQL field types. For every Pervasive.SQL field type, the DataFlex field type is shown and the maximum physical length supported for that field. The length column shows the length as it will be reported back by the DF_FIELD_LENGTH attribute.
| Pervasive.SQL Type | DataFlex Type | Physical Length | Length | Remark |
|---|---|---|---|---|
| STRING (1) | ASCII | X (1-255) | X | |
| INTEGER | NUMERIC | 1,2,4,8 | 3,5,10,14 | Error when too large |
| IEEE (float) | NUMERIC | 4,8 | 22 | Precision is 8 |
| DATE | DATE | N/A | N/A | |
| TIME | NUMERIC | N/A | 8 | |
| DECIMAL | NUMERIC | x(1,10) | (x*2)-1 | |
| MONEY | NUMERIC | x(1,10) | (x*2)-1 | |
| LOGICAL | NUMERIC | 1 | 3 | |
| NUMERIC | NUMERIC | x (1-15) | X | |
| BFLOAT | NUMERIC | N/A | N/A | Not supported |
| LSTRING (1,2) | ASCII | x(1-256) | x-1 | |
| ZSTRING (1) | ASCII | x(1-256) | x-1 | |
| NOTE | TEXT | x(1-32767) | X | |
| LVAR | N/A | N/A | N/A | Not supported |
| BINARY | NUMERIC | 1,2,4 | 3,5,10 | |
| AUTOINC | NUMERIC | 1,2,4 | 3,5,10 | |
| NUMERICSTS | NUMERIC | x(1-15) | X | |
| NUMERICSA | NUMERIC | x(2-15) | x-1 | |
| LONGVARCHAR | TEXT | 8 + x | X | |
| LONGVARBINARY | BINARY | 8 + x | X | |
| CURRENCY | NUMERIC | 8 | 14.8 | |
| TIMESTAMP | NUMERIC | 14.4 | Decimal contains milliseconds |
(1) The size specified is the size officially supported by Pervasive.SQL. The driver, however, accepts larger sizes.
(2) Normally, the first byte of an LSTRING contains the length of the actual data. The driver also supports a format where the first two bytes contain the length of the actual format. It will do this when the physical length of the field is larger than 255.