Page 1 of 1
Dynamic SQL ErrorSQL error code = -804
Posted: Fri 01 Mar 2013 02:16
by inageib
Hi,
Trying to use the TIBCSQL to return value from SQL and it work but the second time I call the function that host the code I get this error:
Dynamic SQL ErrorSQL error code = -804
SQLDA missing or incorrect version, or incorrect number/type of variables.
here is the function code which I founded in the documentation:
Code: Select all
SQL1.SQL.Text := 'Select sum(qty) from mytable where myid = :myID';
SQL1.ParamByName('myID').AsInteger := mastertable.FieldByName('myID').AsInteger;
with TParam(SQL1.Params.Add) do begin
ParamType := ptOutPut;
DataType := ftInteger;
Name := 'Total_Qty';
end;
SQL1.Prepare;
SQL1.Execute;
while SQL1.ExecuteNext do
mytotal := SQL1.ParamByName('Total_Qty').AsInteger;
SQL1.UnPrepare;
This error actually occur in the line of "while SQL1.ExecuteNext do". I guess I should remove the paramters and I tried that but did not work either
please advise
Thanks
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Fri 01 Mar 2013 09:12
by AndreyZ
Hello,
Please specify the following:
- the script to create the mytable table;
- the exact version of IBDAC. You can learn it from the About sheet of TIBCConnection Editor;
- the exact version of your IDE;
- the exact version of InterBase or Firebird server and client. You can learn it from the Info sheet of TIBCConnection Editor.
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Fri 01 Mar 2013 11:58
by inageib
mytable:
Code: Select all
CREATE TABLE mytable (
ID1 INTEGER NOT NULL,
myID INTEGER,
qty INTEGER
);
ALTER TABLE mytable ADD CONSTRAINT PK_mytable PRIMARY KEY (ID1);
IBDAC 4.5.10
Delphi IDE 14.0.3593.25826
Firebird 2.5 64bit
forgot to say
important thing: the error appear only on the second function call (or later calls) it work normally the first time only.
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Fri 01 Mar 2013 12:51
by AndreyZ
I have reproduced this problem. When you execute your code first time, there are no parameters. When you set the SQL property, the first parameter is created. After this, you create a second parameter manually, so there are two parameters. When you execute your code second time, there are already two parameters. This happens because you do not change the SQL property. IBDAC stores the information that your command has two parameters, and when you set the SQL property, IBDAC creates two parameters. After this, you again create a parameter manually, so there are three parameters. This causes the error on execution.
To avoid such problem, you should check if parameter exists and create it only if it does not. Here is a code example:
Code: Select all
if SQL1.Params.FindParam('Total_Qty') = nil then
with TParam(SQL1.Params.Add) do begin
ParamType := ptOutPut;
DataType := ftInteger;
Name := 'Total_Qty';
end;
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Fri 01 Mar 2013 13:01
by inageib
thanks
can you please give me code how to remove all parameters from the component ?
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Fri 01 Mar 2013 21:42
by inageib
I tried to use these methods but still got same error
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Mon 04 Mar 2013 09:25
by AndreyZ
You should clear the SQL property of the TIBCSQL component. Here is a code example:
Code: Select all
SQL1.SQL.Clear;
SQL1.SQL.Text := 'Select sum(qty) from mytable where myid = :myID';
...
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Tue 05 Mar 2013 14:25
by inageib
Thanks
If I want to add more than one parameter, same like this one:
Code: Select all
with TParam(SQL1.Params.Add) do begin
ParamType := ptOutPut;
DataType := ftInteger;
Name := 'Total_Qty';
end;
how I could do that ?
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Wed 06 Mar 2013 09:19
by AndreyZ
You can use the same approach. Here is an example:
Code: Select all
begin
SQL1.SQL.Clear;
SQL1.SQL.Text := 'Select sum(qty), avg(qty) from mytable where myid = :myID';
SQL1.ParamByName('myID').AsInteger := mastertable.FieldByName('myID').AsInteger;
with TParam(SQL1.Params.Add) do begin
ParamType := ptOutPut;
DataType := ftInteger;
Name := 'Total_Qty';
end;
with TParam(SQL1.Params.Add) do begin
ParamType := ptOutPut;
DataType := ftInteger;
Name := 'Avg_Qty';
end;
SQL1.Prepare;
SQL1.Execute;
while SQL1.ExecuteNext do begin
mytotal := SQL1.ParamByName('Total_Qty').AsInteger;
myavg := SQL1.ParamByName('Avg_Qty').AsInteger;
end;
SQL1.UnPrepare;
end;
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Wed 06 Mar 2013 12:33
by inageib
I already tried these approaches but it give me errors so was thinking there should be another way:
invalid statement handle.
Dynamic SQL ErrorSQL error code = -804
SQLDA missing or incorrect version, or incorrect number/type of variables.
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Thu 07 Mar 2013 04:24
by inageib
I see now this error because returned values should be equal to parameters
one question only for information is there any other way to clear parameters than using sql.clear method ?
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Thu 07 Mar 2013 09:08
by AndreyZ
In this case, clearing of the SQL property is the only way to clear parameters.
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Thu 07 Mar 2013 12:54
by inageib
ok thanks alot
Re: Dynamic SQL ErrorSQL error code = -804
Posted: Thu 07 Mar 2013 13:29
by AndreyZ
I am glad I could help.