Getting RETURNING value from INSERT INTO

[zd]

Hi!

I need some help with my problem. I'm trying to get the "RETURNING" value from an INSERT INTO statement, but I can't seem to get it right. I read up the documentation, but couldn't solve the problem based on it... (the info is very vague, for me at least)

I'm using Firebird 2.0 with Delphi 7 and IBDAC 2.0 BTW.

Here is an example query:

(note: RowID is an automatically generated value)

Main.DBQuery.DMLRefresh:=true;
Main.DBQuery.SQL.Text:='INSERT INTO Table(RowID, RowData)
VALUES(NULL, :rd) RETURNING RowID';
Main.DBQuery.ParamByName('rd').AsString:="Example Data";
Main.DBQuery.ExecSQL;

Tag:=Main.DBQuery.FieldByName('RET_RowID').AsInteger;

The result:

RET_RowID isn't found.

I've also tried using

Tag:=Main.DBQuery.Fields[0].AsInteger;

But that doesn't work either.

As it seems the me, the returned value is not returned as a field.
If so, how can I read it out?

Could you please give me actual example code I can use?

Thanks in advance:
Z

[Alex]

The returned value is placed to the IBCQuery parameters, not to fields. So you should change only one line of your code from:

Code: Select all

Tag:=Main.DBQuery.FieldByName('RET_RowID').AsInteger; 

to:

Code: Select all

Tag:=Main.DBQuery.ParamByName('RET_RowID').AsInteger; 

[zd]

Thank you, solves the problem!

Do I need the DMLRefresh parameter? Because it seems to work without that too...

[Alex]

The DMLRefresh property enables RETURNING support for dataset updates (Insert\Post operations). When you explicitly execute an insert statement (e.g. Query.SQL.Text := 'Insert into ...') , this property doesn't have any effect.