what is replacement for UpdateBatch from ADO to UNIDAC

[caksul]

using UniConnection.ApplyUpdates is enough to replace this :

Code: Select all

ADODataSet1.UpdateBatch(arCurrent);
ADODataSet1.UpdateBatch(arAll);

it's enough?

thanks

Caksul

[AlexP]

Hello,

UniDAC has no method similar to UpdateBatch in ADO. When using ApplyUpdates, you can specify the records (updated, inserted or deleted) to be transferred to the DB.

UniQuery1.ApplyUpdates([ukUpdate, ukInsert, ukDelete]);

[caksul]

This is my complete coding (ADO)

Code: Select all

MASTER DETAIL

qryAPInv


SELECT [APInvoiceID]
      ,[VendorID]
      ,[InvoiceNo]
      ,[Description]
      ,[InvoiceDate]
      ,[DueDate]
      ,[DiscountDate]
      ,[InvoiceAmount]
      ,[PaidAmount]
      ,[DiscountAmount]
      ,[DiscPC]
      ,[DiscountTaken]
      ,[Owing]
      ,[PurchaseOrderNo]
      ,[GLPeriod]
      ,[GLYear]
      ,[Posted]
      ,[CurrencyID]
      ,[ReceiptID]
      ,[UpdateNo]
      ,[timestamp]
  FROM [APInv]
WHERE [APInvoiceID]=10001



qryAPInvDet



SELECT [APInvoiceID]
      ,[Seq]
      ,[GLAccountID]
      ,[SubAccount1ID]
      ,[SubAccount2ID]
      ,[SubAccount3ID]
      ,[GLAmount]
  FROM [APInvDet]
WHERE [APInvoiceID]=10001





procedure TfrmAPInvoice.btnOKClick(Sender: TObject);
begin
  if qryAPInv.State in [dsInsert, dsEdit] then qryAPInv.post;
  if qryAPInvDet.State in [dsInsert, dsEdit] then qryAPInvDet.post;

  frmKaifa.ADOConnection1.BeginTrans;
  try
    qryAPInv.UpdateBatch(arCurrent);
    qryAPInvDet.UpdateBatch(arAll);
    frmKaifa.ADOConnection1.CommitTrans;
  except
    on E:EOleException do begin
      frmKaifa.ADOConnection1.RollbackTrans;

      qryAPInv.UpdateCursorPos;   //Master error.
      if (rsNew in qryAPInv.RecordStatus) or (rsModified in qryAPInv.RecordStatus) then begin
        //showmessage(IntToStr(frmKaifa.ADOConnection1.Errors[0].NativeError) + ' ' + E.Message);
        if frmKaifa.ADOConnection1.Errors[0].NativeError = 2627 then begin   //Key violation (key already exists).
          with editInvoiceNo do begin Show; SetFocus; end;
          raise(exception.create('Invoice no. already exists for this vendor'));
        end else if frmKaifa.ADOConnection1.Errors[0].NativeError = 32 then raise(exception.create('Unable to insert or update invoice...' +#13 + 'It has been changed or deleted since last retrieved'))
        else raise(exception.create('Unable to insert or update invoice...' +#13 + E.Message));
      end;

      DBGrid1.Setfocus;   //Detail error.

      qryAPInvDet.first;   //Move to invalid record.
      while not qryAPInvDet.EOF do begin
        qryAPInvDet.UpdateCursorPos;
        if (rsNew in qryAPInvDet.RecordStatus) or (rsModified in qryAPInvDet.RecordStatus) then break;
        qryAPInvDet.next;
      end;

      Application.MessageBox(PChar('Unable to insert or update invoice...' +#13 +#13 + E.Message), PChar(Application.Title), mb_OK + mb_DefButton1 + mb_IconStop);
      btnCancelClick(sender);
      exit;

    end else begin   //Other type of exception.
      frmKaifa.ADOConnection1.RollbackTrans;
      raise;
    end;
  end;

  if qryAPInvUpdated then begin
    try
      if qryAPInvInserted then begin
        frmAPInvoices.qryAPInv.requery;   //Refresh list to add new record.
        frmAPInvoices.qryAPInv.Locate('APInvoiceID', qryAPInvAPInvoiceID.value, []);
      end else begin
        frmAPInvoices.qryAPInv.Locate('APInvoiceID', qryAPInvAPInvoiceID.value, []);
        frmAPInvoices.qryAPInv.UpdateCursorPos;
        frmAPInvoices.qryAPInv.Recordset.Properties['Unique Table'].Value := 'APInv';
        frmAPInvoices.qryAPInv.Recordset.Properties['Resync Command'].Value :=
        'SELECT I.*, V.VendorNo, V.VendorName, V.Phone, V.Email ' +
        'FROM APInv I ' +
        'LEFT OUTER JOIN Vendor V ON V.VendorID = I.VendorID ' +
        'WHERE I.APInvoiceID = ?';
        frmAPInvoices.qryAPInv.RecordSet.resync(adAffectCurrent, adResyncAllValues);   //Refresh existing record on the list.
        frmAPInvoices.qryAPInv.resync([]);
      end;
    except; end;
    if not qryAPInvReceiptID.IsNull then begin   //If invoice was created from a PO receipt, refresh receipts list to show invoice no.
      try
        frmInvReceipts.mnuRefreshClick(sender);
      except; end;
    end;
  end;

  Close;
end;

how about using UNIDAC?

Thanks

Caksul

[AndreyZ]

As UniDAC has no UpdateBatch method, it is difficult for us to give you the similar code that replaces the one above. Instead of UpdateBatch you can use the ApplyUpdates in UniDAC, but it will change the behaviour of your application. ApplyUpdates saves all changes that were made in a dataset, where UpdateBatch(arCurrent) saves only the current record. Please describe in details why you need to use the UpdateBatch method with the arCurrent parameter.

[caksul]

example:





master

Code: Select all

JournalID   JournalName

1001           Payment Journal

detail

Code: Select all

JournalID LineNumber  GLAccount  Amount
1001        1                 1-001         1000
1001        2                 1-002         1000
1001        3                 1-003        -2000

This transaction was changed to


master

Code: Select all

JournalID   JournalName

1001           Payment Journal USD

detail

Code: Select all

JournalID LineNumber  GLAccount  Amount
1001        1                 1-001         1500
1001        2                 1-002         1500
1001        3                 1-003        -3000

if pressed OK button then posting transactions, but
if pressed CANCEL button then the transaction remains on the original

master

Code: Select all

JournalID   JournalName

1001           Payment Journal

detail

Code: Select all

JournalID LineNumber  GLAccount  Amount
1001        1                 1-001         1000
1001        2                 1-002         1000
1001        3                 1-003        -2000

how about using UNIDAC?

Thanks

Caksul

[AndreyZ]

For such situations, you can use transactions and the cached updates mode. For example:

Code: Select all

procedure TMainForm.ButtonOKClick(Sender: TObject);
begin
  UniConnection.StartTransaction;
  try
    UniQuery.ApplyUpdates; // cached data goes to the database, but the changes are not committed to the database because there is an active transaction
    UniConnection.Commit;  // if there were no errors, commit transaction
  except
    // if there were errors
    UniQuery.RestoreUpdates; // return the cache of updates to its state before calling ApplyUpdates
    UniConnection.Rollback;  // roll back transaction
  end;
end;

procedure TMainForm.ButtonCancelClick(Sender: TObject);
begin
  UniQuery.CancelUpdates; // clear all pending cached updates from cache and restore dataset in its prior state
end;

For more information about used methods, please refer to the UniDAC documentation.

[caksul]

what if the master detail?
UniQuery is master and
UniQuery1 is detal

like this?

Code: Select all

procedure TMainForm.ButtonOKClick(Sender: TObject);
begin
  UniConnection.StartTransaction;
  try
    UniQuery.ApplyUpdates; // cached data goes to the database, but the changes are not committed to the database because there is an active transaction

    UniQuery1.ApplyUpdates;
    UniConnection.Commit;  // if there were no errors, commit transaction
  except
    // if there were errors
    UniQuery.RestoreUpdates; // return the cache of updates to its state before calling ApplyUpdates 

    UniQuery1.RestoreUpdates;
    UniConnection.Rollback;  // roll back transaction
  end;
end;

procedure TMainForm.ButtonCancelClick(Sender: TObject);
begin
  UniQuery.CancelUpdates;
  UniQuery1.CancelUpdates; // clear all pending cached updates from cache and restore dataset in its prior state
end;

thanks
caksul

For such situations, you can use transactions and the cached updates mode. For example:

Code: Select all

procedure TMainForm.ButtonOKClick(Sender: TObject);
begin
  UniConnection.StartTransaction;
  try
    UniQuery.ApplyUpdates; // cached data goes to the database, but the changes are not committed to the database because there is an active transaction
    UniConnection.Commit;  // if there were no errors, commit transaction
  except
    // if there were errors
    UniQuery.RestoreUpdates; // return the cache of updates to its state before calling ApplyUpdates
    UniConnection.Rollback;  // roll back transaction
  end;
end;

procedure TMainForm.ButtonCancelClick(Sender: TObject);
begin
  UniQuery.CancelUpdates; // clear all pending cached updates from cache and restore dataset in its prior state
end;

For more information about used methods, please refer to the UniDAC documentation.

[AndreyZ]

Yes, you can use your code. But please note that when establishing master/detail relationship, the cached updates mode of detail dataset works properly only when the TUniQuery.Options.LocalMasterDetail property is set to True. You can find more information in the descriptions of the CachedUpdates and LocalMasterDetail properties in the UniDAC documentation.

[caksul]

thanks for your fast respon

Yes, you can use your code. But please note that when establishing master/detail relationship, the cached updates mode of detail dataset works properly only when the TUniQuery.Options.LocalMasterDetail property is set to True. You can find more information in the descriptions of the CachedUpdates and LocalMasterDetail properties in the UniDAC documentation.

[AndreyZ]

If any other questions come up, please contact us.