Hello everybody,
I've use the newest TMS Aurelius Version. When I Use the Firebird 3 Adapter together with the FIBPlus Driver, I get the ECannotGetLastInsertId Error.
I use Firebird 4 with Autoincrement.
So I debugged a while and find out that the Aurelis.Commands.Inserter will retrieve the next record and read the Returning ID. But the Returning ID will be delivered in the current ResultSet.
Before the call ResultSet.Next the ResultSet.GetFieldValue(0) retrieves the current id-value.
So the call to Result.Next is unnecessary. I think other databases handle the current id in the next resultset, but firebird not.
I know Firebird 4 ist not supported, but I think for future Versions of Aurelius is this important. Maybe it's related to Firebird 3? I didn't have testet this behavior in Firebird 3 so far. It's a brand new project I'm working on and I wanted to use the newest release of Firebird beacause of performance improvements.
ResultSet.Next call has nothing to do with Dataset.Nextcall. It's needed for IDBStatement. If you check the implementation of Next in unit Aurelius.Drivers.FIBPlus, you will what I mean.
FIBPlus is really an old library and we have no tests for it using Firebird 3. I can only guess this is a bug in FIBPlus, which might return Dataset.EOF as True even though there is an existing record returning the inserted id?
FIBPlus is really an old library and we have no tests for it using Firebird 3. I can only guess this is a bug in FIBPlus, which might return Dataset.EOF as True even though there is an existing record returning the inserted id?
Jep that's the problem. I debugged a while arround in the FIBPlus and found a dirty workaround for the TFIBPlusResultSetAdapter.Next function:
function TFIBPlusResultSetAdapter.Next: Boolean;
begin
if not FFetching then
FFetching := True
else
FDataset.Next;
Result := not FDataset.Eof
or ((FDataset.SQLType = SQLExecProcedure) and FDataset.ProcExecuted);
end;
Background: INSERTs with a RETURNING ID statement are treated as SQLExecProcedure by the fbclient.dll and not as SQLInsert. So we can check, if the executed SQL Statement is a SQLExceProcedure and if its executed. This solution works for me in the moment.
I can't replace the FIBPlus components in the moment.
Is there a chance, that you can test the fix and apply it official to TMS Aurelius?
Shouldn't it be opposite? I understand that with the above code, Next will never return False when such SQL statements using RETURNING ID are executed?
Thank you for your fast reply!
No, when a procedure is detected and executed, the query is open and can retrieve fields. So it is not eof.
This code returns True, when there is a SQL-Procedure called and the the procedure is executed.
In the FIBQuery source code is this set to True when a procedure is called:
and when the the query will be closed, this is set to False.
I will test this in my project and look for any sideeffects, because of this change.
That's expected and correct. The problem is if the user of interface keeps calling IDBResultSet.Next, it will never return false and will stay in a loop forever.
I introduced a private field named FInsertFetched: Boolean. This contains if a insert returned a valid field and if it's already fetched. If not, the Next result retuns True and the private field will be set to True. If there are more calls to this function in this context, the result will be False, because the data is fetched. So there are no infinity loops.
In addition I added some test, if there are valid data in the returning dataset, so we make sure that there accesable fields with data.
function TFIBPlusResultSetAdapter.Next: Boolean;
begin
if not FFetching then
FFetching := True
else
FDataset.Next;
Result := not FDataset.Eof;
if not Result
and (FDataset.SQLType = SQLExecProcedure)
and (FDataset.ProcExecuted) then
begin
if not FInsertFetched then
begin
if FDataset.FieldCount > 0 then // we've got a valid resultset
begin
if not FDataset.Fields[0].IsNull then // additinal test if data available
begin
FInsertFetched := True;
Result := True;
end;
end;
end;
end;
end;
I've made a GIT patch file. I hope this helps for easier integration.
Patch-File: Aurelius.Drivers.FIBPlus.pas (1.3 KB)
I think this solution is much better than the first.
Best regards!
