In terms of the way that they are used, there is not a lot of difference, but to migrate from API1 to API2 is nowhere near what you describe. It took me 5 hours to migrate only this aspect of the project to API2.
RecordSet → RowSet
- Making this change resulted in almost 700 errors within the project. With each updated method and property, that number varied unpredictably.
Here are the other changes that had to be made. (Keep in mind that these are almost never simple serach and replace operations, and in many cases custom objects with methods based on API1 have to be rewritten to accomodate the changes.)
SQLSelect → SelectSQL
.MoveFirst → .MoveToFirstRow
.EOF → .AfterLastRow
.BOF → .BeforeFirstRow
.Field → .Column
.MoveNext → .MoveToNextRow
.Edit → .EditRow
.Update → .SaveRow
.RecordCount → .RowCount
IdxField(1) → ColumnAt(0) // Careful! This is a ones-based to zero-based conversion!
.DeleteRecord → .RemoveRow
SQLiteDatabase:
.TableSchema → .Tables
.InsertRecord → .AddRow
DatabaseRecord → DatabaseRow
- This was a huge mess, because I had many methods and properties using the word “Record” which all now should use “Row”. To my mind this is a poor design choice because “Row” is used for controls whereas “Record” is obviously a database term, so all these methods lost their uniqueness and the potential for error by confusion/collision is far greater than before. That said, after everything has been changed over and starts working again, it doesn’t seem quite so terrible.
.BooleanColumn(“name”) → Column(“name”).BooleanValue
.IntegerColumn(“name”) → Column(“name”).IntegerValue
This change seems an obvious improvement.
DatabaseField → DatabaseColumn
- At this point, no errors and the project built but broke immediately with an unhandled DatabaseException, as expected.
Exception handling:
- The new paradigm requires rewriting all the error handling. Exceptions must be handled whereas before they could simply be ignored. In cases where the error doesn’t matter, this is slightly annoying. All operations are now in try … catch blocks and for me that kind of code is harder to organise.
After all the error handling was updated, an hour was spent figuring out why, after making the change over to API2, it still did not work.
The most noticeable benefit we get from moving to API2 here is that exceptions are more accurate or at least more descriptive. Whereas API1 gave us Error 0: “not an error”, API2 gave us Error 0: “Unable to allocate memory requested for the operation”, which makes much more sense.
I had already looked for any PRAGMA settings in SQL which might cause this, and I asked ChatGPT for help. Nothing worked. On the forum, this same error in a similar context was already reported in 2020 here. And if you check the error tracker I also had filed a bug report related to this same problem in 2019. The problem is this:
If the result of your query is 1GB or more in size, the Xojo SQLite plugin cannot return those results.
I don’t think Xojo is going to do anything about this, because for most users there are practical ways around the problem, like loading in chunks. By design, we can’t do that. So I tried the MBS SQL plugin, adapting it slightly to make it a “drop in” replacement for the Xojo object, and it works just fine. (So many thanks to Christian Schmitz for his work.)