Return of Firebird generator value

[betadevart]

Hello,
I know this question arised many times before, but I still can't get it into practice.
Is there a way to return JUST the generator value of a newly inserted master record to the client and setting up detail foreign key fields?

1.
I would avoid using DMLRefresh=true because it brings back everything, that seems absolutely overkill me.

2.
I found no way get back generated id by params and setting ReturnParams=true. I used BeforeUpdateExecute as I saw in the older posts. No success.

A started with "select id, name from partner".
Leaved UniDac to generate the insert statement.
Had no "returning" clause. (No DMLRefresh)
ReturnParams=true
ListDs->Params->Items[ListDs->Params->Count-1]->ParamType = ptInputOutput;
ListDs->Params->Items[ListDs->Params->Count-1]->DataType = ftInteger;
Had no new value in "id" parameter.
?

[betadevart]

Sorry!
The starter statement is "select * from partner where id=:id".

[AndreyZ]

Hello,

UniDAC automatically returns the new value generated by the generator. Here is a code example that demonstrates this:

Code: Select all

UniQuery.SQL.Text := 'select * from partner';
UniQuery.KeyFields := 'id';
UniQuery.SpecificOptions.Values['KeyGenerator'] := 'GeneratorName';
UniQuery.Open;
UniQuery.Append;
UniQuery.Post; // here the generated value will be assigned to the id field value

For more information, please read the "Using UniDAC with InterBase/Firebird" article in the UniDAC documentation.

[betadevart]

Thanks for replying,

I have trigger on the sever side! As far as I know your example is for client controlled key generation.
I need the id back after server trigger has been applied. Just the primary key, not all the fields.

How about that?

(I have skimmed through documentation you mentioned before, and I found no answers
for this case. That's why I turned to this forum.)

[ZEuS]

Hello.

Yes, you are right. The TUniQuery.KeyFields property and the KeyGenerator option are used for client controlled key generation.
When using triggers to fill in the key field and there is a need to return the key field value, you should do the following:
- add the "RETURNING ID" statement to the end of the UniQuery.SQLInsert property text, where ID is the name of the key field;
- set the UniQuery.Options.ReturnParams property to True.
After doing this, the newly generated value of the key field will be accessible through the UniQuery.FieldByName('ID').Value property.

So, the example above has to be modified like the following:

Code: Select all

UniQuery.SQL.Text := 'select * from partner';
UniQuery.SQLInsert.Text := 'insert into partner(id, field1, field2) values(:id, :field1, :field2) returning id';
UniQuery.Options.ReturnParams := True;
UniQuery.Open;
UniQuery.Append;
UniQuery.Post;
ShowMessage(UniQuery.FieldByName('id').AsString);

[betadevart]

Hello,

I understand your options now. Still there is a question. If I have write SQL insert even the simplest table inserts, what is the reason not to write the whole sql insert statement myself (ExecSQL)? I could imagine that if KeyField is SET AND ReturnParams is TRUE, then RETURNING KeyField is inserted by the component. That's the main reason for using it anyway, from my point of view.
Again, returning all fields is not very usefull (DMLRefresh), but returning the unknown, newly generated id is necessary in any case of master-detail. Also DMLRefresh can easily replaced by a reread. The unknown id just can't be retreived!

Do you have any other idea, instead of having all those hand written SQL-s?
Is there a place/event where I could modify the automatically generated SQLInsert, and just append "returning id" at the and of it?

The other possibility is recognizing AutoInc field by devart. AutoInc field should be returned (or with some options)!?

The problem is interesting becaause it seems to be such a substantial requrirement. How come others can live without master sequences???

Also I have found a similar topic. It says in the end:
"qMaster.ApplyUpdates; // here you are retrieving the IDMaster field value from the server"
How? It doesn't.

[ZEuS]

Yes, you can use the TUniQuery.KeyFields property to specify the name of the key field in the table.
In this case, when you set the TUniQuery.SQLInsert property empty, the insert statement will be generated automatically and will return only the newly generated key field value.

If you need to set the TUniQuery.SQLInsert property manually, you can use the TUniQuery.BeforeExecute event handler to append the "RETURNING ID" statement to the end of it, for example:

Code: Select all

procedure TForm1.UniQueryBeforeExecute(Sender: TObject);
begin
 if Pos('RETURNING ID', (Sender as TUniQuery).SQLInsert.Text) = 0 then
   (Sender as TUniQuery).SQLInsert.Add('RETURNING ID');
end;

There is no possibility to recognize AutoInc fields in Firebird, because Firebird does not have the built-in "autoincrement" field type and uses generators and triggers to provide autoincrement.

[betadevart]

I've followed your suggestion, but I didn't succeded unfortunately.
I couldn't get any "returning id", only in the case of DMLRefresh that gets all fields.
I set KeyFields="id", I also tried with ReturnParams=true.
No "RETURNING" clause.
I tried with and without "id" changed (0) in the insert.
No "RETURNING" clause.
Do you have any idea?