Load from VirtualTable to TMSQuery

[Hooman]

Hi,

In my application, I need to save data (TMSQuery) to workstation and next time load from file instead of database.

I implemented the code as follow:

VirtualTable := TVirtualTable.Create(Application);
VirtualTable.Assign(MSQuery1);
VirtualTable.SaveToFile(FileName);

This works fine for saving. Now we need to load data and I implemented as follow:

VirtualTable := TVirtualTable.Create(Application);
VirtualTable.LoadFromFile(FileName);
VirtualTable.Open;
MSQuery1.Assign(VirtualTable);
MSQuery1.Active := True;

The problem is the last line. When Delphi run MSQuery1.Active := True; it automatically calls the MSQuery1.SQL which is not what I need.

I need to load data from VirtualTable to MSQuery1 and have the MSQuery1 open at the end without calling the SQL statement to database.

Please let me know the best way to achieve this.

Thanks
Hooman Yazdi

[AndreyZ]

Hello,

You can copy all records from one dataset to another using our TCRBatchMove component. TCRBatchMove serves for transferring records between datasets. Here is an example:

Code: Select all

CRBatchMove.Source := MSQuery1;
CRBatchMove.Destination := VirtualTable1;
CRBatchMove.Execute;

You can find detailed description of the TCRBatchMove component in the SDAC documentation.

[Hooman]

Hi,

I used CRBatchMove component as follow:

CRBatchMove.Source := VirtualTable;
CRBatchMove.Destination := MSQuery1;
CRBatchMove.Execute;

My data is loaded to virtual table from file and now I want to copy these records to MSQuery1. When Execute is called, I get PRIMARY KEY error. I checked the SQL profiler and it generated INSERT INTO ...

This is not what I want. I need data to be copied from Virtual Table to TMSQuery without inserting or updating data to database. Basically VirtualTable is a exact copy of data from SQL but loaded from file instead so I don't want MSQuery1 to Add/Edit any record whlie loading data from VirtualTable.

We were previously using TAdoDataset and it had a method called LoadFromFile. Now TMSQuery does not have it and all we are trying to do is to achieve this using VirtualTable.

Please let me know the best way.

Thanks
Hooman Yazdi

[AndreyZ]

To prevent TMSQuery execute INSERT INTO statements, you can set the TMSQuery.CachedUpdates property to True.

[Hooman]

Ok,

Now it copies the data to MSQuery1 but the query is closed after copying data. When I call MSQuery1.Open, it calls to database again.

How I can avoid this?

Basically I need to copy data from VirtualTable to TMSQuery and have the Query open at the end.

[AndreyZ]

You can use the following code:

Code: Select all

begin
  VirtualTable1.LoadFromFile('filename');
  VirtualTable1.Open;
  MSQuery1.SQL.Text := 'select * from tablename';
  MSQuery1.CachedUpdates := True;
  MSQuery1.Open;
  CRBatchMove1.Source := VirtualTable1;
  CRBatchMove1.Destination := MSQuery1;
  CRBatchMove1.Execute;
  ShowMessage(BoolToStr(MSQuery1.Active, True)); // here MSQuery1 is in the active state
end;

[Hooman]

Thanks for the reply.

The code is very good but my goal is to not call anything to database even before copying records from VirtualTable to TMSQuery. Maybe there is another method.

This is for remote users who should access DB on WAN and it is slow for them to open table because of data size. For them, we need to open MSQuery1 from database once and then save a copy to local computer. Then on next tries open MSQuery1 from File instead of database so the point is to not call database at all on 2nd+ tries.

Would you please let me know the best way to implement this.

[AndreyZ]

You can use tho following approach:
- when the user opens your application and there is a backup file, you load it to TVirtualTable;
- if there is no backup file, you open TMSQuery, receive data from the server, and store it to backup file. After this, you load the backup file to TVirtualTable and close TMSQuery.
Using such approach, your users receive the data from the server only once, when there is no backup file.