SQLite Query Error - Unable to allocate memory requested for the operation

Just did a test with SQLite3 cli and it works super fast and ok , no issues there so I guess something fishy in the SQLite3 plugin.

Well the OP said he’s using 2019r3.2, which generates for 64-bit only, at least for macOS, IIRC. The 32-bit dylib won’t work AT ALL in that environment, surely. I have no trouble with SQLite in the 64-bit realm so I don’t see that it can be that.

Not a 2019r3.2 issue is it?

Hello,
I recently also experienced this problem. I could solve this for my situation by using LIMIT and OFFSET, so I retrieved the query data in a loop in small chunks of 100000 records each.
https://www.sqlitetutorial.net/sqlite-limit/

Is RowSet.RowCount Broken as well ? as apparently I put a query limit of 2 let’s say and once I run the query and get the result, my result is always RowSet.RowCount = 0 even if I know that I have 2 records there .

[quote=497305:@Christian Mézes]Hello,
I recently also experienced this problem. I could solve this for my situation by using LIMIT and OFFSET, so I retrieved the query data in a loop in small chunks of 100000 records each.
https://www.sqlitetutorial.net/sqlite-limit/[/quote]
Well I will eventually go there but on my side is little bit tricky as the last column [quote] Kkrr16[/quote] is dynamic and I have to keep all in a loop and limit to 1 let’s say to make it fast but I need to find a way to keep the loop until all is processed.

So in my case I need to filter all the fields where [quote]Kkrr16=0[/quote] then from there get the first record , get the value of the first record save it, do some additional filtering based on 6 columns , then based on what I find I need to replace the Kkrr16 with the initial value I found in the first record in another column, and repeat the process until Kkrr16 <> 0 so quite tricky for me to always check the value I guess, I could as well take the records 1 by one and do the processing but it is as well based on some filters.

[quote=497304:@Tim Streater]Well the OP said he’s using 2019r3.2, which generates for 64-bit only, at least for macOS, IIRC. The 32-bit dylib won’t work AT ALL in that environment, surely. I have no trouble with SQLite in the 64-bit realm so I don’t see that it can be that.

Not a 2019r3.2 issue is it?[/quote]
Being compiled AS 64 bit doesnt implicitly mean everything moves to being 64 bit :slight_smile:
Its possible something in the plugin is defined as an int32_t and then you have 32 bit int issues

Not all db plugins support row counts

Well SQLite used to before as I use it quite a lot. so I assume that it might be broken , hope not.

With the move to API 2 its possible something got fubarred

You dont HAVE to use the API 2 style - the old one still exists just that it wont autocomplete without some help :slight_smile:

Apparently MBS Plugin has same crash , so I assume that it is something inherited from the original plugin of XOJO as sqlite cli works super fast and as expected.

I guess in the end if this still does not have a workaround except the one @Christian Mzes mentioned then I’ll have to do this in another programming language that does not have this issue.

Funnily on SQLite it says that [quote]An SQLite database is limited in size to 140 terabytes[/quote] but I see it causes problems on 2 Gb and most of it from the client part and not the DB part.

Why would you assume that a crash is “inherited”? Make an example and ask Christian if he can fix the problem.

To the problem with SQLite where long lasting queries block/freeze the user interface:
I made positive exepriences using SQLiteDatabase.ThreadYieldInterval
http://documentation.xojo.com/index.php/SQLiteDatabase.ThreadYieldInterval

Its possible its related to whatever setting is used for SQLITE_MAX_MEMORY when compiling
https://sqlite.org/compile.html