Dynamic SQL ErrorSQL error code = -804

[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

[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.

[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.

[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;

[inageib]

thanks
can you please give me code how to remove all parameters from the component ?

[inageib]

I tried to use these methods but still got same error

Code: Select all

Params.clear
Params.Delete();

[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';
...

[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 ?

[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;

[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.

[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 ?

[AndreyZ]

In this case, clearing of the SQL property is the only way to clear parameters.

[inageib]

ok thanks alot

[AndreyZ]

I am glad I could help.