Delphi ADO - how to set default dataset column type without designer

Question

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.

Answer 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;