Faça SQL Server consultas distribuídas com arquivos foxpro .dbf
Este artigo apresenta como executar uma consulta distribuída SQL Server para recuperar dados dos arquivos FoxPro .dbc e .dbf usando o Driver ODBC do VFP ou o Provedor OLE DB do VFP.
Versão original do produto: Visual FoxPro
Número de KB original: 207595
Resumo
Este artigo demonstra como executar uma consulta distribuída SQL Server para recuperar dados do FoxPro .dbc e .dbf arquivos usando o Driver ODBC do VFP ou o Provedor OLE DB do VFP.
Mais informações
O Microsoft SQL Server 2000 fornece a capacidade de executar consultas em provedores OLE DB. Essa consulta é feita usando as OpenQueryOpenRowset funções transact-SQL ou usando uma consulta com nomes de quatro partes, incluindo um nome de servidor vinculado.
Por exemplo:
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
Você deve usar o provedor OLE DB da Microsoft para ODBC (MSDASQL) e o driver ODBC do Visual FoxPro para configurar um servidor vinculado para executar consultas distribuídas no FoxPro .dbc e .dbf nos arquivos. Não há suporte para o uso do Provedor OLEDB do Jet com o FoxPro. O driver ODBC do VFP não é seguro para threads. Como SQL Server é multi-threaded, o Driver ODBC do VFP pode causar problemas em algumas circunstâncias. Se for possível, recomendamos usar o Provedor OLE DB do VFP para se conectar aos dados SQL Server.
O exemplo de código T-SQL a seguir demonstra como configurar e usar consultas distribuídas com o FoxPro com funções OpenQuery e OpenRowset. Ele também demonstra como atualizar uma tabela foxpro remota de SQL Server 2000. Você pode testar esse código no Analisador de Consultas SQL depois de instalar o driver ODBC do Visual FoxPro em um computador SQL Server 2000. Você precisará alterar os nomes da fonte de dados e o caminho para os arquivos FoxPro conforme apropriado:
/* 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 = "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:\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<BR/>
-- 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
Você também pode usar o Provedor OLE DB do Visual FoxPro para criar uma consulta distribuída. É a tecnologia preferida a ser usada. Embora esse código mostre como atualizar e excluir dados, não há suporte para adicionar, atualizar (editar) e excluir dados em uma consulta distribuída usando o Provedor OLE DB.
O exemplo de código T-SQL a seguir demonstra como configurar e usar a consulta distribuída com o FoxPro com OpenQuery e OpenRowset funções. Você pode testar esse código no Analisador de Consultas SQL depois de instalar o Provedor OLE DB do Visual FoxPro em um computador SQL Server 2000. Você precisará alterar os nomes da fonte de dados e o caminho para os arquivos FoxPro conforme apropriado:
'/* 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
Referências
Para obter mais detalhes sobre como configurar e usar consultas distribuídas, confira sp_addlinkedserver, OpenQuery, OpenRowset e tópicos relacionados no SQL 7.0 Books Online.
Para saber mais sobre a FoxPro e .dbf.dbc os arquivos, consulte a documentação do produto FoxPro.