Удаленное подключение к внешним базам данных из личного кабинета - 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 &lt;&gt; '1'

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

Falcon Space - функциональная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space
Насколько полезной была статья?

Google поиск по нашей документации

Falcon Space

Это снижение стоимости владения

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

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы

Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Сайт использует Cookie. Правила конфиденциальности OK