Migrating from MyDAC to UniDac : Stored Procedure issue

[swierzbicki]

Hello,

I'm no more able to execute a stored procedure after migrating my project from MyDac components to UniDac components !

I'm always getting an error telling me that the parameter doesn't exists.
Here is the storefd procedure defined into mySQL

Code: Select all

CREATE DEFINER = 'Steph'@'%' FUNCTION `DetermineNouvelleDate`(
        DateARecalculer DATETIME,
        HeureBasculement TIME,
        CompterSamediFerie BOOLEAN
    )
    RETURNS datetime
    NOT DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE DateRecalculee DateTime;

Set HeureBasculement = IFNull(HeureBasculement,'12:00:00');
Set DateARecalculer = IF((DateARecalculer Is Null) OR (Date(DateARecalculer)  Time(HeureBasculement)) Then DateARecalculer
         When (CompterSamediFerie) AND (DAYOFWEEK(DateARecalculer) not in (7,1)) AND (Time(DateARecalculer) > Time(HeureBasculement)) Then DateARecalculer
         when NOT(CompterSamediFerie) AND DAYOFWEEK(DateARecalculer) in (2) Then DATE_SUB(DateARecalculer, INTERVAL 2 DAY)
         when NOT(CompterSamediFerie) AND DAYOFWEEK(DateARecalculer) in (1,3,4,5,6,7) Then DATE_SUB(DateARecalculer, INTERVAL 1 DAY)
         when (CompterSamediFerie) AND DAYOFWEEK(DateARecalculer) in (2) Then DATE_SUB(DateARecalculer, INTERVAL 3 DAY)
         when (CompterSamediFerie) AND DAYOFWEEK(DateARecalculer) in (3,4,5,6,7) Then DATE_SUB(DateARecalculer, INTERVAL 1 DAY)
         when (CompterSamediFerie) AND DAYOFWEEK(DateARecalculer) in (1) Then DATE_SUB(DateARecalculer, INTERVAL 2 DAY)
    
    End
  );
 

This is the Delphi code :

Code: Select all

function RecalculDateCreation(AMyConnection: TUniConnection; AHeureBascule: TTime; ACompterSamediFerie: Boolean)
  : TDateTime;
Var
  Sp: TUniStoredProc;
Begin
  // Result :=AMyConnection.ExecProc('DetermineNouvelleDate',[nulldate,AHeureBascule,True]) ;

  Sp := TUniStoredProc.create(nil);
  Try
    Sp.Connection := AMyConnection;
    Sp.StoredProcName := 'DetermineNouvelleDate';
    Sp.ParamByName('DateARecalculer').Clear;
    Sp.ParamByName('HeureBasculement').astime := AHeureBascule;
    Sp.ParamByName('CompterSamediFerie').AsBoolean := ACompterSamediFerie;
    Sp.execute;

    Result := Sp.ParamByName('Result').AsDateTime;
  Finally
    Sp.Free
  End;
End;

Thank you in advance

[AndreyZ]

Hello,

To solve the problem, you should call the PrepareSQL method. Here is an example:

Code: Select all

function RecalculDateCreation(AMyConnection: TUniConnection; AHeureBascule: TTime; ACompterSamediFerie: Boolean) 
  : TDateTime; 
Var 
  Sp: TUniStoredProc; 
Begin 
  // Result :=AMyConnection.ExecProc('DetermineNouvelleDate',[nulldate,AHeureBascule,True]) ; 

  Sp := TUniStoredProc.create(nil);
  Try 
    Sp.Connection := AMyConnection; 
    Sp.StoredProcName := 'DetermineNouvelleDate';
    Sp.PrepareSQL; 
    Sp.ParamByName('DateARecalculer').Clear; 
    Sp.ParamByName('HeureBasculement').astime := AHeureBascule; 
    Sp.ParamByName('CompterSamediFerie').AsBoolean := ACompterSamediFerie; 
    Sp.execute; 

    Result := Sp.ParamByName('Result').AsDateTime; 
  Finally 
    Sp.Free 
  End;
End;