Просмотров: 93541

Связанные сервера поставщики

Закрыть ... [X]

Дата публикации: 09.12.2013

Обновлено: 31.10.2016

В этой статье мы пошагово рассмотрим все этапы регистрации связанного сервера MSSQL 2012 к базе данных Firebird/InterBase и работу с ним.

  • MSSQL 2012 Express R2 x64 (English). Windows Server 2008 R2 x64 (Russian) c English UI.
  • Firebird 2.5.3. 26709 x64 SuperClassic.
    • Тестовая база данных: employee.fdb (3 диалект) из поставки FB2.5

1. Установите IBProvider на сервер с MSSQL

Используйте инсталляторы правильной разрядности:

  • Для 64-битного MSSQL нужно установить 64-битный IBProvider
  • Для 32-битного MSSQL нужно установить 32-битный IBProvider

Рекомендации:

  • В случае 64-битной операционной системы, настоятельно рекомендуется установить оба провайдера – 32 и 64 бита.
  • Для работы с MSSQL не рекомендуется использовать «IBProvider Lite».
  • Установите так же «Samples» и «Binaries of examples» — они понадобятся при проверке вашей FB/IB базы данных. Потом их можно будет деинсталлировать.

2. Установка клиента InterBase/Firebird на сервер с MSSQL

Начиная с версии v3.27, IBProvider может напрямую подключаться к Firebird через TCP/IP без помощи серверного клиента (fbclient.dll). Для этого нужно указать в строке подключения «dbclient_type=fb.direct».

Модули с серверными клиентами:

32-битный клиент 64-битный клиент InterBase gds32.dll ibclient64.dll Firebird fbclient.dll (32-битная версия!) fbclient.dll (64-битная версия!)

Вы можете найти эти DLL в дистрибутивах вашего InterBase/Firebird сервера.

В случае Firebird, постарайтесь не перепутать файлы.

Обратите внимание, что серверные клиенты используют дополнительные DLL «Visual C++». Вы можете скачать установочные файлы «Microsoft Visual C++ Redistributable Package» с сайта IBProvider-a.

Варианты установки клиента

1. Скопировать DLL в системный каталог Windows.

Системный каталог для 32-битных DLL Системный каталог для 64-битных DLL 32-битная Windows C:\Windows\System32 64-битная Windows C:\Windows\SysWOW64 C:\Windows\System32

В случае 64-битной Windows, рекомендуется установить обоих клиентов – для 32-х бит и 64-х бит.

2. Скопировать DLL в отдельный каталог.

Пример для 64-битного клиента InterBase:

  • На несистемном диске создайте каталог (например) d:\InterBaseClientbit
  • Скопируйте в этот каталог ibclient64.dll
  • Проверьте права доступа (на уровне файловой системы) к ibclient64.dll

В случае 64-битной Windows, рекомендуется установить (в разные каталоги) обоих клиентов – для 32-х бит и 64-х бит.

3. Проверьте корректность установки провайдера и серверного клиента

Если у вас нет опыта установки и настройки IBProvider-а и серверного клиента для работы с InterBase/Firebird, то имеет смысл проверить этап (1) и (2) без участия MSSQL.

Пример проверки для «Windows 2008 R2 64bit».

На компьютере с MSSQL cоздайте файл «test_connection.vbs» со следующим VBS-скриптом:

option explicit dim cn set cn=createobject("ADODB.Connection") cn.Provider="LCPI.IBProvider.3" 'fbclient.dll from system directory of Windows cn.ConnectionString="location=HOME2:e:\database\employee.fdb;user id=sysdba;password=masterkey;dbclient_library=fbclient.dll" call cn.Open() wscript.echo "Provider: "&cn.Properties("Provider Name").Value&" "&cn.Properties("Provider Version").Value wscript.echo "OK!"

NOTE: в строке подключения должны быть ваши значения свойств «location», «user id», «password» и «dbclient_library». Описание всех свойств инициализации можно посмотреть здесь.

Запустите ваш файл «test_connection.vbs» из консоли (cmd.exe).

Тестирование 64-битного доступа к базе данных:

Тестирование 32-битного доступа к базе данных:

Добейтесь того, чтобы этот скрипт начал отрабатывать без ошибок.

4. Проверка подключаемой базы данных

Перед тем как подключать вашу FB/IB базу данных к MSSQL, нужно проверить отсутствие ошибок в её системных таблицах.

В противном случае – IBProvider или MSSQL могут отказаться работать с вашей базой данных.

В поставке дистрибутива IBProvider-а есть VBS-скрипт «prog_check_system_tables.wsf», который находит часть известных ошибок.

Для установки этого скрипта, в инсталляторе нужно разрешить установку «Examples» и «Binaries of examples».

Скрипт находится в каталоге: «<ProgramFiles>\LCPI\IBProvider.3\TestCode\ActiveX\IBP\test_system\prog»

Скрипт запускается из консоли (cmd.exe) через «cscript.exe».

Параметры запуска, которые будут указаны для проверки нашей базы данных:

/db_location «home2:e:\database\employee.fdb» /db_user sysdba /db_password masterkey
/db_client fbclient.dll

В тестовой базе employee.fdb, которую мы будем подключать к MSSQL, этот скрипт нашел одну ошибку:

Основным результатом работы скрипта является пара файлов – «check_system_table.log» и «check_system_table.sql».

Находим в «check_system_table.log» описание ошибки:

46. [EMPLOYEE] — {TABLE}
1. [EMP_NO]
domain [EMPNO] already processed
………
10. [SALARY]
domain [SALARY] already processed
11. [FULL_NAME]
domain [RDB]
VARCHAR
charset_id: 0
charset_name: [NONE]
ERROR: Wrong char count: 0. BytesPerChar: 1. FieldLength: 37

FULL_NAME – это вычисляемая текстовая колонка. По непонятной причине, в описании этой колонки не определено количество символов.

В сгенерированном файле «check_system_table.sql» предлагается скрипт для исправления ошибки:

/1 ——————————— /
/
VARCHAR DOMAIN USED AT {TABLE}[EMPLOYEE].[FULL_NAME]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [37]
CURRENT CHAR_LENGTH: [0]
/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=37
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME=’EMPLOYEE’ AND
RDB$FIELD_NAME=’FULL_NAME’) AND
RDB$CHARACTER_LENGTH=0;
/ ——————————— /
COMMIT;
//

Внимательно изучаем сгенерированные запросы для исправления базы данных и накатываем этот скрипт на нашу базу данных (например, через IBExpert).

Снова запускаем «prog_check_system_tables.wsf». На этот раз скрипт не нашел ошибок. Но это не значит, что их там нет. В процессе работы связанного сервера, возможно будут выявлены другие проблемы.

Внимание!
  • В случае вашей базы данных, все может оказаться гораздо сложнее.
  • Сначала потренируйтесь на тестовой базе данных.
  • Перед исправлением ошибок, рекомендуется сделать ПОЛНЫЙ бакап вашей базы данных.

5. Сконфигурируйте координатор распределенных транзакций (MSDTC)

Для выполнения запросов через связанный сервер нужна постоянно запущенная служба MSDTC.

  1. Откройте консоль для управления службами на сервере с MSSQL
  2. Найдите службу координатора
  3. Установите ей автоматический запуск и запустите её.

1. Запустите «SQL Server Management Studio»

2. Подключитесь к MSSQL, на котором будет настраиваться связанный сервер

3. Настройте использование OLE DB провайдера

Найдите IBProvider в списке доступных провайдеров. Если его там нет, то попробуйте обновить список (Refresh). Если не помогло — значит на первом этапе вы не допустили ошибки.

Откройте диалог настройки свойств:

Поставьте галочки напротив следующих настроек:

  • Dynamic Parameters
  • Nested queries
  • Allow In Process (обязательно!)
  • Supports ‘Like’ operations

Закройте диалог (OK).

4. Создание связанного сервера

Перейдите на элемент «Linked Servers» и выберите в меню пункт «New Linked Server…»:

Откроется диалог для настройки связанного сервера.

Заполните поля на странице основных настроек.

«Linked Server»

Имя связанного сервера, которое будет использоваться в запросах. В нашем случае это будет FB_EMPL.

Provider

Выберите «LCPI OLE DB Provider for InterBase [v3]».

«Product Name», «Data Source»

Укажите в этих полях любой текст. В нашем случае, здесь будет продублировано имя связанного сервера – «FB_EMPL».

«Provider String»

Здесь определяются свойства для настройки работы провайдера. Рекомендуемая настройка:

Свойство Значение Заметка auto_commit true nested_trans true truncate_char false dbtime_rules 1 Только для MSSQL 2008, 2012. support_odbc_query true asynch_fetch 2 Только для MSSQL 2005, 2008, 2012. Использовать для ускорения загрузки больших множеств. dbclient_library gds32.dll
ibclient64.dll
fbclient.dll Укажите имя или полный путь к DLL серверного клиента. ctype Укажите имя кодовой страницы для текстовых данных вашей БД. ctype_none Для БД, созданных с использованием кодовой страницы NONE. Укажите кодовую страницу ваших текстовых данных. schema_ldr_cfg__check_constraints 0 Запрещаем публикацию сведений о CHECK-ограничениях в схемах метаданных. ignore_err_param 4 Ослабляем проверку параметров запросов. Необходимо для запуска хранимых процедур.

В нашем случае, строка подключения выглядит так:

auto_commit=true;nested_trans=true;truncate_char=false;dbtime_rules=1;support_odbc_query=true;asynch_fetch=2;
dbclient_library=fbclient.dll;ctype_none=ASCII;schema_ldr_cfg__check_constraints=0;ignore_err_param=4;

Вы можете проверить вашу строку подключения в тестовом скрипте из пункта «Проверка корректности установки провайдера и серверного клиента».

«Location»

Путь к подключаемой базе данных. В нашем случае база данных находится на сервере HOME2 в файле «e:\database\employee.fdb»:

HOME2:e:\database\employee.fdb

Переключитесь на страницу «Security» и укажите логии и пароль подключения к базе данных:

Переключитесь на страницу «Server Options»:

Рекомендуется установить свойства:

  • «Collation Compatible»=true
  • «RPC»=true
  • «RPC Out»=true

На этом настройка связанного сервера завершена – нажимаем «OK».

Если все было настроено правильно, то связанный сервер будет создан и появится в дереве объектов:

1. В SQL запросах, выполняемых через связанный сервер, следует указывать имена таблиц и колонок в верхнем регистре. Соответственно, в вашей базе данных Firebird/InterBase названия всех объектов, с которыми вы будете работать через связанный сервер, должны быть «upper-case».

В случае «mixed-case» имен объектов базы данных Firebird/InterBase, у вас возникнут проблемы связанные с ошибками в генераторе гетерогенных запросов MSSQL. Причина: в некоторых случаях, MSSQL генерирует запросы без использования кавычек для квотирования имен.

2. Для упрощения кода, будет использоваться уровень связанные сервера поставщики изоляции транзакций «по-умолчанию»: read_committed.

Нажмите «New Query» для перехода в окно редактирования и запуска SQL скриптов.

Запрос на выборку данных с использованием явной транзакции

begin distributed transaction; select from FB_EMPL...EMPLOYEE; commit;

Результат работы:

Если из строки подключения убрать «schema_ldr_cfg__check_constraints=0», то запрос к таблице EMPLOYEE будет генерировать следующее сообщение:

Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ‘)’.

(42 row(s) affected)

Это связанно с тем, что MSSQL, на стадии подготовки запроса, запрашивает у IBProvider-а различную информацию о таблице EMPLOYEE. В том числе, он получает информацию о CHECK-ограничении, которое и провоцирует данное сообщение:

salary >= (SELECT min_salary FROM job WHERE job.job_code = employee.job_code AND job.job_grade = employee.job_grade AND job.job_country = employee.job_country) AND salary <= (SELECT max_salary FROM job WHERE job.job_code = employee.job_code AND job.job_grade = employee.job_grade AND job.job_country = employee.job_country)

Указав в строке подключения «schema_ldr_cfg__check_constraints=0», мы полностью исключаем из схем метаданных сведения о CHECK-ограничениях. Как следствие – устраняем причины для такого рода сообщений.

Свойство инициализации «schema_ldr_cfg__check_constraints» доступно начиная с IBProvider v3.18.

Выборка данных с использованием автоматической транзакции

Поскольку в нашей строке подключения было указано «auto_commit=true», то мы можем опустить явный запуск и коммит транзакции.

select from FB_EMPL...EMPLOYEE;

Результат работы запроса будет аналогичным – 42 строки.

В промышленном коде, рекомендуется всегда явно стартовать и завершать транзакции.

Уровень изоляции «автоматической транзакции» определяется в свойстве инициализации «auto_commit_level».

Выборка данных с использованием параметров

begin distributed transaction; declare @first_name as varchar(32); set @first_name='Scott'; select from FB_EMPL...EMPLOYEE empl where empl.FIRST_NAME=@first_name; commit;

Результат работы:

Модификация данных

При выполнении SQL-запросов INSERT, UPDATE и DELETE, MSSQL пытается стартовать вложенную транзакцию. IBProvider реализует поддержку вложенных транзакций через точки сохранения Firebird/InterBase. По-умолчанию эта поддержка отключена. Для её включения нужно указать в строке подключения «nested_trans=true».

Firebird поддерживает точки сохранения начиная с версии 1.5.

InterBase поддерживает точки сохранения начиная с версии 7.1.

Если вы работает со старой версией FB/IB, в которой отсутствует поддержка точек сохранения, то надо указать в начале скрипта:

SET XACT_ABORT ON;

Или вообще отказаться от явного управления транзакциями. В последнем случае, в строке подключения должно быть указано «auto_commit=true».

Вставка нового ряда

begin distributed transaction; insert into FB_EMPL...COUNTRY (COUNTRY,CURRENCY) values ('Mars','Snickers'); commit transaction;

Результат работы:

Обновление ряда

begin distributed transaction; declare @country varchar(32); set @country='Mars'; update FB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=@country; commit;

Результат работы:

Удаление ряда

begin distributed transaction; declare @country varchar(32); set @country='Mars'; delete from FB_EMPL...COUNTRY where COUNTRY=@country; commit;

Результат работы:

Выполнение скрипта

begin distributed transaction; declare @country varchar(32); set @country='Mars'; insert into FB_EMPL...COUNTRY (COUNTRY, CURRENCY) values (@country,'Mars'); select from FB_EMPL...COUNTRY where COUNTRY=@country; update FB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=@country; select from FB_EMPL...COUNTRY where COUNTRY=@country; delete from FB_EMPL...COUNTRY where COUNTRY=@country; select from FB_EMPL...COUNTRY where COUNTRY=@country; commit;

Результат работы:

Создание VIEW на основе связанного сервера

Создадим в тестовой базе MSSQL (с названием TEST) представление (VIEW) для таблицы COUNTRY:

use TEST; go create view V_COUNTRY (COUNTRY,CURRENCY) as select COUNTRY, CURRENCY from FB_EMPL...COUNTRY; go

Теперь выполним выборку из этого представления:

use TEST; go select from V_COUNTRY; go

Результат работы:

Давайте убедимся, что V_COUNTRY будет работать в рамках явной транзакции:

use TEST; go begin distributed transaction; insert into FB_EMPL...COUNTRY (COUNTRY,CURRENCY) values('Russia', 'Ruble'); select from V_COUNTRY where COUNTRY='Russia'; delete from FB_EMPL...COUNTRY where COUNTRY='Russia'; select from V_COUNTRY where COUNTRY='Russia'; commit;

Результат работы:

Выборки из V_COUNTRY «видят» изменения в FB_EMPL…COUNTRY.

Вызов хранимой процедуры

У FB/IB есть два типа хранимых процедур:

  • Возвращающие результат в виде множества.
  • Возвращающие результат через OUT-параметры.

В обоих случаях, вы используете унифицированный синтаксис вызова:

EXEC name [param [,param …]];

Все остальное за вас сделает IBProvider.

Выполнение хранимой процедуры возвращающей множество

В employee.fdb есть хранимая процедура, которая возвращает идентификаторы проектов сотрудника:

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT) RETURNS (PROJ_ID CHAR(5)) AS BEGIN FOR SELECT PROJ_ID FROM EMPLOYEE_PROJECT WHERE EMP_NO = :EMP_NO INTO :PROJ_ID DO SUSPEND; END

Получим список проектов для сотрудника «Bruce Young»:

begin distributed transaction; declare @emp_no integer; set @emp_no=(select EMP_NO from FB_EMPL...EMPLOYEE where FIRST_NAME='Bruce' and LAST_NAME='Young'); print 'emp_no='+convert(varchar(32),@emp_no); exec FB_EMPL...GET_EMP_PROJ @emp_no; commit;

Результат работы:

Выполнение хранимой процедуры возвращающей OUT-параметры

В employee.fdb отсутствуют хранимые процедуры, возвращающие OUT-параметры. Поэтому создадим собственную процедуру, которая будет вычислять разность двух INTEGER-чисел:

CREATE PROCEDURE SP_SUB(A INTEGER, B INTEGER) RETURNS (RESULT INTEGER) AS BEGIN RESULT=A-B; END

Вызываем эту хранимую процедуру через Linked Server:

begin distributed transaction; declare @A integer; declare @R integer; set @A=4; exec FB_EMPL...SP_SUB @A, 1, @R output; print 'R='+convert(varchar(32),@R); commit;

Результат работы:

Выполнение запросов через инструкцию «EXEC AT»

Для запроса к связанному серверу, который не поддерживается MSSQL, можно воспользоваться инструкцией EXEC:

{ EXEC | EXECUTE }
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + …n ]
        [ { , { value | @variable [ OUTPUT ] } } [ …n ] ]
    ) 
    [ AS { LOGIN | USER } = ' name ' ]
    [ AT linked_server_name ]
[;]

Через «exec(…) at» можно выполнить любые запросы, которые поддерживаются IB/FB, включая DDL и DML запросы с параметрами. Например, запрос «UPDATE RETURNING»:

begin distributed transaction; declare @emp_no bigint; set @emp_no=(select EMP_NO from FB_EMPL...EMPLOYEE where FIRST_NAME='Robert' and LAST_NAME='Nelson'); print 'emp_no='+convert(varchar(32),@emp_no); declare @fn varchar(32); exec('update EMPLOYEE set FIRST_NAME=upper(FIRST_NAME) where EMP_NO=? returning NEW.FIRST_NAME', @emp_no, @fn OUTPUT) at FB_EMPL; print 'first_name='+@fn; rollback;

Результат работы:

Для работы с OUTPUT параметрами требуется IBProvider с версией не ниже 3.19 (сборка 15830). В строке подключения должно быть указано «ignore_err_param=4».

Уровни изоляций транзакций

По умолчанию, распределенная транзакция будет иметь уровень изоляции «READ COMMITTED». Вы можете указать другой уровень изоляции, используя команду:

SET TRANSACTION ISOLATION LEVEL <isolation_level_name>;

В случае распределенных транзакций, поддерживаются следующие уровни изоляции:

  • READ COMMITTED
  • REPEATABLE READ

В промышленном коде рекомендуется использовать уровень изоляции «REPEATABLE READ».

Работа в транзакции с уровнем изоляции «READ COMMITTED»

set transaction isolation level READ COMMITTED; begin distributed transaction; select from FB_EMPL...EMPLOYEE; commit;

Работа в транзакции с уровнем изоляции «REPEATABLE READ»

set transaction isolation level REPEATABLE READ; begin distributed transaction; select from FB_EMPL...EMPLOYEE; commit;

Дата публикации: 09.12.2013. Права на материал принадлежат: IBProvider. При перепечатке ссылка на сайт http://www.ibprovider.com/rus обязательна.


Источник: http://www.ibprovider.com/rus/documentation/mssql_2012.html


Поделись с друзьями



Рекомендуем посмотреть ещё:



Связанные серверы (ядро СУБД ) Microsoft Docs Плетеная мебель modesto

Связанные сервера поставщики Связанные сервера поставщики Связанные сервера поставщики Связанные сервера поставщики Связанные сервера поставщики Связанные сервера поставщики

ШОКИРУЮЩИЕ НОВОСТИ