I have some SQL that I have been fighting with for a while. There are millions of records involved and simple indexing of a complex Query didn’t give me the results I need. So I was forced to use a Temporary Table in Mysql. Then I send a second query on the temporary table so all the recursive SQL statements only act on a few dozen records rather than a few million. (Its ugly but it gets me there)
Here is what the two statements look like.
CREATE temporary TABLE ghostTable AS (SELECT id, widgetNo FROM customers WHERE type = 'gold' AND Field1 NOT LIKE '%foo%' AND Field2 LIKE '%bar%' AND cost > 10000 AND profit < 999999 AND theOtherThing > 1000 AND state IN( 'TX', 'WV', 'NV', 'IL' ) AND county <> 'cook' AND whatever IS NOT NULL AND color <> '');
Then I call
SELECT id, widgetNo
group by widgetNo
having count(*) = 1;
It all works nice and neat from MySQL Workbench. But how do I do it from Xojo?
dim FirstSQL as string = //All the stuff from the top statement (CREATE temporary TABLE ghostTable AS ........blah blah blah)
dim SecondSQL as string = //The query on the ghost table (SELECT id, widgetNo ...blah blah)
Can I call sqlExecute (FirstSQL)
Followed BY sqlSelect(SecondSQL)
I assume I cant use the same table all the time “ghostTable” could be called by 30 different users. So I will have to devise something that does:
CREATE temporary TABLE Name_W_SessionStamp AS .....
When will it die? And how do I kill it off if necessary form Xojo?
What else am I missing?