Writing FLOATs to dBase III tables

[Dmitry Samokhin]

I'm developing an application which reads data from a server-side database
and exports it to DBF (dBase III format) tables. To perform this, the ADO
and the Jet Database Engine is utilized. But when writing fields of 'float'
datatype the engine rounds them incorrectly (always truncates): my DBF has,
for exampe, a field of type NUMERIC(15,5), and the double-precision value
4.78457852348625 is written to this field as 4.78457, not 4.78458 as
expected.

Unfortunalely, there are some machines where it rounds correctly, but some
machines where is does not. I can't find the cause.

Here is my environment:
Win XP SP2 (and Win Srv 2003)
File versions:
msjet40.dll - 4.0.8618.0
msjetoledb40.dll - 4.0.8227.0
msxbde40.dll - 4.0.8025.0
ADO connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<my
dir>;"Extended Properties=dBASE III;"

Thanks for any help.
Dmitry.

 

[onedaywhen]

I'm developing an application which reads data from a server-side database
and exports it to DBF (dBase III format) tables. To perform this, the ADO
and the Jet Database Engine is utilized. But when writing fields of 'float'
datatype the engine rounds them incorrectly (always truncates): my DBF has,
for exampe, a field of type NUMERIC(15,5), and the double-precision value
4.78457852348625 is written to this field as 4.78457, not 4.78458 as
expected.

Your expectations may be at fault <g>.

Jet's own DECIMAL type (maps to ADODB.DataTypeEnum.adNumeric and has
NUMERIC as a synonym) exhibits symmetric truncation e.g. the following
Jet SQL syntax:

create TABLE TestDecimals (
float_col FLOAT NOT NULL,
dec_col NUMERIC(15,5)
)
;
INSERT INTO TestDecimals (float_col)
VALUES (4.78457852348625)
;
UPDATE TestDecimals
SET dec_col = float_col
;
SELECT dec_col
FROM TestDecimals
;

returns 4.78457.

Unfortunalely, there are some machines where it rounds correctly, but some
machines where is does not. I can't find the cause.

That may be due to the behaviour of floating point values being
hardware-dependent but that doesn't normally get exhibited at 6sf.

The rule of thumb is to declare a scale that is one greater than the
scale you require, i.e. in your case NUMERIC(16,6), so that you will
have an extra significant figure with which to perform *explicit*
rounding with the algorithm of your choice, rather than leave it to the
whim of the data type/SQL platform/hardware.

Alternatively, do the rounding in Jet, noting that different
functionality exhibit different rounding behaviour (ROUND=banker's,
FORMAT=rounds 5 away from zero, FIX=
truncates symmetrically, INT=truncates asymmetrically, etc) e.g.

INSERT INTO [dBase III;Database=<my dir>;].TestNumerics (numeric_col)
SELECT ROUND(float_col, 5)
FROM TestDecimals
;

Jamie.

--