Malfunction in parametrized query
Posted: Sun 09 Mar 2014 12:34
Hello,
PgDac 4.2.6, Delphi 7, Win 7 x64.
I have a very strange situation.
In a form with PgConnection, Provider, PgQuery and a ClientDataset, with a parametrized query.
Below is the source code:
Obviously, the result should be 2 rows the first with 5 and the 2nd with 590, but is only the first row, with 5.
More odd, if either of parameter is eliminated or the where clause in the 2nd select is changed like ' :b <5' and the value of b is set to 2 or is change like ' :b >0' (!!!), it'll show the correct result.
I need a solution, because this situation appears in a report with a huge and complex query in production.
Thank you,
Radu B.
PS
The culprit is the number of parameters, which has to be equal in all the select statement of the union.
A temporary workaround would be placing the second parameter in the first select.
Unfortunately, in my case isn't functioning because in the first select I have a bunch of parameters which appears in the select list and they have to be paired with the subsequent selects.
PgDac 4.2.6, Delphi 7, Win 7 x64.
I have a very strange situation.
In a form with PgConnection, Provider, PgQuery and a ClientDataset, with a parametrized query.
Below is the source code:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var i: integer;
begin
PgConnection1.Connected:=true;
with ClientDataset1 do begin
if Active then Close;
CommandText:=
' select 5 where :a >0'+#13#10+
' union all'+#13#10+
' select 590 where :a >0 and :b <0 ';
for i:=0 to Params.Count-1 do begin
Params[i].ParamType:=ptInput;
Params[i].DataType:=ftInteger;
if Params[i].Name='a' then Params[i].Value:=1;
if Params[i].Name='b' then Params[i].Value:=-1;
end;
Open;
end;
end;
More odd, if either of parameter is eliminated or the where clause in the 2nd select is changed like ' :b <5' and the value of b is set to 2 or is change like ' :b >0' (!!!), it'll show the correct result.
I need a solution, because this situation appears in a report with a huge and complex query in production.
Thank you,
Radu B.
PS
The culprit is the number of parameters, which has to be equal in all the select statement of the union.
A temporary workaround would be placing the second parameter in the first select.
Unfortunately, in my case isn't functioning because in the first select I have a bunch of parameters which appears in the select list and they have to be paired with the subsequent selects.