Sharing Violation 1

[audiopro]

I have a networked EPOS application where the .exe and free tables are stored on a single machine. Other machines on the network run versions of the centrally stored .exe
whilst accessing and editing the shared tables through a mapped drive.
Up to now I have had no need to exclusively open any of the tables so have managed to avoid any sharing violations.
I now wish to run an analysis report from any one of the machines whilst the tables are being accessed by the others.

Overview of analysis function:-
Query the main table and place relevant records into a cursor in stock code order.
Step through the cursor, record by record and populate an additional table with totals of items sold etc.
Create a report from this table in supplier order.
This works properly but each time I run the analysis I need to delete all records from the previously populated additional table and pack it. Naturally Pack causes a sharing violation.
I have tried to set the additional table to exclusive access but to no avail.
All the tables within the data environment of the form are set to shared with the location being z:.
I thought that setting to location of the additional table, within the data environment to c:\whatever and making it exclusive would force each instance of the app
to use its own local version of the additional table and cure the sharing violation but I still get the sharing error.
I hope I have explained my problem well enough for someone to point me in the right direction.

Keith

http://www.studiosoft.co.uk

 

[bborissov]

Keith, you can do this that way. Leave the table on server so any user can relate on this table.
In Init event of the form put:

**** FOR VFP8 and later
LOCAL lbExitForm
TRY
  SELECT YourTable
  USE YourTable EXCLUSIVE
***
  ZAP
** or
  DELETE FOR ...
  PACK
***
CATCH
  MesageBox("Can't open the table")
  lbExitForm = .t.
FINALLY
  SELECT 0
  USE YourTable SHARED
ENDTRY
IF lbExitForm
   ** Exit the form, before even start it
   RETURN .f.
ENDIF

**** In VFP7 and previous
LOCAL lcOnError, lbError
lcOnError = ON("ERROR")
ON ERROR lbError = .t.
SELECT YourTable
USE YourTable EXCLUSIVE
***
  ZAP
** or
  DELETE FOR ...
  PACK
***
ON ERROR &lcOnError
IF lbError
  MesageBox("Can't open the table")
  RETURN .f.
ENDIF

Other way is w/o exit the form

**** FOR VFP8 and later
LOCAL lbError
TRY
  SELECT YourTable
  USE YourTable EXCLUSIVE
***
  ZAP
** or
  DELETE FOR ...
  PACK
***
CATCH
  lbError = .t.
FINALLY
  USE YourTable SHARED IN SELECT("YourTable")
ENDTRY

IF lbError
  SELECT YourTable
****
  DELETE ALL
*** or
  DELETE FOR ...
ENDIF

*** HIDE Deleted records
SET DELETED ON


**** In VFP7 and previous
LOCAL lcOnError, lbError
lcOnError = ON("ERROR")
ON ERROR lbError = .t.
SELECT YourTable
USE YourTable EXCLUSIVE
***
  ZAP
** or
  DELETE FOR ...
  PACK
***
ON ERROR &lcOnError
USE YourTable SHARED IN SELECT("YourTable")
IF lbError
  SELECT YourTable
****
  DELETE ALL
*** or
  DELETE FOR ...
ENDIF
SET DELETED ON

Borislav Borissov

 

[audiopro]

Thanks for the response.
I should have mentioned I am using VFP6.
If I understand your code correctly the table is deleted but not packed under error conditions.
I was really trying to get my head round using a local cursor where I can delete and pack without error. There must be a way of using these tables exclusively where they do not affect each other.

Keith

http://www.studiosoft.co.uk

 

[bborissov]

You can do this, but what happens if you want to change the structure of that file? Then you must do this in ALL local stations instead of doing this on 1 computer.
Also you can use a cursor instead of table itself, something like:
(In VFP6)

SELECT * FROM Yourtable WHERE .f. INTO CURSOR crsTest && Just empty cursor
USE IN Yourtable
SELECT 0
USE (DBF("crsTest")) ALIAS YourTable AGAIN SHARED
USE IN crsTest

but that way you can't update the data in main table. But as I see there is no need of this.

Other way:
There is no need of table at all, if you everytime regenerate the data. Use CURSOR insted:

CREATE CURSOR YourTable (.......)

If you need you can put this code in Load event of the form in case you have controls binded on that table.





Borislav Borissov

 

[audiopro]

I have decided to go down the cursor route but have hit another problem.
I can create the cursor

CREATE CURSOR SOLDIT (CODE C(10), QTY N(4), SALE_NUM N(10), PRODUCT C(60), PRINCIPLE C(4));

I need to index the cursor on the PPRINCIPLE field but do not know the syntax. My reference books are many miles away and could do with getting this sorted ASAP.
Googlin - reveals references to unique but that is as far as I got.

Keith

http://www.studiosoft.co.uk

 

[mkrausnick]

...force each instance of the app to use its own local version of the additional table ...

Not unless you write code to do that, which seems like it might be a solution in this case, but I need more information.

I take it from your explanation that "Additional Table" is accessed between report runs. Is that right? Is it a static (i.e. read-only) table that's populated only by the report generation routine and then read by other users until the next time you generate the report?

If so, there are a couple of work-arounds. First, the classic method is to avoid the necessity of packing the table by re-using deleted records, thusly:

set deleted off
locate for deleted()    && locate an available deleted record
if found()
  recall                && recall it for use if found
else 
  append blank          && or add a record if not found
endif 
replace ...             && populate the record 
set deleted on

Another solution is to have the users use a local copy. In the app's startup code, copy the network table to a local copy, then close the network table.

Which brings up another question. If this is a static view-only table for the users, why is it always open? Is there a way to change the code to keep the table closed and either
a) open it only when the user needs to see the data, or
b) make a fresh copy for the user to view and then close it

That way the time the table is open in shared mode is minimized.

Maybe I'm misunderstanding the situation though.


Mike Krausnick
Dublin, California

 

[bborissov]

I see you are VC++ or VC# Programmer )))))))))))))
If you end the line with [;] ))))))))))))

The syntax is easy:

CREATE CURSOR SOLDIT (CODE C(10), QTY N(4), SALE_NUM N(10), PRODUCT C(60), PRINCIPLE C(4))
INDEX ON PRINCIPLE TAG SOLDIT

Borislav Borissov

 

[bborissov]

Mike, If the table is static and there is no need to save the data in it, there is no need of table at all )
You can simply retreive data with SELECT SQL or via CRATE CURSOR and then loop through tables.

Borislav Borissov

 

[mkrausnick]

While that's generally true, it would depend on how long it takes to generate the table, and what response time is needed when it is to be displayed. It is usually more efficient to generate the data once and store it rather than overloading the system by repetitively performing the same calculations.

As it is, It sounds like this table ISN'T needed between report runs, and Keith is exploring a cursor-based solution.

Mike Krausnick
Dublin, California

 

[audiopro]

I have re-wriiten part of the code so that it uses two cursors but will not be able to test it on the active system until later in the week.
The first cursor is created from an SQL statement which gathers the relevant files in item code order. The cursor data is then processed line by line and a multi dimensional array is formed from the item totals The second cursor is populated from the array. Finally, a report is created from the data within the second cursor.
I assume this cursor will stay active until the program is closed down. Does a new cursor of the same name over write the old on or is the user prompted to over write it?




Keith

http://www.studiosoft.co.uk

 

[mkrausnick]

A new cursor of the same name created by a SQL statement will overwrite the old one without prompting. However, it's good practice if you're going to do this to close the cursor before creating another one of the same name. The only problem with closing the cursor in this manner that I know of is if the cursor is the data source for a grid in a form.

Mike Krausnick
Dublin, California

 

[audiopro]

Would a cursor created programatically with CREATE CURSOR over write without a prompt?
Also, how do I close a cursor whilst leaving the rest of my data set active? According to my reference books, a cursor is closed using CLOSE ALL, surely that closes all the open data tables.

Keith

http://www.studiosoft.co.uk

 

[bborissov]

To close only the cursor you want:

*** Close only Cursor/Table opened in YourCursorAlias
USE IN SELECT("YourCursorAlias")

Borislav Borissov

 

[audiopro]

Thanks for shedding some light on it for me.
I find the easy stuff the hardest to work out.

Keith

http://www.studiosoft.co.uk

 

[bborissov]

)
Sometimes me too

Borislav Borissov