Does TVirtualTable support Delta records ?

[thelvaci]

Can we find inserted, updated and deleted records on TVirtualTable ? If we can; can we see old and new values on updated records ?

Somebody told me VirtualTable fast as TClientDataset, it is good but we need delta records in-memory datasets. Is it possible on VirtualTable ?

[AlexP]

Hello,

To retrieve the old and new values in VirtualTable, you can use the CachedUpdates mode. The following example demonstrates such kind of behavior:

Code: Select all

  VirtualTable1.CachedUpdates := True;
  VirtualTable1.Open;
  VirtualTable1.Insert;
  VirtualTable1ID.Value   := 1;
  VirtualTable1.Post;
  VirtualTable1.ApplyUpdates;
  VirtualTable1.Edit;
  VirtualTable1ID.Value := 2;
  VirtualTable1.Post;
  ShowMessage(IntToStr(VirtualTable1ID.OldValue));  // 1
  ShowMessage(IntToStr(VirtualTable1ID.NewValue)); //2
  VirtualTable1.CancelUpdates; // 1 will remain
//VirtualTable1.ApplyUpdates;  // 2 will be applied

[thelvaci]

Thanks AlexP,

Just imagine that;

Code: Select all

// Pseudo code
procedure LoadData(const VirtualTable : TVirtualTable);
var
  aProc : TSomeStoredProc;
begin
  aProc := TSomeStoredProc.Create( nil );
  
  try
    aProc.Connection := SomeConnection;
    aProc.ProcedureName := 'SomeStoredProcName';
    aProc.Open;

    aProc.First;
    while not aProc.Eof do
    begin
      // Append data into VirtualTable
      aProc.Next;
    end;
  finally
    aProc.Free;
  end;
end;

and after the LoadData method we can change some data using code or some grid. We can insert new records, change some records and delete some records also.

Code: Select all

// Pseudo code
procedure ProcessDeltaRecords(const VirtualTable : TVirtualTable);
var
  aProc : TSomeStoredProc;
begin
  // i need new inserted records after LoadData
  // i need updated data (old values and new values) after LoadData
  // i need deleted data after LoadData

  aProc := TSomeStoredProc.Create( nil );
  aProc.Connection := SomeConnection;

  try
  // ** Process inserted data after LoadData ** //
    // looping inserted records here and execute a insert stored procedure for each one.
    aProc.ProcedureName := 'SomeInsertStoredProcName';
    aProc.Parameters.Refresh;
    aProc.Parameters.ParamByName('SomeParamName').Value := InsertedRecordsSomeFieldsValue;
    ...
    ...
    aProc.ExecProc;

  // ** Process updated data after LoadData ** //
    // looping updated records here and execute a update stored procedure for each one.
    aProc.ProcedureName := 'SomeUpdateStoredProcName';
    aProc.Parameters.Refresh;
    aProc.Parameters.ParamByName('SomeParamName').Value := UpdatedRecordsSomeFieldsNewValue;
    aProc.Parameters.ParamByName('UsingWhereConditionParamName').Value := UpdatedRecordsSomeFieldsOldValue;
    ...
    ...
    aProc.ExecProc;

  // ** Process deleted data after LoadData ** //
    // looping deleted records here and execute a delete stored procedure for each one.
    aProc.ProcedureName := 'SomeDeleteStoredProcName';
    aProc.Parameters.Refresh;
    aProc.Parameters.ParamByName('SomeParamName').Value := DeletedRecordsSomeFieldsValue;
    ...
    ...
    aProc.ExecProc;
  finally
    aProc.Free;
  end;
end;

As you can see, i need changed, updated and deleted records. Because i want to use my stored procedures for updates, inserts and deletes. I can do that using TClientDataSet's Delta info, but i wonder can i do that using TVirtualTable.

Now, i hope i am clear now.

[AlexP]

Hello,

There is no property in VirtualTable similar to ClientDataSet. However, you can use one of our DAC products (ODAC, MyDAC, SDAC, PgDAC, IBDAC, LiteDAC, UniDAC - depending on the used DB) instead of VirtualTable. In these components, you can use various stored procedures in one component to get, insert ,update and delete data.

[thelvaci]

Thanks for your interest and answer, but i didnt understand really why dont you implement this functionality in your great tools. I think it is indeed required.

If some programmer move on from Client Server programming to n-Tier architecture, your in-memory dataset will not be enough.

We are using SQL Server in our product, we have four stored procedure for each table. Browse, Insert, Update and Delete. And all tables have one or more triggers(Ins., Update, Delete). All business logic coded into that triggers and we have a Datasnap application server. I have more proxy classes like my table structure. May be like ORM.

At this situations, programmer needs delta records in their clients to send a server. And may be delta records must be support json data format to comminicate from every environment. After sending supported delta information to server, server processes that json delta data, and execute relational stored procedure in a transactional environment.

Programmer dont interest what these classes are and what they do, they knows classes are there and it works. He/she can call in every environment like iOs, Android, web, win32/64, MacOs etc.

So i am trying to say that, i think your TVirtualTable must have delta records and it can export as a json format. If your team decide to make this functionality, your n-tier customers may will be satisfied.

Just an idea..

Anyway thanks for all..

[AlexP]

Hello,

TVirtualTable is a local component, in contrast to TClentDatSet, and it is not for work with any DB even via TDataProvider, the Delta property similar to the one in TClentDatSet is not necessary in TVirtualTable. Therefore we won't modify this component behavior for the time being. This suggestion is already added in our UserVoice http://devart.uservoice.com/forums/1046 ... entdataset, you can also vote for it.

[thelvaci]

I don't agree with you. Users choose your SDAC product for access and use some spesific features in SQL Server. If your SDAC customer implement many stored procedures for data insert, update and delete; and also your customer implement business rules many triggers on his/her table and if he/she wants to connect his database environment using Datasnap technology (because your customers customers access the data on every platform like android, ios, etc) then how can he/she use your TVirtualTable ? Because your customer needs changed data to send his/her Datasnap server. Do you say, our product doesnt support Datasnap or any other n-Tier architecture ?

You said "programmer may be specify in his/her SDAC components Insert, Update and Delete procedure". But how SDAC components use Datasnap proxy clients ? Is it possible ?

If i use SQL Server 2008 R2 for example, i want to use all SQL Server's power. If i use Oracle, i want to use all the Oracle's power. For that reason; i have to implement business rules in my database. I know, this is a hard mission, but if i want performance and power and much more spesific control; i have to implement this.

Anyway, i voted..

[AlexP]

Hello,

The DataSnap technology implements communication between the client and a server application, Our components in three-element applications can only be used in a server-side part, as they work with database only, so the implementation of Datasnap proxy clients is in no way connected with our data access components.
As I wrote above, our components support correct work with stored procedures, i.e. for example, if for obtaining and modifying data you use stored procedures, then, specifying appropriate procedures in the properties OraQuery.SQL, OraQuery.SQLInsert, OraQuery.SQLUpdate, etc., you will be able to receive the filled DataSet which you are returning in the procedure, as well as automatically (when you call the Insert, Update, Delete methods) to substitute the necessary parameters and invoke the appropriate procedures, in this case, you do not need to parse the Delta parameter manually for generating stored procedure call.

[thelvaci]

Hello again AlexP, thanks for your answer. But i think i cant explain myself what i mean.

Client App <-----> Datasnap Server <-----> SDAC Components <-----> Database Server

In Win32/Win64 client we can call a method like this;

Pseudo codes here..

// Client Side..
MemData: TClientDataSet or TVirtualTable;

var
Reader : TDBXReader;
JSON : TJsonValue;
begin
Reader := DatasnapServerClass.GetCustomer();
or
JSON := DatasnapServerClass.GetCustomer();
MemData := JSONToDataSet(JSON); or
MemData := ReaderToDataSet(Reader);

DataSource1.DataSet := MemData;
Grid.DataSource := DataSource1;
end;

After this method, user can change data, insert new data or delete some data right ?

Now lets assume user clicks Save Changes; how can i send a changed delta data to datasnap server with TVirtualTable ?

function ToJSON : TJSONValue;
begin
// find delta data
// convert it json and return back
end;

procedure <Form>.SendData(Sender : TObject);
var
JSON : TJSONValue;
begin
JSON := MemData.ToJSON; // or something smilar
DatasnapServerClass.SendCustomerDelta(JSON);
end;

Programmers needs this functionality if he/she use n-tier architecture. It may be not true if your components can be transfer cross server to client and vice versa. But i dont think so.

So, if SDAC customer want to use SDAC components on the DataSnap Server side, he/she must implement a method returning TDBXReader or may be good alternative TJSONValue etc. If he/she implement it like his;

type
TDataSnapServerClass = class....
public
function GetCustomer : TDBXReader; overload;
function GetCustomer : TJSONValue; overload;
...
...
end;
..
..
function TDataSnapServerClass.GetCustomer : TDBXReader;
var
SDACComponent : TSDAC...;
begin
SDACComponent := TSDAC...Create(nil);
try
SDACComponent.Connection := SomeConnection;
SDACComponent.ProcedureName := 'SomeStoredProcedureName';
SDACComponent.Open;

Result := ConvertSDACComponentToTDBXReader;
finally
SDACComponent.Free;
end;
end;

function TDataSnapServerClass.GetCustomer : TJSONValue;
var
SDACComponent : TSDAC...;
begin
SDACComponent := TSDAC...Create(nil);
try
SDACComponent.Connection := SomeConnection;
SDACComponent.ProcedureName := 'SomeStoredProcedureName';
SDACComponent.Open;

Result := ConvertSDACComponentToTJSONValue;
finally
SDACComponent.Free;
end;
end;

and then clients call GetCustomer, what will happen when user wants to send back changed data to server with TVirtualTable component ? I have to know what records deleted that i can call DeleteCustomer remote method or better way send a json delta to server.

On the other hand we must return TDBXReader or TJSON.. because our clients can be Android client, iOS client, Blackberry client etc. So JSON known at theese platforms.

Then i hope i am clear now.

Thanks for your effort.