I had a strange problem with MySQL Community Edition. I had created a new query in a new Web dialog. The place it was used the very first time after launching the program in Debug a SQLselect worked fine and returned the expected results. The very next statement anywhere in the program that did a SQLselect or SQLexecute returned an error of “Commands Out Of Sync; you can’t run this command now” error.
After a little digging it turned out I had an error in the SQL code with a semi-colon NOT at the end of SQL statement then another part of a WHERE clause beginning with “and” followed by a second ordered by clause and also ending with a semi-colon.
It is my normal process to build a query in a tool then copy/paste it back in my Xojo code for use with some “tweaking”. Somehow I had the extra “junk” at the end of the statement. I found it by adding a MsgBox to display the SQL then pasting it back into my query tool to test. The query tool said I had a syntax error. From there it was pretty easy to see the problem.
Xojo had apparently executed the part up to the first semi-colon and it worked ok then the extra “stuff” past the first semi-colon also executed but left a partially complete SQL statement pending. The next SQL statement then would be sort of “combined” with the first left over part and get the strange error.
I only mention this here in case somebody else has this same error.
As Greg mentioned, and something that a lot of people do not seem to realize… When you write a program in Xojo (or any other language) that includes methods that access a database (mySQL, SQLite or any other database engine)… You are in fact dealing with TWO totally independent languages. In this case “Xojo” and “mySQL”. Xojo can build the command strings that you send TO mySQL, but they must meet the requirements of mySQL not of Xojo… Any errors in execution of an SQL query or function is handled by mySQL and reported back to Xojo, but the message Xojo displays is directly dependant on the mySQL engine.
You will notice that a lot of functions “Uppercase”, “Lowercase” etc… might have different syntax in mySQL than they do in Xojo…
So Xojo never executes any SQL… it passes it to the appropriate SQL engine.
Yep … I figured Xojo “mostly” just passed it through. The fact that my query client got back from MySQL that the statement was not valid must indicate that Xojo and the MySQL interface somehow had MySQL first execute only the part up to the first semi-colon and returned recordset results. Then MySQL must have “buffered” the balance beyond the semi-colon and when the next SQL statement was sent to MySQL from Xojo that part combined with the previous piece was invalid and resulted in the odd error message.
I don’t think this is a Xojo problem more than sometimes the error messages just don’t tell the real problem.