Parameter value is incorrectly truncated

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
gezas
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Parameter value is incorrectly truncated

Post by gezas » Mon 10 May 2010 04:00

Here is a nasty one:

Use the following SQL to create paramtest table:

Code: Select all

create table paramtest (id int, subject varchar(200));

insert into paramtest values (1, 'DE');
insert into paramtest values (2, 'PWX');
And now try the following code:

Code: Select all

var
  varQry: TMSQuery;
begin
  varQry := TMSQuery.Create(nil);
  try
    varQry.Connection := MSConnection1;
    varQry.SQL.Text := 'SELECT ID FROM PARAMTEST WHERE SUBJECT LIKE :SUBJECT';

    varQry.ParamByName('SUBJECT').AsString := 'AX';

    varQry.Prepared := TRUE;

    varQry.Open;

    ASSERT(varQry.IsEmpty); //This one is ok

    varQry.Close;

    varQry.ParamByName('SUBJECT').AsString := 'PWX';

    varQry.Open;

    ASSERT(varQry.FieldByName('ID').AsInteger = 2);  //This one fails

  finally
    varQry.Free;
  end;

end;
The 2nd assertion fails. Should not.

Here are some details of my investigation so far:

DBMonitor does not show anything suspicous, all the paramters are correctly assigned.

However looking at the trace log of SQL Server Profiler reveals that when the query is opened the 2nd time, the parameter value assigned is "PW" and not "PWX".

Probably this happens because the first time only 2 character length was allocated.

Note that this is only happening if you prepare the query after the parameter is initially assigned and does not happen if you prepare the query before assigning the parameters.

However not all queries can be prepared before assigning the parameters. E.g.: preparing this particular query before assigning the parameter results in exception EMSError: "Statement(s) could not be prepared. Argument data type sql_variant is invalid for argument 2 of like function"

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 11 May 2010 08:32

This problem occurs because query is prepared with the length of the SUBJECT parameter less than maximum available parameter value.
To solve the problem you should prepare the query for the empty parameter, like this:

Code: Select all

  varQry.ParamByName('SUBJECT').DataType := ftString;
  varQry.Prepare; 

gezas
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Post by gezas » Tue 11 May 2010 20:48

Thanks for the workaround.

Although I was more hoping for a reply like:

"We could recreate the problem and it will be fixed in the next build"

Neither ADO or BDE have this behavior.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 12 May 2010 07:37

We will change this functionality in the next SDAC build.

gezas
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Post by gezas » Wed 12 May 2010 20:39

Awesome, thanks

Morris
Posts: 2
Joined: Tue 24 Mar 2009 16:03

Parameter Valie is truncated

Post by Morris » Wed 04 Aug 2010 23:51

Has this problem been fixed in the latest version x.59?

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 05 Aug 2010 08:41

We have added the DescribeParams option that provides automatic parameters describing when a statement is prepared. To solve the problem set this option to True.

Post Reply