Use BCDField problem

Use BCDField problem

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

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

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

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

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

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

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

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

Postby wenli0202 » Tue 08 Jun 2010 03:10

Hello,


I got it. I fixed source. Now the type map is correct.
Thank you very much.
wenli0202
 
Posts: 5
Joined: Thu 03 Jun 2010 08:48

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

Postby 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?
flavio80s
 
Posts: 5
Joined: Mon 05 Sep 2011 12:09

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


Return to dbExpress driver for Oracle