|
<< Click to Display Table of Contents >> dBASE: Reverse CREATE modes |
  
|
|
<< Click to Display Table of Contents >> dBASE: Reverse CREATE modes |
|
As the nature of numeric fields in standard dBASE (III+, IV and 5) tables - due to their text based decimal based levels - are not readily compatible with the in most other databases used binary defined numeric types SmallInt (16-bit, ≡ Short), Integer (32-bit, ≡ Long) and Float (64-bit, ≡ Double), conversion between these numeric types comes not without issues.
This has been the basis for introducing user selectable Reverse SQL numeric modes via the menu [rev.SQL]:
The PdxEditor [rev.SQL] sub-menu [dBASE: Reverse SQL Numerics].
• Mode 0: This is intended for generating SQL statements used to make structural exact copies of the dBASE source numeric fields using the SQL DECIMAL(s,p) syntax.
It may be noted, that the SQL DECIMAL(s,p) form is the only mode creating the standard NUMERIC type fields across all dBASE versions.
• Mode 1 to 4: These are meant for increasing cross database system compatibility concerning numeric units.
Please see the SQL data type translation BDE.
Taking a dBASE 4 table with a range of numerically specified fields like:
we may either want to re-create an exact copy getting the traditional dBASE III+, IV or 5 type NUMERIC(s,p) fields, or to generate a structure compatible with either the dBASE 7 Long and Double fields, the Paradox style SmallInt, Integer and Float fields, or for re-creating the tables in a completely distinct third type of database.
Example selecting either 4: Binary Scale Generic or 3: Strict Safe Forward Compatible conversion on the same dBASE table.
Each field created via SQL as named - e.g. FNUM040 as DECIMAL(04,0) giving a field of type NUMERIC(4,0).
When creating standard dBASE tables with SQL, INTEGER will result in NUMERIC(11,0) and SQL FLOAT will create NUMERIC(20,4).
When reverse interpreting dBASE NUMERIC fields for practical normal use, it may seem reasonable to use the same upper Integer cut off above NUMERIC(11,0), even though the max. positive number of the 32-bit signed Integer is the 10-digit value 2.147.483.647.
As 10-digit fields may contain up to the value 9.999.999.999 - to be strict, the cut off from Integer to the 64-bit Float should actually be values above NUMERIC(9,0), as may be selected.
The practical drawback with this mode is, that standard dBASE numbers gradually will migrate from Integer to Float type fields for each conversion back and forth.
A problem avoided with Paradox or sticking to dBASE 7 tables, both using binary type numerics, Paradox exclusively and dBASE 7 as a default.