Devart Forums

lucashaluch

Updating the IDE version to 10.4.2 solved the problem here. So far I haven't had any more problems.

lucashaluch

Updating the IDE to version 10.4.2 solved the issue!

Thanks for your help and patience.

lucashaluch

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.

lucashaluch

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?

lucashaluch

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.

lucashaluch

I have encountered a problem regarding the BCD field for ORACLE in the UPDATE operation. In the code example below (that has access to a test DB where the problem occurs) when applying the UPDATE on the field 'CLIE_NAME' i get an error indicating the record in the DB was not found.

Using dbMonitor you can verify that the field 'CLIE_VALUEALUGUEL', in the WHERE condition of the UPDATE, has a value of 0.0001, but the value of the field in the DB is 0.

Using the 'EnableBCD' parameter to False, or the 'NumericMapping' setting to True of the DataSet, the problem does not occur again, but I am not sure if my application would work right if I change this. As these modifications are not necessary at all while using the Delphi 7 driver version, I hope this bug can be fixed.

Errors encountered using Oracle Client 11g, Delphi 10.4 and Oracle 8.0 DevArt Driver

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,
  Vcl.Grids, Vcl.DBGrids, Data.SqlExpr, Datasnap.DBClient, SimpleDS,
  Vcl.ExtCtrls, Vcl.StdCtrls, DBXDevartOracle, Data.DBXPool;

type
  TForm1 = class(TForm)
    smpldtst1: TSimpleDataSet;
    con1: TSQLConnection;
    btn1: TButton;
    sqlmntr1: TSQLMonitor;
    procedure FormActivate(Sender: TObject);
    procedure btn1Click(Sender: TObject);
  private
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const
  USER:String = 'TestOracle';
  PASSWORD:String = '9Xc6H^4E';
  SERVER:String = 'ORACLEDEV';

procedure TForm1.btn1Click(Sender: TObject);
begin
  //Bug ocur in the column CLIE_VALORALUGUEL (NUMERIC(12,2))
  smpldtst1.Edit;
  smpldtst1.FieldByName('CLIE_NOME').AsString:='Trying Update This Value'; //change this value after a succes UPDATE
  smpldtst1.Post;
  smpldtst1.ApplyUpdates(0);
end;

procedure TForm1.FormActivate(Sender: TObject);
begin
  con1.DriverName:='DevartOracle';
  con1.GetDriverFunc:='getSQLDriverORA';
  con1.LibraryName:='dbexpoda41.dll';
  con1.VendorLib:='OCI.DLL';
  with con1.Params do begin
    clear;
    Add('Oracle TransIsolation=ReadCommited');
    Add('OS Authentication=False');
    Add('Multiple Transaction=True');
    Add('Trim Char=True');
    Add('BlobSize=-1');
    Add('LocaleCode=0000');
    Add('ErrorResourceFile=');
    Add('DriverName=DevartOracle');
    //The problem stop if set EnableBCD to False
    //Add('EnableBCD=False');
    Add('User_Name='+USER);
    Add('Password='+PASSWORD);
    Add('DataBase='+SERVER);
  end;
  con1.Open;
  //Also set NumericMapping to True solve the problem
  //smpldtst1.DataSet.NumericMapping:=True;
  smpldtst1.Open;
end;

end.

Unit.dfm:

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 216
  ClientWidth = 190
  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 btn1: TButton
    Left = 56
    Top = 72
    Width = 75
    Height = 25
    Caption = 'Test UPDATE'
    TabOrder = 0
    OnClick = btn1Click
  end
  object smpldtst1: TSimpleDataSet
    Aggregates = <>
    Connection = con1
    DataSet.CommandText = 
      'SELECT * FROM ERP.clientes WHERE ERP.clientes.clie_status='#39'N'#39' OR' +
      'DER BY ERP.clientes.CLIE_CODIGO'
    DataSet.MaxBlobSize = -1
    DataSet.Params = <>
    Params = <>
    Left = 56
    Top = 128
  end
  object con1: TSQLConnection
    DriverName = 'DevartOracle'
    KeepConnection = False
    LoginPrompt = False
    Params.Strings = (
      'DriverUnit=DbxDevartOracle'
      
        'DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver270.' +
        'bpl'
      
        'MetaDataPackageLoader=TDBXDevartOracleMetaDataCommandFactory,Dbx' +
        'DevartOracleDriver270.bpl'
      'ProductName=DevartOracle'
      'GetDriverFunc=getSQLDriverORA'
      'LibraryName=dbexpoda41.dll'
      'VendorLib=oci.dll'
      'LibraryNameOsx=libdbexpoda41.dylib'
      'VendorLibOsx=libociei.dylib'
      'MaxBlobSize=-1'
      'LocaleCode=0000'
      'Oracle TransIsolation=ReadCommitted'
      'RoleName=Normal'
      'LongStrings=True'
      'EnableBCD=True'
      'UseQuoteChar=True'
      'CharLength=0'
      'UseUnicode=True'
      'UnicodeEnvironment=True'
      'DriverName=DevartOracle'
      'UNLICENSED_DRIVERS=0'
      'Database='
      'Password='
      'User_Name='
      'HostName='
      'InternalName=')
    Left = 16
    Top = 128
  end
  object sqlmntr1: TSQLMonitor
    SQLConnection = con1
    Left = 136
    Top = 128
  end
end

tnsnames.ora:

Code: Select all

ORACLEDEV =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = bdtesteoracle.rpinfo.com.br)(PORT = 1521))
      )
      (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
      )
   )

lucashaluch

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

Devart Forums

Search found 7 matches

Re: NUMERIC and BCD type problem in UPDATE condition

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

Re: Driver problem with BCD type

NUMERIC and BCD type problem in UPDATE condition

Driver problem with BCD type