I just tried this:
Code:
Clear
Open Database c:\Users\Public\vfp9\samples\northwind\northwind.Dbc
Use northwind!orders
Set Filter To Recno()<6
Go 5
Set Order To OrderDate
Browse Nowait
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
Suspend && screenshot 1
* Resume && typed in the command window
nInitial = Select(0)
cTempAlias = '_sql'+Sys(2015)
Select 0
Use northwind!orders Again Alias (cTempAlias)
Select * From (cTempAlias) Where Isnull(_vfp.DoCmd('set')) ;
Into Cursor Result NOFILTER && screenshot2 during query
Use In Select (cTempAlias)
Select (nInitial)
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
Before the query (after Orders is opened and recno, filter order are set):
[pre] [/pre]
RESUME in the command window and during the query I could make this screenshot:
You can see the tempalias _sql_66... I open, on top of that (literally) the SQL engine uses 3 workareas during the query: C,D, and Result, the Orders workarea is not touched.
The active workarea C has the same metrics (Records: 830) as Orders, so it is the orders.dbf, no idea what D is for, but likely something for query optimization, and Result is, well, the Result that's generated because of Into Cursor Result.
[pre] [/pre]
after the query ran, the output and datasession window is showing what has been tidied up by the SQL engine:
You see the SQL engine closes C and D, and, of course, keeps Result open
In this case there actually was no need for the tempalias, The already open Orders.dbf wasn't touched by the SQL engine anyway.
But let's look at the case I query another table, like foxcode when I don't have it open before the query, without using the tempalias, something like this:
Code:
Clear
Open Database c:\Users\Public\vfp9\samples\northwind\northwind.Dbc
Use northwind!orders
Set Filter To Recno()<6
Go 5
Set Order To OrderDate
Browse Nowait
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
nInitial = Select(0)
Select * From foxcode Where Isnull(_vfp.DoCmd('set')) ;
Into Cursor Result NOFILTER && screenshot2 during query
Use In Select (cTempAlias)
Select (nInitial)
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
After that - I spare another screenshot - The foxcode table is open and not closed after Result was generated.
But introducing the tempalias, foxcode is not left open:
Code:
nInitial = Select(0)
cTempAlias = '_sql'+Sys(2015)
Select 0
Use foxcode Again Alias (cTempAlias)
Select * From foxcode Where Isnull(_vfp.DoCmd('set')) ;
Into Cursor Result NOFILTER && screenshot2 during query
Use In Select (cTempAlias)
Select (nInitial)
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
[pre] [/pre]
With this code only two workareas remain used: Orders and Result.
While this is quite complicated it tricks VFPs SQL engine to only use the DBF it queries in one of its temporary single letter named workareas it closes at the end of the query. So indeed during the query foxcode is open twice, but the workarea C (or whatever) is closed by the SQL engine and the workarea _sql_... is closed by the Use In Select(cTempAlias), so as net effect Result is the only new workarea.
Code that would open foxcode later without AGAIN and without checking whether USED('foxcode') is true, will therefore not fail. As Mike Lewis said, it's actually bad code that wouldn't check this and simply open a table it assumes isn't open yet. But if you don't like to mend the legacy code to manage workareas better, then this helps.
It's still just covering the simple case of a single table query. You can extend this to make use of several tempalias names for all dbfs you want to keep as they are currently. It's complicated, sometimes unnecessary, especially when the involved tables are already opened, but this overrides VFPs SQL habit to not close DBFs it queries, whether they already were open, or the SL query opened them.
I bet it's still confusing, but at least it's a reference you may understand, eventually, after you played with this yourself.
By the way, I have this from a session of Christof Wollenhaupt (Foxpert), he also used _vfp.DoCmd('Suspend'), but for some reason that doesn't work, as if it's ignored. The command SET simply shows the datasession window and does so for every record queried, which makes the query slow but you can see the temporary workareas SQL uses.
Chriss
