ClientDataSet rounds number to 15 digits

Question

I have a Delphi XE application, which accesses an Oracle XE database using a TClientDataSet and a TDataSetProvider plus a TADOQuery behind it. The table I want to write into has a NUMBER(19) field.

I am writing a value into it like this:

myDataModule.myClientDataSet.FieldByName('ID').AsLargeInt := ID;

Let's say I have an ID like 1234567890123456789 it gets rounded to 1234567890123460000.

How can I avoid that?

Can you check myDataModule.myClientDataSet.FieldByName('ID').DataType? — Uwe Raabe, Aug 17 '12 at 15:53
That is giving me "Not available value", though stepping through the DB.pas code shows that it is incorrectly recognized as TFloatField. Explicitly casting the field for the assignment doesn't help. So I need to find out how to force ftLargeint on creation as Arnaud said. — Sebastian Zartner, Aug 18 '12 at 07:24
Doing so returns an EVariantOverflowError "'Overflow while converting variant of type (Decimal) into type (Currency)." — Sebastian Zartner, Aug 18 '12 at 09:51
Then consider to migrate to 3d party Delphi Oracle data access components. — da-soft, Aug 18 '12 at 14:15
I'd like to solve my issue without having to spend hundreds of dollars for another software just for something like this. Of course it's clear that you want to advert your software. — Sebastian Zartner, Aug 18 '12 at 18:37

score 3 · Answer 1 · edited May 23 '17 at 12:04

Ensure that your TClientDataSet field is explicitly created as ftLargeint otherwise you may have such rounding issues.

Forget about using ADO for accessing Oracle. Note that all OleDB providers interfaced by ADO are buggy for handling BLOBs: Microsoft's version just do not handle them, and Oracle's version will randomly return null for 1/4 of rows... I won't be surprised if similar issues occur in your case, about numerical fields.

You may take a look at other mean to access Oracle, like the DBExpresss driver or even the BDE. Some third-party components are available also.

Our free direct access classes to Oracle handles such numerical fields natively, and will handle Int64 values directly, with no TDataSet conversion (which may also be the cause of the issue). For instance, I think it is the only Delphi unit implementing the latest 11g feature named "64-bit Integer Host Datatype for OCI Bind and Define calls". It requires no client driver (nor BDE, nor Provider, nor local configuration): you can connect directly to Oracle just by copying the Oracle Instance Client dll with your Delphi exe. Resulting speed is amazing.

First I tried the DBExpress components for accessing Oracle. These work fine in Delphi 7. Though in Delphi XE they are causing big trouble. Unfortunately I can't tell anymore, which problems exactly because that was half a year ago. Since then I moved to ADO, which worked fine until now. — Sebastian Zartner, Aug 18 '12 at 07:28
ADO/OleDB works fine if you do not use BLOBs, and if you do not face rouding problem as your question! ;) — Arnaud Bouchez, Aug 18 '12 at 09:52
I already heard about the BLOB problem before. I don't use any BLOBs in my tables, so luckily I don't have any problems with that. Though I still don't know how to tell the `TClientDataSet` to use the `FieldDefs` I defined for it now. — Sebastian Zartner, Aug 18 '12 at 09:55

score 1 · Answer 2 · answered Aug 18 '12 at 00:31

Have you set use debug .dcus in your Delphi project options and stepped through all the code from the initial variable assignment up through when it hands the data to the Oracle transport driver? Have you verified when the value is being rounded to 15 digits?

Here are a couple of posts in other forums where non-Delphi users had the same issue, which was caused by using the to_char() function. I wonder if the number(19) (or BigInt) value is being converted to a character string somewhere between when you set the value and when it posts to the database.

dbaforums.org (describes 2 instances, one that works, one that doesn't)
forums.devshed.com (not very informative, but an example of the problem)

score 0 · Accepted Answer · edited Mar 05 '13 at 23:42

I decided to throw away the TClientDataSet and TDataSetProvider and use the TADOQuery directly to insert the values.

I.e. I have something like this now:

myDataModule.myQuery.SQL.Clear;
myDataModule.myQuery.SQL.Add('INSERT INTO mytable (ID) VALUES(:ID)');
myDataModule.myQuery.Prepared := True;
myDataModule.myQuery.Parameters.ParamByName('ID').Value := 1234567890123456789;
myDataModule.myQuery.ExecSQL;

Not a perfect solution but it works.

ClientDataSet rounds number to 15 digits

3 Answers3