Ne SQL Server des requêtes distribuées avec des fichiers .dbf FoxPro

Article
09/08/2020

Cet article explique comment effectuer une requête distribuée SQL Server pour récupérer des données à partir de fichiers .dbc et .dbf FoxPro à l’aide du pilote ODBC VFP ou du fournisseur OLE DB VFP.

Version d’origine du produit : Visual FoxPro
Numéro de la base de connaissances d’origine : 207595

Résumé

Cet article explique comment effectuer une requête distribuée SQL Server pour récupérer des données à partir de FoxPro .dbc et .dbf des fichiers à l’aide du pilote ODBC VFP ou du fournisseur OLE DB VFP.

Informations supplémentaires

Microsoft SQL Server 2000 permet d’effectuer des requêtes sur des fournisseurs OLE DB. Cette requête est effectuée à l’aide des OpenQuery fonctions Transact-SQL ou OpenRowset ou à l’aide d’une requête avec des noms en quatre parties, y compris un nom de serveur lié.

Par exemple :

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

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

Vous devez utiliser le fournisseur Microsoft OLE DB pour ODBC (MSDASQL) et le pilote ODBC Visual FoxPro pour configurer un serveur lié afin d’effectuer des requêtes distribuées sur FoxPro .dbc et .dbf les fichiers. L’utilisation du fournisseur Jet OLEDB avec FoxPro n’est pas prise en charge. Le pilote ODBC VFP n’est pas thread-safe. Étant donné que SQL Server est multithread, le pilote ODBC VFP peut entraîner des problèmes dans certaines circonstances. Si possible, nous vous recommandons d’utiliser le fournisseur OLE DB VFP pour se connecter aux données SQL Server.

L’exemple de code T-SQL suivant montre comment configurer et utiliser des requêtes distribuées avec FoxPro avec les fonctions OpenQuery et OpenRowset. Il montre également comment mettre à jour une table FoxPro distante à partir de SQL Server 2000. Vous pouvez tester ce code dans l’Analyseur de requêtes SQL après avoir installé le pilote ODBC Visual FoxPro sur un ordinateur SQL Server 2000. Vous devez modifier les noms des sources de données et le chemin d’accès aux fichiers FoxPro selon les besoins :

/* 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

Vous pouvez également utiliser le fournisseur OLE DB Visual FoxPro pour créer une requête distribuée. Il s’agit de la technologie préférée à utiliser. Bien que ce code indique comment mettre à jour et supprimer des données, l’ajout, la mise à jour (modification) et la suppression de données dans une requête distribuée à l’aide du fournisseur OLE DB ne sont pas pris en charge.

L’exemple de code T-SQL suivant montre comment configurer et utiliser une requête distribuée avec FoxPro avec OpenQuery et OpenRowset des fonctions. Vous pouvez tester ce code dans l’Analyseur de requêtes SQL après avoir installé le fournisseur OLE DB Visual FoxPro sur un ordinateur SQL Server 2000. Vous devez modifier les noms des sources de données et le chemin d’accès aux fichiers FoxPro selon les besoins :

 '/* 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

References

Pour plus d’informations sur la configuration et l’utilisation des requêtes distribuées, consultez sp_addlinkedserver, OpenQuery, OpenRowset et les rubriques associées dans la documentation en ligne de SQL 7.0.

Pour en savoir plus sur FoxPro et .dbf les fichiers et .dbc , reportez-vous à la documentation du produit FoxPro.

Ne SQL Server des requêtes distribuées avec des fichiers .dbf FoxPro

Résumé

Informations supplémentaires

References

Ressources supplémentaires