Problem to post a Record with UniTable OrderBy MS-SQL 2008

[oxel99]

I´m trying to Post a Record in a table with about 170.000 Records.

UniTable1.OrderFields:= 'Patient';
UniTable1.LockMode:= lmPessimistic;
UniTable1.Open;
UniTable1.First;
UniTable1.Edit;
UniTable1.FieldByName('Patient').AsString:= 'Any kind of text';
UniTable1.Post;

Results with a Error-Message:
"The connection is busy with the results of another request."
"Die Verbindung ist mit Ergebnissen eines anderen Befehls beschäftigt."

The same thing, with using:

UniTable1.IndexFieldNames:= 'Patient';

works fine, without any Error but is very slow on Opening the Table.
Does anyone have an idea how to solve this problem?

[stevel]

Try if there are data aware controls connect to the dataset
using DisableControls / EnableControls

[oxel99]

The table is stand alone, without any controls attached.
It seems to be a locking problem.
LockMode lmNone works fine without error.

[AndreyZ]

Hello,

This error can occur if you set the FetchAll option to False. In this case execution of such queries blocks the current session. To solve the problem you should set the UniTable.SpecificOptions.FetchAll option to True. Setting UniTable1.IndexFieldNames := 'Patient' helps because it turns on the FetchAll option and all data is fetched from the server (that's why it's slow). IndexFieldNames cannot work with not fetched data.
To perform locking UniDAC uses transaction that cannot start because of blocked session. That's why you shouldn't use locking with the FetchAll option set to True.

[oxel99]

We are working with large tables, up to 4.000.000 Records.
We have to update and insert a lot of Records in a fast way.
Opening with FetchAll=False seems to be the fastest way without reading all of the Records from SQL-Server to local place, but trying to insert a new Record hangs up, when the Table has more than 150.000 Records.
With FetchAll=True it works fine, but very slow on Opening the Table.
Please, can anyone tell me, what is the best and fastest way, to work in BDE-Like order with Insert, Edit and Post Records in a Table, if possible with Record-Locking.

[AndreyZ]

SQL Server doesn't allow deleting or updating records if there is not fetched data. In this way SQL Server tries to avoid ambiguity in data.

[invent]

Hi oxel99,

okay, the support from Devart is not very helpful.

I had the same problems. We have a big ERP-application which supports Interbase, Oracle and MS SQL-Server.

Because of the "FetchAll"-property you have to filter the Table before opening:

MyUniTable.Tablename := 'Customers';
MyUniTable.FilterSql := 'CustomerId = ' + QuotedStr ('0');
MyUniTable.Open;
MyUniTable.Insert;
(...)

With FilterSql you open here an empty dataset and than insert is very fast. But you have to know the name of the PrimaryKey-field (here: CustomerId).

When you want to edit a row it's the same. First the old BDE-Version:

MyBDETable.Tablename := 'Customers';
MyBDETable.Open;
if MyBDETable.Locate ('CustomerId', '12345', ...)
then begin
MyBDETable.Edit;
(...)
end;

With Unidac Locate on large tables is very slow. Here a better solution:

MyUniTable.Tablename := 'Customers';
MyUniTable.FilterSql := 'CustomerId = ' + QuotedStr ('12345');
MyUniTable.Open;
if MyUniTable.RecordCount = 1
then begin
MyUniTable.Edit;
(...)
end;

I hope, this helps.

Kind regards,
Gerd Brinkmann
invent GmbH