FW: PyQt5 QMYSQL3 driver and temporary MySQL tables ."Commands out of sync" Possible bug

[Enrique Boelcke]

-----Original Message-----
From: Phil Thompson [mailto:phil at riverbankcomputing.com] 
Sent: Monday, April 27, 2020 5:24 PM
To: Enrique Boelcke <eboelcke at gmail.com>
Subject: Re: PyQt5 QMYSQL3 driver and temporary MySql tables ."Commands out
of sync" Possible bug

The email address for posting to the list is pyqt at riverbankcomputing.com

Phil

On 27/04/2020 19:03, Enrique Boelcke wrote:
> I was having trouble running  on Windows 8 , Python 3.7.1, PTQT5 
> 5.11.3.
> accessing a MySql (version 8.0.15) database. When calling a stored 
> procedure on temporary tables using the recommended position bound 
> parameters only got the above mentioned error message. The process 
> consisted in inserting records from a query (MS Access) into a MySql 
> temporary table. The query was defined outside a while loop where the 
> parameters were updated and the query executed, Changing the table to 
> not temporary gave the same results. Placing the query definition 
> inside the while loop and closing (or clearing the query) at the end 
> of the loop just deleted the temporary tables. Finally y got it 
> working  by placing the query definition at the beginning of the loop 
> and clearing -
> QSqlQuery.clear() at the end of the loop as far as the MySql was not 
> temporary. While debugging with PyCharm I could notice that the 
> "commands out of sync" message was emitted after a first successful 
> execute (First time around).
> 
> I also had the similar problem running a stored procedure with no 
> parameters after  executing a stored procedure with them.
> The only  practical solution I could find was to run the query using a 
> format string of the form query.exec("CALL stored_procedure({}, 
> {},....., {}).format(var(1), var(2),....,var(n)).
> Looking at  MySQL manuals you can see that the error is address and 
> some solutions are provided but I can not figure out how could they be 
> applied from PyQt5 since the solutions refer to msql methods without 
> equivalent QSql's. You can see further details of these issues at 
> https://stackoverflow.com/questions/61398897/commands-out-of-sync-mysq
> l-pyqt5-using-temporary-tables and I would be happy to provide you 
> further information if necessary.
> I will appreciate any information related to these issues Yours 
> sincerely,
> 
> Erick Boelcke