VFPOLEDB as linked server and data modification problems

[abraxas]

Hello all,
I dug this up here and I wish to know if it's still valid
*****
philhege (Programmer) 23 Nov 05 12:59
The bottom line, and I got this straight from the MS PSS horse's mouth last winter, is "we don't support data manipulation through the link server and VFPOLEDB." Select, yes; I/U/D no.
*****

I have never dealt with fox pro before and have found myself thrown in the abyss so here we go...

Provider is VFPOLEDB 9.0.00.5815 and sp_enum_oledb_providers indicates VFPOLEDB is available to sql2005

I can't modify vfp dbf files using win2003/sql 2005 (x86) linked server and vfpoledb provider, but i can select with no problems.
Successful queries through Sql SMS
(4 part) Select * from vfpoledb...mydbf where [criteria]
or
Select * from openquery(vfpoledb, 'select * from mydbf')
also
successfully imported dbf tables via SSIS

this indicates to me that data source path is ok. SQL user is sa, windows user is administrator. SQL 2005 and dbf's are on same machine. Allow in process in provider is True/Enabled

update vfpoledb...mydbf set price = 100 where id = 1
** msg 7333 - Cannot fetch row using bookmark from ole db... **
or
update openquery(vfpoledb, 'select * from mydbf') set price = 100 where id = 1
** msg 7301 - Cant obtain the required interface 'IID_IROWSETCHANGED' for ole db vfpoledb **

Within .NET
oldbVfp = new OleDbConnection(@"Provider=vfpoledb;Data Source=C:\DBFS;Collating Sequence=machine;");
I can update the dbf table successfully.. until the cows come home

ABOUT THE DBF's
There are only tables and no containers in directory. The dbf's come in 3 flavours
addr 0
0xF5 FoxPro 2.x (or earlier) with memo
0x03 FoxBASE+/Dbase III plus, no memo
0x30 Visual FoxPro

the table i wish to deal with is F5 at address 0 so i gather it's old, it has 6 indexes all of which are regular type (no candidate types). What functions as a unique identifier is character datatype

I setup a linked server to the northwind.dbc that came with the provider which only allows select queries and returns the same aforementioned errors on updates. I have not tried insert,... um yes I have and I can, tho had problem with memo field.

Less than a week ago (when I still felt this was all a practical joke) i managed to update successfully using opendatasource or openrowset on a xp box and sql 2000 but i cant remember how i addressed the provider. It may of been using MSDASQL I dont have the setup anymore to test it. I Component checked 2003 and got MDAC 2.8 on win server 2003 SP2.

I sincerely want to know if this is possible (update dbfs via sql linked server). I have researched this to some extent, trawlling thru foxite, getting dizzy and destabilized from content 'sharing', actually finding someone who was working on the exact same problem before he disappeared. Will I dream of Cindy Winegarden?

Many thanks

 

[Olaf Doschke]

Havent investigated this, but it seems the lack of a primary key for the old Fox 2.x table prevents updates. Fox 2.x tables even don't support primary keys, just a candidate index is possible.

You may split this into two linked servers and use the latest ODBC driver for Fox 2.x and Foxbase/dbase II tables making up one linked server while using OLEDB for the Visual Foxpro tables as the other linked Server. This may be a solution in the expectation, that ODBC would cope with old tables even without primary keys present.

But overall I think you can't overcome this shortcoming of the old table formats without primary keys without migration of data to newer DBFs at least.

Bye, Olaf.

 

[abraxas]

Thanks Olaf,
I thought i may of put my audience to sleep! I tried playing around with the vfpodbc (6.1.86291) this morning to no avail unless you want to walk me through how to do it. I am looking at a product called adavantage database server and some other advantage products. I'm waiting for Sybex sales to call me back about how backward, backward compatibility is. J Mullin posted a nice vid here

http://jdmullin.blogspot.com/2008/02/getting-started-with-visual-foxpro-and.html

Why can't sybex do that? For their product?? that they sell???

Anyway, while everyone was watching expensive horse flesh i went on a road trip and retrieved my XP/sql2000 laptop and dug up this query...

update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=C:\Documents and Settings\Aenea\Desktop\vfp;
SourceType=DBF',
'select * from table')
set price = 4516.7
where id = 'ABC1'

it worked! Several times. I thru this query on the win2003/sql2005 box and that worked. It's a start at least, though I knew I'd get authentication problems when I put the .dbf's on the network.

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\shrike\test;exclusice=No;
SourceType=DBF',
'select * from table')
where id = 'ABC1'

tried mapped drive, and dsn as linked server source but both returned following error or very similar on select queries.

odbc visual fox pro driver: File table.dbf not exist
7399 MSDASQL reported error.

Not sure of how to affect permissions. If you can, could you offer some pointers on this please? I'll be doing the usual forum loop the loop on google

Thank you

 

[Olaf Doschke]

While you ask how to work with ODBC you already did. 'Driver=Microsoft Visual FoxPro Driver' opposed to 'Driver=VFPOleDBPRovider.1' means you're using VFP ODBC.

Regarding the file access permission I'd say you're out of luck. The SQL Server Service typically runs as the system account 'NT-AUTHORITY\NETWORK SERVICE' and as thus is no domain account and cannot access shared folders on network drives. Either you change running SQL Server as a domain account having access to the share with DBFs or you store DBFs on the server.

Bye, Olaf.

 

[abraxas]

Thanks again Olaf,
out of luck? Not me. Why I was asked to do this. The company requires distributed queries to 30 plus instances of Visual Fox Pro applications (they migrated from interbase) on their VPN. At the moment nothing spectacular with the reading and writing of dbf's, just one numeric column that will be modified in one table and inserts and deletes for a memo/text field in a separate table. This has to be done no matter what, so I have to lay down an ACCURATE and RELIABLE foundation. I have four vectors of attack...... or proposal

1) VFPODBC. SQL server runs on domain account, at moment I'm cut off from vpn to test this and I'm not sure how odbc driver sits on win 2008.

2) VFPOLEDB. Doesn't play happy with SQL, where i'd like to situate this tier. For fun, I setup a VFPOLEDB data conn on 64 bit machine with Visual Studio 2008, this connected to x86 win2003 .dbfs and updated them and were subsequently read by XP Pro box running visual studio 2003 and VFPOLEDB data conn. I am troubled as to why SQL has dramas doing this and Visual Studio doesn't?

3)Advantage/Sybase (sorry said sybex earlier) staff sobered up and phoned me. Didn't take too long to lose them and suggested I email requirements since they have to contact parent company for such specifics. The sales people were very friendly.

4) Write apps in vfp and push it to sql then pull it back when appropriate. Which leads me to a last question and a bonne nuit. If I am using MVFP 9.0 to access and modify aforementioned table content, will vfp try to reformat/change table structures to something modern? I saw a message a few days ago to the effect of "VFP 9.0 needs to convert xxx.dbf table", though I haven't seen it since.

Thank you for your assitance Olaf. I'll most probably be annoying you for Visual Fox Pro tips

Anthony

 

[GriffMG]

I don't think the ODBC will be a solution in a production environment - it's not multi-threaded and is ok for testing, but I found it lets you down in the real world these days.

Could the message you are getting, have gotten, about updates have been to do with code pages?

If so you can turn off the update prompt:

SET CPDIALOG OFF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

In regard to 1) ODBC, I'd not say it let's you down, not with the legacy tables you are working on. What's important to know is, that 64 bit and 32 bit are different worlds in regard to ODBC and the SQL Server 64 Bit version can't use the 32 bit vfp ODBC driver.

Also any driver you use will be used by the system account of SQL Server and might not have access to network shares. That's a general problem. Of course no ODBC driver or OLEDB
provider can't do anything on files it can't access. That may be the major difference why your experiments did work in VS but not in SQL Server, as VS runs as invoker (under your account).

Advantage Database's ODBC driver should be compatible, but is mostly of interest to people needing either a 64 bit driver or an ODBC driver capable to work on newer DBFs. You might need it, if your SQL Server is a 64bit version.

In regard to 4) VFP9 does not need to convert, but it will ask for codepage conversion, especially if the DBFs are DOS codepaged of course, which might be the case. Griff has given you a solution to that prompt SET CPDIALOG OFF. You might find some characters not fitting Windows ANSI codepage 1252, which you most probably use, especially in text fields.

Bye, Olaf.

 

[abraxas]

Thanks guys for the heads up regarding cpdialog. Correct me if I'm mistaken but all this will be for naught if the vfp application (the app that the company has bought) has exclusive access of the data tables in directory?

No, the 64 bit os was just a test on my dev environment to see what visual studio could do that sql couldn't. The production environment itself is virtual so I can click and drag any server architecture and any RDBMS into creation, so to a point I can partially tailor an environment to a solution.

The Data Connection versatility of both VS 2003 and 2008 using VFPOLEDB has me flummoxed when comparing to SQL's use of it. Out of interest this is the provider string i have set up in VS 200x and i can read and update remotely

Provider=VFPOLEDB.1;Data Source=T:\TEST;Mode=ReadWrite|Share Deny None;Extended Properties="";User ID=administrator;
Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;Exclusive=No;
CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5

While a linked server with similar provider string can read dbf's (on same machine) but will not update. Using linked server in openquery gives an error message suggesting permissions (4 part gives generic err).

I have access to virtual domain so I'll try some similar tests using domain/administrator user. Also been going through visual fox pro 9.0 help in case all else fails.

Ant