would think that if you check db.error and db.errormessage after the query there’d be some indication
Nope, as there’s no exception and no error. The error is a Nil rowset error after the select runs, which I don’t think is even possible. If the query succeeds you are at least getting back a rowset with zero rows. If it fails you have a catchable exception.
OK so there were 2 basic things wrong. First, I needed a prerelease of MBS plugins that fixed a bug. Secondly, apparently MBS gets confused if you keep short accounts with connections. My practice (due to 20 years of having it drummed into me in the .NET environment) is open a connection to the DB, grab data, close the connection. This works in ADO.NET because there’s a connection pool behind the scenes, making this practice the much more efficient approach. There is no such thing in Xojo, and according to Christian, one must have a global connection whose lifetime is that of the app and is never closed – or maybe some entirely separate connections, also kept open, for different purposes if you like. In fact that proves to be literally true; I’ve commented out the DB close method because even orderly closing / reopening seems to be profoundly discombobulating to … Xojo, MBS, or something.
At any rate the final lingering problem is that rowsets are tied (clearly in ways that can vary … my existing code was working fine with Postgres on MacOS) to the DB connection as well. I have one place in the app where I was holding a rowset property that I wanted to iterate repeatedly as needed. Not the most efficient structure but it was good enough for the purpose. I’m now having to recast that as an array of arrays (or, because I can’t figure out how to make the IDE understand that metaphor and don’t feel like figuring it out, an array of a custom class with 2 strings – and no, a dictionary isn’t the appropriate metaphor in this case). Because even with a static DB connection that’s never closed, old rowsets seem to lose touch with the query that hydrated them.
I’m sure all this is old news to old Xojo hands and it would be to me too by now if different DBs / drivers didn’t have significantly different implementations or didn’t make different assumptions.
Glad you’re making progress
As for rowsets and connections yes they are often tied together
Otherwise with a dynamically paged RowSet (ie/ server side cursor) you might have to download millions of rows before you could use the rowset
Or you’d lose the server side reference when you closed the connection
It’s just “the model”
Connection pools would be nice though
But with rousts the way they are its not that useful like it is in ADO.Net