Firebird 3/4 ECannotGetLastInsertId Error

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.

Thanks in advance!

Are you using SQL dialect FIREBIRD3 instead of FIREBIRD?

implementation

uses
  Aurelius.Drivers.Base
  , Aurelius.Drivers.Interfaces
  , Aurelius.Drivers.FIBPlus
  , Aurelius.Engine.DatabaseManager
  , Aurelius.Schema.Firebird3
  , Aurelius.Sql.Firebird3
  , XData.Aurelius.ConnectionPool
  , XData.Server.Module
  ;

And the connection factory uses the TFIBPlusConnectionAdapter as follow:
IDE

Is the implementation allright?

Best regards!

I've done some research and installed Firebird 3 and tested again.

The Problem occurs in Firebird 3 too and is not a Firebird 4 problem.

Hope this helps to find out whats wrong here ;).

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?

Thanks for your reply.

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.

Allright. Thats what I feared. I will search for a better solution.

1 Like