My error handler has recently reported two instances of the following scenario:

User causes a SQL SELECT query to run.

Suddenly, the application is being shut down via my custom main window's

QueryUnload().

Normal shutdown requires access to tables that may be implicated in the SQL

SELECT query.

VFP Error 3 ("File is in use") occurs.

I use a custom main window instead of the VFP _SCREEN, which is suppressed.

My code calls the QueryUnload() of child windows at times, but it NEVER

calls the MainWindow.QueryUnload(). So the only way that method can be

called is via the user pressing the "x" (close) button or the upper-left

icon for the window popup menu, or if the window still exists when CLEAR

WINDOWS, RELEASE WINDOWS, or QUIT is issued. Of those three, only CLEAR

WINDOWS appears in my code, after everything related to databases and

tables is done. In fact, it only occurs after the shutdown code issues

CLOSE DATABASES ALL.

The query expressions are assembled by different methods of different

objects under different circumstances, but the code that executes them

resides in a single method of my query object. That object is instanciated

separately in each of these cases.

In one of these cases, the user starts the query in a modal child window,

and therefore should not be able to click the "x" close button or the

upper-left-corner popup-menu button to trigger QueryUnload(). So that's

another headache.

But in the other case the window in which the user starts the query is not

modal.

There are other anomalies. For example, in one of these instances there is

NO error-handler in the stack trace. That is, no line for any object's

Error() method and no line for my error-handler program. However, my

messaging object's code for logging errors and emailing the log file gets

triggered, and announces VFP Error 3, so it must have occurred. But the

trace jumps from the method where the query is actually executed directly

to the messaging object without any intervening error-handling code.

Question 1: Does a SQL SELECT place a lock on a VFP .dbf table such that

the same user can't issue a USE on the table while the query is running?

Question 2: Insofar as possible, should I program defensively by disabling

controls related to shutting down while queries are running?

Thanks again everybody.

Ken Dibble

www.stic-cil.org

_______________________________________________

Post Messages to: ProFox@leafe.com

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/5.2.1.1.1.20150713110142.020911b8@POP-Server.stny.rr.com

** 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.

As far as I know, SQL SELECT uses its own data session, so it shouldn't

interfere with other users.

Laurie

On 13 July 2015 at 16:27, Ken Dibble <krdibble@stny.rr.com> wrote:

> My error handler has recently reported two instances of the following

> scenario:

>

> User causes a SQL SELECT query to run.

> Suddenly, the application is being shut down via my custom main window's

> QueryUnload().

> Normal shutdown requires access to tables that may be implicated in the

> SQL SELECT query.

> VFP Error 3 ("File is in use") occurs.

>

> I use a custom main window instead of the VFP _SCREEN, which is

> suppressed. My code calls the QueryUnload() of child windows at times, but

> it NEVER calls the MainWindow.QueryUnload(). So the only way that method

> can be called is via the user pressing the "x" (close) button or the

> upper-left icon for the window popup menu, or if the window still exists

> when CLEAR WINDOWS, RELEASE WINDOWS, or QUIT is issued. Of those three,

> only CLEAR WINDOWS appears in my code, after everything related to

> databases and tables is done. In fact, it only occurs after the shutdown

> code issues CLOSE DATABASES ALL.

>

> The query expressions are assembled by different methods of different

> objects under different circumstances, but the code that executes them

> resides in a single method of my query object. That object is instanciated

> separately in each of these cases.

>

> In one of these cases, the user starts the query in a modal child window,

> and therefore should not be able to click the "x" close button or the

> upper-left-corner popup-menu button to trigger QueryUnload(). So that's

> another headache.

>

> But in the other case the window in which the user starts the query is not

> modal.

>

> There are other anomalies. For example, in one of these instances there is

> NO error-handler in the stack trace. That is, no line for any object's

> Error() method and no line for my error-handler program. However, my

> messaging object's code for logging errors and emailing the log file gets

> triggered, and announces VFP Error 3, so it must have occurred. But the

> trace jumps from the method where the query is actually executed directly

> to the messaging object without any intervening error-handling code.

>

> Question 1: Does a SQL SELECT place a lock on a VFP .dbf table such that

> the same user can't issue a USE on the table while the query is running?

>

> Question 2: Insofar as possible, should I program defensively by disabling

> controls related to shutting down while queries are running?

>

> Thanks again everybody.

>

> Ken Dibble

> www.stic-cil.org

>

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

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/CAMvTR9eeHQM8rjEyYCkp2wHfaD0PUWX2ktq-Kf_xmb-9AZ1mrg@mail.gmail.com

** 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.

>As far as I know, SQL SELECT uses its own data session, so it shouldn't

>interfere with other users.

Thank you Laurie. Unfortunately, that was the one rational explanation I

could think of for this behavior.

I know that modality in VFP is sometimes... er... unreliable, but only one

of these cases involved a modal window.

Some people have said that timers mess up the event chain. There is a

constant timer running in my application; it keeps track of whether the

user interacts with the program and will start a shutdown process after 15

minutes of inactivity. There doesn't seem to be inactivity associated with

these instances, they seem to occur right after the user does something.

And there's no timer code in the stack trace. But still.... there has to be

some explanation for these bizarre chains of events.

Thanks.

Ken Dibble

www.stic-cil.org

>On 13 July 2015 at 16:27, Ken Dibble <krdibble@stny.rr.com> wrote:

>

> > My error handler has recently reported two instances of the following

> > scenario:

> >

> > User causes a SQL SELECT query to run.

> > Suddenly, the application is being shut down via my custom main window's

> > QueryUnload().

> > Normal shutdown requires access to tables that may be implicated in the

> > SQL SELECT query.

> > VFP Error 3 ("File is in use") occurs.

> >

> > I use a custom main window instead of the VFP _SCREEN, which is

> > suppressed. My code calls the QueryUnload() of child windows at times, but

> > it NEVER calls the MainWindow.QueryUnload(). So the only way that method

> > can be called is via the user pressing the "x" (close) button or the

> > upper-left icon for the window popup menu, or if the window still exists

> > when CLEAR WINDOWS, RELEASE WINDOWS, or QUIT is issued. Of those three,

> > only CLEAR WINDOWS appears in my code, after everything related to

> > databases and tables is done. In fact, it only occurs after the shutdown

> > code issues CLOSE DATABASES ALL.

> >

> > The query expressions are assembled by different methods of different

> > objects under different circumstances, but the code that executes them

> > resides in a single method of my query object. That object is instanciated

> > separately in each of these cases.

> >

> > In one of these cases, the user starts the query in a modal child window,

> > and therefore should not be able to click the "x" close button or the

> > upper-left-corner popup-menu button to trigger QueryUnload(). So that's

> > another headache.

> >

> > But in the other case the window in which the user starts the query is not

> > modal.

> >

> > There are other anomalies. For example, in one of these instances there is

> > NO error-handler in the stack trace. That is, no line for any object's

> > Error() method and no line for my error-handler program. However, my

> > messaging object's code for logging errors and emailing the log file gets

> > triggered, and announces VFP Error 3, so it must have occurred. But the

> > trace jumps from the method where the query is actually executed directly

> > to the messaging object without any intervening error-handling code.

> >

> > Question 1: Does a SQL SELECT place a lock on a VFP .dbf table such that

> > the same user can't issue a USE on the table while the query is running?

> >

> > Question 2: Insofar as possible, should I program defensively by disabling

> > controls related to shutting down while queries are running?

> >

> > Thanks again everybody.

> >

> > Ken Dibble

> > www.stic-cil.org

> >

> >

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

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/5.2.1.1.1.20150715100222.02171eb8@POP-Server.stny.rr.com

** 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.

>

> > In one of these cases, the user starts the query in a modal child window,

> > and therefore should not be able to click the "x" close button or the

> > upper-left-corner popup-menu button to trigger QueryUnload(). So that's

> > another headache.

> >

I was able to talk to the user involved in this one. He says he used that

modal window, finished with it, closed it, and pressed the "X" button in

the main window to shut down the application. If so, why was the process

controlled by the modal window still in the stack trace when Error 3 was

triggered during the shutdown?

Ken Dibble

www.stic-cil.org

_______________________________________________

Post Messages to: ProFox@leafe.com

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/5.2.1.1.1.20150715100635.020b8f90@POP-Server.stny.rr.com

** 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.

At 10:08 AM 7/15/2015 -0400, you wrote:

>> > In one of these cases, the user starts the query in a modal child window,

>> > and therefore should not be able to click the "x" close button or the

>> > upper-left-corner popup-menu button to trigger QueryUnload(). So that's

>> > another headache.

>> >

>

>I was able to talk to the user involved in this one. He says he used that

>modal window, finished with it, closed it, and pressed the "X" button in

>the main window to shut down the application. If so, why was the process

>controlled by the modal window still in the stack trace when Error 3 was

>triggered during the shutdown?

I will just add that after several tries, I am unable to use the visual

interface in my application fast enough to start a query operation and then

do something that interrupts it and causes an error. Every time I start a

query and then, as fast as I can, close the window I started it in or close

the application before it returns results, the thing just forgets it was

running a query and proceeds normally to do what I asked it to do. So it is

just surpassing strange. I cannot imagine what someone could have done to

get these results.

Ken

_______________________________________________

Post Messages to: ProFox@leafe.com

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/5.2.1.1.1.20150715110723.020ad7b8@POP-Server.stny.rr.com

** 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.

On Mon, Jul 13, 2015 at 11:27 PM, Ken Dibble <krdibble@stny.rr.com> wrote:

> Question 1: Does a SQL SELECT place a lock on a VFP .dbf table such that the

> same user can't issue a USE on the table while the query is running?

It will open a table in shared mode. It seemed that a table or an

alias referred by a SQL query was already opened. The SQL query might

have been fired twice by different windows.

A quick-and-dirty solution is to check USED("_table_name_") before

issuing any USE _table_name_! Here is an example:

SELECT 0

IF USED("customer")

SELECT customer

ELSE

USE customer SHARED READONLY

ENDIF

EXECUTE_SQL_QUERY()

You might also consider using USE...AGAIN ALIAS.... in all your SQL queries.

> Question 2: Insofar as possible, should I program defensively by disabling

> controls related to shutting down while queries are running?

Why not use modal windows all the time so that users are always using

one window a time!

--

.~. Might, Courage, Vision. SINCERITY!

/ v \ 64-bit Ubuntu 9.10 (Linux kernel 2.6.39.3)

/( _ )\ http://sites.google.com/site/changmw

^ ^ May the Force and farces be with you!

_______________________________________________

Post Messages to: ProFox@leafe.com

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/CAGv=MJAzTFCLRMai=T_RnovtJfCPHqeN_WdTB3+KF891R2-r6g@mail.gmail.com

** 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.

>A quick-and-dirty solution is to check USED("_table_name_") before

>issuing any USE _table_name_! Here is an example:

Thank you. I always do that. In fact, my framework has a data manager class

that handles this, so USE and SELECT are only issued in one place. If

USED() is .T., then I SELECT that work area.

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.

I don't use USE AGAIN or ALIAS SomethingDifferent because it complicates

the task of keeping track of what's been opened.

>You might also consider using USE...AGAIN ALIAS.... in all your SQL queries.

I don't use USE AGAIN or ALIAS SomethingDifferent because it complicates

the task of keeping track of what's been opened.

> > Question 2: Insofar as possible, should I program defensively by disabling

> > controls related to shutting down while queries are running?

>

>Why not use modal windows all the time so that users are always using

>one window a time!

Most of my windows are modal, but sometimes people have to switch back and

forth between two open windows to accomplish a task.

Thanks.

Ken

_______________________________________________

Post Messages to: ProFox@leafe.com

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/5.2.1.1.1.20150716091231.0209d858@POP-Server.stny.rr.com

** 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.

I've gotten into the habit of using:

USE mytable IN SELECT([mytable])

If it's not USED the SELECT() function returns the equivalent of the next available workarea. If the assumption is that you just want that table open, this eliminates any amibiguity. Of course as you've pointed out, if another user or process has an exclusive lock on the table, you'll still get some kind of file access error. Yes, there's some overhead to reopening the table but there's also overhead to checking to see if it is already in use, so as Uncle Ted would point out, you have to test that in your application environment to determine if that overhead is meaningful.

You can also wrap your USE statements in a TRY..CATCH and handle both error 3 or 108 or any other file access specific error as appropriate.

--

rk

-----Original Message-----

From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ken Dibble

Sent: Thursday, July 16, 2015 9:21 AM

To: profoxtech@leafe.com

Subject: Re: "File In Use" on USE "during" SQL SELECT Execution

>A quick-and-dirty solution is to check USED("_table_name_") before

>issuing any USE _table_name_! Here is an example:

Thank you. I always do that. In fact, my framework has a data manager class that handles this, so USE and SELECT are only issued in one place. If

USED() is .T., then I SELECT that work area.

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.

I don't use USE AGAIN or ALIAS SomethingDifferent because it complicates the task of keeping track of what's been opened.

>You might also consider using USE...AGAIN ALIAS.... in all your SQL queries.

I don't use USE AGAIN or ALIAS SomethingDifferent because it complicates the task of keeping track of what's been opened.

> > Question 2: Insofar as possible, should I program defensively by

> > disabling controls related to shutting down while queries are running?

>

>Why not use modal windows all the time so that users are always using

>one window a time!

Most of my windows are modal, but sometimes people have to switch back and forth between two open windows to accomplish a task.

Thanks.

Ken

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

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/BN4PR10MB0913A3E3A3F047F738C4C403D2990@BN4PR10MB0913.namprd10.prod.outlook.com

** 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.

On Thu, Jul 16, 2015 at 9:21 AM, Ken Dibble <krdibble@stny.rr.com> wrote:

>

>

> 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

>

USED() only tells you if an ALIAS name is used, not a table.

create table temp (field1 c(10), field2 d)

USE

use temp alias nottemp in 0

? used("temp") && .F.

_______________________________________________

Post Messages to: ProFox@leafe.com

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/CACW6n4sgQyBhLkJdmcvf2NYeh0adZ7WLVS6mBKoo=eXLGQs29Q@mail.gmail.com

** 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.

>USED() only tells you if an ALIAS name is used, not a table.

>

>create table temp (field1 c(10), field2 d)

>USE

>use temp alias nottemp in 0

>? used("temp") && .F.

Yup. But my code never sets up ALIASes that are different from the table name.

I'm about to post a very long message providing excruciating detail about

this.

Be afraid. Be very afraid.

Thanks.

Ken

_______________________________________________

Post Messages to: ProFox@leafe.com

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/5.2.1.1.1.20150716121858.020c5e88@POP-Server.stny.rr.com

** 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.