Although I found some conversations in this forum about this subject I would like to start a new one to just point out the issue I am facing now.
I have a PostgreSQL database having straight forward records containing 100 Mb+ of binary data each.
Within a thread I query a record from the remote database, which takes significant time.
On the GUI I started a timer and update some controls, right after launching the tread. The timer is used to keep an eye on the state of the thread: still running or done.
Unfortunately this timer will not fire a long as the select query is being executed within the thread. Once the record is received, it fires for the first time.
It seems that it does not make sense to query the database within a corporative thread, since the plugin is not giving any timeslice to the application as long as it is performing a database action.
Do I really need to go via a helper-app to keep my GUI responsive while performing long database-actions ?
Xojo threads give the illusion of background operation but in the real world it does not work the way you would expect. One way to handle this outside of a helper app is to break the binary data up into smaller chunks so you can make smaller faster requests and do some thread yielding while iterating through the chunk count.
A helper app just makes it easier all around as you can just build it as a synchronous call and let the Shell object catch its output when its ready. The downside for using a helper app for SQL queries is you now have to serialize your RecordSet somehow between the helper app and the main app.
This is an area where Xojo suffers compared to newer programming languages.
seems that if you use mbs sql plugins, you get a real responsive UI when making long select queries, (I did not check it myself)
I faced your exact same problem and finally did make my huge request with a method on the server,
so I did not have that UI problem on the client side, but still you cannot make a thread to call sql queries in xojo.
would be nice if this was fixed.
I think you should file a Feedback case for this. I believe the SELECT does yield for long queries, but it’s possible your large binary data is affecting that in some way.
With SQLDatabaseMBS you need to use the SQLSelect[b]MT/b and SQLExecute[b]MT/b variations with the thread. I do exactly what you are trying run the thread then launch the Timer to get feedback from the thread’s variables.
In the case of Sqlite, apparently the cause is that Xojo don’t manage (or not completely) a Sqlite handler (“sqlite3_busy_handler”). Without going into details, this handler allows Xojo, when the database is “busy”, not to block the entire application, to leave time to other threads. (Of course, in your case and in the case of a client/server database, the database is not busy, but maybe there is a postgreSQL event of type “wait, operation running” as long as the request is not completed, to leave time for the threads/app). (Christian manages this Sqlite handler, and this (Feedback 42554) sqlite problem is thus solved if we use his excellent plugin).
