I working in a VCL project. I'm populating a TUniQuery with a Insert that uses a sub select. Looking at the MySQL log file, I can see the command is hitting the database. There are no errors or exceptions being reported and everything seems to be executing properly. When I query the database though, the data isn't there. If I copy and paste the command from the log file and run it in dbForge then it executes the command properly and populates the table. Below is the code without the fields specified:
UniQuery->Close();
UniQuery->SQL->Clear();
UniQuery->SQL->Add("INSERT INTO tmplegacypricingdistinct (...) ");
UniQuery->SQL->Add("SELECT DISTINCT ... FROM tmplegacypricing");
UniQuery->Execute();
I plan to make the tables used into temp tables, however they are currently real tables.
Some thoughts I had were that it could be the connection, but since its being logged in the mysql log file I don't see that being the issue. I have a work around which would be to pull the information down then insert one record at a time, but I'd hate to pull the data over the wire and have to do that. Thoughts or ideas on what I can do to make it all happen on the server's end or what my issue with the above approach could be?
Make sure that you don't have any TEMPORARY tables and REAL tables with the same name's. MySQL lets you do that.