False error message "SQL Statement doesn´t return rows"

[OWurdak]

Hello

I want to open a SQL Statement like
Insert into Table (ID, Field1,...) Values (GEN_ID(ID_GEN,1), 'asdf' ,...) RETURNING ID;
and want the returning ID (this is valid since Firebird 2.0)
If I call
TIBCQuery.open;
an error message "SQL Statement doesn´t return rows" is displayed which isn´t true.
Perhaps your SQL parse overlook the returning clause.
If I call
TIBCQuery.execute;
the query runs fine but the value of returning TField object is always 0

What should I do?

regards
Oliver Wurdak

[ViktorV]

On executing an INSERT query, the statement doesn't return the recordset - and you get the "SQL statement doesn't return rows" error when calling the TIBCQuery.Open method. For such queries, you should call the TIBCQuery.Execute method of the dataset instead of TIBCQuery.Open.
For the fields, that are specified after RETURNING, there are automatically generated output parameters with the RET_ prefix. This is described in the documentation: http://www.devart.com/ibdac/docs/index. ... ataset.htm
To access the output value of the ID parameter, you should use the following code: TIBCQuery.ParamByName('RET_ID').Value

[OWurdak]

Thank you for the answer. It works perfect now.
In your example you should write: TIBCQuery.ParamByName('RET_ID').Value
The point which is a little distracting to me is that if you autogenerate all TFields you get a
IBCQueryID: TIntegerField which is completely useless.

regards
Oliver Wurdak

[ViktorV]

It is good to see that the problem has been solved. Feel free to contact us if you have any further questions about IBDAC.

[soyjoy]

This occurring the same with me, but when I delete a record. Below is the code:

DELETE FROM
TB_PERM
WHERE
FD_CAR = :FD_CAR

UniQueryPerm.Close;
UniQueryPerm.Params[0].asInteger := UniQueryTabFD_CAR.asInteger;
UniQueryPerm.Execute;

Error return:
"SQL statement doesn't return rows"

[ViktorV]

Unfortunately, we couldn't reproduce the problem. To investigate the problem, please compose a small sample reproducing the problem and send it to viktorv*devart*com., including scripts for creating database objects.

[soyjoy]

The error occurs if I am using a query, but if I change the component by a uniSQL the .Execute command will function normally.
Its the uniQuery execute commands such as INSERT or DELETE?

[ViktorV]

TUniQuery alloows to execute INSERT and DELETE queries correctly using the TUniQuery.Execute method. As we wrote earlier, we couldn't reproduce the problem. To investigate the problem, please compose a small sample reproducing the problem and send it to viktorv*devart*com., including scripts for creating database objects.