BINARY DOUBLE datatype

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

BINARY DOUBLE datatype

Post by LeifS » Thu 13 Nov 2008 10:35

Hi,

I have a table containing a BINARY DOUBLE field. When data is saved into this field, I think the dbxOda driver (v3.0) truncates the precision of the data, at least it seems so when looking at the data using Oracle SQL*Plus.

How should I configure the driver to handle double precision data in BINARY DOUBLE fields correctly?

Kind regards
Leif Suonvieri

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Thu 13 Nov 2008 12:35

more info:

The field maps to the VCL class TFMTBCDField.
The class property Precision is set to 32.

My TSQLConnection component doesn't seem to have all settings specified compared to the default "ORACLE BY CORE LAB" connection.
For example, my connection doesn't have the EnableBCD setting (if thats is important in my case).

Should I've used the TCRSQLConnection instead of TSQLConnection?

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Fri 14 Nov 2008 11:53

You can set the EnableBCD driver option to False in two ways:
1. Use the TCRSQLConnection component, and add EnableBCD=False to its Param property.
2. For the standard TSQLConnection component you can set the option using the following code:

const
coEnableBCD = TSQLConnectionOption(102); // boolean
. . .
SQLConnection1.SQLConnection.SetOption(coEnableBCD, Integer(False));

In this case BINARY DOUBLE will be mapped on TFloatField.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Fri 14 Nov 2008 12:11

Thanks for your answer.

Just to clarify things:
When I write a small value, say 1e-20, the value is rounded to zero.
Is this an expected behaviour?

Will this rounding disappear by setting EnableBCD to false?
If so, will that change the mapping for my other fields that use NUMBER(38) as a storage for long integer values?

Would redesigning the table using DOUBLE PRECISION instead of BINARY_DOUBLE help in any way?

/Leif

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Tue 18 Nov 2008 08:21

If you set EnableBCD to False, values like 1e-20 are not rounded to zero.
NUMBER(38 ) is also mapped to TFloatField in this case.

Using DOUBLE PRECISION instead of BINARY_DOUBLE will not help. TBcd struct cannot hold floating point values like 1e-20. So these values are rounded to zero. You should set EnableBCD to False for using DOUBLE PRECISION or BINARY_DOUBLE column.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Thu 29 Jan 2009 14:59

OK, I have tested some different values with IntegerPrecision and FloatPrecision and it works like expected.

I've used IntegerPrecision=11 and FloatPrecision=38 with the following redesigned table:

CREATE TABLE T_MeasData
(
SeqNo NUMBER(10),
MeasInstrID NUMBER(10) NOT NULL,
NumValue BINARY_DOUBLE NOT NULL,
Name VARCHAR2(20),
Status NUMBER(10),
SampleTime TIMESTAMP
);

This gives me what I want, i.e. SeqNo maps to TIntegerField and NumValue maps to TFloatField.

The problem is that my database have a lot of other tables containing NUMBER(38) fields, which I want to map to TIntegerField.
Is it possible to do this in any way?
I wouldn't like to have to redesign the rest of the database.

Why does the driver map a BINARY_DOUBLE to anything other than TFloatField anyway? It doesn't make sense, do it?

If BINARY_DOUBLE always were mapped to TFloatField regardless of any existing precision settings it would both be more logical, and probably map to the most common use case.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Thu 29 Jan 2009 15:27

More tests...
my original table looks like this:

CREATE TABLE T_MEASDATA (
SEQNO NUMBER(38),
MEASINSTRID NUMBER(38) NOT NULL,
NUMVALUE BINARY_DOUBLE NOT NULL,
NAME VARCHAR2(20),
STATUS NUMBER(38),
SAMPLETIME TIMESTAMP
);

Setting both IntegerPrecision and FloatPrecision to 38 seems to do what I ask for.

But this feels a bit unpredictable.
Can you explain why this works?
Has it any side effects?

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Fri 30 Jan 2009 13:09

We use TFmtBCDField for BINARY_DOUBLE and DOUBLE PRECISION for compatibility with the Borland driver, which uses such mapping.

If you set EnableBCD=False, this is equivalent to setting IntegerPrecision=9 and FloatPrecision=38. In this case NUMBER with precision <= 9 is mapped to TIntegerField. NUMBER with greater precision is mapped to TFloatField because Delphi Integer data type can hold only 9 decimal digits.

You are right. You need to set IntegerPrecision=38 and FloatPrecision=38. IntegerPrecision affects fields with zero scale. If you set it to 38, all such fields are mapped to Integer. FloatPrecision affects any fields but it is checked after IntegerPrecision.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Wed 11 Feb 2009 15:59

I've tested the above setting (both Integer- and FloatPrecision to 38) and it works for Table and Query based datasets, but it seems that my stored procedures maps my NUMBER parameters incorrect .
When I link my TSQLStoredProc components to the procedures they create ftFloat parameters instead of ftInteger.

Is this behaviour a bug of dbExpress / dbxOda?
Can change this somehow with perhaps a driver parameter or do I have to redeclare my NUMBER parameters in any way?

example of my SP declaration:

CREATE PACKAGE pkg_Measure AS
PROCEDURE InsOne
(p_MeasureID IN OUT NUMBER,
p_MeasureNo NUMBER,
p_Name VARCHAR2,
p_MeasComment VARCHAR2);
END pkg_Measure;

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Mon 16 Feb 2009 07:56

This problem can be caused by incorrect design-time settings.

If you set the StoredProcName property at design-time, parameters are created automatically. These parameters will have correct type if you set IntegerPrecision and FloatPrecision at design-time. In Delphi 2007 and higher you can set these options in the Params property of TSQLConnection. For older Delphi versions you should use TCRSQLConnection component instead of TSQLConnection. You can set the options in the Params property of TCRSQLConnection.

Note that when you make correct settings, NUMBER parameters are mapped on ftFloat (not ftInteger) because they can contain fractional values.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Thu 19 Feb 2009 08:04

I use TCRConnection at designtime and have set the precision for both Integer and Float as discussed earlier.
Plash wrote: Note that when you make correct settings, NUMBER parameters are mapped on ftFloat (not ftInteger) because they can contain fractional values.
OK, and this is exactly what my question was aimed at; can I somehow change this behaviour for stored procedure parameters declared as NUMBER?

I can't change the declaration of the parameters since Oracle doesn't support a declaration with a specific precision. (e.g. NUMBER(9))
I've tried with declaring them as INTEGER, but the result is the same (INTEGER is just an alias for NUMBER).

Is there a way to override the default mapping without getting runtime exceptions due to wrong datatype?

Kind regards / Leif

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Thu 19 Feb 2009 09:55

We'll fix this problem in the next build of DbxOda.
Parameters of INTEGER data type will be mapped to ftInteger. And parameters of NUMBER data type will be mapped to ftFloat.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Thu 19 Feb 2009 13:04

Wonderful!

This will be great.
and even more if you also could backport that change into v3.0
Even though I already have upgraded my license, I don't yet have the possibility to upgrade the actual application, which still uses v3.
(Naturally this implies an extra switch in the dbxconnections.ini to preserve compatibility.)

/Leif

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Mon 23 Feb 2009 09:10

We will not add the fix to DbxOda 3. Please tell us what problems or incompatibilities you have with the latest version.

LeifS
Posts: 15
Joined: Sat 02 Sep 2006 07:29
Location: Sweden

Post by LeifS » Tue 24 Feb 2009 07:42

OK, I'll have to work harder to upgrade my application then.
I haven't discovered any other problems with dbxOda yet, that stops me from upgrading.
The incompabilities sofar seems more related to the new C++Builder, but I'm sure I will solve that soon.
Best regards
Leif Suonvieri

Post Reply