Page 1 of 1
CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Fri 21 Sep 2018 15:28
by witzl
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
Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Sat 22 Sep 2018 09:25
by MaximG
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;
Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Mon 24 Sep 2018 09:24
by witzl
Thank, Perfect!
Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Tue 25 Sep 2018 05:25
by MaximG
We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning our product usage.
Crash: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Tue 02 Oct 2018 09:05
by witzl
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
Re: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Tue 02 Oct 2018 09:13
by witzl
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.
Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Tue 02 Oct 2018 11:54
by witzl
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?
Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Thu 04 Oct 2018 14:26
by MaximG
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
Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Thu 13 Dec 2018 18:00
by witzl
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
Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Thu 13 Dec 2018 18:02
by witzl
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?
Re: CRASH: best way to copy blobs from SDAC to LiteDAC over the network in Firemonkey FMX
Posted: Sat 22 Dec 2018 13:30
by MaximG
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.