How to get last autoinc value in Interbase

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

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

[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 ?

[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;