Use BCDField problem

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
wenli0202
Posts: 5
Joined: Thu 03 Jun 2010 08:48

Use BCDField problem

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
Devart Team
Posts: 643
Joined: Fri 12 Mar 2010 07:55

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
Posts: 5
Joined: Thu 03 Jun 2010 08:48

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
Devart Team
Posts: 643
Joined: Fri 12 Mar 2010 07:55

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
Posts: 5
Joined: Thu 03 Jun 2010 08:48

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
Devart Team
Posts: 643
Joined: Fri 12 Mar 2010 07:55

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
Posts: 5
Joined: Thu 03 Jun 2010 08:48

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
Devart Team
Posts: 643
Joined: Fri 12 Mar 2010 07:55

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 8) 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 8) 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
Posts: 5
Joined: Thu 03 Jun 2010 08:48

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
Posts: 1
Joined: Wed 07 Sep 2011 08:57

Post by lizy11 » Wed 07 Sep 2011 09:03

Thanks a million!!! The information My Local Cause you've provided helped me a lot!
Last edited by lizy11 on Mon 24 Jun 2013 12:25, edited 2 times in total.

flavio80s
Posts: 5
Joined: Mon 05 Sep 2011 12:09

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

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.

Post Reply