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
from ghostTable
group by widgetNo
having count(*) = 1;
It all works nice and neat from MySQL Workbench. But how do I do it from Xojo?
Lets assume:
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)
Question #1
Can I call sqlExecute (FirstSQL)
Followed BY sqlSelect(SecondSQL)
Question #2
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?
Question #3
What else am I missing?