用VFP的DBF进行SQLServer的分布式查询
点击次数：27 次 发布日期：2008-11-22 13:38:38 作者：源代码网
|
本文演示如何执行一个 SQL Server 分布式查询来从 FoxPro .dbc 和 .dbf 文件获取数据. 更多信息 Microsoft SQL Server 7.0 提供了执行基于 OLE DB providers 的查询的能力. 这是靠使用 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 driver 来 设置一个已连接服务器 (linked server) 来执行基于 FoxPro .dbc 和 .dbf 文件的分布式查询. 不支持用 Jet OLEDB Provider 和 FoxPro. 以下 T-SQL 示例代码演示如何用 OpenQuery 和 OpenRowset 函数设置和使用分布式查询 FoxPro. 它也演示了如何从 SQL Server 更新一个远程 FoxPro 表. 你可以在一个 SQL Server 7.0 机器上安装了 Visual FoxPro ODBC driver 后, 在 SQL Query Analyzer 中测试这些代码 . 你需要改变数据源名和路径到适当的 FoxPro 文件: /* OPENROWSET 和 OPENQUERY 示例. 以 VFP 通过 ODBC OLE DB provider */ /* 这些 OPENROWSET 示例依赖于示例文件 VFP98dataTestdata.dbc 如果你的数据在不同的位置请修改你的代码 */ ==================================================== 使用 DBC 文件 , 读和更新 ==================================================== OPENROWSET DSN-less 示例 select * from openrowset("MSDASQL", "Driver=Microsoft Visual FoxPro Driver; SourceDB=e:VFP98dataTestdata.dbc; SourceType=DBC", "select * from customer where country != "USA" order by country") go select * from openrowset("MSDASQL", "Driver=Microsoft Visual FoxPro Driver; SourceDB=e:VFP98dataTestdata.dbc; SourceType=DBC", "select * from customer where region="WA"") go Update openrowset("MSDASQL", "Driver=Microsoft Visual FoxPro Driver; SourceDB=e:VFP98dataTestdata.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:VFP98dataTestdata.dbc; SourceType=DBC", "select * from customer where region="Seattle"") go OPENROWSET DSN 示例 /* 注意：如果 SQL Server 是配置为使用本地帐号 DSN 示例可能失败.*/ select * from openrowset("MSDASQL", "DSN=Visual FoxPro Database; SourceDB=e:VFP98dataTestdata.dbc; SourceType=DBC", "select * from customer where country != "USA" order by country") go /* sp_addlinkedserver 示例 */ sp_addlinkedserver example with DSN /* 你需要生成一个 DSN 并使它指向 Testdata 数据库. 修改你的代码以反映 DBC 位置 */ /* 注意：如果 SQL Server 是配置为使用本地帐号 DSN 示例可能失败.*/ 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 = "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:VFP98dataTestdata.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 ==================================================== 使用 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:VFP98data; SourceType=DBF", "select * from customer where country != "USA" order by country") go perform UPDATE Update openrowset("MSDASQL", "Driver=Microsoft Visual FoxPro Driver; SourceDB=e:VFP98data; 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:VFP98data; SourceType=DBF", "select * from customer where region = "WA"") 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:VFP98data; SourceType=DBF", "select * from customer where country != "USA" order by country") 源代码网供稿. |
