Driver problem with BCD type

lucashaluch · Post by **lucashaluch** » Tue 17 May 2022 19:29

I found a problem related to BCD typing in the SqlServer driver. In the project example notice that the field 'Forn_CodVinc', a NUMERIC(5) column, is showing floating point values in the DBGrid. Not only is this value wrong in the DBGrid, but it is also used with floating point in the UPDATES SQLs.

I realized that this problem is occurring because of the BCD typing, since by setting the 'EnableBCD' parameter to False the error no longer occurs. The error occurs in both the Direct mode and other modes using a vendor library.

Problem encountered using latest version of the DevArt Driver (9.1) and Delphi 10.4

Unit.pas:

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, DBXDevartSQLServer,
  Data.FMTBcd, Data.SqlExpr, Vcl.Grids, Vcl.DBGrids, Datasnap.DBClient,
  Datasnap.Provider, SimpleDS, Data.DBXMsSQL, Data.DBXPool;

type
  TForm1 = class(TForm)
    dbgrd1: TDBGrid;
    ds1: TDataSource;
    con1: TSQLConnection;
    smpldtst1: TSimpleDataSet;
    procedure FormActivate(Sender: TObject);
  private
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const
  USER:String = 'TestSqlServer';
  PASSWORD:String = '9Xc6H^4E';
  SERVER:String = 'bdtestesqlserver.rpinfo.com.br';
  DATABASE:String = 'erp';

procedure TForm1.FormActivate(Sender: TObject);
begin
//Code for demonstrate the problem occuring in the column 'Forn_CodVinc'
  with con1.Params do begin
    //When setting EnableBCD=False the problem with column 'Forn_CodVinc' do not occur again
    //Add('EnableBCD=False');
    Add('User_Name='+USER);
    Add('Password='+PASSWORD);
    Add('HostName='+SERVER);
    Add('DataBase='+DATABASE);
  end;
  con1.Open;
  smpldtst1.Open;
end;

end.

Unit.dfm:

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnActivate = FormActivate
  PixelsPerInch = 96
  TextHeight = 13
  object dbgrd1: TDBGrid
    Left = 0
    Top = 0
    Width = 635
    Height = 299
    Align = alClient
    DataSource = ds1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
    Columns = <
      item
        Expanded = False
        FieldName = 'Forn_Codigo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Nome'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_RazaoSocial'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CNPJCPF'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Situacao'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CodVinc'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_InscricaoEstadual'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_InscricaoMunicipal'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_RegJuntaComercial'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Atividade'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Endereco'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_EnderecoNumero'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_EnderecoCompl'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Bairro'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CEP'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CxPostal'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Muni_Codigo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Fone'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Fax'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_EMail'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Marcas'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_PercFunrural'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_ObsPedidos'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_ContaContabil'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Comp_Codigo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_TransfFundos'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_EnderecoInd'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_MuniInd_Codigo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_FoneIndustria'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_FaxIndustria'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CodEAN'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_ObsTrocas'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_DataCad'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_DataAlt'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Usua_Codigo'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Contribuinte'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_BloqAutPgto'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_AtTabPrForn'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CaracTrib'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_ContaBloqueio'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_ProntaEntrega'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_SenhaCot'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Status'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Extra1'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_Tipos'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CExtra1'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CExtra2'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CExtra3'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CExtra4'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_ContribPrev'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_UsuAlt'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CodigoServicos'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CPFProdutor'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CAEPF'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_IndIntermediador'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_NomeUsuIntermediador'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_CodOrigemPVD'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_DtHrManutencao'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_DtHrIntegracao'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_PercGilrat'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'Forn_PercSenar'
        Visible = True
      end>
  end
  object ds1: TDataSource
    DataSet = smpldtst1
    Left = 96
    Top = 128
  end
  object con1: TSQLConnection
    DriverName = 'DevartSQLServerDirect'
    LoginPrompt = False
    Left = 16
    Top = 128
  end
  object smpldtst1: TSimpleDataSet
    Aggregates = <>
    Connection = con1
    DataSet.CommandText = 
      'SELECT * FROM fornecedores WHERE forn_Status='#39'N'#39' ORDER BY FORN_C' +
      'ODIGO'
    DataSet.MaxBlobSize = -1
    DataSet.Params = <>
    Params = <>
    Left = 56
    Top = 128
  end
end

pavelpd · Post by **pavelpd** » Thu 02 Jun 2022 09:19

Hey Lucas,
Thanks for contacting Devart!

We were unable to reproduce the BCD input issue you mentioned. Please provide us with the screenshots of "correct" and "incorrect" data.
If the "EnableBCD" option is active, then the NUMERIC(5) type maps to the TBCDField type in Delphi, and when the "EnableBCD" option is not active, then the NUMERIC(5) type maps to the TFloatField type in Delphi.

lucashaluch · Post by **lucashaluch** » Thu 02 Jun 2022 12:49

When the configuration "EnableBCD" is set to true (or using default):

If you refresh the connection the float point values changes:

When setting the configuration "EnableBCD" to false the value stays as it should be:

(Changing the NumericMapping configuration to true from the TInternalSQLDataSet component (DataSet of TSimpleDataSet) also works)

The reason we are not comfortable using these configuration is that in Delphi 7 these problems do not occur at all, and since we are updating the code for Delphi 10.4 we are not sure of the consequences of using these configurations.

Could you tell me the reason why you were not able to run the project I created? I created the project with access to a database with remote access where the problem is occurring. This way I can check what went wrong.

-

I checked the remote access of the database and indeed the connection was down, now it should be possible to run the project.

pavelpd · Post by **pavelpd** » Thu 16 Jun 2022 04:45

Hi Lucas,
Thanks for your request!

Kindly note, that we were unable to reproduce the issue you mentioned in our environment.

We have compiled a sample application based on the data you provided.
https://download.devart.com/sample/f_57335_sample.zip

Please test this application on your side and let us know about the results.

lucashaluch · Post by **lucashaluch** » Fri 01 Jul 2022 17:37

Unfortunately the error persists in the application you attached:

https://i.imgur.com/4g6SP5o.jpg

The values, marked in the red box, should be 15237, 12162 and 12162 respectively (without the floating point numbers). The field "Forn_CodVinc" is numeric(5,0).

Didn't this problem occur when you executed the project?

pavelpd · Post by **pavelpd** » Thu 14 Jul 2022 12:28

Hey Lucas,
Thanks for getting back to us!

Kindly be informed, that we were unable to reproduce the issue you mentioned.
In our environment, the result of the application we sent you earlier looks like this:

Please note, that we are still investigating this issue.
We will let you know as soon as we have the results.

lucashaluch · Post by **lucashaluch** » Thu 14 Jul 2022 18:06

I am trying to run your project without the floating point error. So far I have tried to run the project on a virtual machine (with a clean installation of Windows and Delphi), but without success.

Software I used for testing:

- VirtualBox 6.1.34 r150636 (Qt5.6.2)

- Windows 10 Pro 21H2 - OS build 19044.1288 (All regional configuration for United States)

- Delphi 10.4 Version 27.0.37889.9797

- dbExpress SQL Server 9.1.1

Is there anything else I could configure to run the project without error? Even with a clean installation and just running the project the error persists.

pavelpd · Post by **pavelpd** » Fri 15 Jul 2022 15:42

Hi Lucas,
Thank you for the additional information provided.

Please note, that we've reproduced the issue you mentioned with incorrect display of fractional parts for fields with the BCD type, and found that the error occurs precisely in Delphi 10.4 version 27.0.37889.9797, which you pointed out to us earlier.

We also made comparisons with versions 10.4, 10.4.2 and 11.1.

Therefore, please update your IDE to version 10.4.2 or higher to fix the issue.

lucashaluch · Post by **lucashaluch** » Thu 11 Aug 2022 13:12

Updating the IDE to version 10.4.2 solved the issue!

Thanks for your help and patience.

pavelpd · Post by **pavelpd** » Tue 23 Aug 2022 09:20

Hey Lucas,

You're always welcome!

We're glad to hear that you've managed to solve the issue with incorrect display of fractional parts for fields with the BCD type.

Please feel free to contact us if you have any further questions about our products!

Devart Forums

Driver problem with BCD type

Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type