Run UPDATE and DELETE statements on VFP DBF files on SQL Server

Question

We're migrating our legacy system based Visual FoxPro to Java, and we need to configure the SQL Server to CRUD the DBF files of the system, because we'll rewrite the system in parts. So the employees will use both interfaces in the same time and we need real-time updates in both systems.

Right now, I'm able to INSERT and SELECT data on SQL Server but I can't UPDATE and DELETE.

I've run the following command to create the linked server:

sp_addlinkedserver @server = 'DEN',
@srvproduct = 'foxpro',
@provider = 'VFPOLEDB.1',
@datasrc = 'D:\BaseTeste\denny\denny_db.dbc'

And run the following SQL to update a table:

UPDATE DEN...produtos SET familia=1 WHERE id=35

And I've received this error:

OLE DB provider "VFPOLEDB" for linked server "DEN" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7333, Level 16, State 2, Line 1

Cannot fetch a row using a bookmark from OLE DB provider "VFPOLEDB" for linked server "DEN".

How to solve that? Thanks.

Answer 1

I use the VFP OleDB regularly, and have no problem doing ANY inserts, updates, deletes, selects.... One thing to note. Your connection string can either point to a directory where the tables are located. In addition, the database can be included if a specific .DBC is associated with the tables in question.

When running a query, insert, update or delete, you do not need to qualify the database such as

DEN....produtos (which I assume is meant to be Denny_db.Produtos -- thus indicating Database.Table to run query against). Don't do that... the database is open and "visible" from the connection.... you should just be able to do....

Update Produtos set x = 1 where something = whatever

or

insert into Produtos (fld1, fld2, fld3) values ( something1, another2, last3)

Another thing about VFP, when the table is associated with a given database, as soon as it is opened, if the corresponding database is NOT opened, it will be FORCED open to utilize any triggers and such. So you COULD simplify your connection to just point to the path and let the rest of the stuff just WORK for you.

Another note.... if you have a directory structure that has paths under it with data in other locations, such as

C:\SomeFolder\MainDataPath\
C:\SomeFolder\MainDataPath\SomeArchives\
C:\SomeFolder\MainDataPath\OtherFolder\

and make your connection to just the "C:\SomeFolder\MainDataPath\" location, your queries can use relative path to get at data inside the other locations such as

select whatever 
   from SomeRootTable SRT
      join SomeArchives\SubFolderTable SFT
         on SRT.KeyID = SFT.LinkKeyID

Answer 2

Having VFPOLEDB installed with SQL Server Express 2012 32bit the following seems to be working:

SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\file.dbf';'';'','file')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\file.dbf';'';'','update file set n=2 where n=1')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\file.dbf';'';'','delete from file where n=2')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\db2.dbc';'';'','tab2')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\db2.dbc';'';'','update tab2 set somedate=ctod("12/30/2000") where n=1')

The only inconvenience is that update and delete statements result with error 7357 telling you that no rows to be returned. You can wrap it in try/catch block and ignore 7357 as an expected condition since actual statements were executed anyway. To use dynamic parameters it is possible to execute this through exec(@sqltext).

Answer 3

You're out of luck with a linked server:

When you use Visual FoxPro OLE DB Provider as a SQL Server-linked server, only queries are supported. The Visual FoxPro OLE DB Provider does not support update, insert, or delete operations through a linked server.

http://msdn.microsoft.com/en-us/library/0xzsac67(v=vs.80).aspx

Instead try OPENROWSET with the MSDASQL provider and pass the fox-pro ODBC driver as the 2nd argument (Driver={Microsoft Visual FoxPro Driver})