Problem with to long Values for a Param and prepared Statements

[Jank]

Hello,

ich have a problem with IBDAC 4.0.2 (also in 3.5.0) and Delphi xe2/2009.

I have a Firebird-table with char(10) field. I try a select with this field in my where-clause. The value has more than 10 chars, the value is '1234567890123' and I have a row in my table, with the value '1234567890'. If I open my query unprepared, the query don't find this row (recordcount = 0). If I open the query prepared i find the row.
A different behaviour is very bad in this case.

An excample:

My Table:
CREATE TABLE TEST (
TEXT CHAR(10) NOT NULL,
constraint PK_TEST primary key (TEXT))

My Source:
procedure TForm1.Button1Click(Sender: TObject);
var
Dataset: Tibcquery;
Value: string;
begin
Dataset := TIBCQuery.Create(nil);
Dataset.sql.Text := 'Select * from TEST where TEXT = :TEXT';
Dataset.Options.CacheBlobs := false;
Dataset.Options.DescribeParams := true;
Dataset.Prepare;

Value := '1234567890123';
Dataset.parambyname('TEXT').Value := Value;
Dataset.open;
caption := inttostr(Dataset.recordcount);
Dataset.Append;
Dataset.fieldbyname('TEXT').Value := Value;
Dataset.Post;
Dataset.close;

Dataset.UnPrepare;
Dataset.Free;
end;

Execute this, it will append a line to the table. With the Next klick, the recordcount is 1 after the open, after that it is 2. Then comment the prepare/unprepare-lines out and run it again, the recordcount ist 0, every time.

Is there in 4.2 still the same behaviour? Is it volitional?

Regards Jan

[ZEuS]

Setting the TIBCDataSetOptions.DescribeParams property to True is used to query parameters properties (in particular, the Size property) from the server when executing the Prepare method of the dataset. In this case, the parameter value will be set empty, if the parameter size retrieved from the server differs from the existing parameter size.
In your example, when the Prepare method is called explicitly, the correct 'TEXT' parameter size (10 chars) is retrieved from the server and then the parameter obtains the correct value ('1234567890').
In the second case, the parameter value is initially set to '1234567890123' (13 chars) and when the Dataset.Open method is called, the parameter retrieves the correct size from the server (10 chars) and it's value is set to null. So, in this case you retrieve no data in the opened dataset.

[Jank]

Hello,

thank you for your answer.

Setting the TIBCDataSetOptions.DescribeParams property to True is used to query parameters properties (in particular, the Size property) from the server when executing the Prepare method of the dataset. In this case, the parameter value will be set empty, if the parameter size retrieved from the server differs from the existing parameter size.

Thats that what I am expecting, but not what I see.
My Query has DescribeParams = true, it is prepared, the size of the parameter retrieved from the server (10) is shorter then the existing size (13) but the param ist set to empty. I watched in the TGDSCommand.BindParams method to the value of PAChar(Param.ValuePtr). It is 10 characters long, in my example.
If I do not prepare the query, this value is empty, as you've discribed it.

Greetings Jan

[ZEuS]

Yes, the parameter isn't set to empty because the Prepare method is called (and the parameter retrieved its size) before setting the parameter value.
When the Prepare method is commented, it will be called implicitly after setting the parameter value (when the Open method is executed). In this case, the parameter value will be set to empty, because the parameter size retrieved will differ from the size of the existing parameter value.