Refresh Current Record To Access Value Of Newly Inserted AutoInc Field

[Nathan Wild 3]

I am trying to append records to a pair of tables in a master/detail relationship.  The master dataset is a writable FDQuery with an AutoInc key field.  When I post to this dataset the autoinc field has a value of -1.  I want to go on and write out detail records, but nothing I've tried will give me the actual value for autoinc field in the newly posted master dataset.

 

I am not able to call FDQuery.Refesh() as it gives me a "SQLBind Parameter has not been called for parameter" error (I've posted about this before and apparently it is a known bug).

 

FDQuery.RefreshRecord() gives me an error "[FireDAC][DApt]-400. Fetch command fetched [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user"

 

As my master dataset is sorted by the autoinc key field, I was able to solve the issue by calling FDQuery.First() and FDQuery.Last(), but this feels wrong, so I thought I would check in and see if anyone had a suggestion for how this *SHOULD* be done?

 

Thanks in advance...

[Uwe Raabe 2057]

What database are you using?

[Ruslan 5]

Usualy I use "returning" to get the value of new autoincremented field (Firebird).

[Uwe Raabe 2057]

There actually is some documentation about this: Auto-Incremental Fields (FireDAC)

[Nathan Wild 3]

Sorry...  I am using Pervasive.

[Nathan Wild 3]

1 hour ago, Ruslan said:

Usualy I use "returning" to get the value of new autoincremented field (Firebird).

What do you mean by "returning"?

[jobo 1]

"returning" mentioned here is part of sql command, it >returns values of auto id and other expressions.

 

Maybe this is not available in Pervasive. Here is firebird documentation explaining it:

http://www.firebirdsql.org/refdocs/langrefupd21-insert.html

 

This is of course a solution coming from the database side. Firedac offers client side solutions.

[Uwe Raabe 2057]

On 4/12/2019 at 1:20 PM, Nathan Wild said:

I am using Pervasive.

AFAIK, Pervasive is not a directly supported database in FireDAC (only via ODBC). Therefore it may be necessary to handle those AutoInc fields manually as described in the documentation.

[Hans J. Ellingsgaard 21]

If you can call the Generator directly, like in Interbase/Firebird and other db's, I would prefer
that approach. I can't find any documentation that this is possible with Pervasive db's, but you can
check it out.

 

Create a query that calls the Generator and wrap it in a function like this. (The example is made in
Interbase syntax).

 

function GetGenID(GeneratorName: string): integer;
begin
    with FDQGetGenID do
    begin
        SQL.Clear;
        SQL.ADD('SELECT GEN_ID ('+GeneratorName+', 1) from rdb$database');
        Open;        
        Result := Fields[0].AsInteger;
        Close;
    end;
end;

Then when you insert new data you will call the GetGenID function.

FDQuery.insert;
FDQueryID.Value:=GetGenID('GeneratorName');
FDQueryMASTERDATA.AsString:= 'Some value';
FDQuery.Post;

 

You can also place the call to GetGenID in the FDQuery events, like OnNewRecord or BeforeInsert.

 

---
If this approach is not possible with Pervasive, then this approach could work. It is at least working with Interbase.
In this example I have used a DBGrid and A DataSource component to connect to the FDQuery. And the query has two fields
called "ID" and "MASTERDATA".

 

Set the AutoGenerator parameter value of ID field to arAutoInc (if you've not already done so).

 

In the OnDataChange Event of the DataSource put the following code:

 

if (FDQMasterID.Value < 0) and (FDQMaster.State in [dsInsert]) and not FDQMasterMASTERDATA.IsNull then
begin
   FDQMaster.Post;
   FDQMaster.Refresh;
end;

 

You need to check if at least one of the fields has a value apart from the ID field, or you will get
an error on the Post command, if the user adds to empty records.
 

[TurboMagic 92]

Some Firebird documentation about generators I just read recommends to NOT directly query a generator like that for master/detail tables, as in multi user scenarious you cannot be sure whether somebody already further incremented the generrator etc.

[TurboMagic 92]

On 4/12/2019 at 11:56 AM, Uwe Raabe said:

There actually is some documentation about this: Auto-Incremental Fields (FireDAC)

This is exactly the help topic I don't fully understand!
I'm having the same issue right now, but with a Firebird database.
The help topic doesn't show me (at least not in a form I can understand) hot to access the generated value after calling ExecSQL.
And the sample linked I don't understand either.

[TurboMagic 92]

In my case I have used this one:

 

FQuery.SQL.Text := 'insert into MYTABLE ' +
                   '(LASTNAME, FIRSTNAME) ' +
                   'VALUES (:NEW_LASTNAME, :NEW_FIRSTNAME);';

FQuery.UpdateOptions.AutoIncFields := 'PERSON_ID';
FQuery.CachedUpdates := false;
FQuery.Prepare;

Later on I fill in values like this, which works and created unique IDs:

  FQuery.ParamByName('NEW_LASTNAME').AsString  := 'Doe';  
  FQuery.ParamByName('NEW_FIRSTNAME').AsString := 'John';
  FQuery.ExecSQL;

But how to retrieve the generated new PERSON_ID?

[Uwe Raabe 2057]

AFAIK, that won't work with Inserts via SQL (at least I never did it that way). The reason may be, that an INSERT query will never retrieve any fields like a SELECT query. There simply are no fields you can get the ID from.

 

The usual way you have something like SELECT * FROM MYTABLE which retrieves all fields including the ID field. For adding a new record you just call Append or Insert, set the field values as needed and call Post. If everything is set up correctly, the new ID should then be available in the PERSON_ID field.

[TurboMagic 92]

The developer of FireDAC somewhere recommended this one:

FireDAC.Comp.Client.TFDCustomConnection.GetLastAutoGenValue

 

But that also retrieves the last value of the generator and thus should better be avoided for most RDMS, at least in multi user scenarios.

Somewhere else adding this to the insert statement is recommended: RETURNING ID INTO :ID
This would return the value in a field named ID. But: FireDAC doesn't seem to support this. If I add this to the insert the Prepare
call throws an error: Dynamic SQL error. Error code -104, Token unknown, Line 1 column 138.

Where column 138 (at least when I copy out the SQL text in the debugger) is one of the parameters before...

[TurboMagic 92]

4 minutes ago, Uwe Raabe said:

AFAIK, that won't work with Inserts via SQL (at least I never did it that way). The reason may be, that an INSERT query will never retrieve any fields like a SELECT query. There simply are no fields you can get the ID from.

 

The usual way you have something like SELECT * FROM MYTABLE which retrieves all fields including the ID field. For adding a new record you just call Append or Insert, set the field values as needed and call Post. If everything is set up correctly, the new ID should then be available in the PERSON_ID field.

Thanks! Sounds helpfull and I'll try this later!

[TurboMagic 92]

1 hour ago, Uwe Raabe said:

AFAIK, that won't work with Inserts via SQL (at least I never did it that way). The reason may be, that an INSERT query will never retrieve any fields like a SELECT query. There simply are no fields you can get the ID from.

 

The usual way you have something like SELECT * FROM MYTABLE which retrieves all fields including the ID field. For adding a new record you just call Append or Insert, set the field values as needed and call Post. If everything is set up correctly, the new ID should then be available in the PERSON_ID field.

I just tried this, but it does not work. It throws an EDatabaseError exception that the PERSON_ID must not be null.
That field is the primary key and as soon as I delcare it as primary key the DB management tool adds a not null constraint.
The exception occurs on the Post line.

 

My code looks like this:

FQuery.SQL.Text := 'select Person_ID, LastName, FirstName from persons';

FQuery.Open;
FQuery.Append;

FQuery.FieldByName('LastName').AsString     := 'Doe';
FQuery.FieldByName('FirstName').AsString     := 'John';

FQuery.Post;
Result := FQuery.FieldByName('Person_ID').AsInteger;

The generator value us applied via some ACTIVE BEFORE  INSERT trigger by the way...

Edited June 5, 2022 by TurboMagic

[TurboMagic 92]

Found a fix myself!

 

FQuery.UpdateOptions.AutoIncFields := 'PERSON_ID';

 

This makes FireDAC aware that the not null constraint will be fullfilled by the database itsself.

[Stano 143]

Um, they advised me, and I follow that. Query the ID value in advance and use it in SQL. If you fail, you drop it.
I have no idea if this can be done in your case. I do not have knowledge and skills.

[Fr0sT.Brutal 900]

16 hours ago, TurboMagic said:

But how to retrieve the generated new PERSON_ID?

Modern way is BEFORE INSERT trigger that would generate ID (either always or only if field value is null - as you wish) + RETURNING INTO statement. If DB lib doesn't support this feature, you can ask devs to add it or use classic old-school way:

1. old: SELECT GEN_ID(GenName, 1) / recommended: SELECT NEW VALUE FOR GenName

2. Fill the INSERT query with the received value

 

Generators are guaranteed by the DB engine to be atomically unique. No issues with multiuser env as far as you use it right (no decrements, no manual increments and so on).

 

LastAutogenValue is something awkward from MSSQL AFAIK, I wouldn't recommend using it without strong requirements. FB has much more convenient mechanism

Edited June 6, 2022 by Fr0sT.Brutal

[Hans J. Ellingsgaard 21]

On 6/5/2022 at 3:59 PM, TurboMagic said:

Some Firebird documentation about generators I just read recommends to NOT directly query a generator like that for master/detail tables, as in multi user scenarious you cannot be sure whether somebody already further incremented the generrator etc.

I can not see why this should be a problem, you got your ID and stick to it until your record is finally posted to the database. You use it as a primary key on your mastertable and a foreign key on the detail table. The generator is running outside the transactions, and you are guaranteed to get a unique value each time you request for an ID.