I stumbled about a problem regarding FireDAC and MSSQL 2016. The table has four fields, one being of type varbinary(max). When I omit this blob field from the SELECT, I can update any field without problems using the edit and post methods of TFDQuery. It is only when I include that blob field in the SELECT, I cannot update the other fields anymore (the blob is not changed). The post runs into a timeout. It seems that the table is somehow locked. That guess is backed by the fact that even a simple UPDATE query executed from Management Studio is also blocked until I reset my application inside the debugger.
I stumbled about a problem regarding FireDAC and MSSQL 2016. The table has four fields, one being of type varbinary(max). When I omit this blob field from the SELECT, I can update any field without problems using the edit and post methods of TFDQuery. It is only when I include that blob field in the SELECT, I cannot update the other fields anymore (the blob is not changed). The post runs into a timeout. It seems that the table is somehow locked. That guess is backed by the fact that even a simple UPDATE query executed from Management Studio is also blocked until I reset my application inside the debugger.
Interestingly an INSERT runs without problems.
Has anyone encountered something similar - or even better - has a solution?
Interestingly an INSERT runs without problems.
Has anyone encountered something similar - or even better - has a solution?
/sub
ReplyDeleteOk folks, thanks for the suggestions.
ReplyDeleteAttila Kovacs sp_who2 only shows the Management Studio connection, but that may be due to my limited access rights to the server (can't do anything about that over this weekend).
Richard Baroniunas alas the blob size is 8100 in this case which is outside the range. I may do some tests with smaller blobs later.
Anyway, after some hours of sleep I found a workaround: A cahnge of FetchOptions.CursorKind from ckAutomatic to ckDynamic does the trick.
Now I remember that this scenario used to work before when IndexFieldNames was set, but that gave a long delay when opening the dataset so I tried without it.
Haven't seen this problem - probably because we always use stored procedures to manipulate rows.
ReplyDeleteThat is the norm, yes - but not always necessary. In general, our core service is the main actor in the DB, so for tables with fairly slow changing data, all fields are updated. For tables that need frequent changes to a few specific columns, there are specialized stored procs.
ReplyDeleteIn our core classes, we have a RTTI based diff mechanism in the save routines which compares the original read with a fresh read and the current write and logs discrepancies so that we can detect anomalies.
Executing updates in pure SQL is not allowed by the DB permissioning. Using methods also gives us validation opportunity, so that third party integrations are policed for good behaviour.
W/o research ir sounds like your DAC mixes transaction handling and blob fetches. Make an mvs and send to your DAC vendor.
ReplyDeleteDany Marmur which is hard as Dmitry Arefiev got fired by Idera.
ReplyDeleteDany Marmur Well, as it actually is FireDAC the vendor is Embarcadero. I will do some more testing against other MSSQL versions first before filing a QP report.
ReplyDeleteJeroen Wiert Pluimers the most important thing when choosing a lib is vendors' heart. Really difficult to measure.
ReplyDeleteSorry, on a phone. The money between professional and enterprise will render one capable of subscribing to a host of 3 rd party highquality libs.
ReplyDeleteDany Marmur that's the problem for quite a few of the Delphi 3rd party parts that Embarcadero bought, then pulled of the market for all but the most recent Delphi version and finally fired the authors for.
ReplyDeleteUwe Raabe how many rows your dataset returns ?
ReplyDeleteDmitry Arefiev RowSetSize is set to 50, but the SELECT will return about 21000 records when fetched completely.
ReplyDeleteProbably SQL Server keeps a lock on not fetched records. Try one of the following 1) call FetchAll 2) change FetchOptions.CursorKind 3) specify WITH(NOLOCK).
ReplyDeleteDmitry Arefiev Something like that is also my impression. Although the update is done on the current record, which obviously IS fetched. The FetchAll should work, as well as setting IndexFieldNames (which has the same effect). Currently I prefer the CursorKind = ckDynamic approach, as it keeps the delay for the first record at minimum. Will try the WITH(NOLOCK) later.
ReplyDelete/sub
ReplyDelete