Return last autoInc value after an INSERT

[kneighbour]

I have an autoinc field on a table called ID. Works fine. I want to use an INSERT statement to insert a record, and return the value of the autoinc field. This is on a Firebird 2.5 database. Something like

INSERT INTO MYTABLE (field1, field 2) VALUES (2,'something') RETURNING ID;

I can use the GUI interface IBExpert with this query and it works just fine, so I seem to have the query correct. I have tried many UNI components and cannot get it to work.

Standard UniQuery
UniQuery.close;
UniQuery.sql.text:='INSERT INTO MYTABLE (field1, field 2) VALUES (2,'something') RETURNING ID';
UniQuery.open;

This gives me an error in that I get the message 'SQL statement does not return rows'.
If I try UniQuery.ExecSQL, then nothing is returned.

I have tried using the UniDacDemo program with this query and get the same results.
ie UpDateSQL tab/demo.
Open - 'SQL statement does not return rows'
Execute - no response

SQL tab
this works - if I read the parameter RET_ID, then it has the correct value in it

Is there anyway I can get the UniQuery mode to work?

[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 TUniQuery.Open method. For such queries, you should call the TUniQuery.Execute method of the dataset instead of TUniQuery.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: https://www.devart.com/ibdac/docs/index ... ataset.htm
To get the OUT parameter value, use the following code:

Code: Select all

  UniQuery.Close;
  UniQuery.Sql.Text := 'INSERT INTO MYTABLE (field1, field 2) VALUES (2,''something'') ' +
    ' RETURNING ID';
  UniQuery.Execute;
  ShowMessage(UniQuery.ParamByName('RET_ID').AsString);