Devart Forums

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

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?

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.

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

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.

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.

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?

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.

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;

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.

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

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.

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

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

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