Удаленное подключение к внешним базам данных из личного кабинета - MySQL, PostgreSQL

Работа с внешней БД из MS Sql Server на примере MySQL. 

Решение продемонстрировано на демостенде Подключение к MySQL

Данная возможность позволяет управлять данными из кабинетов Falcon Space не только из базы данных SQL Server, но и из других СУБД. Здесь мы рассмотрим пример для MySQL.  

На данный момент подключение к внешним базам, отличным от MS Sql Server, доступно только в Windows версии SQL Server (реализуется на VPS Windows Server, на Win хостинге работать не будет).

Отличие использования от стандартного использования будет только в написании SQL запросов (будет задействована возможность openquery). 

Настройка коннектора

На сервере устанавливаем ODBC коннектор MySQL: https://www.mysql.com/products/connector/

Ссылка для postgresql (внизу списка последние версии): https://www.postgresql.org/ftp/odbc/versions/msi/

Если ругается, что нет VS2019, то ставим пакет (15Мб) - https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads

Настраиваем коннектор

Запускаем команду odbcad32 (win+r => odbcad32)

Заходим во вкладку System DSN, нажимаем Add

Выбираем коннектор

Примечание. Для Postgree выбираем Pastgree Unicode x64. 

Заполняем настройки сервера 

Для MySQL:

Для PostgreSQL:

Этот Data Source Name нам понадобится дальше

Проверяем соединение кнопкой Test. Сохраняем настройки.

Добавление удаленного сервера в SQL Server

Заходим в Server Objects/Linked Servers, в меню выбираем New Linked Server

Выбираем Other data source и Provider для ODBC, в Data Source вписываем Data Source Name из настроек коннектора

В разделе Security добавляем пользователей, которые могут пользоваться этим сервером, и для них указываем удаленные логин/пароль:

Чтобы не было ошибки при создании, добавьте текущего пользователя (локального администратора сервера). 

В Server Options включаем RPC и RPC out, иначе не все типы запросов будут отрабатывать

Сохраняем настройки

Примеры SQL запросов 

Создание таблицы

exec

('CREATE TABLE RemotelyCreatedTable

(id int,

  data1 nvarchar(100),

  data2 nvarchar(100)

)') at [MYSQL];

INSERT

insert into openquery(MYSQL, 'select * from RemotelyCreatedTable') 
values (1, 'test1', 'test1'), (2, 'test2', 'test2')

-- in falcon
declare @id int
select @id = id from openquery(MYSQL1, 'select  id from RemotelyCreatedTable order by id desc limit 1')
set @id = isnull(@id, 0) + 1

insert into openquery(MYSQL1, 'select * from RemotelyCreatedTable')
values (@id, @text, @text)

SELECT

select * from openquery(MYSQL, 'select * from RemotelyCreatedTable')

-- for falcon
declare @result TABLE(
		id nvarchar(max),
		p1 nvarchar(max),
		p2 nvarchar(max)
)

INSERT INTO @result
select * from openquery(MYSQL1, 'select * from RemotelyCreatedTable')
    

UPDATE

update openquery(MYSQL1, 'select data1 from RemotelyCreatedTable where id = 1')
set data1='new_valueXXXX'

-- Вариант в Falcon 
declare @sql nvarchar(max) = ''
set @sql = '
       update openquery(MYSQL1, ''select data1 from RemotelyCreatedTable where id =
       '+cast(@itemID as nvarchar)+''' )
        set data1='''+@value+'''
        where data1 <> '''+@value+'''
    '
EXEC (@sql)

DELETE

 exec('delete from remotelyCreatedTable where id='+ @itemID) at MYSQL1

Примечание: MYSQL1 в примерах - это Linked Server, подключенный через MS Management Studio. 

Возможные проблемы 

Ошибка "OLE DB provider “MSDASQL” for linked server “[name]” was unable to begin a distributed transaction"

Необходимо отключить использование транзакций в Falcon Space (в настройках appsettings.json (в MVC версии - в web.config в App Settings) добавляем ключ disableTransactions: "1", в MVC версии    key="disableTransactions" value="1"). 

Ошибка при обновлении поля (Update) в СУБД MySQL

MySQL выдает ошибку при попытке сохранить поле, значение которого совпадает с новым значением. Поэтому используйте подобную конструкцию: 

update openquery(mysql, 'select * from remotelyCreatedTable where id=3')
set data1='1'
where data1 <> '1'

При этом в настройках Linked Server / Server Options / Enable prpmotion of Distributed transactions for RPC = true