ODAC 9.3.9 - Still has Boolean Results bug on TOraStoredProc!!!!

[paulzip]

In v9.38 several issues were logged in relation to errors with TOraStoredProc params, in particular boolean parameters. See here : http://forums.devart.com/viewtopic.php?f=5&t=29528

You replied....

We have added a global variable ForseProcNamedParams and an option ProcNamedParams that allow using named parameters on generating a stored procedure call. Setting a global variable in your case will let you solve the problem with parameters.

However only part of the problem has been fixed, the boolean parameter issue STILL exists. This is getting beyond frustration for us, especially given the infrequent releases of ODAC (once a month or whatever) and the fact it doesn't appear that you tried the test case I gave you, which demonstrates the boolean function issue. When will you have a fix for this?

Please see the problem by testing with the following, with "Run All" radio button enabled.

You'll see these results (I can explain and accept 2 of the errors when ForceProcNamedParams = True):
sprGetTotalPayment. ForceProcNamedParams = False. ErrorCount = 5
sprGetTotalPayment. ForceProcNamedParams = True. ErrorCount = 2
sprIsContainer. ForceProcNamedParams = False. ErrorCount = 5
sprIsContainer. ForceProcNamedParams = True. ErrorCount = 5



Test package :

Code: Select all

create or replace package P_CUSTOMER as  
function  IsContainer(pCustomerID number) return boolean;  
end;
/

create or replace package body P_CUSTOMER as

function  IsContainer(pCustomerID number) return boolean is
begin
  return true;
end;

end;
/ 

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, MemDS, DBAccess, Ora, StdCtrls, OraClasses;

type
  TOps = (opDefault, opPrepare, opCreateProcCall,
          opInternalCreateProcCallNoDescr, opInternalCreateProcDescr);
  TOraStoredProcHack = class(TOraStoredProc);
  TForm1 = class(TForm)
    OraSession1: TOraSession;
    sprGetTotalPayment: TOraStoredProc;
    Memo1: TMemo;
    btnRunTests: TButton;
    sprIsContainer: TOraStoredProc;
    radGetTotalPayment: TRadioButton;
    radIsContainer: TRadioButton;
    memErrors: TMemo;
    lblErrors: TLabel;
    radAll: TRadioButton;
    lblLog: TLabel;
    procedure FormShow(Sender: TObject);
    procedure btnRunTestsClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Memo1DblClick(Sender: TObject);
  private
    { Private declarations }
    DefaultSQLText : string;
    DefaultParams : TOraParams;
    fForceNames : boolean;
    ErrorCount : array[boolean] of integer;
    procedure DoLog(const Str: string);
    function ParamsAsString(Params: TOraParams): string;
    procedure ResetStoredProc(AStoredProc: TOraStoredProc; ClearParams: boolean);
    procedure DoClearDataSetParams(Params: TParams; SetBound: boolean);
    procedure DefaultsForStoredProc(AStoredProc: TOraStoredProc);
    procedure ProcessTest(AStoredProc: TOraStoredProc; Info: string;
      ClearParams: boolean);
    procedure RunTests(AStoredProc: TOraStoredProc);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses
  TypInfo;

procedure TForm1.FormShow(Sender: TObject);
begin
  Memo1.Lines.Clear;
  DefaultParams := TOraParams.Create(nil);
end;

procedure TForm1.DefaultsForStoredProc(AStoredProc : TOraStoredProc);
begin
  DefaultSQLText := AStoredProc.SQL.Text;
  DefaultParams.Assign(AStoredProc.Params);
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  DefaultParams.Free;
end;

procedure TForm1.DoLog(const Str : string);
begin
  Memo1.Lines.Add(Str);
end;

function TForm1.ParamsAsString(Params : TOraParams) : string;
var
  m : integer;
begin
  for m := 0 to Params.Count - 1 do
    Result := Result + Params[m].Name + ', ';
end;

procedure TForm1.ResetStoredProc(AStoredProc : TOraStoredProc; ClearParams : boolean);
begin
  if AStoredProc.Prepared then
    AStoredProc.UnPrepare;
  AStoredProc.SQL.Text := DefaultSQLText;
  if ClearParams then
    AStoredProc.Params.Clear
  else
    AStoredProc.Params.Assign(DefaultParams);
end;

procedure TForm1.DoClearDataSetParams(Params : TParams; SetBound : boolean);
var
  i:integer;
begin
  for i := 0 to Params.Count-1 do
  begin
    Params[i].Clear;
    if SetBound then
      Params[i].Bound := True;
  end;
end;

procedure TForm1.ProcessTest(AStoredProc : TOraStoredProc; Info : string; ClearParams : boolean);
const
  SEP  = '-----------------------------------------------';
  SEP2 = '===============================================';
  CONTRNO = '103/A';
var
  n : TOps;
  procedure LogInfo(AOp : TOps; ASQL : string);
  var
    ABoolean : boolean;
  begin
    DoLog(GetEnumName(TypeInfo(TOps), integer(AOp)) + ': ' + #13#10);
    DoLog('Params Order : ' + ParamsAsString(AStoredProc.Params) + #13#10);
    DoLog(ASQL);
    try
      DoClearDataSetParams(AStoredProc.Params, True);
      if AStoredProc = sprGetTotalPayment then
      begin
        AStoredProc.ParamByName('ContractID').AsString := ContrNo;
        AStoredProc.ParamByName('Posted').AsBoolean := True;
      end
      else
        AStoredProc.ParamByName('PCUSTOMERID').AsInteger := 0;
      AStoredProc.ExecProc;
      if AStoredProc = sprIsContainer then
        ABoolean := AStoredProc.ParamByName('RESULT').AsBoolean;
    except
      on E:Exception do
      begin
        DoLog(E.Message);
        inc(ErrorCount[fForceNames]);
      end;
    end;
    DoLog(SEP);
  end;
begin
  DoLog(Info);
  for n := Low(TOps) to High(TOps) do
  begin
    ResetStoredProc(AStoredProc, ClearParams);
    case n of
      opPrepare :
        AStoredProc.Prepare;
      opCreateProcCall :
        AStoredProc.CreateProcCall(AStoredProc.StoredProcName, 0);
      opInternalCreateProcCallNoDescr, opInternalCreateProcDescr :
//        TOraStoredProcHack(AStoredProc).InternalCreateProcCall(AStoredProc.StoredProcName, 0, n = opInternalCreateProcDescr); // Version 5.80
        TOraStoredProcHack(AStoredProc).InternalCreateProcCall(AStoredProc.StoredProcName, n = opInternalCreateProcDescr);    // Version 9.3.8
    end;
    LogInfo(n, AStoredProc.SQL.Text);
  end;
  DoLog(SEP2);
end;

procedure TForm1.RunTests(AStoredProc : TOraStoredProc);
var
  n : boolean;
begin
  for n := False to True do
  begin
    fForceNames := n;
    ErrorCount[n] := 0;
    OraClasses.ForceProcNamedParams := n;
    DefaultsForStoredProc(AStoredProc);
    ProcessTest(AStoredProc, 'Params From DFM', False);
    ProcessTest(AStoredProc, 'Params Cleared', True);
    ResetStoredProc(AStoredProc, False);
  end;
  for n := False to True do
    memErrors.Lines.Add(format('%s. ForceProcNamedParams = %s. ErrorCount = %d', [AStoredProc.Name, GetEnumName(TypeInfo(boolean), integer(n)), ErrorCount[n]]));
end;


procedure TForm1.btnRunTestsClick(Sender: TObject);
begin
  if radAll.Checked or radGetTotalPayment.Checked then
    RunTests(sprGetTotalPayment);
  if radAll.Checked or radIsContainer.Checked then
    RunTests(sprIsContainer);
end;

procedure TForm1.Memo1DblClick(Sender: TObject);
begin
  Memo1.SelectAll;
  Memo1.CopyToClipboard;
end;

end.

Test unit (dfm).

Code: Select all

object Form1: TForm1
  Left = 319
  Top = 124
  Width = 1305
  Height = 750
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnDestroy = FormDestroy
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  object lblErrors: TLabel
    Left = 880
    Top = 8
    Width = 27
    Height = 13
    Caption = 'Errors'
  end
  object lblLog: TLabel
    Left = 240
    Top = 8
    Width = 18
    Height = 13
    Caption = 'Log'
  end
  object Memo1: TMemo
    Left = 240
    Top = 24
    Width = 633
    Height = 665
    ScrollBars = ssBoth
    TabOrder = 0
    WordWrap = False
    OnDblClick = Memo1DblClick
  end
  object btnRunTests: TButton
    Left = 88
    Top = 272
    Width = 75
    Height = 25
    Caption = 'Run Tests'
    TabOrder = 1
    OnClick = btnRunTestsClick
  end
  object radGetTotalPayment: TRadioButton
    Left = 72
    Top = 152
    Width = 113
    Height = 17
    Caption = 'GetTotalPayment'
    TabOrder = 2
  end
  object radIsContainer: TRadioButton
    Left = 72
    Top = 176
    Width = 113
    Height = 17
    Caption = 'IsContainer'
    TabOrder = 3
  end
  object memErrors: TMemo
    Left = 880
    Top = 24
    Width = 385
    Height = 145
    TabOrder = 4
  end
  object radAll: TRadioButton
    Left = 72
    Top = 200
    Width = 113
    Height = 17
    Caption = 'Run All'
    Checked = True
    TabOrder = 5
    TabStop = True
  end
  object OraSession1: TOraSession
    Username = 'FLEETWARE'
    Server = 'CURRENT'
    Left = 136
    Top = 56
  end
  object sprGetTotalPayment: TOraStoredProc
    StoredProcName = 'P_FM_CONTRACT.GETTOTALPAYMENTS'
    Session = OraSession1
    SQL.Strings = (
      'declare'
      '  v_POSTED boolean := sys.DIUTIL.INT_TO_BOOL(:POSTED);'
      'begin'
      
        '  P_FM_CONTRACT.GETTOTALPAYMENTS(:CONTRACTID, :DUEDATEFROM, :DUE' +
        'DATETO, :INVDATEFROM, :INVDATETO, v_POSTED, :PAYMENTTYPE, :PCOUN' +
        'T, :FINANCE, :SERVICES, :INSURANCE);'
      'end;')
    Left = 200
    Top = 56
    ParamData = <
      item
        DataType = ftBoolean
        Name = 'POSTED'
        ParamType = ptInput
      end
      item
        DataType = ftString
        Name = 'CONTRACTID'
        ParamType = ptInput
      end
      item
        DataType = ftDateTime
        Name = 'DUEDATEFROM'
        ParamType = ptInput
      end
      item
        DataType = ftDateTime
        Name = 'DUEDATETO'
        ParamType = ptInput
      end
      item
        DataType = ftDateTime
        Name = 'INVDATEFROM'
        ParamType = ptInput
      end
      item
        DataType = ftDateTime
        Name = 'INVDATETO'
        ParamType = ptInput
      end
      item
        DataType = ftFixedChar
        Name = 'PAYMENTTYPE'
        ParamType = ptInput
      end
      item
        DataType = ftInteger
        Name = 'PCOUNT'
        ParamType = ptOutput
      end
      item
        DataType = ftFloat
        Name = 'FINANCE'
        ParamType = ptOutput
      end
      item
        DataType = ftFloat
        Name = 'SERVICES'
        ParamType = ptOutput
      end
      item
        DataType = ftFloat
        Name = 'INSURANCE'
        ParamType = ptOutput
      end>
  end
  object sprIsContainer: TOraStoredProc
    StoredProcName = 'P_CUSTOMER.ISCONTAINER'
    Session = OraSession1
    SQL.Strings = (
      'declare'
      '  v_RESULT boolean;'
      'begin'
      '  v_RESULT := P_CUSTOMER.ISCONTAINER(:PCUSTOMERID);'
      '  :RESULT := sys.DIUTIL.BOOL_TO_INT(v_RESULT);'
      'end;')
    Left = 200
    Top = 88
    ParamData = <
      item
        DataType = ftInteger
        Name = 'PCUSTOMERID'
        ParamType = ptInput
      end
      item
        DataType = ftBoolean
        Name = 'RESULT'
        ParamType = ptOutput
      end>
  end
end

[paulzip]

Any news on this?

[AlexP]

In the first case, when setting ForceProcNamedParams = True, errors occur due to that you are trying to access remote parameters: parameters don't exist at the moment of attempting to assign values.
The second issue is being investigated yet.