Parameter value is incorrectly truncated

[gezas]

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]

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]

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]

We will change this functionality in the next SDAC build.

[gezas]

Awesome, thanks

[Morris]

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

[Dimon]

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.