SQL out of memory error after 5 insert

[gnithyalakshmi]

Hi,

I am using your library in ESP8266 12 E board. As you suggested, I have changed the stacksize to 6144, Page size of db is 512.
It worked fine initially and after 10 insert statements, I am getting error SQL out of memory.

Do you have any idea to fix this ?

On checking free heap, it is 27672 before executing insert statement. AFter it get SQL out of memory error, free heap is 31304.

Any help would be appreciated.

[siara-cc]

Hi, please see this reply, but the example is for esp32 library:
siara-cc/esp32_arduino_sqlite3_lib#7 (comment)
You may be able to get it to work using prepared statement.
However, if your requirement is for saving sensor data, you may be better off using https://github.com/siara-cc/sqlite_micro_logger_arduino
and use this library only for creating indices and reading.
sqlite_micro_logger_arduino library will not lead to memory issues. However there are several restrictions as mentioned in its README.md

[bcookew]

Hi Arun,
I am having a similar problem on an ESP32 board. I have shied away from using your micrologger because it seems geared towards high rate logging of analog inputs.

I am trying to use your library to log some data that is being gathered by the ESP32 PCNT and then processed(min,max,avg type thing) before logging.
I am very new to C++ (I come from a Python & JS background) and I am pretty fuzzy on pointers and memory management.

From what you said above on Jun26, is this library not appropriate for inserting data? Can you explain the mechanics behind why it is ony after a period of working that it goes out of memory? I (probably foolishly) would have thought that the memory requirement would be the same for each statement and then would be released once it was written to the db file.

Thank you for any time you can spare commenting.

FYI:
Every minute a log_data method of a custom class gets triggered and it executes calls to your api in the following order:

sqlite3_open( )
sqlite3_exec( )
sqlite3_close( )

Of course, params are passed in and the insert query is generated but it is split across two different files so not terribly easy to share.

Cheers,
Ben

[siara-cc]

Hi, Although SQLite is light for desktops, its not so light for ESP32/ESP8266. The SQLite source heavily uses dynamic memory allocation and I could not say much why you are facing issues unless some sample code can be shared.

However in general, using prepared statement as in this example should help: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/blob/0850945aa8f20504d4dc3ad3e9a7c36939e51fbc/examples/sqlite3_bulk_data_insert/sqlite3_bulk_data_insert.ino#L164

There is also a lot about when memory is released and fragmentation over time:
see this: siara-cc/esp32_arduino_sqlite3_lib#7 (comment)
and this: siara-cc/esp32_arduino_sqlite3_lib#7 (comment)

[hydrafi]

Hi, I've read all the various posts on the "out of memory" error but I detect inconsistent behavior.... I write a small record on the SD every 30 sec and sometimes it goes into error, sometimes not, always with a good heap, you see.

INSERT INTO ele_log(V,A,P,cosFi)VALUES(1,1,1,0);
Heap to: 266228 free: 83656 min: 33232 max: 61428
SQL error: out of memory
Time taken:95463

INSERT INTO ele_log(V,A,P,cosFi)VALUES(1,1,1,0);
Heap to: 266196 free: 83316 min: 19460 max: 61428
Operation done successfully
Time taken:110576

In any case even when there is an error, the record is written... any ideas?
Thanks
Massimo