Retrieving the value of an Auto incremented ID Field in Firebird [SOLVED] lazarus / 16-Databases ---------------------------------------------------------------------- cov · 2013-06-20 07:27 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 · 2013-06-20 10:28 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 · 2013-06-20 12:17 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 Quote- 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 · 2013-06-20 12:36 Quote from: cov on June 20, 2013, 02:17:49 pmThanks, 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 · 2013-06-20 12:42 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 · 2013-06-20 13:03 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 · 2013-06-20 13:41 Thanks very much. I'll implement that. ---------------------------------------------------------------------- BigChimp · 2013-06-20 14:15 Quote from: JD on June 20, 2013, 12:28:19 pmThe 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 QuoteSQLQuery1.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 · 2013-06-20 14:25 Quote from: BigChimp on June 20, 2013, 04:15:47 pmQuote from: JD on June 20, 2013, 12:28:19 pmThe 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 QuoteSQLQuery1.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 · 2013-06-20 14:37 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 · 2013-06-20 14:59 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 · 2013-06-20 15:33 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 · 2013-06-20 15:49 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 · 2013-06-20 16:45 Quote from: JD on June 20, 2013, 12:28:19 pmThe 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 · 2013-06-20 20:43 Quote from: ttomas on June 20, 2013, 06:45:10 pmIn 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 ---------------------------------------------------------------------- cov · 2013-06-21 05:59 That was my concern, too; the notion that two (or more) users may be entering records at the same time opens up the (albeit remote) possibility that the wrong ID may be returned. Quote from: kodok.buncit on June 20, 2013, 05:49:14 pmuse 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 I still can't get this to work. Even using SQLQuery1.Open. Returns the 'Unknown token' Error as above. Even if I do use spaces One of the posts here suggests that there is an issue with RETURNING INTO. ---------------------------------------------------------------------- cov · 2013-06-21 06:21 Just to reiterate (using a different table). Table setup: Code: [Select]  IBConnection1.ExecuteDirect('CREATE TABLE PATHS('+               'ID INTEGER NOT NULL PRIMARY KEY, '+               'PATH VARCHAR(800), '+               'INCEPTION TIMESTAMP, '+               'DUPDATE TIMESTAMP)');Trigger setup: Code: [Select]  IBConnection1.ExecuteDirect('SET GENERATOR GEN_PATHS_ID TO 0;');   IBConnection1.ExecuteDirect('CREATE TRIGGER TR_PTH FOR PATHS ACTIVE BEFORE INSERT POSITION 0 AS '+                               'BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_PATHS_ID, 1);END;'); This works: Code: [Select]    SQLQuery1.Close;     SQLQuery1.SQL.Text:='INSERT INTO PATHS (PATH,INCEPTION,DUPDATE) VALUES ('''+                      DPath+''','''+dCreatedS+''','''+dUpdatedS+''')';     SQLQuery1.ExecSQL;     SQLQuery1.Close;     SQLQuery1.SQL.Text:='select gen_id(GEN_PATHS_ID, 0) from rdb$database';     SQLQuery1.Open;     PathID:= SQLQuery1.FieldByName('GEN_ID').AsInteger;  This doesn't work (Generates an 'Unknown Token' Error): Code: [Select]    SQLQuery1.SQL.Text:='INSERT INTO PATHS (PATH,INCEPTION,DUPDATE) VALUES ('''+                      DPath+''','''+dCreatedS+''','''+dUpdatedS+''') RETURNING ID INTO :ID';     SQLQuery1.Open; ---------------------------------------------------------------------- kodok.buncit · 2013-06-21 06:45 hmm see my image attachment on how it works http://www.lazarus.freepascal.org/index.php?action=dlattach;topic=2124 0.0;attach=6616;image ---------------------------------------------------------------------- cov · 2013-06-21 06:45 .... And THIS works! Code: [Select]    SQLQuery1.Close;     SQLQuery1.SQL.Text:='INSERT INTO PATHS (PATH,INCEPTION,DUPDATE) VALUES ('''+                      DPath+''','''+dCreatedS+''','''+dUpdatedS+''') RETURNING ID';     SQLQuery1.Open;     PathID:=SQLQuery1.FieldByName('ID').AsInteger;     SQLQuery1.Close;  Marking the thread as [SOLVED] ---------------------------------------------------------------------- kodok.buncit · 2013-06-21 06:56 congrats  and did you know TSQLite3Connection and TMySQLConnection have getInsertID method that can return auto increment id after insert without do this trick.