Retrieving the value of an Auto incremented ID Field in Firebird [SOLVED]

[cov]

I have created a table:

Code: [Select]

  IBConnection1.ExecuteDirect('CREATE TABLE FIELDS(ID INTEGER NOT NULL PRIMARY KEY, FIELD1 VARCHAR(60))');
I have created a generator and a trigger to implement auto incrementing the ID Field.

Code: [Select]


  IBConnection1.ExecuteDirect('CREATE GENERATOR GEN_FIELDS_ID;');
  IBConnection1.ExecuteDirect('SET GENERATOR GEN_FIELDS_ID TO 0;');


  IBConnection1.ExecuteDirect('CREATE TRIGGER TR_FLD FOR FIELDS ACTIVE BEFORE INSERT POSITION 0 AS '+
                              'BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_FIELDS_ID, 1);END;'); 
All seems to be working ok.

But I need to use the 'ID' field created in another table so that I can link to it. How do I retrieve this?

I'm sure it's something pretty simple.

 

[JD]

The SQL statement below will retrieve the last ID in the Fields table:

Code: [Select]

select gen_id(gen_fields_id, 0) from rdb$database

JD

[cov]

Thanks, JD.

Will that give me the ID of the record I've just inserted?

I had tried:

Code: [Select]

SQLQuery1.SQL.Text:='INSERT INTO FIELDS(FIELD1) VALUES(:F)RETURNING ID INTO :ID';
SQLQuery1.ExecSQL;
But I got an error message after SQLQuery1.ExecSQL

- Dynamic SQL Error
- SQL Error code = -104
- Token unknown
- ?

I was hoping to use:

Code: [Select]

id:=SQLQuery1.Params.ParamByName('ID').AsInteger;
to obtain the :ID Parameter, but it never got that far.

[JD]

Thanks, JD.

Will that give me the ID of the record I've just inserted?

Yes it will. It queries the generator that creates the auto IDs. That is the method I use in my own application. The value of the ID will be in a Gen_ID field.

[cov]

Ok.

How do I assign a variable to the result of the query?

I'm assuming that the ID retains it's integrity, even if another user might be inserting another record concurrently?

[JD]

Code: [Select]

with MyQuery do begin
  SQL.Clear;
  SQL.Add('select gen_id(gen_fields_id, 0) from rdb$database');
  Open;
  // Get the ID of the new record
  intNewID := FieldByName('GEN_ID').AsInteger;
  Close;
end;

I use it in a multiuser environment with updates from the company LAN or over the Internet. There are no problems whatsoever since it is Firebird that does the work. I'm just asking the Firebird engine to give me the last committed ID value.

JD

[cov]

Thanks very much.

I'll implement that.

[BigChimp]

The SQL statement below will retrieve the last ID in the Fields table:

Code: [Select]

select gen_id(gen_fields_id, 0) from rdb$database


Are you sure? I'd suspect it will give the *next* vailable ID value.
http://www.firebirdsql.org/refdocs/langrefupd20-genid.html

SQLQuery1.SQL.Text:='INSERT INTO FIELDS(FIELD1) VALUES(:F)RETURNING ID INTO :ID';

I'd go the INSERT...RETURNING route... making sure to avoid typos (like missing spaces).

[JD]

The SQL statement below will retrieve the last ID in the Fields table:

Code: [Select]

select gen_id(gen_fields_id, 0) from rdb$database


Are you sure? I'd suspect it will give the *next* vailable ID value.
http://www.firebirdsql.org/refdocs/langrefupd20-genid.html

SQLQuery1.SQL.Text:='INSERT INTO FIELDS(FIELD1) VALUES(:F)RETURNING ID INTO :ID';

I'd go the INSERT...RETURNING route... making sure to avoid typos (like missing spaces).

I'm definite! When a new generator is created, its value is zero. Firebird adds 1 to the value retrieved from the generator when creating a new record.

This method has so far served me in maintaining data integrity in a multiuser WAN/Internet environment. 

JD

[BigChimp]

I suspect you're talking about getting a generator value and using that when inserting a new record. I think cov is talking about getting the value after a record has been added (e.g. when using generators/sequences+a trigger to simulate autoincrement behaviour).

If I misunderstood you, perhaps it's a good idea to file a bug report against the documentation in my previous link.

[cov]

Big Chimp,

You're correct in saying I want to insert a record and then retrieve the ID of that inserted record.

As I understand it, JD's method uses (gen_fields_id, 0) as opposed to (gen_fields_id, 1) which would increment the value.

I've tried again using the 'INSERT INTO ... RETURNING ID INTO :ID', but inspite of checking all spaces I cannot see where the typo occurs.

I had seen a post on Stackoverflow which implied that the use of RETURNING INTO was not possible in DSQL. Unfortunately I can't seem to find the page... 

[BigChimp]

Mmm yes, you're right, cov (and JD). The 0 does change things... Shame on me for not reading the docs I've linked 
However, I would *strongly* suggest to read the documentation when you cannot get an SQL statement working before doing other research.

As for insert... returning not working for DSQL: very strange... what else would it be used for?[1]
See
http://www.firebirdsql.org/refdocs/langrefupd21-insert.html#langrefupd21-insert-returning



I've used it in Lazarus. It works.

Re typos:

Code: [Select]

SQLQuery1.SQL.Text:='INSERT INTO FIELDS(FIELD1) VALUES(:F)RETURNING ID INTO :ID';

Shouldn't VALUES(:F) be VALUES (F:)?
and (:F)RETURNING be (:F) RETURNING?

[1] Yes, the docs list another possible use.

[kodok.buncit]

use open instead ExecSQL, because execsql doesn't return resultset

Code: [Select]

SQLQuery1.SQL.Text:='INSERT INTO FIELDS(FIELD1) VALUES(:F)RETURNING ID INTO :ID';
SQLQuery1.open;
//then you can do
intNewID := SQLQuery1.FieldByName('ID').AsInteger;
its work for me

[ttomas]

The SQL statement below will retrieve the last ID in the Fields table:

Code: [Select]

select gen_id(gen_fields_id, 0) from rdb$database

JD

This is wrong.
In heavy used system, it's matter of time when this query return same result for different inserts.
Generators and gen_id work out of transaction
1. User A insert record  ID=1
2. User B insert record  ID=2
3. User A read gen_id(xxx,0) result=2   :-(
4. User B read gen_id(xxx,0) result=2
In my systems I use OnBeforePost event with
select gen_id(xxx,1) from rdb$database
and set my ID

[JD]

In my systems I use OnBeforePost event with
select gen_id(xxx,1) from rdb$database
and set my ID

I have a trigger that does the same thing so it works out fine.

JD