Problems with fetchAll=false

[maddn]

Because of the bigger flexibility we decide to change from DBISAM to UniDAC. In our first tests we made some first experiances by simple opening tables.

Basis
We took a dbgrid, datasource and Unitable
We choose a datatable with about 150000 rec
DBMS: MySQL 5.5.13

1. Fetchall = true
We choose fetchall=true to see all the data
We wait about 30 sec

This is what we normaly want but it tooks to much time. With DBISAM this tooks about 1sec.


2. Fetchall = false
same components
We choose fetchall=false to see only 25 Records
We wait less then 1 sec

This would be ok too, but when we now want to edit,delete or post there comes an error that fetchall doesn't work with lockmode pessimistic.
When I change the lockmode to lmNone or lmOptimistic we can edit, delete or post but then there is no more lock on the record and inconsistent data might be created.

Our question: Is there a way to increase the speed of opening tables larger than 100000 record fetchall=true? Or is there a way to use fetchall=false by keeping the lockmode pessimistic?

[AndreyZ]

Hello,

When FetchAll=False, execution of such queries blocks current session. UniDAC creates an additional session if it is necessary by default. But in this case you cannot use the Lock method, because it is incompatible with FetchAll=False. But you can set the LockMode property to lmPessimistic, it is allowed. Here is an example that demonstrates it:

Code: Select all

UniQuery.SQL.Text := 'select * from tablename';
UniQuery.SpecificOptions.Values['FetchAll'] := 'False';
UniQuery.LockMode := lmPessimistic;
UniQuery.Open;
UniQuery.Edit;
UniQuery.FieldByName('fieldname').AsString := 'test';
UniQuery.Post;

[maddn]

Hi AndreyZ,

sorry but I don't understand your example.

Could you please explain me step by step to open a large but simple table (not a query) with fetchall=false and lmpessimistic and how to edit the table with eg dbnavigator.

[AndreyZ]

Here is a way of opening a large table with enabled lmPessimistic locking mode and with FetchAll=False, that allows editing this table:

Code: Select all

UniTable.TableName := 'tablename'; // your large table name
UniTable.SpecificOptions.Values['FetchAll'] := 'False'; // to avoid fetching all data
UniTable.LockMode := lmPessimistic; // needed locking mode
UniTable.Open;

After that, you can edit data in this table in any way that you want, through code or with the help of visual components.

[maddn]

Hi AndreyZ, thank you for your answer! It seems to work well but now I have another problem with fetchall because when I want to de-/activate fetchall at runtime it doesn't work.

In my testapplication I simply took a checkbox and wrote in the OnClickEvent

Code: Select all

procedure TForm_Main.CheckBoxFetchClick(Sender: TObject);
begin
  UniTable.Close;
  if CheckBoxFetch.Checked then
  begin
    UniTable.SpecificOptions.Values['FetchAll'] := 'true';
  end
  else
  begin
    UniTable.FetchRows := StrToInt(EditFetch.Text);
    UniTable.SpecificOptions.Values['FetchAll'] := 'false';
  end;
  UniTable.Open;
end;

but nothing changed. When set fetchall=true by default it stays on true as well as set fetchall=false by default. How can I change the fetchmode at runtime?

[AndreyZ]

To solve the problem with setting the FetchAll property in run-time, try using the following code:

Code: Select all

procedure TForm_Main.CheckBoxFetchClick(Sender: TObject); 
begin 
  UniTable.Close; 
  if CheckBoxFetch.Checked then 
  begin 
    UniTable.SpecificOptions.Values['MySQL.FetchAll'] := 'true'; 
  end 
  else 
  begin 
    UniTable.FetchRows := StrToInt(EditFetch.Text); 
    UniTable.SpecificOptions.Values['MySQL.FetchAll'] := 'false'; 
  end; 
  UniTable.Open; 
end;

[maddn]

Thanks a lot! Now it works