使用 FoxPro .dbf 檔案執行 SQL Server 分散式查詢

2020-09-08

本文介紹如何使用 VFP ODBC Driver 或 VFP OLE DB 提供者，執行 SQL Server 分散式查詢，從 FoxPro .dbc 和 .dbf 檔案擷取數據。

原始產品版本： Visual FoxPro
原始 KB 編號： 207595

摘要

本文示範如何使用 VFP ODBC Driver 或 VFP OLE DB 提供者，執行 SQL Server 分散式查詢，從 FoxPro .dbc 和 .dbf 檔案擷取數據。

其他相關資訊

Microsoft SQL Server 2000 提供對 OLE DB 提供者執行查詢的能力。此查詢是使用 OpenQuery 或 OpenRowset Transact-SQL 函式，或使用包含連結伺服器名稱的四部分名稱的查詢來完成。

例如：

sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')

您應該使用 Microsoft OLE DB provider for ODBC （MSDASQL）和 Visual FoxPro ODBC 驅動程式來設定連結的伺服器，針對 FoxPro .dbc 和 .dbf 檔案執行分散式查詢。不支援使用 Jet OLEDB 提供者與 FoxPro。 VFP ODBC 驅動程式不是安全線程。由於 SQL Server 是多線程的，因此 VFP ODBC 驅動程式在某些情況下可能會造成問題。如果可行，建議您使用 VFP OLE DB 提供者來連線到 SQL Server 數據。

下列 T-SQL 程式碼範例示範如何設定並以 FoxPro 使用 OpenQuery 和 OpenRowset 函式來進行分散式查詢。它也示範如何從 SQL Server 2000 更新遠端 FoxPro 數據表。在 SQL Server 2000 計算機上安裝 Visual FoxPro ODBC 驅動程序之後，您可以在 SQL Query Analyzer 中測試此程式代碼。您需要根據需求變更資料來源名稱和 FoxPro 檔案的路徑。

/* OPENROWSET and OPENQUERY examples with VFP via ODBC OLE DB provider */ 

/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc
Modify your code accordingly for differences in location or DBC name */ 

--====================================================
-- Using DBC file , read and update
--====================================================
-- OPENROWSET DSN-less example

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country != "USA" order by country')
go

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
go

Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
set region = "Seattle" 
go

-- check to verify which rows were updated
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="Seattle"') 
go

-- OPENROWSET DSN example
/* Note the DSN Example might fail if SQL Server is configured to use a local account.*/ 
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Database;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country != "USA" order by country'
go

/* sp_addlinkedserver examples */ 
-- sp_addlinkedserver example with DSN

/* You will need to make a DSN and point it to the Testdata database. 
Modify your code accordingly for differences in location or DBC name */ 

/* Note this Example may fail if SQL Server is configured to use a local account.*/ 
sp_addlinkedserver 'VFP Testdata Database With DSN', 
 '', 
 'MSDASQL',
 'VFP System DSN'
go

sp_addlinkedsrvlogin 'VFP Testdata Database With DSN', FALSE, NULL, NULL, NULL
go 

SELECT *
FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where region = "Seattle"') 
go

-- Update using OpenQuery
Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where region="WA"') 
set region = &quot;Seattle&quot; 
go

/* SP_addlinkedserver example with DSN-less connection */ 

/* This example also depends on the sample files Testdata.dbc
Modify your code accordingly for differences in location or DBC name */ 

sp_addlinkedserver 'VFP Testdata Database With No DSN', 
 '', 
 'MSDASQL',
 NULL,
 NULL,
'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP98\data\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'
go

sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL
go

SELECT *
FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country != "USA" order by country')
go

--====================================================
-- Using VFP 6.0 driver, read and update data from VFP sample dbf files
--====================================================

-- OPENROWSET DSN-less example

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go

-- perform UPDATE

Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where region="Seattle"')
set region = "WA"
go

-- verify update

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where region = "WA"')
go&amp;lt;BR/&amp;gt;

-- OPENROWSET DSN example
-- DSN points to the folder where .dbf files are.
/* Note this Example may fail if SQL Server is configured to use a local account.*/ 
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Tables;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country') 
go"?
-- SQL Server's QUOTED_IDENTIFIER has to be set to OFF.

SET QUOTED_IDENTIFIER OFF

-- OPENROWSET DSN-less example

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data\Testdata.dbc;

SourceType=DBC',

'select * from customer where country = "USA" order by city')

go

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data\Testdata.dbc;

SourceType=DBC',

'select * from customer where region="WA"')

go

Update openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data\Testdata.dbc;

SourceType=DBC',

'select * from customer where city = "Seattle"')

set region = "WW" 

go

-- check to verify which rows were updated

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data\Testdata.dbc;

SourceType=DBC',

'select * from customer where region="WW"') 

go

-- OPENROWSET DSN example

/* Note the DSN Example might fail if SQL Server is configured to use a local account.*/ 

select * from openrowset('MSDASQL',

'DSN=Visual FoxPro Database;

SourceDB=e:\VFP90\samples\data\Testdata.dbc;

SourceType=DBC',

'select * from customer where country = "USA" order by city')

go

/* sp_addlinkedserver examples */ 

-- sp_addlinkedserver example with DSN

/* You will need to make a DSN and point it to the Testdata database. 

Modify your code accordingly for differences in location or DBC name */ 

/* Note this Example may fail if SQL Server is configured to use a local account.*/ 

sp_addlinkedserver 'VFP Testdata Database With DSN', 

'', 

'MSDASQL',

'VFP System DSN'

go

sp_addlinkedsrvlogin 'VFP Testdata Database With DSN', FALSE, NULL, NULL, NULL

go 

SELECT *

FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ') 

go

-- We will set the region back to "WA" if it currently is "WW".

-- Update using OpenQuery

Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ') 

set region = "WA" 

go

-- Make sure that the region got updated.

SELECT *

FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ') 

go

/* SP_addlinkedserver example with DSN-less connection */ 

/* This example also depends on the sample files Testdata.dbc

Modify your code accordingly for differences in location or DBC name */ 

sp_addlinkedserver 'VFP Testdata Database With No DSN', 

'', 

'MSDASQL',

NULL,

NULL,

'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP90\samples\data\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'

go

sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL

go

SELECT *

FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by city') 

go

--====================================================

-- Using VFP 6.0 driver, read and update data from VFP sample dbf files

--====================================================

-- OPENROWSET DSN-less example

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data;

SourceType=DBF',

'select * from customer where country != "USA" order by country')

go

-- perform UPDATE

Update openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data;

SourceType=DBF',

'select * from customer where city = "Seattle"')

set region = "WW" 

go

-- verify update

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP90\samples\data;

SourceType=DBF',

'select * from customer where region = "WW"')

go

-- OPENROWSET DSN example

-- DSN points to the folder where .dbf files are.

/* Note this Example may fail if SQL Server is configured to use a local account.*/ 

select * from openrowset('MSDASQL',

'DSN=Visual FoxPro Tables; 

SourceDB=e:\VFP90\samples\data;

SourceType=DBF',

'select * from customer where country != "USA" order by country') 

go

您也可以使用 Visual FoxPro OLE DB 提供者來建立分散式查詢。這是慣用的技術。雖然此程式碼示範如何更新和刪除資料，但在分散式查詢中使用OLE DB提供者新增、更新（編輯）和刪除資料是不支援的。

下列 T-SQL 程式代碼範例示範如何設定和使用分散式查詢搭配 FoxPro 搭配 OpenQuery 和 OpenRowset 函式。在 SQL Server 2000 計算機上安裝 Visual FoxPro OLE DB 提供者之後，您可以在 SQL Query Analyzer 中測試此程式代碼。您需要根據需求變更資料來源名稱和 FoxPro 檔案的路徑。

 '/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc

'Modify your code accordingly for differences in location or DBC name */

--*====================================================

--* Using the DBC file, reading and updating data.

--*====================================================

--* A couple of OPENROWSET queries.

select * from openrowset('VFPOLEDB',

'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',

'select * from customer where country != "USA" order by country')

go

Select * from openrowset('VFPOLEDB',

'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',

'select * from customer where region="WA"')

go

--* Need to use an error trapping routine with the UPDATE and DELETE functions:

select * from

openrowset('VFPOLEDB',

'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

'Update Customer Set city = "SEATTLE" where region = "WA" ') 

go

declare @upderror int

select @upderror = @@error

print ''

if @upderror != 7357 and @upderror != 0

print 'Update failed with error '+convert(varchar(5),@upderror)

else

print 'Ignore the error above, the Update succeeded'

go

-- check to verify which rows were updated

select * from openrowset('VFPOLEDB',

'E:\VFP7junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

'select * from customer where region = "WA"')

go

--* Change the City field back to "Seattle".

select * from

openrowset('VFPOLEDB',

'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

'Update Customer Set city = "Seattle" where region = "WA" ') 

go

declare @upderror int

select @upderror = @@error

print ''

if @upderror != 7357 and @upderror != 0

print 'Update failed with error '+convert(varchar(5),@upderror)

else

print 'Ignore the error above, the Update succeeded'

go

--* The DELETE fucntion also causes an error, but the DELETE works.

select * from

openrowset('VFPOLEDB',

'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

'Delete from Customer where country = "Spain" ') 

go

declare @delerror int

select @delerror = @@error

print ''

if @delerror != 7357 and @delerror != 0

print 'Delete failed with error '+convert(varchar(5),@delerror)

else

print 'Ignore the error above, the Delete succeeded'

go

--* Check to see that the records are deleted.

Select * from openrowset('VFPOLEDB',

'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',

'select * from customer where country = "Spain"')

go

--* Here are some examples using the VFP OLE DB Provider to create Linked Servers.

--* Using sp_addlinkedserver to create the Linked Server.

sp_addlinkedserver @server='VFP_Linked_Server',

@srvproduct='Microsoft Visual FoxPro OLE DB Provider', 

@provider='VFPOLEDB',

@datasrc = 'E:\vfp7junk'

go

SELECT *

FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "Seattle"')

go

-- The Update command will update the table with the OPENQUERY function when using the 

-- linked server, but the same error 7357 error will occur.

select * from

OPENQUERY([VFP_Linked_Server],

'Update Customer Set city = "SEATTLE" where region = "WA" ') 

go

declare @upderror int

select @upderror = @@error

print ''

if @upderror != 7357 and @upderror != 0

print 'Update failed with error '+convert(varchar(5),@upderror)

else

print 'Ignore the error above, the Update succeeded'

go

-- Check and see if the City field is all uppercase with "SEATTLE".

SELECT *

FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where region = "WA"')

go

--* Let's check for how many records have the word "London" in the City field.

SELECT *

FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "London"')

go

-- We can also use the Delete command to remove records with the OPENQUERY function when using the 

-- linked server, but the same error 7357 error will occur.

select * from

OPENQUERY([VFP_Linked_Server],

'Delete from Customer where city = "London"') 

go

declare @delerror int

select @delerror = @@error

print ''

if @delerror != 7357 and @delerror != 0

print 'Delete failed with error '+convert(varchar(5),@delerror)

else

print 'Ignore the error above, the Delete succeeded'

go

/* SP_addlinkedserver example with DSN-less connection */

/* This example also depends on the sample files Testdata.dbc

Modify your code accordingly for differences in location or DBC name */

sp_addlinkedserver 'VFP Testdata Database With No DSN',

'',

'MSDASQL',

NULL,

NULL,

'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP8junk\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'

go

SELECT *

FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by country')

go

參考資料

如需設定和使用分散式查詢的詳細資訊，請參閱 sp_addlinkedserver《SQL 7.0 在線叢書》中的、OpenQuery、OpenRowset 和相關主題。

若要深入瞭解 FoxPro 和.dbf.dbc檔案，請參閱 FoxPro 產品檔。

共用方式為

使用 FoxPro .dbf 檔案執行 SQL Server 分散式查詢

摘要

其他相關資訊

參考資料

其他資源