Is it possible to abort a MySQL query from within an Xojo app?
I have a Xojo desktop app that queries a MySQL database. Occasionally I enter queries that are taking too much run time, typically meaning I wrote a poor query. Is there a way to kill the query from within my Xojo app?
Well, if you’d use MBS Xojo SQL Plugin for your MySQL queries, you could call Cancel method from one thread, while another thread executes a statement with SQLCommandMBS class.
Or in SQLConnectionMBS or SQLDatabaseMBS, there is a CancelAllCommands method to cancel them all.
For the MySQLCommunityServer class, I don’T think there is a cancel method.
may be you could send your query to a worker class, then you would have a way to cancel it.
I agree with Christian. I used to have PostgreSQL search hundreds of millions of log entries by placing them in a thread and calling mySQLDatabaseMBS.SelectSQLMT (so the UI would update) with a progress bar. If I wanted to perform a new query before the old one had completed, I would Kill the old thread.
I guess what would occur if you open a DB connection to MYSQL from a “query thread” and expose that connection to another thread. It would be interesting to know what would occur calling querythread.mysqldb.close() from another place while your query thread was busy taking forever to return from mysqldb.SelectSQL(). Would it be reentrant and break an ongoing connection and forcing the SelectSQL() fail in an exception and freeing all sides of the task? Or internal locks will lock you too waiting forever? Or an exception will rise impeding you? If you couldn’t break an ongoing parallel connection, maybe Xojo could implement some way of enabling it like mysqldb.ForcedClose() and a parallel ongoing task would break in an exception and the server side transactions will rollback as usual.