NUMERIC and BCD type problem in UPDATE condition

[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)
      )
   )

[rushhorseradish]

The problem does not recur if I set the 'EnableBCD' parameter to False or the 'NumericMapping' setting in the DataSet to True, but I'm not sure if my application will function correctly if I do so. I'm hoping this bug can be fixed, as the Delphi 7 driver version does not require any of these modifications.


phrazle

[lucashaluch]

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