NUMERIC and BCD type problem in UPDATE condition

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
lucashaluch
Posts: 5
Joined: Tue 17 May 2022 19:18

NUMERIC and BCD type problem in UPDATE condition

Post by lucashaluch » Wed 18 May 2022 20:45

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

JessicaReynolds
Posts: 3
Joined: Mon 04 Jul 2022 02:47

Re: NUMERIC and BCD type problem in UPDATE condition

Post by JessicaReynolds » Thu 21 Jul 2022 09:25

I got the same error. How do you fix these errors? phoodle

rushhorseradish
Posts: 1
Joined: Fri 29 Jul 2022 09:03

Re: NUMERIC and BCD type problem in UPDATE condition

Post by rushhorseradish » Fri 29 Jul 2022 09:06

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

Post Reply