Page 1 of 1
How to get last autoinc value in Interbase
Posted: Tue 16 Oct 2012 12:30
by Tsagoth
If I use TUniquery to do INSERT into Interbase XE db, how do I retrieve the autoinc value that was used by the generator for the primary key field ? Help file says LastInsertID property only valid for MySql and Postgresql.
Interbase XE doesn't support a RETURNING option, so it's not clear how to get last insertid.
Re: How to get last autoinc value in Interbase
Posted: Tue 16 Oct 2012 14:29
by AndreyZ
Hello,
The TUniQuery component can work with generators and returns the generated value to the client automatically. To work with generators, you can use the KeyGenerator, GeneratorMode, and GeneratorStep specific options. Here is an example:
Code: Select all
UniQuery1.SQL.Text := 'SELECT * FROM MYTABLE';
UniQuery1.SpecificOptions.Values['KeyGenerator'] := 'GEN_MYTABLE_ID';
UniQuery1.KeyFields := 'ID';
UniQuery1.Open;
UniQuery1.Append;
UniQuery1.Post; // here the new value from the generator is put to the ID field
You can find information about using generators in the "Using UniDAC with InterBase/Firebird" article of the UniDAC documentation.
Re: How to get last autoinc value in Interbase
Posted: Tue 16 Oct 2012 15:14
by Tsagoth
So code should be [code]
UniQuery1.SQL.Add('INSERT INTO SYSTEM(pkey,name,playerid)');
UniQuery1.SQL.Add('VALUES(GEN_ID(mygen1,1), :name,:playerid)');
UniQuery1.ParamByName('name').Asstring := zstar.Name;
UniQuery1.ParamByName('playerid').AsInteger := zstar.PlayerID;
UniQuery1.SpecificOptions.Values['GeneratorMode'] := gmInsert;
UniQuery1.SpecificOptions.Values['KeyGenerator'] := 'mygen1';
UniQuery1.KeyFields := 'pkey';
UniQuery1.execute;
lastid := Uniquery1.ParamByName('pkey').AsInteger;
[/code]
Is that right ?
Re: How to get last autoinc value in Interbase
Posted: Wed 17 Oct 2012 09:11
by AndreyZ
No, it is not correct. If you want to execute INSERT statements, you should get the generator value before executing them. Here is a code example that demonstrate this:
Code: Select all
UniQuery1.SQL.Text := 'SELECT GEN_ID(MYGEN1, 1) FROM RDB$DATABASE';
UniQuery1.Open;
lastid := UniQuery1.Fields[0].AsInteger;
UniQuery1.Close;
UniQuery1.SQL.Clear;
UniQuery1.SQL.Add('INSERT INTO SYSTEM(pkey, name, playerid)');
UniQuery1.SQL.Add('VALUES(:pkey, :name, :playerid)');
UniQuery1.ParamByName('pkey').AsInteger := lastid;
UniQuery1.ParamByName('name').AsString := zstar.Name;
UniQuery1.ParamByName('playerid').AsInteger := zstar.PlayerID;
UniQuery1.Execute;