Data Environment - Exclusive access

[AndrewMozley]

I am using the data environment for a form which does some file maintenance operations. It needs exclusive control of several tables. If it cannot get this control, the user needs to be warned.

At present there is an ON ERROR method in the Data Environment which traps the error (of trying to open the table exclusively). And at present I set a PUBLIC variable ~ CanWeProceed to “NO” so that the Load method of the form can decide not to proceed.

A couple of questions (in addition to ‘Is this the right way to cope with the issue’ !)
[ol 1]
[li]Is it possible to use a property of the form to pass this information to the Load method? I tried to do just this, but found that a property set in the ON ERROR method of the Data Environment was being reset by the time the Load method fires. Have not go to the bottom of this.[/li]
[li]Can I find out which is the table which is failing to open exclusively, so that this can be reported back to the user.[/li]
[/ol]

Thank you.

 

[Olaf Doschke]

Since we have a discussion about public variables this is a nice challenge to solve.

Well, use the Error event here. In the DataEnvironment.Error event you can set THISFORM.TAG = SYS(2018)

Look at the help about SYS(2018). If you find something in TAG you know it is the source of the error. You can of course also stay more error info in more properties, but as far as I tried the properties are not reset after the DE loaded tables, no.

Bye, Olaf.

 

[AndrewMozley]

Thanks Olaf. I'll check this out.

 

[Mike Lewis]

Andrew,

I agree with Olaf that you should use the Error event in this case.

An alternative solution would be to (try to) open the tables explicitly in the Load event (with USE commands). Wrap the commands in a TRY/CATCH/ENDTRY. If the opening fails, issue your warning and exit the form (via [tt]RETURN .F.[/tt])

That will also tell you which of the tables failed to open. In fact, you could find out all the tables that failed to open that way, not just the first one.

Note that I am not suggesting this approach in general for your forms - only for this one special case.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, everyone knows I rather avoid the DE and use framework data access classes starting with a database manager and including biz objects maintaining cursor adapters. In this scenario, each biz object clinks into structured and general error handling and knowing which table fails is just known by which biz object or which CA within it fails. It again like always boils down to frameworks starting with their own way of data access based on USE and SQL and views and SPT and nowadays on cursor adapter already had much more features than the DE before the DE was extended and became better. These are all advances coming too late, if you already have invested in having your own code caring for all aspects of data access including to manage and nest or un-nest transactions (or use one transaction for a hierarchy of biz objects), hook a biz object into a parent biz object or add child objects to cascade queries, updates, etc. Much more than the DE alone can do and make reusable as a DE class.

I actually remember especially Rick Strahl judging the CA as I judge the DE as an advance of VFP coming too late and while there already are advanced application frameworks with data access classes managing cursors, but indeed the CA class offers some events you don't have anywhere else, eg all the Before/After event hooks. CA made it into my domain of actively used VFP language and classes.

Anyway, SYS(2018) shows you the file having failed without needing to change to individual USE commands in TRY..CATCH blocks. It's true you can only store one SYS(2018) in TAG, but you could also build up a collection of names of failed tables, or simply make it a comma separated list in the tag property. And you can return from Error so the DE continues trying to open further tables. That said I would suggest you build up your own set of properties related to the reporting of form starting problems to the user, using TAG was just a simple example.

Bye, Olaf.

 

[AndrewMozley]

Thanks Olaf and Mike

The code in the ON ERROR method of the data environment does indeed trap the fact that one of the tables is not able to be opened exclusively ( I realise that it does not need WITH Thisform . . .).

LPARAMETERS nError, cMethod, nLine
LOCAL lTemp
WITH Thisform
SysCanWeProceed = "NO"
lTemp = SYS(2018)
SET STEP ON
MESSAGEBOX("You need exclusive access to tables xcust, xents")
ENDWITH
RETURN DODEFAULT(nError, cMethod, nLine)

If one of the tables (XENTS.DBF) is already open, the MessageBox certainly comes up to inform the user, and the 'Can we proceed' flag is later picked up by the Load method. However SYS(2018) is just coming up as an empty character string - had hoped that it might give an indication of the source of the problem. Am I doing something wrong?

Mike, you are right, I could explicitly open the tables in the LOAD event (I used to do it throughout this application); but I had got into the way of letting the Data Environment organise the tables. Perhaps I will need to make this screen an exception.

 

[Mike Lewis]

Andrew,

A couple of points. First, I don't know why you have SET STEP ON. I assume it's just a leftover from when you were debugging.

More importantly, your code doesn't tell which tables do not have exclusive access. It looks like, if one fails, you assume that they all (both?) fail. With a TRY/CATCH/ENDTRY you could trap each table individually before you issue the message.

I'd also query the language of your error message. Would an end user know what xcust and xents are, or even understand what "exclusive access" means? Then again, you know your users better than I do, so maybe my comment is not relevant. But I think at least you should advise the user what they should do next if they encounter that error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thanks Mike. The SET STEP is indeed there because I am still debugging, because I would like to find out which tables the Data Environment is failing to open. In particular I wanted to investigate the SYS(2018) suggestion.

The opening of the tables is performed automatically by the data environment; so at present I do not have specific code to put a TRY . . . . CATCH sequence round. But if that is possible, in the Data Environment, I will happily change the code - grateful for any guidance..

As per the first message in this thread, I would like to be able to find out which tables the Data Environment is failing to open.

You are right about the final message. This was from a test form; on the live system the user is in fact advised that other users are on the system, and that the System Administrator needs to make sure he is the only person using it.

Andrew

 

[Olaf Doschke]

Are you calling the error message from your general error handling? In my tests, SYS(2018) gave the file name of the table not accessible exclusive when I changed the property of the table in the DE to be opened exclusive and opened it before, shared, in another VFP session. I don't see how it not reliably gives a name.

If you only handle errors indirectly and raise further handling or call an error handling routine instead of simply letting the error event happen, then SYS(2018) cannot reflect the file involved in the original error. SYS(2018) surely has to be called in the first level of error handling and, well, it has to be an error related to not being able to open a table exclusive, not any other non-file related error. What is AERROR() reporting as the error number and message in case SYS(2018) is empty? And/or what is nError at that time? It should be Error 1705 ("File access is denied"). If there is anything else wrong in your code and any other error is handled, then SYS(2018) of course can be empty.

Bye, Olaf.

 

[Mike Lewis]

The opening of the tables is performed automatically by the data environment; so at present I do not have specific code to put a TRY . . . . CATCH sequence round. But if that is possible, in the Data Environment, I will happily change the code - grateful for any guidance..

Not, that's not what I was suggesting. My suggestion was to open the tables in the Load event, with USE statements. This would be in place of opening them in the data environment. The point is that you could trap each USE individually, which in turn would tell you which table failed to open.

Off the top of my head, you could do something like this:

lcMessage = ""

TRY
  USE TableA IN 0
CATCH
  lcMessage = "TableA "
ENDTRY

TRY
  USE TableB IN 0
CATCH
  lcMessage = lcMessage + "TableB "
ENDTRY

TRY
  USE TableC IN 0
CATCH
  lcMessage = lcMessage + "TableC "
ENDTRY

IF NOT EMPTY(lcMessage)
  MESSAGEBOX("The following table(s) failed to open: " + lcMessage)
  RETURN .F.   && Exit the form
ELSE
  RETURN .T.  && Everything OK
ENDIF

This is not meant to be finished code - just enough to give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike, Andrew won't need to put that much effort into changing from a DE to code. SYS(2018) will work, if the error handling is done correct, so it pays more to get to the ground of the problem, why it stays empty and investigate what errors happen.

Bye, Olaf.

 

[Mike Lewis]

I agree, Olaf. I was not suggesting there was anything inferior about using the DE and/or SYS(2018). I was merely throwing in another possible approach for the sake of discussion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Andrew,

I see your code has SET STEP ON, so I assume you did halt here to inspect lTemp. I don't question SYS(2018) could be empty, but then you don't had a file related error. Could you also inspect nError, do AERROR(laERROR) in the command line and in debugger Locals window inspect the array elements of laError? I assume you have some other non dbf access related error you see here. But SYS(2018) works, as can easily be tested in a new simple form having nothing but Thisform.Tag = SYS(2018) in the DE Error event and a table in its DE, that you open up before starting the form and then SET EXCLUSIVE ON, so the form DE will fail to open it exclusive. There is not much to do to test this. The problem must be in your environment or other error handling. I see you do DODEFAULT and lTemp of course doesn't get forwarded here.

Bye, Olaf.

 

[AndrewMozley]

Thanks Olaf for the time spent on this. It is a bit of a diversion, but I have not been able to get to the bottom of this SYS(2018) matter. In case you are interested, I have created a simple test form (using the basic VFP controls) which just opens a table exclusively. (The listing below is output from the class browser)

*

DEFINE CLASS form1 AS form
	Caption = "Form1"
	Name = "Form1"

	ADD OBJECT cmdaction AS commandbutton WITH ;
		Caption = "Action", ;
		Name = "cmdAction"

	ADD OBJECT cmdclose AS commandbutton WITH ;
		Caption = "Close", ;
		Name = "cmdClose"

	PROCEDURE Load
		SET PROCEDURE TO AMUTILS
		*!*	ON ERROR DO ErrorDebug
	
	PROCEDURE cmdaction.Click
		USE XSUPP EXCL

	PROCEDURE cmdclose.Click
		USE IN SELECT("XSUPP")
		Thisform.Release

When I run the form normally, I can click on cmdAction and no error occurs. If however I open the table from another form, then run this form I do get an error; however SYS(2018) comes up with “CKEY” (which is not a field name in table XSUPP).

Curiously, if I include the ‘ON ERROR DO ErrorDebug’ - which only copies SYS(2018) to a local variable - SYS(2018) reverts to an empty value ””.

 

[Olaf Doschke]

You didn't make use of the DE nor the error method. I thought you said you open tables in the form DE, not in a CommandButton. Of course, this doesn't work out as intended.

You do ON ERROR ErrorDebug and should also pass in ERROR(), MESSAGE(), PROGRAM(), LINENO(). Look at the simple help topic of ON ERROR and you already will advance your error handling.

What is the error number? I suspect you even get an error on file not found, not because of failing to get exclusive access, and then SYS(2018) won't work. Also, I assume your error handling prg is not in the context of the same datasession as the form is. So it could already help to make use of error methods, in this case, the cmdButton.error would be the first level of getting triggered by errors, then the form.error method and only if there is nothing like that the ON ERROR procedure.

Bye, Olaf.

 

[Mike Lewis]

run this form I do get an error; however SYS(2018) comes up with “CKEY” (which is not a field name in table XSUPP)

I saw that as well, but it was when I tried to open a file in the command window. I was wondering what CKEY means.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I wonder what you are doing, you most probably only get error 3 and not 1705, because your own process already uses the file, you have to open the table in another process to get error 1705.

Just open one VFP session and do:

SET EXCLUSIVE OFF && just making sure, this is not the blocking factor
USE browser

broewer.dbf is one of the tables VFP automatically will find in HOME(), take any other DBF of interest, it doesn't matter.

Now open another VFP and do

ON ERROR ? ERROR(), MESSAGE(), SYS(2018)
USE browser EXCLUSIVE

I get the file name mentioned first in the MESSAGE and then from SYS(2018).

Of course, you don't get the file name, if your error doesn't mention it at all, eg Error 3 just says "file is in use", but not which file. But if you get Error 3 you don't have the problem of getting exclusive access, you have the problem of already having the table open and then would need to make use o the AGAIN clause.

Bye, Olaf.