Charlie, thank you. This is helpful, and I have some responses in-line.
AUSED() - can return an array of all aliases in use (and you can specify a
datasession if you want)
DBC() - returns the path and name of the actual "file" that us associated
with an alias
So, if you build an array of aliases using AUSED() you can then loop
through the array using DBC() and log which file is actually used. This
works for cursors created by SQL queries too.
I think before I scrap the code I will add this to the error logging and
see what I get. That should help me understand what's going on, for sure.
Side note: A standard programming practice I've taken is to always add the
NOFILTER clause to my SQL. This ensures the resulting cursor is not just a
"filter alias" (a long time ago I think you could get a bit of a
performance boost if the resulting cursor was just a subfilter of the
source table, and indexed on the WHERE clause - but nowadays with loads of
PC memory, fast drives, etc, I don't think there is a reason to want a
"filter alias").
My previous understanding was that if there is a WHERE clause or an ORDER
BY clause in the query, then VFP always creates the temp file on disk.
Therefore, to avoid this precise problem, my query class always adds
"NOFILTER" to queries that don't have WHERE or ORDER BY clauses.
However, your example:
>*-- assume MYTABLE is indexed on a field "id"
>SELECT * FROM mytable WHERE id=25 into cursor tempitem
>USE IN (SELECT("MYTABLE"))
>USE MYTABLE in 0 && this will error out because "tempitem" is actually
just >an alias to MYTABLE with a 'SET FILTER TO ID==25'
indicates that my assumption is wrong. I get the same results as you, even
if I add an ORDER BY clause. I don't get the error if I add a JOIN to the
query though.
However, if this really was the ultimate source of my problem, I would be
deluged with error emails every time somebody used the system. That's not
happening.
And adding NOFILTER to a complex query that doesn't happen to have a JOIN
can still slow it down considerably. I don't have a "hot" development
machine; I write and test code on the same machines everybody else here
has. Currently my Win 7 Ultimate 32-bit machine is a few years old; it has
an AMD Athlon X2 (I think that's duo-core) processor running at 2.9 GHz and
4 GB of RAM. Other machines here are older and slower. So I'm reluctant to
adopt this practice as a default, and the particular query involved in this
scenario does contain a JOIN.
I'll repeat that all of my code to open tables resides in a single method
in my framework. That code never assigns a specific alias to the table, and
it never issues "USE AGAIN". Also, I never use private datasessions;
everything occurs in the default datasession.
Before my query class runs a query, it makes a list of all of the tables in
the field list, and any JOIN expressions, of the query that are not already
open. Then I can tell it to close any tables that were opened expressly by
the query after the query runs.
Some time ago I read somewhere (I can't remember) that VFP may open some
tables in a work area called "QUERY" when running a SQL SELECT query
without an INTO clause. If there are more than one of these, a digit is
appended to "QUERY". (In testing I see that it actually puts the results
cursor in that work area, not tables opened by the query.) So although I
never run queries without an INTO clause in production code, and these
"hidden" work areas should contain only results cursors if there is no INTO
clause, in the spirt of "just in case" my code for closing queried tables
also contains a little DO WHILE loop that looks for work areas whose names
begin with "QUERY" and closes them also.
In the situation being described, the command to close the query-opened
tables is issued after the user responds to a display of the query results.
The display appears in a modal picklist window if there are any matches;
otherwise s/he gets a MESSAGEBOX() saying "no matches". I suppose if some
bizarre event prevents the display of either of these, or if the user can
somehow defeat the modality of the picklist window to shut down the
application, then that command would not get executed and there would be
open tables when the shutdown code runs. This should not be possible but
under certain circumstances the modality of VFP windows is not 100% reliable.
However, again, when my shutdown code runs, it explicitly checks for USED()
on every table and will SELECT it if it's open instead of trying to USE it.
However, I have also seen this before, rarely, in other situations:
IF NOT USED("mytable")
USE mytable IN 0 && Error 3; File in use.
ENDIF
I kid you not. It's very strange.
I actually think there are processes in VFP that impose locks on tables
that aren't always detected correctly. In the above case, it would seem
that some OTHER user has the table open and locked for some purpose. The
error reported should be 108, but sometime's it's 3.
>FYI in 100% of my tracing problems of this nature, it has always been
software >code that caused the condition. In other words, a table got
opened exclusive, or >I tried to open something exclusive that was shared
(and thought was closed), >etc. An FLOCK() or RLOCK() will not prevent
another open in Shared mode.
I'm going to add the status of SET("EXCLUSIVE") and SET("MULTILOCKS") to my
error logging.
>Others may have already pointed this out but in your sample above,
"USE >MYTABLE IN 0" will throw an error if the table was opened under
another alias. >With the functions above, you could easily determine if the
table is actually >open or not.
See above; I never assign other aliases or issue USE AGAIN.
>Also as others have mentioned, other applications can lock files (like
AV). >Plus, if you allow ODBC access to the tables I think the it defaults
to >EXCLUSIVE when trying to pull data (not sure though, and it can be
changed).
We use the server-based (business) version of avast!. It has global
exclusions for .dbf, .cdx, and .fpt files set at the server. Users cannot
change any settings on the avast! client; it requires a password that they
don't have.
There are no ODBC implementations.
Again, this was very helpful.
I'd appreciate any further thoughts you have on my additional information.
Ken
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
