No one can help

[limelect 48]

After so many years of successful Delphi, I almost give up

on my free program.

After out of memory  I made a new test program and this is what I came up with

1. NO PROBLEM with SQL.

2. I use SQLite for the database.

3. I have a large database.

4. leaving only one large record in the database, NO PROBLEM. Record size 61094574.

5. USING the whole database reading into string s := FDMemTable1.FieldByName('Description').AsString;

   make out of memory error. BUT

6. This same instruction works when the table is ONLY this one record. This large record

7. Now for my solution that does not do much. I thought if I free most of the database the program

     memory will decrease.So I use the memory table to load 1 record

 

8.   i := FDQuery4.FieldByName('SectionsId').AsInteger;
  ProjectsFDTable.Filtered := False;
  ProjectsFDTable.Filter := 'FilesIndex=' + IntToStr(i);
  ProjectsFDTable.Filtered := True;
  FDMemTable1.CloneCursor(ProjectsFDTable); <<< I load the memory database with 1!!!! record.
//  FDMemTable1.RecordCount << made sure memory has something

>>>>>>>>> I close and free all my tables to be left with 1 record in memory.

ProjectsFDTable.Close;
FilesFDTable1.Close;
 FDConnection1.Close;<< even the connection
 ProjectsFDTable.Free;
FilesFDTable1.Free;
FDConnection1.Free;
 
  s := FDMemTable1.FieldByName('Description').AsString;<<<< memory error.

 

P.S the above software give me 5 filtered record

Using 4 of them does not make any error only the large one.

 

I doubt that anyone can help since it is not a database problem but a Delphi PROGRAM problem.

Once I use a database the program memory is large and unmanageable.

Unless someone else thinks differently.

 

Luckily I do not make money out of this program so I keep searching for solutions for knowledge only.

I think I will move to a different DATABASE as it is my only option.

Or give the user an option to make a new database when it reaches a certain size.

 

One thing adding records does not have any problems. 

I wrote this for the benefit of you the developer to know what is going on with my SQLite database.

Edited December 9, 2022 by limelect

[Attila Kovacs 629]

Try to find some resources on "relational database" first.

Instead of stuffing everything into a giant BLOB in one record, split them up in other tables in many rows and have a reference to the main document.

[programmerdelphi2k 237]

hi @limelect

 

can you upLOAD just SQLite DB with NONE-records, just structure?

 

Edited December 9, 2022 by programmerdelphi2k

[limelect 48]

1. It will be too complex to split the data as it is text and pictures going into TAdvRichEditor

2. As seen from my explanation I clear all the records and left only with one record big one

   And this is OK.

3. I free all the connections and database as it will not have any effects

    and left with one record in-memory database that did not help.

 This supposes to have smaller program memory. It did not.

4. Once the program uses a large chunk of memory I am stacked with it.

 

I made hundreds of professional programs making money out of them never had

such behavior.

Edited December 9, 2022 by limelect

[Vandrovnik 214]

For Firebird, I use IBX component, which have "Unidirectional" property. When set to True, only current record is kept in memory. Maybe your database components have similar functionality?

[programmerdelphi2k 237]

"Description" field: what type on DB and size?

[limelect 48]

Number 4 in my description

It is a ftBlob

4. leaving only one large record in the database, NO PROBLEM. Record size 61094574.

If I leave 1 record NO PROBLEM. it has nothing to do with DB.

Only the Delphi program This is what I think and I might be wrong

Maybe you have something?

I read the blob into a string  then

St := TStringStream.Create(s);

 AdvRichEditor.LoadFromStream(St);

 

If the above works on any small record it is not the logic.

Edited December 9, 2022 by limelect

[limelect 48]

@VandrovnikFD has unidirectional i tried it allready

when I activate it in the object inspector it gives an error

[Brian Evans 105]

It would not take much memory fragmentation for 60MB allocations to start to fail for 32bit / 2GB of address space. SQLite is an in process database so shares the same 2GB address space. Multiple copies of record/field data that would not be an issue from smaller amounts of data become an issue. 

 

There are ways to stream blobs in Firedac to reduce memory usage. See: Support for Blob Streaming in FireDAC - RAD Studio (embarcadero.com)

 

 

 

 

[Vandrovnik 214]

3 minutes ago, limelect said:

@VandrovnikFD has unidirectional i tried it allready

when I activate it in the object inspector it gives an error

Which error?

May be it cannot work together with filtering... But you should be able to do filtering using SQL query and than use Unidirectional.

[limelect 48]

@VandrovnikRunnig the program

No oparation just openning the DATAbase

Edited December 9, 2022 by limelect

[programmerdelphi2k 237]

normally, to read or save a BLOB you would use some like this

Quote

var
  MyMemStream:TStream;
begin
   MyMemStream:=  FDQuery1.CreateBlobStream(FDQuery1MyBLOB, bmReadWrite); // read/write or (read or write only)!

   try
      AdvRichEditor.LoadFromStream(MyMemStream); // here, we can have 2x Memory = MyMemStream + AdvRichEditor-Memory-Stream

    finally

        MyMemStream.FREE; // then free it quickly

    end;
end;

... better, TStream ... not TMemoryStream!

 

in fact, the BLOB fields (data) are not loaded when in "select'ing", just a BLOB ID is known!

now when read your blob then, the "data" will be known!

Edited December 9, 2022 by programmerdelphi2k

[tgbs 14]

34 minutes ago, Vandrovnik said:

For Firebird, I use IBX component, which have "Unidirectional" property. When set to True, only current record is kept in memory. Maybe your database components have similar functionality? 

Two records :) Current and the previous one

[programmerdelphi2k 237]

FireDAC:

Quote

Specifies possible directions of navigation by dataset records.

Use the Unidirectional property to specify whether an application will navigate by dataset records in forward-only direction or in forward, backward and random directions. To change Unidirectional for an already used dataset, an application can call the Disconnect method first. 

Setting Unidirectional to True enables an application to navigate in forward-only direction. FireDAC will automatically discard prior rows from the internal data storage after moving the current position in the dataset, because they are not needed anymore. This dramatically reduces memory consumption on big result sets.  Setting Unidirectional to True for TFDTable disables the live data window mode.

Note: Do not use Unidirectional datasets to display data in grid control. It requires bi-directional datasets.

The Unidirectional property is synchronized with IsUniDirectional.

then, just 1 record on memory! what is seen on screen it's another history!

Edited December 9, 2022 by programmerdelphi2k

[Vandrovnik 214]

4 minutes ago, tgbs said:

Two records :) Current and the previous one

OK, did not check 🙂 Just wondering, why previous record, too?

[limelect 48]

@programmerdelphi2k trying your suggestion

[programmerdelphi2k 237]

4 minutes ago, programmerdelphi2k said:

FireDAC will automatically discard prior rows from the internal data storage after moving the current position in the dataset, because they are not needed anymore.

 

[programmerdelphi2k 237]

Just now, limelect said:

trying your suggestion

you record in question is = 60MBytes (text + pictures, etc...)?

I think that this is not problem at all for "out of memory"

it's necessary more info:

it's possible you "upload your UNIT + FORM" in zip file for analise?

[tgbs 14]

5 minutes ago, Vandrovnik said:

OK, did not check 🙂 Just wondering, why previous record, too?

I don't know why, you have to ask one of Team B.
It's very convenient for me because you can go to the next record and check if you're an EOF, come back and do something. Without unidirectional if there are multiple close/open you will get out of memory
Sorry for the offtopic

[programmerdelphi2k 237]

Unidirectional is used to specifics DB that support it! old-Fashion... or for quick access to records info without needs see any others!

 

Quote

TSQLTable is a unidirectional dataset. Unlike other datasets, unidirectional datasets do not buffer multiple records in memory. Because of this, you can only navigate using the First and Next methods. There is no built-in editing support: you can only edit the data in an SQL table by explicitly creating an SQL UPDATE command or by connecting the table to a client dataset using a provider. Features that require buffering multiple records, such as filters or lookup fields, are not available.

Before you can use a TSQLTable component, it must be connected to the database server. Therefore, the first step to take when working with TSQLTable is to set the SQLConnection property. Once the dataset is connected to a server, use the TableName property to specify the table this component represents.

https://docwiki.embarcadero.com/Libraries/Aleandria/en/Data.SqlExpr.TSQLTable

 

Edited December 9, 2022 by programmerdelphi2k

[limelect 48]

@programmerdelphi2k I do not believe your solution did it.But it did

also, it is on my test program I now will move to my main program

 MyMemStream:=  ProjectsFDTable.CreateBlobStream(ProjectsFDTable.FieldByName('Description'),bmRead);
 AdvRichEditor.BeginUpdate;
AdvRichEditor.LoadFromStream(MyMemStream);
AdvRichEditor.EndUpdate;

 

[programmerdelphi2k 237]

take care about "memory" usage with working with "TStream" like this! 

[programmerdelphi2k 237]

my advice:

• select xxx WHERE fieldx=xxx
  • not use "FILTERS" in "unidirectional mode" // read above
• after, this read a blob, write and save

[limelect 48]

@programmerdelphi2k It worked on my test bed but on the real application

as you can see.

Same Database size and content

[programmerdelphi2k 237]

without see your code used is hard for me!

I would like see your "unit/form" with your components definitions etc... if possible, your DB file (without any records, just struture)

Edited December 9, 2022 by programmerdelphi2k