Use BCDField problem

wenli0202 · Post by **wenli0202** » Thu 03 Jun 2010 09:59

Hello,
I am using DbxOda version 4.50.20 now,but it always map Oracle number type to TFMTBCDField. I really want TBCDField.How can I fix it?

thanks,
wenli

bork · Post by **bork** » Thu 03 Jun 2010 12:39

Hello

The field data type depends on field precision in the database. If field precision in the database is more then the Delphi constant MaxBcdPrecision in the FMTBcd unit then the field will have the fldFmtBCD data type, otherwise the field will have the fldBCD data type. The NUMBER data type has precision 38 and it is more then the MaxBcdPrecision constant value. Unfortunately we cannot change the behavior of the Delphi classes and we cannot change the values of the Delphi constants.

wenli0202 · Post by **wenli0202** » Fri 04 Jun 2010 02:35

hi,
This is my table schema:
create table ST_SYS_FUNCTION
(
FNCODE VARCHAR2(10) not null,
FNNAME VARCHAR2(30) not null,
FNMODULE VARCHAR2(30) not null,
FNMEMO VARCHAR2(255),
FNCLASSNAME VARCHAR2(100),
FNPOSITION NUMBER(2),
FNICON VARCHAR2(50),
FNSEPERATE CHAR(1) default 'N'
)

the FNPOSITION NUMBER(2) column always map to FMTBcd .

Thanks

bork · Post by **bork** » Fri 04 Jun 2010 08:09

Hi

I created a table:

Code: Select all

create table ST_SYS_FUNCTION
(
FNCODE VARCHAR2(10) not null,
FNNAME VARCHAR2(30) not null,
FNMODULE VARCHAR2(30) not null,
FNMEMO VARCHAR2(255),
FNCLASSNAME VARCHAR2(100),
FNPOSITION NUMBER(2),
FNICON VARCHAR2(50),
FNSEPERATE CHAR(1) default 'N'
)

I put to the form the following components: TSQLConnection, TSQLQuery, TDataSetProvider, TClientDataSet. In TSQLConnection I selected driver "Devart Oracle". To the TSQLQuery component I put the following query: "select * from ST_SYS_FUNCTION". And I executed the following code:

Code: Select all

var
  i: integer;
begin
  SQLQuery1.Open;
  ClientDataSet1.Open;

  for i := 0 to ClientDataSet1.FieldCount - 1 do
    ShowMessage(ClientDataSet1.Fields[i].FieldName + ' - ' + ClientDataSet1.Fields[i].ClassName + #13 +
                'DataType: ' + GetEnumName(TypeInfo(TFieldType), Integer(ClientDataSet1.Fields[i].Datatype)));
end;

For the FNPOSITION field I see the TBCDField field type.

Try to delete all fields from the fields editor from from the TSQLQuery and TClientDataSet components and execute the code as stated above.

wenli0202 · Post by **wenli0202** » Fri 04 Jun 2010 10:54

My environment is
Delphi® 2010 Version 14.0.3593.25826 (update4,update5)
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Oracle Client 10.2.0
Oci.dll 10.2.0.1.0

I get TFMTBcd field type.

bork · Post by **bork** » Fri 04 Jun 2010 14:07

The Oracle server 11 version has a problem: if you create a table as
create table ST_SYS_FUNCTION

Code: Select all

(
FNCODE VARCHAR2(10) not null,
FNNAME VARCHAR2(30) not null,
FNMODULE VARCHAR2(30) not null,
FNMEMO VARCHAR2(255),
FNCLASSNAME VARCHAR2(100),
FNPOSITION NUMBER(2),
FNICON VARCHAR2(50),
FNSEPERATE CHAR(1) default 'N'
)

then the Oracle server will return precision 2 for the FNPOSITION field for all queries.

But if you create a table as:

Code: Select all

create table ST_SYS_FUNCTION
(
FNCODE VARCHAR2(10) not null,
FNNAME VARCHAR2(30) not null,
FNMODULE VARCHAR2(30) not null,
FNMEMO VARCHAR2(255),
FNCLASSNAME VARCHAR2(100)
)

and later add a column as

Code: Select all

alter table ST_SYS_FUNCTION
add FNPOSITION NUMBER(2)

then the Oracle server will return precision 38 for the FNPOSITION field for all queries.

Previous Oracle versions return correct precision in this case.

You can find discussion about this problem here: http://www.devart.com/forums/viewtopic.php?t=17432

wenli0202 · Post by **wenli0202** » Mon 07 Jun 2010 04:42

Hi,
Thank you very much,but in delphi7 ,Oracle 11,DbxOda version 4.50
The map type is correct.

bork · Post by **bork** » Mon 07 Jun 2010 16:16

Hello

Problem is that the DataTypeMap Delphi data type is responsible for equivalence between database data types and Delphi data types.

In the Delphi 7 this type declared as:

Code: Select all

  DataTypeMap: array[0..MAXLOGFLDTYPES - 1] of TFieldType = (
    ftUnknown, ftString, ftDate, ftBlob, ftBoolean, ftSmallint,
    ftInteger, ftFloat, ftBCD, ftBytes, ftTime, ftDateTime,
    ftWord, ftInteger, ftUnknown, ftVarBytes, ftUnknown, ftCursor,
    ftLargeInt, ftLargeInt, ftADT, ftArray, ftReference, ftDataSet,
    ftTimeStamp, ftFMTBCD);

Data type with the BCD data type (code

will be mapped to the ftBCD Delphi data type.

But in the Delphi 2007 the ftBCD data type is removed at all:

Code: Select all

  DataTypeMap: array[0..TDBXDataTypes.MaxBaseTypes - 1] of TFieldType = (
    ftUnknown, ftString, ftDate, ftBlob, ftBoolean, ftSmallint,
    ftInteger, ftFloat, ftFMTBCD, ftBytes, ftTime, ftDateTime,
    ftWord, ftInteger, ftUnknown, ftVarBytes, ftUnknown, ftCursor,
    ftLargeInt, ftLargeInt, ftADT, ftArray, ftReference, ftDataSet,
    ftTimeStamp, ftFMTBCD, ftWideString);

Data type with the BCD data type (code

and with the FMTBCD data type (code 25) both will be mapped to the ftFMTBCD Delphi data type.

In the Delphi 2009 the ftBCD Delphi data type was returned but it has invalid index:

Code: Select all

  DataTypeMap: array[0..TDBXDataTypes.MaxBaseTypes - 1] of TFieldType = (
    ftUnknown, ftString, ftDate, ftBlob, ftBoolean, ftSmallint,
    ftInteger, ftFloat, ftFMTBCD, ftBytes, ftTime, ftDateTime,
    ftWord, ftInteger, ftUnknown, ftVarBytes, ftUnknown, ftCursor,
    ftLargeInt, ftLargeInt, ftADT, ftArray, ftReference, ftDataSet,
    ftTimeStamp, ftBCD, ftWideString);

You can find this declaration in the “SqlExpr.pas” file in each Delphi version. If try to use native dbExpress driver for Oracle then you find same behavior: it will return ftFMTBCD data type for the column in the Delphi 2009 because of the DataTypeMap data type declaration.

So if you want to get ftBCD Delphi data type for your field then you should ask Embarcadero (owner of Delphi) to restore declaration of the DataTypeMap data type.

wenli0202 · Post by **wenli0202** » Tue 08 Jun 2010 03:10

Hello,

I got it. I fixed source. Now the type map is correct.
Thank you very much.

lizy11 · Post by **lizy11** » Wed 07 Sep 2011 09:03

Thanks a million!!! The information My Local Cause you've provided helped me a lot!

flavio80s · Post by **flavio80s** » Tue 11 Oct 2011 18:42

wenli0202 wrote:Hello,

I got it. I fixed source. Now the type map is correct.
Thank you very much.

Can you detail it?

AlexP · Post by **AlexP** » Fri 14 Oct 2011 12:04

Hello,

To make the NUMBER(2) field recognized as TBCDField in Delphi, you need to save the SqlExpr.pas file with your project, swap ftBCD and ftFMTBCD in the DataTypeMap array, and add this file to your project.