Search code examples
delphifirebirdc++builder

Firebird TIBQuery insert with returning ... INTO

I have a firebird 2.x database with Generator and a trigger to generate the key field. I need to get the returned value from below query.

INSERT INTO XXXX (vdate,description) values ('"+ VDate +"','"+ Description +"') returning vno INTO :ParamVoucherNo

I tried several versions of below code but it dont wrok and I get

Dynamic sql error sql error code = -104

Is it really possible to get the return value in delphi using TIBQuery ?

Query1->SQL->Clear();
Query1->SQL->Add("INSERT INTO XXXX (vodate,description) values ('"+ VDate +"','"+ Description +"') returning vno INTO :ParamVoucherNo");

Query1->Params->ParamByName("ParamVoucherno")->ParamType = ptResult;
Query1->Params->ParamByName("ParamVoucherno")->DataType = ftInteger;
Query1->Params->ParamByName("ParamVoucherno")->Value = "";
Query1->Prepare();
Query1->ExecSQL();

Any suggestions?

Solution

  • From Firebird README.returning:

    The INTO part (i.e. the variable list) is allowed in PSQL only (to assign local variables) and rejected in DSQL.

    As IBX uses DSQL, you should exclude INTO part from your query.

    INSERT ... RETURNING for DSQL looks the same as a call of a stored procedure, which returns result set. So, you have to use Open instead of ExecSQL.