TUniLoader.OnProgress

[FredS]

Things slow down dramatically with this event.
May I suggest you only fire that when the Percentage changes.

[FredS]

Things I have tried:
- StartTransaction/Rollback for the default Transaction
- Setting a Transaction for TUniLoader with StartTransaction/Rollback
- Tried SavePoint/RollbackToSavepoint on both the Transaction and the default Transaction

Nothing works, the tables already loaded are there, using SQL-Server.

[FredS]

Any Progress on the Rollback issue?

[Stellar]

The OnProgress event is generated for each record. Unfortunately, we cannot generate the event, not for each record, but only when data loading percentage is changed. Because it will change the existing behavior. If you want to handle the event only when changing data loading percentage, then you can use the following sample:

Code: Select all

var
  CurrentPercent: Integer;

procedure TForm1.Button1Click(Sender: TObject);
begin
  CurrentPercent := -1;
  UniLoader1.Load; 
end;

procedure TForm1.UniLoaderProgress(Sender: TObject; Percent: Integer);
begin
  if CurrentPercent <> Percent then begin
    CurrentPercent := Percent;
    //Do something
  end;
end;

You can perform data loading in a transaction, for this, run StartTransaction before data loading and Commit after data loading for the connection where the data is copied. For example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  UniLoader.Connection := UniConnection;

  UniConnection.StartTransaction;
  try
    UniLoader.Load;  

    UniConnection.Commit;
  except
    UniConnection.Rollback;
    raise;
  end;
end;

[FredS]

>> change the existing behavior
Fair enough, but if you make TDALoader.DoLoaderProgress virtual we can override the behavior.

>> run StartTransaction before data loading
I've already tried that and it doesn't work, the first six tables fly by and when I cancel on the last one all their data stays:

Code: Select all

procedure TDataDM.CopyDataToNewSqlServerDb;
const ar : array[0..7] of string = ('MNGDDOMAINS', 'SERVERS', 'SNAP_NOTINFETCH', 'SNAPSHOTHISTORY', 'SNAP_SERVERSHARES',
                                    'SNAP_USERGROUPLIST', 'SNAP_USER2GROUP', 'SNAPSHOTS');
var
  LHadQueryRecCount: Boolean;
  LTableName : string;
begin
//  TUniLoaderAccess(Loader).AutoCommit := False;
  TestConnection.StartTransaction;
//  TestTransaction.Savepoint('COPYTONEWSQLSERVERDB');
  uniCon.Connect;
  try
    Query.Active := False;
    LHadQueryRecCount := Query.Options.QueryRecCount;
    Query.Options.QueryRecCount := True; // Else Percent won't work.
    try
      try
        {- Process Tables in order }
        for LTableName in ar do begin
          Loader.TableName := LTableName;
          TWaitForm.ShowWaitProgressMessage('Copying table: ' + LTableName, 0);
          TWaitForm.ShowCancelBtn := True;
          Loader.Tag := 0; // for Percent
          Query.SQL.Text := 'SELECT * FROM ' + LTableName;
          Query.Open;
          Loader.LoadFromDataSet(Query);
          Query.Close;
        end;
      finally
        Query.Options.QueryRecCount := LHadQueryRecCount
      end;
//      {- Destroys the specified savepoint without affecting any work that has been performed after its creation. }
//      TestTransaction.ReleaseSavepoint('COPYTONEWSQLSERVERDB');
      TestConnection.Commit;
    except
       on E: ECancelCopyToNewSQLServer do begin
//        E.SetErrMessage(True);  // Canceled
//         TestTransaction.RollbackToSavepoint('COPYTONEWSQLSERVERDB');
         TestConnection.Rollback;
       end;
    end;
  finally
    uniCon.Disconnect;
  end;
end;

Relevant Options, TestTransaction is the one assigned to TestConnection. However my first attempts where without a specific Transaction :

Code: Select all

object Loader: TUniLoader
  Connection = TestConnection
  Transaction = TestTransaction
  SpecificOptions.Strings = (
    'SQL Server.KeepIdentity=True'
    'SQL Server.KeepNulls=True')
  OnProgress = LoaderProgress
end

[Stellar]

Unfortunately, data loading via TUniLoader is executed without a transaction. To quickly load data, TUniLoader creates a special session. The open transaction on the connection does not influence the new session, since new sessions are created outside of the transaction. Also, an open transaction of the session can not be applied to a new session.
However, you can add your suggestion at our UserVoice forum ( devart.uservoice.com/forums/104635-delphi-data-access-components?category_id=18939 ). If this suggestion gets enough votes, we will consider the possibility of its implementation.

[FredS]

Since you original suggestion is then wrong, I'm going to assume that help will be updated to reflect this so we won't waste any more development time on this.

[Stellar]

Thank you for the information. We will add the description to the online documentation that the data loading via TUniLoader is executed without transaction.