Delphi FDUpdateSQL Problem

[gkvi...@gmail.com]

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

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 10:14:
> 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?

Yes, there are a lot of changes between Firebird 4 and Firebird 5. They are
described in Release Notes.
In this case I would guess complications with Read Consistency.

--
WBR, SD.

[Mark Rotteveel]

Read Consistency was introduced in Firebird 4.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 16.02.2024 11:16:
>>    In this case I would guess complications with Read Consistency.
>
> Read Consistency was introduced in Firebird 4.

Indeed. So the next guess is an another bug in statement cache.
But it would be better if topic starter perform some investigations using for
example trace and audit to see what exactly queries are executed in context of
which transaction and what results they have.

--
WBR, SD.

[gkvi...@gmail.com]

I found out something more

When i set the protocol from TCPIP to local at my FDConnection component, the exception are not raises.

But i will look, that i can made a log with traces

[gkvi...@gmail.com]

Have made a trace file. At line 292 you see the value "123" i change at the field.

The trace are logged with the exception, and the value are not updated to the field

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 14:00:
> Have made a trace file. At line 292 you see the value "123" i change at the field.
>
> The trace are logged with the exception, and the value are not updated to the field

I see in the log that value is updated but then transaction rolled back.
Debug your program to find the reason why.
The log lack of statement finishing records so it is not obvious how many
records was affected but transaction rollback stats show one backout.

--
WBR, SD.

[Dimitry Sibiryakov]

'Dimitry Sibiryakov' via firebird-support wrote 16.02.2024 14:12:
>   I see in the log that value is updated but then transaction rolled back.
> Debug your program to find the reason why.

Also you can try to turn off prepared statement cache in friebird.conf.

--
WBR, SD.

[gkvi...@gmail.com]

Witch is the parameter, can't find a prepared statement cache parameter in firebird.conf

[gkvi...@gmail.com]

i set the parameter MaxStatementCacheSize = 0, but still the same error

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 14:45:
> i set the parameter MaxStatementCacheSize = 0, but still the same error

If you did it right - ok, we can cross but in the cache out.
Add statement finish to trace parameters and check number of affected
records. Debug components code to see what exactly value is received from server.
Some components compare number of affected records with 1 which makes the
error text misleading if update affected TWO records.

--
WBR, SD.

[gkvi...@gmail.com]

Attached the config file where i made my trace log, "finish" are enabled, i hope

[gkvi...@gmail.com]

i also installed the latest snapshot 5.0.1.1340 still the same fault

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 15:15:
> Attached the config file where i made my trace log, "finish" are enabled, i hope

It has been filtered out by "time_threshold = 100".

--
WBR, SD.

[gkvi...@gmail.com]

made the same trace with time_threshold = 100.

> Some components compare number of affected records with 1 which makes the
error text misleading if update affected TWO records.

I debuged the components, the affected row was 0. Also the exception shows 0

[FireDAC][Phys][FB]-312. Genau update affected [0] Zeilen, [1] wurden angefordert

in eglish

[FireDAC][Phys][FB]-312. exactly update affected [0] lines, [1] were requested

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 15:34:
> made the same trace with time_threshold = 100.

Isn't it obvious that threshold must be zero to get a result?..

--
WBR, SD.

[gkvi...@gmail.com]

miss understood, now the trace with parameter 0

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 15:41:
> miss understood, now the trace with parameter 0

And now the log clearly shows that Firebird updated one record. Continue
debugging to find out where this number was lost.

--
WBR, SD.

[Ertan Küçükoglu]

Hello,

I tried to summarize below relevant parts from your trace log.

2024-02-16T15:38:36.7160 START TRANSACTION - TRA_229

2024-02-16T15:38:36.7170 PREPARE STATEMENT

2024-02-16T15:38:36.7170 EXECUTE STATEMENT START
2024-02-16T15:38:36.7170 EXECUTE STATEMENT FINISH (we see 1 record updated here)

2024-02-16T15:38:41.0460 FREE STATEMENT

2024-02-16T15:38:41.0470 ROLLBACK TRANSACTION - TRA_229

I do not see any problem here on FirebirdSQL side.

However, I do not know if updated record data returned from statement execution is different between v4.and v5.0

If they are different, your FireDAC version might not support new format from FirebirdSQL 5.0

I test the same scenario on my own FirebirdSQL v5.0.0.1306 but using UniDAC 10.0.0 

I do not get any error and the update actually happens.

Thanks & Regards,

Ertan

'Dimitry Sibiryakov' via firebird-support <firebird...@googlegroups.com>, 16 Şub 2024 Cum, 17:45 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6281bcf6-689e-4154-a424-a76a2d257fd3%40ibphoenix.com.

[gkvi...@gmail.com]

Have isolated the problem a bit

at the "FireDAC.Phys.IBWrapper.pas" at line 6576 there are the follow code

  Check(Lib.Fisc_dsql_sql_info(@Error.FStatusVector, @FSTHandle, 1, @cInfo,
    SmallInt(ASize), PISC_SCHAR(ABuff)));

This returns the wrong ABuff back (see attached printscreen)

Aufnahme12 shows with FB4

Aufnahme13 show with FB5

The array element #3 is in FB5 "1" in FB4 "8"

If I interpret this correctly, then the function "Fisc_dsql_sql_info" is a call in the fbclient.dll? Then the client library returns the wrong value?

[Dimitry Sibiryakov]

gkvi...@gmail.com wrote 16.02.2024 17:33:
> If I interpret this correctly, then the function "Fisc_dsql_sql_info" is a call
> in the fbclient.dll? Then the client library returns the wrong value?

Value is right more or less, but you are looking at wrong place. Query type
is requested here, not number of affected records.

--
WBR, SD.

[Vlad Khorsun]

Have isolated the problem a bit

at the "FireDAC.Phys.IBWrapper.pas" at line 6576 there are the follow code

  Check(Lib.Fisc_dsql_sql_info(@Error.FStatusVector, @FSTHandle, 1, @cInfo,
    SmallInt(ASize), PISC_SCHAR(ABuff)));

This returns the wrong ABuff back (see attached printscreen)

Aufnahme12 shows with FB4

Aufnahme13 show with FB5

The array element #3 is in FB5 "1" in FB4 "8"

  FB4 response starts with {21, 0, 4, 8, 0, 0, 0, ...} which corresponds to {isc_info_sql_stmt_type, 4, isc_info_sql_stmt_exec_procedure, ...}

while FB5 response starts with {21, 0, 4, 1, 0, 0, 0, ...} which corresponds to {isc_info_sql_stmt_type, 4, isc_info_sql_stmt_select, ...}

  Note, FB5 changed RETURNING clause handling and now it returns result set, not a single row. Therefore UPDATE... RETURNING

statement was described as isc_info_sql_stmt_exec_procedure before FB5. And since FB5 it is described as isc_info_sql_stmt_select.

Perhaps FireDAC unable to handle this change correctly when asks for counters to detect changes by the statement.

Regards,

Vlad

PS it would be much more useful to post text here, not pictures.

[gkvi...@gmail.com]

Thanks to all for investigations. I think that will get a bigger change to FireDac to handle that.

I also have a thread at (with demo)

https://en.delphipraxis.net/topic/11054-tfdupdatesql-problem/

> PS it would be much more useful to post text here, not pictures.

will do it next time ;-)