Microsoft SQL Server
Настройка и конфигурация

работа с DBF формата FoxPro и Clipper(CDX)

Опубликовано: 24 мар 03
Рейтинг:

Автор: MiCe
Прислал:

Создание Linked server

EXEC sp_addlinkedserver 
        @server = 'FOX_ODBC', 
        @provider = 'MSDASQL', 
        @srvproduct = '',
        @provstr = 'Driver={Microsoft Visual FoxPro Driver}; 
UID=;SourceDB=C:\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Col
late=Russian;Null=No;Deleted=No'

EXEC  sp_addlinkedserver 
        @server = 'FOX_OLEDB', 
        @provider = 'VFPOLEDB',
        @srvproduct = '',
        @datasrc ='z:\',
        @provstr = 'Collating Sequence=RUSSIAN'

Примеры запросов
select * from FOX_OLEDB...[db\medbf] -- относительно datasrc 
select * from FOX_OLEDB...[\\srv\share\db\medbf]  -- UNC
select * from FOX_OLEDB...[c:\db\medbf]  -- local path
---------------------------

Так можно создавать и модифицировать dbf файлы.
select *
from OPENQUERY(FOXODBC,
'select * from \\srv\buh\existdbf ;create dbf \\srv\buh\newdbf (id c(10),name c(50)) ' 
) a

-- файл \\srv\buh\existdbf - пустышка!!!! но он должен существовать
-- лично я использую специально созданный для этого пустой dbf
-- собственно создание пррисзодит во второй команде
Идея состоит в том, чтобы "под прикрытием" запроса к фиктивной таблице передать Linked server-у на выполнение другие инструкции.

PS..
вот перечень команд которые поддерживает 'VFPOLEDB'( для ODBC for Visual FoxPro некоторые не поддерживаются)....
выдержка из из "хелпа" к VS .Net 7.0
--------------------------------------------
The Visual FoxPro OLE DB Provider supports the native Visual FoxPro language syntax for the following commands:

CREATE TABLE - SQL Command
Creates a table having the specified fields.
DELETE - SQL Command
Marks records for deletion.
DROP TABLE Command
Removes a table from the database specified with the data source and deletes it from disk.
INSERT - SQL Command
Appends a record to the end of a table that contains the specified field values.
SELECT - SQL Command
Retrieves data from one or more tables.
UPDATE - SQL Command
Updates records in a table with new values.
The Visual FoxPro Language Reference contains detailed information about the following supported commands:

ALTER TABLE - SQL Command
Programmatically modifies the structure of a table.
CREATE TABLE - SQL Command
Creates a table having the specified fields.
Using Data Definition Language (DDL)
You cannot include DDL in the following places:

In a batch SQL statement that requires a transaction
Following a previously executed statement that required a transaction if not in auto-commit mode and if your application has not yet called SQLTransact.
For example, if you want to create a temporary table, you should create the table before you begin the statement requiring a transaction. If you include the CREATE TABLE statement in a batch SQL statement that requires a transaction, the provider returns an error message.

DELETE - SQL Command
Marks records for deletion.
DELETE TAG Command
Removes a tag or tags from a compound index (.cdx) file.
DROP TABLE Command
Removes a table from the database specified with the data source and deletes it from disk.
INDEX Command
Creates an index file to display and access table records in a logical order.
INSERT - SQL Command
Appends a record to the end of a table that contains the specified field values.
SELECT - SQL Command
Retrieves data from one or more tables.
The Visual FoxPro OLE DB Provider supports the native Visual FoxPro language syntax for this command.

SET ANSI Command
Determines how comparisons between strings of different lengths are made with the = operator in Visual FoxPro SQL commands.
SET BLOCKSIZE Command
Specifies how disk space is allocated for the storage of memo fields.
SET COLLATE Command
Specifies a collation sequence for character fields in subsequent indexing and sorting operations.
SET DELETED Command
Specifies whether records marked for deletion are processed and whether they are available for use in other commands.
SET EXACT Command
Specifies the rules for comparing two strings of different lengths.
SET EXCLUSIVE Command
Specifies whether table files are opened for exclusive or shared use on a network.
SET NULL Command
Determines how null values are supported by the ALTER TABLE - SQL, CREATE TABLE - SQL, and INSERT - SQL commands.
SET PATH Command
Specifies a path for file searches.
Provider Remarks
If you issue SET PATH in a stored procedure, it will be ignored by the following functions and commands: SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE

If you issue SET PATH in a stored procedure and do not subsequently set the path back to its original state, other connections to the database will use the new path (because SET PATH is not scoped to data sessions).

If you want to create, select, or update tables in a directory other than that specified by the data source, specify the full path of the file with your command.

SET REPROCESS Command
Specifies how many times or for how long to lock a file or record after an unsuccessful locking attempt.
SET UNIQUE Command
Specifies whether records with duplicate index key values are maintained in an index file.
UPDATE - SQL Command
Updates records in a table with new values.
--------------
приведу еще синтаксис create table ( так как отличается от MS SQL DDL)

Creates a table having the specified fields.

CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
(FieldName1 FieldType [(nFieldWidth [, nPrecision])]
[NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]]
[DEFAULT eExpression1] [PRIMARY KEY | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS]
[, FieldName2 ...] [, PRIMARY KEY eExpression2 TAG TagName2
|, UNIQUE eExpression3 TAG TagName3]
[, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] REFERENCES TableName3 [TAG TagName5]]
[, CHECK lExpression2 [ERROR cMessageText2]])| FROM ARRAY ArrayName
Parameters
TableName1
Specifies the name of the table to create. The TABLE and DBF options are identical.
NAME LongTableName
Specifies a long name for the table. A long table name can be specified only when a database is open because long table names are stored in databases.
Long names can contain up to 128 characters and can be used in place of short file names in the database.

FREE
Specifies that the table will not be added to an open database. FREE isn't required if a database isn't open.
(FieldName1 FieldType [(nFieldWidth [, nPrecision])]
Specifies the field name, field type, field width, and field precision (number of decimal places), respectively.
A single table can contain up to 255 fields. If one or more fields allow null values, the limit is reduced by one to 254 fields.

FieldType is a single letter indicating the field's data type. Some field data types require that you specify nFieldWidth or nPrecision, or both.

The following table lists the values for FieldType and whether nFieldWidth and nPrecision are required.

FieldType nFieldWidth nPrecision Description
C n – Character field of width n
D – – Date
T – – DateTime
N n d Numeric field of width n with d decimal places
F n d Floating numeric field of width n with d decimal places
I – – Integer
B – d Double
Y – – Currency
L – – Logical
M – – Memo
G – – General

nFieldWidth and nPrecision are ignored for D, T, I, Y, L, M, G, and P types. nPrecision defaults to zero (no decimal places) if nPrecision isn't included for the N or F types. nPrecision defaults to the number of decimal places specified by the SET DECIMAL setting if nPrecision isn't included for the B type.

NULL
Allows null values in the field. If one or more fields can contain null values, the maximum number of fields the table can contain is reduced by one, from 255 to 254.
NOT NULL
Prevents null values in the field.
If you omit NULL and NOT NULL, the current setting of SET NULL determines if null values are allowed in the field. However, if you omit NULL and NOT NULL and include the PRIMARY KEY or UNIQUE clause, the current setting of SET NULL is ignored and the field defaults to NOT NULL.

CHECK lExpression1
Specifies a validation rule for the field. lExpression1 can be a user-defined function. Note that when a blank record is appended, the validation rule is checked. An error is generated if the validation rule doesn't allow for a blank field value in an appended record.
ERROR cMessageText1
Specifies the error message Visual FoxPro displays when the validation rule specified with CHECK generates an error. The message is displayed only when data is changed within a Browse window or Edit window.
DEFAULT eExpression1
Specifies a default value for the field. The data type of eExpression1must be the same as the field's data type.
PRIMARY KEY
Creates a primary index for the field. The primary index tag has the same name as the field.
UNIQUE
Creates a candidate index for the field. The candidate index tag has the same name as the field. For more information about candidate indexes, see Setting a Primary or Candidate Index.
Note Candidate indexes (created by including the UNIQUE option in CREATE TABLE or ALTER TABLE – SQL) are not the same as indexes created with the UNIQUE option in the INDEX command. An index created with the UNIQUE option in the INDEX command allows duplicate index keys; candidate indexes do not allow duplicate index keys. See INDEX for additional information on its UNIQUE option.
Null values and duplicate records are not permitted in a field used for a primary or candidate index. However, Visual FoxPro will not generate an error if you create a primary or candidate index for a field that supports null values. Visual FoxPro will generate an error if you attempt to enter a null or duplicate value into a field used for a primary or candidate index.

REFERENCES TableName2 [TAG TagName1]
Specifies the parent table to which a persistent relationship is established. If you omit TAG TagName1, the relationship is established using the primary index key of the parent table. If the parent table does not have a primary index, Visual FoxPro generates an error.
Include TAG TagName1 to establish a relation based on an existing index tag for the parent table. Index tag names can contain up to 10 characters.

The parent table cannot be a free table.

NOCPTRANS
Prevents translation to a different code page for character and memo fields. If the table is converted to another code page, the fields for which NOCPTRANS has been specified are not translated. NOCPTRANS can only be specified for character and memo fields. This will create what appears in the Table Designer as Character (binary) and Memo (binary) data types.
The following example creates a table named MYTABLE containing two character fields and two memo fields. The second character field CHAR2 and the second memo field MEMO2 include NOCPTRANS to prevent translation.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,;
memo1 M, memo2 M NOCPTRANS)
PRIMARY KEY eExpression2 TAG TagName2
Specifies a primary index to create. eExpression2 specifies any field or combination of fields in the table. TAG TagName2 specifies the name for the primary index tag that is created. Index tag names can contain up to 10 characters.
Because a table can have only one primary index, you cannot include this clause if you have already created a primary index for a field. Visual FoxPro generates an error if you include more than one PRIMARY KEY clause in CREATE TABLE.

UNIQUE eExpression3 TAG TagName3
Creates a candidate index. eExpression3 specifies any field or combination of fields in the table. However, if you have created a primary index with one of the PRIMARY KEY options, you cannot include the field that was specified for the primary index. TAG TagName3 specifies a tag name for the candidate index tag that is created. Index tag names can contain up to 10 characters.
A table can have multiple candidate indexes.

FOREIGN KEY eExpression4 TAG TagName4 [NODUP]
Creates a foreign (non-primary) index, and establishes a relationship to a parent table. eExpression4 specifies the foreign index key expression and TagName4 specifies the name of the foreign index key tag that is created. Index tag names can contain up to 10 characters. Include NODUP to create a candidate foreign index.
You can create multiple foreign indexes for the table, but the foreign index expressions must specify different fields in the table.

REFERENCES TableName3 [TAG TagName5]
Specifies the parent table to which a persistent relationship is established. Include TAG TagName5 to establish a relation based on an index tag for the parent table. Index tag names can contain up to 10 characters. If you omit TAG TagName5, the relationship is established using the parent table's primary index key by default.
CHECK eExpression2 [ERROR cMessageText2]
Specifies the table validation rule. ERROR cMessageText2 specifies the error message Visual FoxPro displays when the table validation rule is executed. The message is displayed only when data is changed within a Browse window or Edit window.
FROM ARRAY ArrayName
Specifies the name of an existing array whose contents are the name, type, precision, and scale for each field in the table. The contents of the array can be defined with the AFIELDS( ) function.
Remarks
The new table is opened in the lowest available work area, and can be accessed by its alias. The new table is opened exclusively, regardless of the current setting of SET EXCLUSIVE.

If a database is open and you don't include the FREE clause, the new table is added to the database. You cannot create a new table with the same name as a table in the database.

If a database isn't open when you create the new table, including the NAME, CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, or REFERENCES clauses generates an error.

Note that the CREATE TABLE syntax uses commas to separate certain CREATE TABLE options. Also, the NULL, NOT NULL, CHECK, DEFAULT, PRIMARY KEY and UNIQUE clause must be placed within the parentheses containing the column definitions.

Комментарии


  • И мне пригодилось, спасибо
    в дополнении здесь линк на dbc показан:
    http://www.mydatabasesupport.com/forums/sqlserver-odbc/187112-import-visual-fox-pro-dbfs-ms-sql-server.html

  • Я это раньше делал со стороны клиента, писал на фоксе ехе-шник который коннектился к серверу и сливал в файлы нужные таблицы.

  • Очень нужная оказалась статья. И главное очень ясно написано.

  • Классная статья. Этот материал рыл по весм инету и нигде не видел

  • Суперская статья. Сам бы фиг догадался. И нигде больше ничего подобного не встретил.



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Настройка и конфигурация / работа с DBF формата FoxPro и Clipper(CDX)