Read Foxpro table being used by another application

Question

Hi

I'm writing an small software that will be part of an existing application which uses dbf foxpro tables. My application just reads 2 tables fill a dataset and closes the connection, pretty fast and simple. It works until one of those tables are being used or by foxpro itself (when the table is opened) or by the main application when it is accessing that table.

When it happens a get the exception

ex = {"Cannot open file c:\data\myFile.dbf."} ErrorCode = -2147217865

Is it possible to specify that I want to access it just for reading, not editing?

PS: I'm using VS 2008 C# to access it. My connection string looks like: "Provider=VFPOLEDB.1;Data Source=C:\data\"

Many thanks

Answer 1

The error code you're getting is HRESULT 0x80040E37 as mangled by some intermediate steps that doesn't know about unsigned int32's -- it's the generic ODBC error for "can't open that table" (normally due to a mis-spelling). No doubt Foxpro and the libs the main app is using are doing some kind of "locking" -- and even if ODBC allowed you to specify that you only want to read, that should still be denied if some other process has it open for writing (two or more processes just wanting to read would be fine, but even just one wanting to write must exclude all others, readers or writers).

If you can't temporarily detach the .DBF file from other uses while you briefly read from it, then one approach might be to copy it to another name (still .DBF) and try to open THAT copy -- does it work, or still fail with the same error? In the latter case, there may be ways to hack the file so its "locked state" is cleaned -- as long as it's not in use (as the copy wouldn't be, until you manage to open it!-). Once you've done the read you want you can delete the copy.

Problem is, this approach, while it might be made to work, won't be entirely reliable: it's possible (if you're unlucky) that foxpro or your main app might be in the midst of making changes to the DB (that's why they're locking it, after all - to be safe they CAN make changes), and the changes might be partially but not totally committed to disk at the very moment you perform the copy. Do you have some way to check whether the data you're reading is sensible or corrupted? If you can tell it's corrupted you could simply try reading again (hoping the saving of the new data to disk was meanwhile completed), but if you can't tell this is really a crapshoot...:-(

I guess the lesson to retain is that certain ways to persist data are simply NOT all that suitable for multi-tasking purposes -- make sure to use a more solid way next time you design any kind of data persistence for your programs!

Answer 2

I am making an assumption when you are referring to "FoxPro itself" you mean someone is running FoxPro 2.6 for DOS or Windows, or Visual FoxPro (any version). If this is the case make sure the user uses the following command in the Command Window

SET EXCLUSIVE OFF

Or they can open each table and include the SHARED clause on each USE command.

If you are referring to an application developed in FoxPro running against the data you have a slightly more complex situation because the app could be designed to be single user and have a SET EXCLUSIVE ON in the code. The best shot you have in this case is to try modify an existing Config.FP or Config.FPW (depending on the version) and adding a line:

EXCLUSIVE = OFF

Or you can create the file if it does not exist. If that does not work you would need the application source code to change it so it does not open tables exclusively.

As for your use of the VFP OLE DB driver with your C# program, you can include a Config.FPW file in the folder with the EXCLUSIVE = OFF and it will ensure you open the files in shared mode, just in case you are attempting exclusive use. This unlikely since the runtime version does not default to exclusive being on and the OLE DB driver is following the runtime standard.

Rick Schummer VFP MVP

Answer 3

Aside from Ricks comment on SET EXCLUSIVE OFF in the actual Foxpro application. There ARE a few instances where a true lock on the file is needed, such as modifying the structure, packing the database (removing records marked for deletion), rebuilding indexes. If any of those are the basis for the locked file, then even copying won't help as you wouldn't be able to get a file handle, and/or the result of the copy may be out of synch, then your query could fail or give other false results.

Answer 4

Rick is correct. Your Foxpro settings are defaulted to give the Foxpro session exclusive rights to any table opened by the "use" command.

When you are opening the table, while the VB app is running, you are stumbling over one another.

I'm assuming that you are double clicking the DBF files in Windows Explorer and opening them and not using the "use" command in Foxpro to view the tables. If you only want to do it that way, then (in VFP 9) close all sessions but one of VFP. Goto Tools->Options->Data Tab and uncheck "Open Exclusive". Now close that session. This will save the settings. The next time you double-click a DBF/DBC, it will open all tables with "shared" access.

Otherwise, to access the tables from within VFP using the "use" command, do:

For exclusive access:

use in 0 exclusive <table-file-path>

For shared access:

use in 0 shared <table-file-path>

You can also give it a noupdate flag to make it read-only. (The safe way to query.)

use in 0 exclusive noupdate <table-file-path>

or

use in 0 shared noupdate <table-file-path>

From the way I understand it, the main problem is with VB taking over "exclusive" rights to the database and crashing when you have the table open (shared or otherwise.)

However, it sounds more like a timing issue than a Foxpro one. Assuming you don't have access or permission to rewrite the VB app, the only other alternative is to find a time that the main VB app isn't running (late at night, perhaps?) and have the run a Foxpro query (.PRG file) on a scheduler. The query could copy out to another file, such as Alex suggested.

The command to run a Foxpro program is easy:

foxpro <program-name>.prg

That can go in a .bat or .cmd file which is run by a generic scheduler.

However, there is a catch: It is always better to compile the .prg from within VFP yourself, rather than let VFP compile it for you (which it will if you don't.) Also, if you make a change and don't recompile, VFP will use the last compiled version (just to spite you.)

If you already knew this stuff, sorry for the overkill.

Good luck.

Answer 5

It's pretty ugly but you might try copying the table via the OS vs from within a Foxpro app. The OS might even be capable of handling a copy and delaying if it is in the middle of a write operation from another process. Once you copy, if there is a DBC backlink to the file and you can't open it, you simply need to FREE the table.. DROP or FREE, my memory fails me :) this is all meant to be generic.. The best solution is to recompile the locking foxpro software as suggested so that it is NOT exclusive. But if you do that, then you have to understand why it might of been exclusive to start with.. deliberate or just shitty coding?