D7 E - Stored procedur ewith datetime parameters

[zzzato]

Hi, All.
I've a strange delphi exception calling a stored procedure with datetime parameters.
I tried a lot of different cases without any positive result.
See examples below:

CREATE PROCEDURE `JNTEST`(
in ASTARTDATE DATETIME,
OUT ASLADATETIME DATETIME)
BEGIN
SET ASLADATETIME = SYSDATE();

END$$

CREATE PROCEDURE `JNTEST2`(
in ASTARTDATE DATETIME)
BEGIN
declare x int;
set x = 0;
END$$

When I try to call it using this delphi sintax:

proc.Params.ParamByName('ADateStart').AsDateTime := Now;
proc.execproc;

delphi raises an exception "invalid value to datetime encode".
Using debugger, I saw that something in dbx crashes when application tries to set parameters value. If I don't set value or write a code like this:

proc.Params.ParamByName('ADateStart').Clear;
proc.execproc;

I've not exceptions.

Note that mysql database version is 5.1 and server is running on debian virtual machine.

Thanks for any suggestion.

Best

[DemetrionQ]

Hello.

This problem occurs due to that Delphi doesn't allow distinguish the DateTime and SQLTimeStamp types. Unfortunately, we cannot influence this behaviour in any way.
As a workaround, you can transfer the value to the parameter via TParam.Value or TParam.AsSQLTimeStamp, for example:

Code: Select all

  proc.Params.ParamByName('ADateStart').Value := Now;

[zzzato]

Hi.
Thanks for your answer.I'll try.
Best.

[zzzato]

Hi, DemetrionQ..
Param.value := now: doesn't work.
Param.AsSqlTimeStamp := DateTimeToSqlTimeStamp(Now): works.

See u.

[DemetrionQ]

Hello.

Please verify what did you mean by "Param.value := now: doesn't work"?
If when using Param.value := now you get an error "invalid value to datetime encode", make sure that Param.DataType is not set to ftDateTime. It must have the ftTimeStamp value.

[zzzato]

Sorry. Paramater was ftDatetime insthead ftTimeStamp.
Now, param.Value := now works.
Many thanks.
See u.

[DemetrionQ]

Hello.

If any other questions come up, please contact us.