Looping through records in TUniQuery - Out of memory error

[stevel]

URGENT SUPPORT REQUIRED.

Using: Delphi XE2 Enterprise Update 4.1; UniDAC 4.6.12 Pro; IBM DB2 Express-C 10.1; Delphi VCL forms 32-bit application; running on Windows 8 64-bit; DB2 database is running on Windows Server 2012.

The objective of my routine is to extract the BLOB data from one of the database table fields, and save it to file on disk. It is a large table - between 1 - 10 million rows.

I am using TUniQuery, created in code, without setting any options (thus, default options). I do a SELECT to select all rows, then loop through the rows from beginning to end saving the content of the BLOB field to disk.

When running the code, after some time it gives 'Out of memory' exception.

Please suggest any option to set on the UniQuery to prevent this from happening or suggest a workaround solution. Maybe my logic is wrong and I should not select all rows. I'm not sure. Please help me.

By the way, I have installed IBM Data Server Runtime Client on the same machine as the Delphi app, so the app is connecting to the database without any problems.

Here is my code:

Code: Select all

procedure TfMain.btStartClick(Sender: TObject);
var
  msg: String;
  p, e, n, schm, tbl, fld: String;
  uq: TUniQuery;
  c, r, pp: Integer;
begin
  if not FormValidate(msg) then
  begin
    ShowMessage(msg);
    Exit;
  end;

  schm := Trim(cmbSchema.Text);
  tbl := Trim(lstTable.Items[lstTable.ItemIndex]);
  fld := Trim(lstField.Items[lstField.ItemIndex]);

  p := deOutPath.Directory;

  if not DirectoryExists(p) then
    ForceDirectories(p);

  e := edtFileExt.Text;

  uq := TUniQuery.Create(nil);
  try
    uq.Connection := UniConnection1;

    uq.SQL.Text := 'SELECT COUNT(1) C FROM ' + schm + '.' + tbl;
    uq.Open;
    r := uq.FieldByName('C').AsInteger;
    uq.Close;

    uq.SQL.Text := 'SELECT ' + fld + ' FROM ' + schm + '.' + tbl;
    uq.Open;

    c := 1;
    while not uq.Eof do
    begin
      n := p + '\FILE' + Format('%.7d', [c]) + e;
      uq.GetBlob(fld).SaveToFile(n);

      pp := Trunc(c / r * 100);
      pbProg.Position := pp;
      lbProg.Caption := Format('%d%% (%d of %d records processed)', [pp, c, r]);
      Application.ProcessMessages;

      Inc(c);
      uq.Next;
    end;
    uq.Close;

    ShowMessage('Done!');

  finally
    uq.Free;
  end;

end;

Regards,
Steve Faleiro

[AlexP]

Hello,

When reading data from the DB, they are stored in the DataSet, and especially when reading large data, the Out of memory error can occur. To avoid this, you can use the UniDirectional mode and disable the FetchAll option. In this case, the DataSet will store only the number of records specified in the FetchRows property (the number of records fetched for one fetch operation). When fetching the next portion of data, the previous one will be deleted.

Code: Select all

...
  uq := TUniQuery.Create(nil);
  try
    uq.Connection := UniConnection1;
    uq.UniDirectional := True;
    uq.SpecificOptions.Values['FetchAll'] := 'False';
    uq.FetchRows := 25;
...

[stevel]

Dear AlexP,

Thank you very much for replying and for presenting this solution. I will try it and let you know whether it worked.

Code: Select all

uq.SpecificOptions.Values['FetchAll'] := 'False';

FetchAll is set to False by default, isn't it?


Bests,
Steve

[stevel]

Dear Alex,

Your recommendation (UniDirectional, FetchAll) worked!


Bests,
Steve Faleiro

[AlexP]

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.

[FCS]

Hello,

Maybe it will be a good idea for you too.

Code: Select all

//---------------------------------------------------------------------------
function TDM3.Ekxport_BLOB(katalog:string): boolean;
var
  UQ_Blob: TUniQuery;
  UQ_Blob2: TUniQuery;
  Plik:string;
begin
  Katalog:=SlashedDirectory(katalog);

  Result:=true;
  try
    UQ_Blob:=TUniQuery.Create(self);
    UQ_Blob.Connection:=UniConnection1;
    UQ_Blob.SQL.Clear;

    UQ_Blob2:=TUniQuery.Create(self);
    UQ_Blob2.Connection:=UniConnection1;
    UQ_Blob2.SQL.Clear;
    UQ_Blob2.SQL.Add('select plik,dane from osr_blob where id=:id');

    UQ_Blob.SQL.Add('select id,plik from osr_blob');
    UQ_Blob.Execute;

    UQ_Blob.First;
    while not UQ_Blob.Eof do begin
      UQ_Blob2.ParamByName('id').AsInteger:=UQ_Blob.FieldByName('id').AsInteger;
      UQ_Blob2.Execute;
      Plik:=Katalog+UQ_Blob2.FieldByName('plik').AsString;
      TBlobField(UQ_Blob2.FieldByName('dane')).SaveToFile(Plik);

      UQ_Blob.Next;
    end;

    UQ_Blob2.Close;
    UQ_Blob2.Free;

    UQ_Blob.Close;
    UQ_Blob.Free;
    MsgBox('The data are in:'+#13#13+Katalog, mfOKK);
  except
    Result:=false;
    msgBox('Error reading BLOB', mfOKB);
  end;
end;

where
plik = filename,
dane = blob data
Katalog = output directory for blob data

Regards
Michal