NULL value & MyConnection.ExecProc/CustomMyStoreProc.Execute

[swierzbicki]

Hello,

How to send NULL values with TMyConnection.ExecProc or TCustomMyStoreProc.Execute ?

Code: Select all

CREATE DEFINER = 'Steph'@'%' FUNCTION `DetermineNouvelleDate`(
        DateARecalculer DATETIME,
        HeureBasculement TIME,
        CompterSamediFerie BOOLEAN
    )
    RETURNS datetime
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
Set HeureBasculement = IFNull(HeureBasculement,'12:00:00');
Set DateARecalculer = IFNull(DateARecalculer,now());
Set CompterSamediFerie = IFNull(CompterSamediFerie,False);
 
/* CODE PARTS REMOVED */
 
RETURN DateARecalculer;
END;

TMyConnection.ExecPRoc return '1899-12-30'

Code: Select all

AMyConnection.ExecProc('DetermineNouvelleDate',[Null,'12:00:00',True]) ;
or
AMyConnection.ExecProc('DetermineNouvelleDate',[NullDate,'12:00:00',True]) ;

TMyStoredProc.Execute return '1899-12-30'

Code: Select all

    Sp:=TMyStoredProc.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;

How can I pass NULL values ?

[Dimon]

The AsDateTime property always returns date value. If returned value is NULL, then AsDateTime returns date for 0, that is equaled to '1899-12-30'. To get NULL value use the Value property, like this:

Code: Select all

var
  Result: Variant;
...
  Result := Sp.ParamByName('Result').Value;

[swierzbicki]

Hi Dimon,

Thank you for your answer but I guess that you didn't understand my question (or I was not able to explain it correctly).
Have a look at my stored function, and check this line below :

Code: Select all

Set DateARecalculer = IFNull(DateARecalculer,now()); 

'DateARecalculer' is an input variable needed when calling the stored function. As you can see, the 'DateARecalculer' variable will be set to current date if it's value is Null.

My problem is that I haven't found a way to pass a Null value to any input variable when calling TMyConnection.ExecPRoc or TMyStoredProc.Execute procedure.
Both of these procedures will convert the NULL value to 0. That's why I'm getting '1899-12-30' instead of the current date !

How can I pass NULL value to stored procedures / functions ?

[Dimon]

I can not reproduce the problem using the TMyStoredProc.Execute procedure. Please send me a complete small sample to dmitryg*devart*com to demonstrate it.

Also supply me the following information:
- the exact version of MyDAC. You can see it in the About sheet of TMyConnection Editor;
- the exact version of your IDE;
- the exact version of MySQL server. You can see it in the Info sheet of TMyConnection Editor.