Delphi 10.4 ClientDataSet + SQL Server executes stored procedure with unrecognizable spaces after parameters

Question

I am using delphi 10.4 to develop android program, to connect to database I am using TSQLConnection(datasnap) and TCLientDataSet. the statement is

var orderNo,date:string;

orderNo := '';
date := '2024-02-04 00:00:00';

With QSysDataSet do
begin
    close; 
    commandText:= 'exec S_SignSR :orderNo,:Date';

    Params.ParamByName('orderNo').dataType := ftstring;
    Params.ParamByName('orderNo').ParamType := ptInputOutput;
    Params.ParamByName('orderNo').size := 20;
    Params.ParamByName('orderNo').value := orderNo;

    Params.ParamByName('Date').dataType := ftstring;
    Params.ParamByName('Date').ParamType := ptInputOutput;
    Params.ParamByName('Date').size := 30;
    Params.ParamByName('Date').value := date;

    execute;
end.

sql server procedure statement is

create procedure S_SignSR
    @orderNo varchar(20) output
    @date varchar(30) output
as
begin
    update table1 set ddate = @date 
end

But after execution the statement traced in sqlserver is

declare @p6 varchar(21)
set @p6='                     ' -- There are 21 spaces here
declare @p9 varchar(31)
set @p9='2024-02-04 00:00:00            ' -- There is spaces after this, with a total length of 31
exec sp_executesql N'exec S_SignSR @p1，@p2',N'@p1 varchar(21) output,@p2 varchar(31) output',@p6 output,@p9 output

Execute update table1 set ddate = @date in database will report "Conversion failed when converting date and/or time from string";

I try insert into temp(text1) values (''''+@date+'111');select * from temp;the only data in the table is '2024-02-04 00:00:00

using set @date = rtrim(@date) it not works, but using set @date = substring(@date,1,19) it works.

If I skipped setting the param size

//Params.ParamByName('orderNo').size := 20;
//Params.ParamByName('Date').size := 30;

the statement traced is

declare @p6 varchar(2)
set @p6='  ' -- There are 2 spaces here
declare @p9 varchar(21)
set @p9='2024-02-04 00:00:00  ' -- There is spaces after this, with a total length of 21

If I used

    Params.ParamByName('orderNo').size := 20;
    Params.ParamByName('orderNo').AsString := orderNo;
    Params.ParamByName('Date').size := 30;
    Params.ParamByName('Date').AsString := date;

the statement traced is

declare @p6 nvarchar(21)
set @p6=N'          븀࿄봈࿄࿄률࿄�࿄'--This will display garbled characters
declare @p7 int
set @p7=3
declare @p9 nvarchar(31)
set @p9=N'2024-02-24 00:00:00           '

If I used

    //Params.ParamByName('orderNo').size := 20;
    Params.ParamByName('orderNo').AsString := orderNo;
    //Params.ParamByName('Date').size := 30;
    Params.ParamByName('Date').AsString := date;

the statement traced is

declare @p6 nvarchar(1)
set @p6=N' '
declare @p7 int
set @p7=3
declare @p9 nvarchar(1)
set @p9=N'2'

I tried to change data types like widestring(date)/ansistring(date)/utf8encode(date),They all don't work

I suspect that the trailing spaces are garbled code that can't be displayed, probably due to encoding conversions.

The date format is not the point, as I have been using string type transfer throughout the process. For example, orderNo also has the same problem. The key is to solve the problem of unknown spaces after parameters are passed into the database

How should this problem be solved

Answer 1

Finally I add a TADOConnection and a TADOStoredProc in server builded by vcl

and used following code in fmx client to call server's method

try
    SQLServerMethod := TSQLServerMethod.Create(nil);
    SQLServerMethod.SQLConnection := SQLConnection1;
    SQLServerMethod.ServerMethodName := 'THHSVMethods.s_sign';
    SQLServerMethod.params[0].value := orderNo;
    SQLServerMethod.params[1].value := date;
    SQLServerMethod.ExecuteMethod;
    orderNo := SQLServerMethod.params[0].value;
    date := SQLServerMethod.params[1].value;
finnaly
    SQLServerMethod.close;
    FreeAndNil(SQLServerMethod);
end;

the statement traced in sqlserver is No more spaces

It may be a bit strange to use both TSQLConnection and TADOConnection in the server at the same time, but I haven't found a better way