1

Using Delphi 2007.

We do not use the designer to configure our DataSet. It is built automatically from the SQL query. So we need a solution where the fields are built at runtime.

I need to SELECT a large number (e.g. 2305843009213693951) from a NUMBER(19,0) column in Oracle using ADO. Our query works in SQLServer where the column is defined as BIGINT and is mapped automatically to a TLargeintField but for Oracle it is mapped to TBCDField. This behaviour is documented in a couple of places for instance here - TADOQuery.EnableBCD.

The problem (as mentioned in the same page) is that our number is in some instances too large and an overflow exception is thrown (it uses decimal under the hood). As expected / documented - if I use TADOQuery.EnableBCD(false) then the column is mapped to a TLargeintField which is what I want. However this option is set for all columns and seems a bit heavy handed.

An alternative is described:

Note: For fields with very large numbers of more than 19 significant digits, you can use TVariantField type persistent field objects. The TFloatField and TBCDField classes lack sufficient capacity for fields of this size. TVariantField allows the getting and setting of the field data as strings, preventing without loss of data due to insufficient capacity. However, arithmetic operations cannot be performed on numbers accessed through a TVariantField object.

So I tried configuring the fields to columns manually as described in this article - Creating a ClientDataSet's Structure at Runtime using TFields. But this doesn't appear to work as I get the exception: "Type mismatch for field 'total_rec' expecting: LargeInt actual: BCD'.

So is there a way to force ADO to return the dataset with a field of type TLargeintField for a NUMBER(19,0) without using the designer?

Interestingly ODAC maps the column to TLargeintField, but we are supposed to support ODAC and ADO.

wayju
  • 130
  • 2
  • 16
  • How do you able to solve the problem when you *do use* the designer? also take a look here: http://www.delphigroups.info/2/d1/218434.html and here: http://stackoverflow.com/a/23295331/937125 – kobik Aug 12 '14 at 10:32
  • I am not sure if using design time field editing will actually help, I don't know we are not using it. Thanks, those links were interesting. This first link is the behavior I prefer, but I don't really like the idea of making changes to ADODB. – wayju Aug 13 '14 at 01:19
  • You wrote "is there a way to force ADO...without using the designer?" - so I assumed you *could* force ADO to change the field type "using the designer". There is no way to force ADO to change a field type AFAIK. The method responsible for initializing it is `TCustomADODataSet.InternalInitFieldDefs`. – kobik Aug 13 '14 at 09:18
  • "but I don't really like the idea of making changes to ADODB" You don't need to change Delphi's ADODB.pas. copy it to your project directory and patch it there. – kobik Aug 13 '14 at 09:20
  • Thanks. From some more reading, I think you are correct. Yea I understood that about the patching, but its not really an elegant solution, and may confuse later developers. I would rather work within the boundaries of the framework unless really pushed to change it myself. I think I will end up just turning off BCD for the affected query. If you want, add your notes to an answer saying its not possible and I will mark it as the correct answer. – wayju Aug 14 '14 at 05:30
  • I'll wait. maybe other users can provide a better solution other than patching ADODB. you could however post (and accept) an answer yourself. I'll up-vote :) – kobik Aug 17 '14 at 13:43

1 Answers1

1

I don't believe there is a way to manually control column data types returned by the ADOQuery. I tested overriding the procedure mentioned by kobik which determines the data type and this worked well. However we are not going to use this solution because I looked at the same code in XE4 and it appears it has been modified for use with large BCD numbers. We can wait until we upgrade from 2007 (hopefully next year).

The code I added to my overridden version of InternalInitFieldDefs was:

After

    if (F.Type_ = adNumeric) and (F.NumericScale = 0) and (F.Precision < 10) then
    begin
      FieldType := TFieldType(ftInteger);
    end

I added 

    else if (F.Type_ = adNumeric) and (F.NumericScale = 0) and (F.Precision >= 19) then
    begin
      FieldType := ftLargeint;
    end;
wayju
  • 130
  • 2
  • 16