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

[cov]

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.

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

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]

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]

hmm see my image attachment on how it works
http://www.lazarus.freepascal.org/index.php?action=dlattach;topic=21240.0;attach=6616;image

[cov]

.... 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]

congrats  and did you know TSQLite3Connection and TMySQLConnection have getInsertID method that can return auto increment id after insert without do this trick.