CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Fri 21 Sep 2018 15:28

Hi,

on serverside I have a SQL-Servertable, with 5000 blobs:

SQLServerTable:
ID: Integer
Pic: VarBinary

In my Firemonkey IOS App I have SDAC-Client and LiteDAC client.

SQLIteTable:
ID: Integer
Pic: VarBinary

I can connect via network to the SQLServerTable and want to copy the data to my local SQLiteTable.

What is the best way to copy the data?

Case 1:

Code: Select all

SQLServerTable : TMSQuery;
SQLiteTable : TLiteQuery;
SQLServerTable.SQL.Text := 'select ID, Pic from SQLServerTable';
SQLServerTable.open;

SQLiteTable.SQL.Text :=  'select ID, Pic from SQLiteTable';
SQLiteTable.open;

while not SQLServerTable.eof do begin
  SQLiteTable.Insert;
  SQLiteTable.FieldByName('ID').Value :=  SQServerTable.FieldByName('ID').Value;
  SQLiteTable.Post;
  SQLServerTable.next;
end;
SQLiteTable.Close;
SQLServerTable.Close;
Or should I use TLiteScript with parameters:

Case 2:

Code: Select all

SQLServerTable : TMSQuery;
SQLiteScript : TLiteScript;

SQLServerTable.SQL.Text := 'select ID, Pic from SQLServerTable';
SQLServerTable.open;

SQLiteScript.SQL.Text := 'insert into SQLiteTable (ID, PIC) values (:ID, :PIC)';

while not SQLServerTable.eof do begin
  SQLiteScript.ParamByName('ID').Value := SQServerTable.FieldByName('ID').Value;
  SQLiteScript.ParamByName('PIC').Value := SQServerTable.FieldByName('PIC').Value;
  SQLiteScript.Execute;
  SQLServerTable.next;
end;
SQLServerTable.Close;
Or do you have a better way? BulkCopy?

Case 2 doesn't work, the Pics are NOT copied!

Thanks in advance,
Dietrich
Last edited by witzl on Tue 02 Oct 2018 09:51, edited 1 time in total.

MaximG
Devart Team
Posts: 1257
Joined: Mon 06 Jul 2015 11:34

Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by MaximG » Sat 22 Sep 2018 09:25

To solve the problem you've described, you can use TCRBatchMove Class, which is part of our components: https://www.devart.com/litedac/docs/dev ... chmove.htm
For example :

Code: Select all

uses CRBatchMove;
...
var
  crBatchMove: TCRBatchMove;
  SQLServerTable : TMSQuery;
  SQLiteTable : TLiteQuery;
begin
  ...
  crBatchMove := TCRBatchMove.Create(nil);
  try
    crBatchMove.Source := SQLServerTable;
    crBatchMove.Destination := SQLiteTable;
    crBatchMove.CommitCount := 100;
    crBatchMove.Execute;
  finally
    crBatchMove.Free;
  end;
end;

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Mon 24 Sep 2018 09:24

Thank, Perfect!

MaximG
Devart Team
Posts: 1257
Joined: Mon 06 Jul 2015 11:34

Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by MaximG » Tue 25 Sep 2018 05:25

We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning our product usage.

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Crash: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Tue 02 Oct 2018 09:05

Hi,

unfortunately TCRBatchMove crashes (closes the App) after UpdateInserting about 3000 JPEG-Images. The Images are between 100KB and 500KB in size, residing in a varbinary Field on the SQL-Server 2016 and should be copied to a SQLiteTable with same Fielddefs.

The Debugging mode in Delphi Tokyo 10.2.3 doesn't recognize the crash.

The IPad-AIR memory shows 20GB free space.

Code: Select all

procedure StartBatch;
var

  BatchMoveServerClient : TCRBatchMove;
  
  SQLServer : TMSConnection;
  SQLServerTable : TMSQuery;

  SQLite : TLiteConnection;
  SQLiteTable : TLiteQuery;

begin

  SQLServer := TMSConnection.Create(nil);
  SQLServer.Options.Provider := prDirect;

  SQLite := TLiteConnection.Create(nil);
  SQLite.Options.Direct ;= True;
  
  SQLServerTable  := TMSQuery.Create(nil);
  SQLServerTable.Connection  := SQLServer;
  SQLServerTable.FetchAll := false;
  SQLServerTable.FetchRows := 25;
  SQLServerTable.Options.QueryRecCount := True;
  SQLServerTable.KeyFields := FKeyField;
  SQLServerTable.SQL.Text := 'select ID, Pic from SQLServerTable';

  SQLiteTable := TLiteQuery.Create(nil);
  SQLiteTable.Connection := SQLite;
  SQLiteTable.FetchAll := false;
  SQLiteTable.FetchRows := 25;
  SQLiteTable.Options.QueryRecCount := True;
  SQLiteTable.KeyFields := FKeyField;
  SQLiteTable.SQL.Text := 'select ID, Pic from SQLiteTable';

  BatchMoveServerClient := TCRBatchMove.Create(nil);
  BatchMoveServerClient.OnBatchMoveProgress := BatchMoveServerClientProgress;
  BatchMoveServerClient.Mode := bmAppendUpdate;
  BatchMoveServerClient.CommitCount := 100;
  BatchMoveServerClient.source := SQLServerTable;
  BatchMoveServerClient.Destination := SQLiteTable;
  BatchMoveServerClient.FieldMappingMode := mmFieldName;

  BatchMoveServerClient.Execute;
  
  BatchMoveServerClient.free;
  
  SQLite.Free;
  SQLiteTable.Free;  

  SQLServer.Free;
  SQLServerTable.Free;

end;

procedure BatchMoveServerClientProgress(Sender: TObject; Percent: Integer);
begin
  ProgressStep := Percent / 100;
end;
It seems like some Buffer is getting full ...

Thanks in Advance,
Dietrich

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Tue 02 Oct 2018 09:13

Actually I only copy the Images which have not been copied before.


When starting the app the first time, about 60% of the images are copied.

when I restart the App and there are less images left (i.e. 2000 images) to copy to the SQLite-table, the app craches earlier after about 30% already.

when I restart the App and there are less images left (i.e. 1000 images) to copy to the SQLite-table, the app craches earlier after about 15% already.

This tells me that SQLite on the Ipad has a problem.

Restarting the App again and again finally copies all images.

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Tue 02 Oct 2018 11:54

ok - I tried on Simulator.

Works perfectly.

Does this mean, its a memory problem? As I wrote, there is plenty of free memory (drive space) on the iPad.

What about the RAM?

MaximG
Devart Team
Posts: 1257
Joined: Mon 06 Jul 2015 11:34

Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by MaximG » Thu 04 Oct 2018 14:26

Try to determine what exactly causes the described issue. For this, try loading all the required data in certain portions (for example, 10% of the total amount). If you successfully load all the data in this way, then the issue really lies in the lack of RAM used by the device. Otherwise, the issue is due to the use of some specific data. Then you have to determine exactly those lines, loading of which leads to the error and let us know

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Thu 13 Dec 2018 18:00

Hi,

since I need to convert the images to Thumbs, and save them on disk, I cannot use BatchMove.

This is my procedure where it crashes after some some hundred records:

Code: Select all

procedure SaveRemoteImagesLocally;
var
  aBitmap : TBitmap;

Begin

  MSQuery1.SQL.Text := 'SELECT ImageFilename, Image FROM ImageTable');
  MSQuery1.open;  

  while not MSQuery1.EOF do Begin

    if (MSQuery1.Fields.FindField(Image) <> nil) then begin

      if MSQuery1.GetBlob(MSQuery1.FieldByName(Image)).Size > 0 then begin

        MSQuery1.GetBlob(MSQuery1.FieldByName(Image)).SaveToFile(MSQuery1.FieldByName(ImageFilename).AsString);

        ABitmap := TBitmap.Create;
        ABitmap.LoadThumbnailFromFile(MSQuery1.FieldByName(ImageFilename).AsString, 240, 180, false);

        ABitmap.SaveToFile(GetImageThumbFile(MSQuery1.FieldByName(ImageFilename).AsString));
        ABitmap.Free;

      end;

    end;

    MSQuery1.Next;

  end;

  MSQuery1.Close;

end;
I am still looking for a more elegant way!

I am using Firemonkey, this procedure runs on an iPad ...

Thanks in advance,
Dietrich

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by witzl » Thu 13 Dec 2018 18:02

This only happens on different iPad Air and iPad Air II.
On iPad Pro things are ok.

Is it possible that the IOS registers high CPU-load and closes the app?

MaximG
Devart Team
Posts: 1257
Joined: Mon 06 Jul 2015 11:34

Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX

Post by MaximG » Sat 22 Dec 2018 13:30

The fact that the behavior of the application you develop depends on the specific iPad model and is not directly related to the work of our access components. Please try finding the appropriate solution by referring to the specialized resources.

Post Reply