Hello @sscala,
Working with Base Firebird embedded is a bit different than Firebird server. There is a point to this.
First, there is no auto increment type in Firebird. Auto increment is done through Generators
which use a sequence
to create a next sequential number. This Generator is used by a Trigger
(BEFORE insert) and obtains the next number to be used for the value.
With this you can use SQL to obtain the current value in the Generator (this is also the last issued value):
SELECT GEN_ID( CUSTOMER_ID_GEN, 0 ) FROM RDB$DATABASE
Now using server this is easy to find since you can easily view Triggers & Generators using a tool such a FlameRobin. But this is not the case when using Firebird embedded in Base. In Base first you need to get the name of the generator. This need be done only once so you can use it in the SQL. That can be done using the SQL found on this page → RE: [firebird-support] reset autoincrement field. My tests show it to produce the name of the Generator you need. Then just use it in the Select statement above (replacing CUSTOMER_ID_GEN
with it) to get the last issued value.
So if your returned generator name is RDB$1
then the statement you need is:
SELECT GEN_ID( RDB$1, 0 ) FROM RDB$DATABASE
You can run this in your macro after your INSERT statement and the result set will return the current value which is the ID from your INSERT.
Finally, I do agree with you on the RETURNING
portion of SQL. Have not taken a lot of time looking into this but will when I have some free time. Not so sure this really applies to the situation regarding the use of Generators. Will post if I find something relevant.