TFDUpdateSQL Problem

[gkobler 31]

I'm migration a project from FB4 to FB5, i use a TFDQuery and a TFDUpdateSQL component.

 

My project runs without any problems. I have updated the database from FB4 to FB5 now. Now i got a problem, when i update a Field in my table. The follow exception are rises

 

Im Projekt Project2.exe ist eine Exception der Klasse EFDDBEngineException mit der Meldung '[FireDAC][Phys][FB]-312. Genau update affected [0] Zeilen, [1] wurden angefordert' aufgetreten.

 

and

 

Im Projekt Project2.exe ist eine Exception der Klasse EFDException mit der Meldung '[FireDAC][DApt]-400. Update-Anweisung updated [0] anstelle von [1] Datensatz. Mögliche Ursachen: Aktualisierungstabelle hat keinen Primärschlüssel oder Zeilenbezeichner, Datensatz wurde von einem anderen Benutzer geändert/gelöscht' aufgetreten.

 

The data field are not updated. When i now set the property UpdateOptions.CountUpdateRecords to False, i just got the first exception and the fiel are updated to the table.

 

What can i do to avoid the exception? I think the problem should be in the TFDUpdateSQL Component, but something must be changed from FB4 to FB5?

 

Thanks for any hint

Gregor

[VLDG 1]

May be this

 

Multi-row RETURNING behaviour

Client-side INSERT … SELECT, UPDATE, DELETE, MERGE and UPDATE OR INSERT queries containing the RETURNING clause may now return multiple records instead of raising error “multiple rows in singleton select” as it happened before.

These queries are now described as isc_info_sql_stmt_select during preparation, while in previous versions they were described as isc_info_sql_stmt_exec_procedure.

Singleton INSERT … VALUES statements, as well as positioned UPDATE and DELETE statements (i.e. the ones containing the WHERE CURRENT OF clause) preserve the existing behaviour, being described as isc_info_sql_stmt_exec_procedure. They also preserve the ability of being executed within a single protocol roundtrip to the server.

However, all these queries, if used in PSQL and the RETURNING clause is applied, are still treated as singleton.

 

 https://firebirdsql.org/file/documentation/release_notes/html/en/5_0/rlsnotes50.html#rnfb50-compat-returning

[Dmitry Arefiev 99]

Please report this issue to quality.embarcadero.com, if it works now ... Or just post here:

• a minimal test project
• SQL script to create DB objects
• steps to reproduce the issue

PS: this may be a changed FB 5 behavior or a regression bug (eg, https://github.com/FirebirdSQL/firebird/issues/7774)

[gkobler 31]

Thanks for clarifying and investigating. Attached my smal demo project. Just start it and try to change a value at any field. Immediately when you try to post this chages, the exception are raising.

 

Quality portal is still in read-only mode, so i can't report the bug.

FireDac Sample.7z

[Dmitry Arefiev 99]

@VLDG, thanks ! Yes, this is the issue in FireDAC support for Firebird 5.

The issue is fixed, the changes will be included into Delphi 12 Update 1.

Edited February 18 by Dmitry Arefiev

[gkobler 31]

@Dmitry Arefiev should i go via the support to get a fix earlyer? Or could you post some infos here to fix it? Quality Portal is read only at the moment and it‘s not an option for that. 

[Dmitry Arefiev 99]

I attached the patch. If you will fail to apply it, then please go through support.

fb5_ret.patch

[gkobler 31]

it works for me. 🙂 Thanks a lot for your support!