I thought you could.
Okay. I know you’re just trying to help, and I do very much appreciate your input. Please accept my apologies. My comments at the top of the report may not have been clear enough. I have edited them to try to make things more clear.
I don’t remember reading them. So maybe not your fault. No need for such stress, I’m pinpointing usual places of attention. You just need to say “no, because this or that”, and I move to other level inspecting. Apologies accepted, as always. I’m looking at your sample as you asked.
Ok, everything works except very large selects.
I could get 10 million rows but as soon as I increased the limit the engine said it couldn’t handle it.
Right. We could get up to 12 million rows of this table structure. More than that fails. Exactly why that is remains unclear, but Xojo should know where to look.
They can increase the limit for a sqlite rowset size in memory a bit, not sure, but… I don’t know your needs, but if possible, you could read and process the rowsets in blocks, like 100 blocks of 1M = 100 million rows.
The good news is that If ask just the ID field, it reads all. But some limit still exists and you will reach it anytime. So, while it fits, in theory, you could get all the IDs and in a second step you could get blocks of one million rows matching one million of those IDs and continue.
At this point we are discussing workarounds.
The “All IDs” pass:
FYI I concur with these results, using a modified version of the code in your Issue 77828 (I used INSERT sql rather than DatabaseRow). So I’ll go back to my own test and make the database bigger.
Edit: the error is caught by the try/catch.
I “solved” the problem by switching to the MSB SQL plugin, which does not have this problem. It isn’t a ready-made drop-in replacement, but it doesn’t take long to modify it to become one.
When the requirement is to have all the data in the table in a returned RecordSet or RowSet, there are no workarounds in Xojo. Reading parts or chunks won’t work for that. The only way to get a Set of Rows or Records is to select them in SQL. You can then modify or delete items in the returned set, but not add them. If there were methods for combining returned sets, then there could be a workaround. There aren’t any such methods, and as far as I can tell there can’t be any such methods, because SQL has no such functionality for combining separate queries.
Well, there are lots of SQL idioms for combining datasets in several ways using JOINS and UNIONS.
This is hard with Xojo. Harder with SQLite and huge rowsets. Some systems let us keep a huge rowset on the server, and we just navigate that rowset at client side as if it was a huge local rowset but in reality behind the scenes there are blocks of rows being dynamically exchanged all the time trading a decrease on speed by getting a small memory use. Xojo only allows client side cursors.
Few things may be occurring:
- MBS has a bigger allocation that means that the problem exists, but it has a higher limit but it is currently good enough for your requirements. or…
- MBS takes advantage of some memory paging scheme, making the problem virtually non-existent.
- MBS uses a more complex scheme to solve it.
I would love Xojo having option 2 at least. If MBS does it, Xojo should too.
In a single query, yes, but that wasn’t what I was talking about. There is no way to combine results from separate queries. That would be the only workaround.
Since we get a Xojo object called RowSet or RecordSet, it’s normal to expect methods which could treat these as arrays and add records or combine arrays if needed, but that doesn’t exist for good reason, because they aren’t arrays, they are pointers to SQL objects, and SQL can’t combine separate results.
It isn’t hard - there’s one line of code to get results, and the results should be there. There’s no reason it shouldn’t work. It works just fine until this memory limit is reached. SQLite itself has no such limits. The root of the problem in Xojo has to be the way in which they compile the SQLite library. There are switches for memory allotment and management and so on. They should change some things in their build steps.
I agree that increasing memory limit it will make room to allow your current use case for a machine with enough memory left. I hope Xojo can find a way to get the best trade off based on a variable amount of free memory at any machine. Let’s hope they can get the best and safer results they can.
If anyone could please log into the bug tracker and comment there to get this issue resolved, we would really appreciate it. It turns out that the MBS plugin requires a license upgrade, which boosts the cost of the whole operation several hundred per year, and we would really rather not have to do that just to get around a bug in the Xojo SQL plugin! Thank you in advance for your help.
Commented and “thumbed it up” to call attention of possibly needing a 64bit upgrade of the entire engine, that can have some 32bit cut somewhere.
1 Like
BIG THANKS to Xojo for fixing this! The issue has been marked by William Yu as fixed and closed, target release 2025r1.
1 Like