Трюки с внешними источниками данных

Выполнили произвольный SELECT в таблице.PNG

Стандартный вызов хранимой процедуры.PNG

Некоторые трюки для преодоления ограничений внешних источников данных.

С чем боремся

Как создаются костыли В прошлой статье речь шла об использовании BULK-операций для ускорения массовой загрузки / обновления данных в базе. В одном из примеров использовались внешние источники данных платформы 1С. Там было сказано о серьезных ограничениях этого механизма при работе с базой данных, а именно:

Отсутствует возможность вызова хранимых процедур с возвратом значений для OUTPUT-параметров.
Также нет возможности получить возвращаемый набор данных из хранимой процедуры.
Недоступно выполнение произвольных SQL-скриптов
И другие специфические ограничения.

В комментариях и в некоторых темах форума есть интерес к этому вопросу, поэтому ниже будет описание обходных путей для таких случаев.

Внимание! Все, что Вы увидите дальше — это воистину костыли, которых еще поискать! Не рекомендую использовать их на рабочем окружении, только если ну очень сильно нужно. В остальных случаях лучше использовать ADO.

Ниже не будет описания механизма источника данных, для этого обратитесь к официальной документации или другим статьям.

Полигон для испытаний

Все дальнейшие действия будут выполняться на простой базе данных, развернутой на SQL Server. Вот скрипт, если захотите создать ее у себя.

Скрипт создания базы данных из примеров

CREATE TABLE [dbo].[KeyOperations](
[ID] [binary](16) NOT NULL,
[Name] [nvarchar](150) NOT NULL,
[Description] [nvarchar](max) NULL,
CONSTRAINT [PK_KeyOperations] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[PerformanceMeasurements](
[KeyOperation] [varchar](34) NOT NULL,
[BeginDate] [numeric](15, 0) NOT NULL,
[SessionID] [numeric](15, 0) NOT NULL,
[PeriodHour] [datetime2](7) NOT NULL,
[Duration] [numeric](15, 3) NOT NULL,
[UserName] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_PerformanceMeasurements] PRIMARY KEY CLUSTERED
(
[KeyOperation] ASC,
[BeginDate] ASC,
[SessionID] ASC,
[PeriodHour] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[ExecCustomCommand]
@SqlCommand nvarchar(max)
AS
BEGIN
EXEC sp_executesql @SqlCommand;
END
GO

CREATE PROCEDURE [dbo].[ProcWithOutputParams]
@inputParam INT,
@outputParam int = NULL OUTPUT,
@outputParamOther int = NULL OUTPUT
AS
BEGIN
SET @outputParam = @inputParam + 10;
SET @outputParamOther = @outputParam * 100;
END
GO

Также для примера будет использоваться конфигурация, исходный код которой можно будет найти на GitHub. Используемая версия платформы 8.3.13.1690, но думаю описанные подходы будут работать и на других версиях.

Просто напиши запрос

И так. у Вас есть внешняя база данных, которая тесно интегрирована в некоторые решение на платформе 1С. Для интеграции используются внешние источники данных. Исходные метаданные выглядят таким образом.

Пример внешнего источника данных

Источник включает в себя несколько таблиц и функцию. Не будем останавливаться на назначении каждого объекта, сейчас это добавлено только для примера. Позже Вы сами все увидите.

Для начала решим простую задачу — добавить таблицу, которая будет показывать текущие активные соединения и текст выполняемого запроса. Для SQL Server запрос может выглядеть так.

Получение списка активных соединений с текстом запроса

Запрос получения информации об активных соединениях, а также тексты SQL-запросов для них.

SELECT
s.session_id,
r.status,
r.cpu_time,
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
r.command,
s.login_name,
s.host_name,
s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

Для этих целей используются несколько системных таблиц (DMV).

Для этого добавим новую таблицу в источник, но в качестве вида таблицы указать "Выражение". В само выражение вставим SQL-запрос (см. под спойлером выше). Что же в таком простейшем примере может пойти не так? А вот что!

Ошибка с переносом строк SQL-запроса во внешнем источнике данных

Идем в конфигуратор и видим странную картину.

Вот и перенос строк появился :)

Окей! Смирились, простили, сделали запрос одной строкой без переносов. Да, неудобно, но что поделать. Пробуем еще раз получить данные.

Что это опять за магия? Все дело в том, что платформа преобразует выражение таблицы к следующему виду.

SELECT TOP 1000
T1.Поле1,
T1.Поле2,
-- Перечисление полей источника
T1.ПолеN
FROM
-- Здесь SQL-запрос из выражения
AS T1

Если бы вместо произвольного запроса была указана таблица, то все работало бы отлично. В этом же случае платформа не оборачивает выражение как вложенный запрос, поэтому происходит ошибка. К счастью, исправить ее не сложно — нужно просто обернуть все выражение в круглые скобки, в начале и в конце.

Попробуем еще раз обратиться к таблице.

И, ура! Все получилось. Обратите внимание — мы поймали запрос, который сами и выполняем. Просто в тестовой базе больше никого нет :).

Вот такие особенности при использовании произвольных выражений в таблицах внешних источников данных. Наверняка Ожидание худшего многие с этим сталкивались и успешно обходили. Но по сравнению с остальными ограничениями и проблемами это лишь небольшая фича.

Где же возвращаемые параметры

Следующей интересной задачей будет вызов хранимой процедуры. Вроде все просто — добавляем функцию во внешний источник данных, определяем ее параметры, и она уже готова к вызову. Но хранимая процедура у нас не простая (по крайней мере для платформы 1С)! Она содержит выходные параметры, которые заполняются внутри самой процедуры при выполнении, а после возвращаются вызывающему коду.

В тестовой базе создана процедура с таким определением.

CREATE PROCEDURE [dbo].[ProcWithOutputParams]
@inputParam INT,
-- Выходные параметры определены со значением по умолчанию = NULL
-- Это сделано для того, чтобы ее можно было вызвать без указания
-- выходных параметров
@outputParam int = NULL OUTPUT,
@outputParamOther int = NULL OUTPUT
AS
BEGIN
SET @outputParam = @inputParam + 10;
SET @outputParamOther = @outputParam * 100;
END

Вызвать эту процедуру без выходных параметров просто — нужно добавить функцию внешнего источника и определить выражение "dbo.ProcWithOutputParams(&1)".

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

Но нам это не подходит, т. к. не позволяет получить назад выходные параметры. Даже если попытаться определить их в выражении "dbo.ProcWithOutputParams(&1, &2, &3)", то при вызове платформа просто не вернет значения в переменные.

 Перем1 = 100;
Перем2 = 0;
Перем3 = 0;

ВнешниеИсточникиДанных.ПримерИсточникаДанных.ProcWithOutputParams(Перем1, Перем2, Перем3);

// Перем2 и Перем3 останутся с исходными значениями.

Как же быть и есть ли выход? Выход есть! К сожалению, он не такой элегантный и интуитивно понятный как использование ADO, но позволяет возвращать любые значений назад в код 1С из внешнего источника данных. Для начала нам понадобиться добавить в базу данных хранимую процедуру для произвольного выполнения команд.

Процедура для произвольного выполнения SQL-команд

Маленькая процедура для SQL Server.

CREATE PROCEDURE [dbo].[ExecCustomCommand]
@SqlCommand nvarchar(max)
AS
BEGIN
EXEC sp_executesql @SqlCommand;
END

Но большая процедура для внешних источников данных!

Эта процедура позволит выполнять произвольные TSQL-команды. Но это еще не все. Мы до сих пор не можем возвращать данные. Возьмем самый доступный в этой ситуации способ — будем использовать глобальную временную таблицу. в которую будем вставлять результаты команд и считывать их отдельным SELECT’ом. Вот так будет выглядеть эта таблица.

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

Скрипт создания глобальной временной таблицы для сохранения результатов SQL-команд.

-- Имя глобальной временной таблицы начинается с ##.
-- Будем считать, что у нас только 1 временная таблица для целей передачи данных,
-- хотя можно было бы усложнить пример и создавать свои временные таблицы
-- для каждой сессии
IF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)
BEGIN
BEGIN TRY
CREATE TABLE ##CallsAndValues_ExternalDataSource
(
-- Поле идентификатора вызова (какой-то GUID)
CallId varchar(36) NOT NULL,
-- Результат в виде текста
Result nvarchar(max) NULL
)
-- Создаем индекс для оптимизации поиска
CREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource
(
[CallId] ASC
)
END TRY
BEGIN CATCH
-- При ошибке ничего не делаем
END CATCH
END

Костыль, не отрицаю!

Для доступа к значению в этой таблице нужно знать идентификатор вызова. Чтобы упростить доступ можно добавить таблицу во внешний источник данных со следующим выражением.

После получения значения лучше всего его удалять из таблицы. Также есть нюанс — если попытаться обратиться к таблице до ее создания, то конечно же мы получим ошибку. Для корректной работы необходимо обрабатывать подобные исключения в конфигурации. Вернемся к примеру. Вот так теперь можно вызвать хранимую процедуру и получить результат.

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

Так выглядит сквозной пример вызова хранимой процедуры с получением результата.

 // Инициализируем идентификатор вызова
ИдентификаторВызова = Новый УникальныйИдентификатор;
ИдентификаторВызова = Строка(ИдентификаторВызова);
ПараметрХранимойПроцедуры = 100;

// Инициализация промежуточной глобальной временной таблицы при необходимости
КомандаИнициализацииПромежуточнойТаблицы = "
|IF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)
|BEGIN
| BEGIN TRY
|  CREATE TABLE ##CallsAndValues_ExternalDataSource
|  (
|   -- Поле идентификатора вызова (какой-то GUID)
|   CallId varchar(36) NOT NULL,
|   -- Результат в виде текста
|   Result nvarchar(max) NULL
|  )
|  -- Создаем индекс для оптимизации поиска
|  CREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource
|  (
|   [CallId] ASC
|  )
| END TRY
| BEGIN CATCH
|  -- При ошибке ничего не делаем
| END CATCH
|END
|";
ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(КомандаИнициализацииПромежуточнойТаблицы);

// Формируем SQL-команду для вызова хранимой процедуры и сохранения
// параметров в промежуточную временную таблицу
КомандаSQL = "
|SET NOCOUNT ON;
|
|DECLARE @outputParam int,
|  @outputParamOther int,
|  @callId nvarchar(36) = '&ИдентификаторВызова'
|
|EXEC [dbo].[ProcWithOutputParams]
|  @inputParam = &ПараметрХранимойПроцедуры,
|  @outputParam = @outputParam OUTPUT,
|  @outputParamOther = @outputParamOther OUTPUT
|
|DECLARE @xmlResult xml =
|(
| SELECT @outputParam as N'outputParam',
|   @outputParamOther as N'outputParamOther'
| FOR XML RAW
|)
|
|DELETE ##CallsAndValues_ExternalDataSource
|WHERE CallId = @callId;
|
|INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
| VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))
|";
КомандаSQL = СтрЗаменить(КомандаSQL, "&ИдентификаторВызова", ИдентификаторВызова);
КомандаSQL = СтрЗаменить(КомандаSQL, "&ПараметрХранимойПроцедуры", XMLСтрока(ПараметрХранимойПроцедуры));
ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(КомандаSQL);

// Получаем результат выполнения в виде строки XML
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| CallsAndValues.Result КАК Result
|ИЗ
| ВнешнийИсточникДанных.ПримерИсточникаДанных.Таблица.CallsAndValues КАК CallsAndValues
|ГДЕ
| CallsAndValues.ID = &ID";
Запрос.УстановитьПараметр("ID", ИдентификаторВызова);
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
Выборка.Следующий();
РезультатXML = Выборка.Result;

// Удаляем результат из промежуточной таблицы
ТекстКомандыОчисткиПолученногоЗначения = "
|DECLARE @sql nvarchar(max) = 'DELETE FROM ##CallsAndValues_ExternalDataSource WHERE CallId = @CallId';
|EXECUTE sp_executesql @sql, N'@CallId varchar(36)', @CallId = '" + ИдентификаторВызова + "'
|";
ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(ТекстКомандыОчисткиПолученногоЗначения);

// Разбираем полученный XML
Парсер = Новый ЧтениеXML;
Парсер.УстановитьСтроку(РезультатXML);
Построитель = Новый ПостроительDOM;
Документ = Построитель.Прочитать(Парсер);
раз = новый РазыменовательПространствИменDOM(Документ, "");
РезультатПоиска = Документ.ВычислитьВыражениеXPath("/row", Документ, раз);
НайденныйЭлемент = РезультатПоиска.ПолучитьСледующий();
outputParam = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParam").Значение;
outputParamOther = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParamOther").Значение;

// Выводим исходную XML и полученные значения
Сообщить("XML: " + РезультатXML);
Сообщить("outputParam: " + outputParam);
Сообщить("outputParamOther: " + outputParamOther);

Общий принцип работы алгоритма следующий:

Инициализируем идентификатор вызова, по которому потом можно будет получить результат. Также подготавливаем все необходимые параметры для процедуры (в нашем случае это один входной параметр "@inputParam" с типом INT).
Проводим инициализацию глобальной временной таблицы, если она еще не создана.
Выполняем SQL-команду для вызова хранимой процедуры с последующим преобразованием результата в XML, и сохранением в промежуточную таблицу.
Получаем значение XML из промежуточной таблицы и удаляем из нее полученное значение.
Выводим результат.

Вот некоторые пояснения к SQL-команде вызова процедуры.

SET NOCOUNT ON;

-- Инициализация необходимых переменных
DECLARE @outputParam int,
@outputParamOther int,
@callId nvarchar(36) = '&ИдентификаторВызова'

-- Вызов процедуры
EXEC [dbo].[ProcWithOutputParams]
@inputParam = &ПараметрХранимойПроцедуры,
@outputParam = @outputParam OUTPUT,
@outputParamOther = @outputParamOther OUTPUT

-- Выбираем выходные параметры с помощью обычного SELECT,
-- преобразовывая результат к XML с помощью выражения "FOR XML RAW"
DECLARE @xmlResult xml =
(
SELECT @outputParam as N'outputParam',
@outputParamOther as N'outputParamOther'
FOR XML RAW
)

-- Очищаем промежуточную таблицу от значений
-- с таким же идентификатором вызова
DELETE ##CallsAndValues_ExternalDataSource
WHERE CallId = @callId;

-- Добавляем XML в промежуточную таблицу, предварительно
-- преобразовав ее к строке
INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))

Как итог, на стороне 1С у нас есть текст XML, который мы разобрали и получили выходные параметры хранимой процедуры. Остается только продолжить с ними работу (преобразовать к нужному типу, сохранить куда-либо и т.д.).

Код получился достаточно объемным. Можно инкапсулировать некоторую функциональность в общем модуле таким способом.

Общий модуль "ПомощникРаботыСВнешнимИсточникомДанных"

В общем модуле оставляем только один экспортный метод "ВыполнитьПроизвольныйСкрипт", остальное только для служебного использования.


#Область ПрограммныйИнтерфейс

Функция ВыполнитьПроизвольныйСкрипт(Знач ТекстСкрипта) Экспорт

ИнициализацияПромежуточнойТаблицы();

ИдентификаторВызова = Новый УникальныйИдентификатор;
ИдентификаторВызова = Строка(ИдентификаторВызова);

ТекстСкрипта = СтрЗаменить(ТекстСкрипта, "&ИдентификаторВызова", ИдентификаторВызова);

ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(ТекстСкрипта);

Возврат ПолучитьРезультатВызова(ИдентификаторВызова, Истина);

КонецФункции

#КонецОбласти

#Область Служебный

Процедура ИнициализацияПромежуточнойТаблицы()

КомандаИнициализацииПромежуточнойТаблицы = "
|IF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)
|BEGIN
| BEGIN TRY
|  CREATE TABLE ##CallsAndValues_ExternalDataSource
|  (
|   -- Поле идентификатора вызова (какой-то GUID)
|   CallId varchar(36) NOT NULL,
|   -- Результат в виде текста
|   Result nvarchar(max) NULL
|  )
|  -- Создаем индекс для оптимизации поиска
|  CREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource
|  (
|   [CallId] ASC
|  )
| END TRY
| BEGIN CATCH
|  -- При ошибке ничего не делаем
| END CATCH
|END
|";

ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(КомандаИнициализацииПромежуточнойТаблицы);

КонецПроцедуры

Функция ПолучитьРезультатВызова(ИдентификаторВызова, УдалитьЗначениеПослеПолучения = Ложь)

Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| CallsAndValues.Result КАК Result
|ИЗ
| ВнешнийИсточникДанных.ПримерИсточникаДанных.Таблица.CallsAndValues КАК CallsAndValues
|ГДЕ
| CallsAndValues.ID = &ID";
Запрос.УстановитьПараметр("ID", ИдентификаторВызова);
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();

Если УдалитьЗначениеПослеПолучения Тогда
УдалитьЗначениеВызова(ИдентификаторВызова);
КонецЕсли;

Если Выборка.Следующий() Тогда
Возврат Выборка.Result;
Иначе
Возврат Null;
КонецЕсли;

КонецФункции

Процедура УдалитьЗначениеВызова(ИдентификаторВызова)

ТекстКоманды = "
|DECLARE @sql nvarchar(max) = 'DELETE FROM ##CallsAndValues_ExternalDataSource WHERE CallId = @CallId';
|EXECUTE sp_executesql @sql, N'@CallId varchar(36)', @CallId = '" + ИдентификаторВызова + "'
|";

ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(ТекстКоманды);

КонецПроцедуры

#КонецОбласти

В процедуру передается текст SQL-команды для выполнения, при этом в скрипте должен быть указан параметр "&ИдентификаторВызова", чтобы его можно было явно подменить перед выполнением. Если такого параметра в скрипте нет, то получить возвращаемое значение будет нельзя.

Алгоритмы можно еще улучшить, но для примера этого должно быть достаточно.

Теперь код вызова будет выглядеть более лаконично.

Инкапсуляция в действии

Теперь вызов хранимой процедуры выглядит проще.

 ПараметрХранимойПроцедуры = 100;

КомандаSQL = "
|SET NOCOUNT ON;
|
|DECLARE @outputParam int,
|  @outputParamOther int,
|  @callId nvarchar(36) = '&ИдентификаторВызова'
|
|EXEC [dbo].[ProcWithOutputParams]
|  @inputParam = &ПараметрХранимойПроцедуры,
|  @outputParam = @outputParam OUTPUT,
|  @outputParamOther = @outputParamOther OUTPUT
|
|DECLARE @xmlResult xml =
|(
| SELECT @outputParam as N'outputParam',
|   @outputParamOther as N'outputParamOther'
| FOR XML RAW
|)
|
|DELETE ##CallsAndValues_ExternalDataSource
|WHERE CallId = @callId;
|
|INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
| VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))
|";
КомандаSQL = СтрЗаменить(КомандаSQL, "&ПараметрХранимойПроцедуры", XMLСтрока(ПараметрХранимойПроцедуры));

РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);

// Разбираем полученный XML
Парсер = Новый ЧтениеXML;
Парсер.УстановитьСтроку(РезультатXML);
Построитель = Новый ПостроительDOM;
Документ = Построитель.Прочитать(Парсер);
раз = новый РазыменовательПространствИменDOM(Документ, "");
РезультатПоиска = Документ.ВычислитьВыражениеXPath("/row", Документ, раз);
НайденныйЭлемент = РезультатПоиска.ПолучитьСледующий();
outputParam = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParam").Значение;
outputParamOther = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParamOther").Значение;

// Выводим исходную XML и полученные значения
Сообщить("XML: " + РезультатXML);
Сообщить("outputParam: " + outputParam);
Сообщить("outputParamOther: " + outputParamOther);

Но работать через ADO все равно будет эффективней!

Вот и все. Мы добавили немного костылей и палок, теперь внешние источники данных можно использовать для более широкого спектра задач.

Вернуть набор из процедуры

С получением выходных параметров хранимых процедур мы разобрались, но есть и более сложный случай — получить набор записей, который эта процедура вернула. Например, есть служебная процедура "sp_who", которая возвращает текущую активность на сервере.

Как же нам получить этот набор данных через внешний источник на стороне 1С? Сделать SELECT к хранимой процедуре нельзя, нужен альтернативный вариант.

На самом деле все просто — модифицируем предыдущий пример и получим такую SQL-команду.

SQL-команда для получения набора записей хранимой процедуры

Самое важное — это передача результатирующего набора процедуры в табличную переменную, который потом можно преобразовать к XML.

DECLARE @callId nvarchar(36) = '&ИдентификаторВызова'

-- Создаем переменную с типом таблица
DECLARE @T Table (
spid INT NULL,
ecid INT NULL,
status nvarchar(50) NULL,
loginname nvarchar(50) NULL,
hostname nvarchar(50) NULL,
blk INT NULL,
dbname nvarchar(50) NULL,
cmd nvarchar(50) NULL,
request_id INT null
);
-- Вызываем процедуру и сохраняем первый возвращаемый набор в таблицу
INSERT @T EXEC sp_who;

-- Сохраняем набор записей в XML
DECLARE @xmlResult xml =
(
SELECT * FROM @T
FOR XML RAW
)

DELETE ##CallsAndValues_ExternalDataSource
WHERE CallId = @callId;

INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))

Сам по себе способ преобразования значений к XML очень универсален, но имеет некоторые накладные расходы как на сериализацию, так и на десериализацию. Вот более подробная информация по инструкции "FOR XML".

Выполнение SQL-команды через внешний источник данных

Тут ничего нового не увидите, все аналогично предыдущему примеру.

 КомандаSQL = "
|DECLARE @callId nvarchar(36) = '&ИдентификаторВызова'
|
|-- Создаем переменную с типом таблица
|DECLARE @T Table (
| spid INT NULL,
| ecid INT NULL,
| status nvarchar(50) NULL,
| loginname nvarchar(50) NULL,
| hostname nvarchar(50) NULL,
| blk INT NULL,
| dbname nvarchar(50) NULL,
| cmd nvarchar(50) NULL,
| request_id INT null
|);
|-- Вызываем процедуру и сохраняем первый возвращаемый набор в таблицу
|INSERT @T EXEC sp_who;
|
|-- Сохраняем набор записей в XML
|DECLARE @xmlResult xml =
|(
| SELECT * FROM @T
| FOR XML RAW
|)
|
|DELETE ##CallsAndValues_ExternalDataSource
|WHERE CallId = @callId;
|
|INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
| VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))";

РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);

// Далее необходимо разобрать XML

Парсинг результата опустим, он не имеет для примера большого значения.

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

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

На самом деле мы получили возможность работать не только с хранимыми процедурами, но выполнять абсолютно любой SQL-скрипт и получать результат любого вида. Главное чтоб его можно было преобразовать в XML. В новых редакциях SQL Server результат можно возвращать также и в JSON-формате.

Например, с помощью внешнего источника данных теперь можно делать то, что раньше казалось недоступным!

Обновление статистики

КомандаSQL = "
|USE [PerfMonitoring];
|UPDATE STATISTICS [dbo].[PerformanceMeasurements] WITH FULLSCAN;";

РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);

Удаление таблицы

КомандаSQL = "
|USE [PerfMonitoring];
|DROP TABLE [dbo].[KeyOperations];";

РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);

Удаление базы данных

КомандаSQL = "
|USE [PerfMonitoring];
|DROP DATABASE [PerfMonitoring];";

РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);

Опасность

Подобный подход работы с базой имеет ряд существенных недостатков: Остановитесь!

Сложность сопровождения, ведь вместо обычных SQL-скриптов приходиться предусматривать маневры для возврата значений на сторону 1С.
Множество избыточных действий могут влиять на производительность (использование временных таблиц, преобразование результатов запросов в XML и обратно и др.).
Большой удар по безопасности, т.к. теперь из кода 1С можно выполнить любую SQL-команду. Конечно, правами учетной записи SQL-сервера можно себя обезопасить, но для этого также потребуется время и ресурсы на настройку и сопровождение.
Также неграмотное составление SQL-команд может привести к неоптимальной работе SQL Server. Например, если вместо передачи значений в запрос использовать не параметры, а явное указание значения. В этом случае кэширование планов запросов не будет эффективно работать. Вот интересный материал про динамические SQL-запросы.

Вот и все

Может быть кому-нибудь это будет полезно, но используйте это только когда больше нет выхода. Вместо этих костылей лучше использовать ADO. Судите сами, вот так будет выглядеть вызов хранимой процедуры с выходными параметрами.

СтрокаСоединения = "DRIVER={SQL Server};SERVER=<ИмяСервера>;UID=<Логин>;PWD=<Пароль>;Trusted_Connection=False;APP=PerfMonitoring;DATABASE=PerfMonitoring;LANGUAGE=русский";

ПараметрХранимойПроцедуры = 100;

Connection  = Новый COMОбъект("ADODB.Connection");
Connection.CursorLocation = 3;
Connection.CommandTimeout = 60;
Connection.ConnectionTimeOut = 60;
Connection.Open(СтрокаСоединения);

Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;

Command.CommandText = "ProcWithOutputParams";
Command.CommandType = 4; // adCmdStoredProc

ТипINT = 3; // adinteger
ТипПараметраВходящего = 1;
ТипПараметраИсходящего = 2;

Command.Parameters.Append(Command.CreateParameter("@inputParam", ТипINT, ТипПараметраВходящего));
Command.Parameters.Append(Command.CreateParameter("@outputParam", ТипINT, ТипПараметраИсходящего));
Command.Parameters.Append(Command.CreateParameter("@outputParamOther", ТипINT, ТипПараметраИсходящего));
Command.Parameters("@inputParam").Value = ПараметрХранимойПроцедуры;
Command.Execute();

outputParam = Command.Parameters("@outputParam").Value;
outputParamOther = Command.Parameters("@outputParamOther").Value;

Сообщить("outputParam: " + outputParam);
Сообщить("outputParamOther: " + outputParamOther);

Так стоит ли усложнять? После этого все то, что мы делали выше, кажется бессмысленным. Но решать конечно же только Вам!

Другие ссылки

53 Comments

Infector 14.03.2019 at 12:34

Лучший трюк с внешним источником, который довелось провернуть — имитация движений документа с записью в необъектную таблицу. Как способ интеграции хорошо заходит.

А чаще действительно пользуем ADO, как автор в конце описал. Тем более несредственное подключение и выполнение запроса, возвращающего таблицу значений, унифицируется до одной функции.

Reply ↓
YPermitin 14.03.2019 at 12:42

(1) ADO наше все! 🙂

Reply ↓
genayo 14.03.2019 at 12:45

(1) Ждём от вас статью про ADO :))

Reply ↓
YPermitin 14.03.2019 at 12:47

(3) не вижу интересных тем по нему. Или они есть?)

Reply ↓
genayo 14.03.2019 at 13:00

(4) Информации много не бывает. В (1) вот упоминается, что работа с ADO унифицируется до одной функции — многим было бы полезно, думаю.

Reply ↓
YPermitin 14.03.2019 at 13:09

(5) ну так то конечно может быть:)

Reply ↓
Infector 14.03.2019 at 13:25

Reply ↓
YPermitin 14.03.2019 at 13:27

(7) плюс за статью!)))

Reply ↓
w.r. 14.03.2019 at 13:42

Был тут один «трюк» с внешими источниками в платформе 8.3.12, из-за которого мне пришлось переписывать обмены с внешней СУБД MS SQL с внешних источников на ADODB. После обновления с 8.3.10 на 8.3.12 перестала работать запись во внешние источники:

Невосстановимая ошибка

Ошибка при выполнении запроса POST к ресурсу /e1cib/logForm:

по причине:

Ошибка SDBL:

В схеме базы данных нет таблицы с именем EDBT33382

Ошибка зафиксирована была мной 8.10.2018

И ответ от 1С

Ошибка платформы 10197269.

Исправлена в 8.3.14, но должна быть перенесена на 8.3.12.

Ошибка исправлена в 8.3.14.907

Сроков переноса исправлений в 8.3.12 пока сообщить не можем.

И, конечно, ничего не перенесли. А не обновляться на 8.3.12 я не мог, так как обновления требовала конфигурация на новой БСП. 8.3.14.907 — это на тот момент была какая-то внутренняя альфа или пре-бета версия, недоступная для скачивания. Тогда я еще раз проклял про себя 1С и начал переписывать обмены

Reply ↓
YPermitin 14.03.2019 at 13:46

(9) возможно, это будет еще один сигнал всем, кто планирует сейчас использовать внешние источники.

Reply ↓
w.r. 14.03.2019 at 13:48

(10) самое интересное, что еще с 8.3.5, когда я только написал обмены на внешних источниках, и по 8.3.10 все отлично работало — это 3 года, а потом в 8.3.12 сломали

Reply ↓
geka-geka 14.03.2019 at 14:23

Есть ли какой-нибудь трюк, чтобы в запросе соединить несколько таблиц внешних источников?

Reply ↓
YPermitin 14.03.2019 at 14:26

(12) разве что сделать говую таблицу, в которой сделать произвольный запрос. В этом запросе на уровне SQL и объединить.

Ну или в СКД использовать несколько наборов данных. И с помощью средств СКД как-раз и объединить.

Reply ↓
Aletar 15.03.2019 at 06:02
А скажите, пожалуйста, вот если я делаю запись через внешние источники данных, т.е.
```
НаборЗаписей = ВнешниеИсточникиДанных.<…>.СоздатьНаборЗаписей();
….
НаборЗаписей.Записать();
```
Как задать время ожидания на блокировке? Как установить LOCK_TIMEOUT для запроса операции записи?

Reply ↓
YPermitin 15.03.2019 at 07:17

(14) к сожалению, нет.

Тут надо либо использовать ADO, либо обходные пути из публикации. Тогда будет возможность установить таймаут на ожидании блокировки.

Reply ↓
Mortum 15.03.2019 at 15:50

(9) надо было немножко подождать, потому что на 30.11.2018 баг был оперативно исправлен после баг-репорта на testplatform@1c.ru.

Reply ↓
Mortum 15.03.2019 at 15:57

(12) Есть. ВнешнийИсточникДанных.ИмяВнешнегоИсточника.Таблица.ИмяТаблицы1 КАК ИмяТаблицы1

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВнешнийИсточникДанных.ИмяВнешнегоИсточника.Таблица.ИмяТаблицы2 КАК ИмяТаблицы2

Даже временные таблицы можно: ПОМЕСТИТЬ ВнешнийИсточникДанных.ИмяВнешнегоИсточника.ВременнаяТаблица.ИмяВТ

Reply ↓
Mortum 15.03.2019 at 16:03

(14) возможно получится в строке соединения к ВИД указать таймаут, но это уже от драйвера внешней базы зависит.

Reply ↓
w.r. 15.03.2019 at 16:35

(16) мы не могли ждать почти 2 месяца (оперативность однако) пока исправят баг

Reply ↓
YPermitin 15.03.2019 at 16:51

(18) тут про параметр LOCK_TIMEOUT для SQL Server. К сожалению его только в скрипте можно.

Reply ↓
YPermitin 15.03.2019 at 16:52

(17) то есть теперь работает соединение таблиц из разных источников или таблиц одного источника?

Reply ↓
Mortum 15.03.2019 at 22:30

(21) из одного да. Из нескольких никак, но тут платформа даже при сильном желании ничего не сможет.

Reply ↓
Darklight 18.03.2019 at 12:53

ADO — это, конечно, хорошо, но доступно только через СOM со всеми вытекающими. И без COM придётся извращаться с внешними источниками.

Reply ↓
YPermitin 18.03.2019 at 12:57

(23) не спорю, но иногда все же лучше, чем внешний источник.

Но иногда и нет 🙂

Reply ↓
acanta 18.03.2019 at 13:02

(23) а внешние источники Com не используют точно? А что тогда?

Reply ↓
Darklight 18.03.2019 at 13:10

(25)Используются драйвер-внешняя-библиотека, указываемый при создании строки подключения (аналогично работает со своей СУБД и сама платформа). Работа с ним не подразумевает обязательного применения COM, но, если будет использован драйвер ODBC или OLE -то он, вроде как сам использует COM технологию.

Reply ↓
DonAlPatino 19.03.2019 at 10:32

Я правильно понимаю, что ADO — это исключительно Windows вариант? Если сервер под Linux, то только внешние источники и odbc?

Reply ↓
YPermitin 19.03.2019 at 17:56

(27) да, ADO — это технология для Windows-платформы. Если сервер 1С под Linux, то вариантов связать 1С с внешним источником:

1. Внешние источники данных со всеми ограничениями + ODBC. Кстати, есть и ODBC-драйвер для Linux

(https://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/) Но такую связку я не тестировал.

Подробнее хорошо описано здесь: https://infostart.ru/public/522751/

2. Делать прослойку в виде веб-сервиса для базы и уже через нее взаимодействовать из 1С. Веб-сервис можно сделать на .NET, Java, GO. Вообщем, к чему душа ближе. На MSDN для .NET все хорошо расписано.

3. Есть менее популярные варианты в виде внешних компонент, использования сторонних утилит и др. Но сопровождать это все будет потом не просто.

Reply ↓
DonAlPatino 20.03.2019 at 15:04

(28) Мы из ЗУПа во внешнюю базу данные по 20 тыс сотрудников и гпхашникам льём. Причем в другой датацентр. Были проблемы с каналом (пропадал 20ый-30ый пакет) — так выгрузка через ODBC стала идти 8 часов вместо одного. Вот надеялся, что переход на ADO поможет. А это получается «vendor-lock». Пункты 2 и 3 как-то не выглядят рабочими при таких вводных вообще…

Reply ↓
YPermitin 20.03.2019 at 15:17

(29) все верно, но вы уже в «vendor-lock» режиме, когда сели на 1С 🙂

Если серьезно, то при необходимости выгрузки очень больших объемов данных во внешнюю базу я бы использовал BULK-операции, в том числе если канал связи не очень хороший. Посмотрите в сторону BCP для SQL Server или COPY для PG. Это выгрузка и загрузка.

Для передачи данных использовать RoboCopy или другие аналоги.

Если скорость выгрузки не является узким местом, то можно и стандартными средствами 1С выгружать в файл.

Если экосистема одна, например оба сервера SQL Server, то их можно залинковать и передавать данные собственными средствами (реаликация и т.д.)

В комментарии подробно сложно все это описать, но как вводная пойдет 🙂

Reply ↓
DonAlPatino 20.03.2019 at 16:14

(30) BULK сейчас смотрим. А вот насчет передачи данных репликацией между SQL серверами… это будет лучше-быстрее чем те же ODBC или ADO?

Reply ↓
YPermitin 21.03.2019 at 15:55

(31) это будет надежней на мой взгляд, т.к. передачу на себя возьмет сам SQL Server. При настройке репликации есть возможность указать качество мвязи и др. параметры. В том числе и конкретные таблицы или условия передачи.

На MSDN есть отличные материалы по этой теме.

Reply ↓
akpaevj 21.03.2019 at 22:11
(31) Можно поэкспериментировать с «EXEC sp_addlinkedserver» и «EXEC AT». Удобно для работы с удаленными серверами. Для быстрой загрузки можно попробовать порционно вставлять инструкциями
```
INS ERT IN TO ….. VALUES (…..), (…..), …..
```
главное длину запроса не превысить

Reply ↓
YPermitin 22.03.2019 at 07:02

(33) линковка серверов очень удобна. Сам использую 🙂

Reply ↓
DonAlPatino 22.03.2019 at 10:21

(30) Про BULK — я правильно понимаю что из 1С мы выгружаем данные в файл, потом перекидываем файл в другой датацентр и там грузим BULK INSERT? Или есть способ напрямую из 1С данные неким здоровым пакетом бросить?

Reply ↓
DonAlPatino 22.03.2019 at 10:23

(34)Я правильно понимаю что при линковке мы вроде как кидаем данные на ближайший сервер MS SQL, но по факту они сразу переправляются на удаленный? А внутри это ближе к репликации данных между серверами или все тому же ODBC/ADO?

Reply ↓
akpaevj 22.03.2019 at 13:53

(36)

Я правильно понимаю что при линковке мы вроде как кидаем данные на ближайший сервер MS SQL, но по факту они сразу переправляются на удаленный? А внутри это ближе к репликации данных между серверами или все тому же ODBC/ADO?

При линковке мы формируем Dynamic statement и отправляем его для исполнения на удаленном сервере.

Если проводить параллели, то это ближе к ADO. Репликация подразумевает под собой автоматический перелив данных/транзакций на slave экземпляр. В отличие от BULK инструкций при линковке не требуется дополнительный шаг загрузки/выгрузки файла, но требуется валидация данных для исключения SQL Injections. Требования к каналу связи +- одинаковы, те же данные переливать. В любом случае, все это обмен средствами СУБД, т.е. консистентность данных на удаленном хосте на вашей совести.

Reply ↓
akpaevj 22.03.2019 at 13:56

(35) Транспорт данных на удаленный хост — на вкус и цвет=) Можно веб-сервис состряпать, голубями отправлять, курьером на флешке, ftp и еще куча разных задорных способов)

Reply ↓
akpaevj 22.03.2019 at 14:36

(36) И как в статье сказано, лучше избегать установки в запросы константных значений что-бы избежать ad-hoc workload и соответственно роста объема кэша планов и деградации производительности при выполнении таких запросов. Если конечно не включен параметр Optimize for Ad-hoc Workload

Reply ↓
newbigrator 26.03.2019 at 22:21

Вопрос оффтоп, но рядом с темой.

Допустим надо каждую ночь писать большой обьем данных из 1С во внешнюю таблицу СГЛ. Насколько правильно и успешно такое можно будет сделать через механизм Вненших Источников Данных?

Reply ↓
YPermitin 27.03.2019 at 14:44

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

Эффективные способы:

1. Выгрузка в CSV или внутренний формат BCP и его загрузка в приемник. То есть использование Bulk Insert, подробнее смотрите в предыдущей статье.

2. Можно сделать выгрузку только измененных данных, если задача позволяет. Тогда и штатные механизмы могут помочь.

Reply ↓
rukalico 31.03.2019 at 15:29

(41) А не правильнее ли в таких ситуациях вообще использовать веб сервисы. Поднимаем веб сервис согласно спецификации, в том числе получаем возможность раздавать xml схему. А дальше кому нужно тянет данные и главное АПИ описан и стандартизирован.

А так через СГЛ писать куда то самим это брать на себя обязательства по выгрузке и фактор АПИ теряется.

Reply ↓
YPermitin 31.03.2019 at 21:36

(42) никто не спорит. Все что сказано в статье это не замена всех возможных способов интеграции, а уж тем более веб-сервисов.

Все таки цели разные. Bulk Insert — это больше для внутреннего пользования, внутрикорпоративного, где нужно оптимизировать определенные операции на уровне SQL Server / PostgreSQL.

Веб-сервисы (SOAP) / HTTP-сервисы для другого — это создание интеграций, в т.ч. и с документированным API, который можно использовать и для внутренних целей, так и для внешних (для клиентов и др.).

То есть все зависит от задачи.

Reply ↓
DonAlPatino 01.04.2019 at 11:58

(38) Мда… переделали с внешних источников на csv и bulk вставку. Непосредственно загрузка данных стало где-то минута вместо 40 минут… В общем «внешние источники» для точечных изменений…

Reply ↓
YPermitin 01.04.2019 at 12:04

(44) bulk тоже не универсальный способ. Например если для внешнего контингента данные нужно предоставлять, то через SQL это не совсем правильно и безопасно. Ниже в комментариях уже говорили.

А так рад, что у вас все получилось!

Reply ↓
v3rter 20.06.2019 at 12:49

Вопрос: будут ли эти трюки легальны для лицензии SQL Runtime? С одной стороны, на SQL-сервере мы выходим за пределы функционала 1С, но с другой стороны, все полученные данные используются исключительно из 1С. Вопрос не самый простой и не праздный, считаю.

Цитирую с сайта одного из партнеров (для тех, кто не в курсе):

«1С» предлагает совместный продукт: лицензии Microsoft SQL Server, предназначенные для использования совместно с системой «1С:Предприятие 8». Эти лицензии бывают 2-х видов: Runtime и Full-use.

Лицензии Runtime можно использовать только с «1С:Предприятие 8».

Лицензии Full-use – без ограничения на право использования. Пользователь может использовать Microsoft SQL Server, приобретенный вместе или для «1С:Предприятие 8», вместе с другими приложениями и в любых целях до тех пор, пока продолжает пользоваться продуктом «1С:Предприятие 8».

Лицензии на сервер и клиентский доступ должны быть в одной категории – или Runtime, или Full-use.

Reply ↓
YPermitin 20.06.2019 at 13:58

(46) к сожалению, у меня нет ответа на этот вопрос.

Я могу исходить лишь из здравого смысла. В части лицензирования он не всегда работает 🙂

Reply ↓
AlexBober78 21.06.2019 at 18:42

Очень интересно.

Главное, такую функцию по выполнению всего-чего-угодно в базе лучше хорошенько прятать как можно дальше…

Reply ↓
YPermitin 22.06.2019 at 12:31

(48)

прятать

Да 🙂

Как и много чего еще.

Reply ↓
ADirks 26.06.2019 at 09:28

Кстати, про сложные запросы. Можно же использовать view’шки, и в 1С обращаться именно к ним.

Например

CREATE VIEW ActiveSessions as

SELECT

s.session_id,

r.status,

r.cpu_time,

Substring(st.TEXT,(r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1

THEN Datalength(st.TEXT)

ELSE r.statement_end_offset

END — r.statement_start_offset) / 2) + 1) AS statement_text,

r.command,

s.login_name,

s.host_name,

s.program_name

FROM

sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

И в 1С уже будет

SELECT … FROM ActiveSessions

Даже какое-то количество SP’шек можно переделать на view’шки. И безопасность меньше пострадает.

Reply ↓
sparhh 27.06.2019 at 10:17
Если требуется писать во внешние источники, то что данный механизм может предложить?

Потребовалось писать данные по справочнику во внешнюю таблицу, ID — PrimaryKey.

Записьобновление строки таблицы сгл сделал так —
```
_ID = Строка(СсылкаНаЭлемент.УникальныйИдентификатор());

ОбъектСГЛ = ТаблицаСГЛ.НайтиПоПолю(«_ID», _ID);
Если ЗначениеЗаполнено(ОбъектСГЛ) Тогда
ОбъектСГЛ = ОбъектСГЛ.ПолучитьОбъект();
Иначе
ОбъектСГЛ = ТаблицаСГЛ.СоздатьОбъект();
ОбъектСГЛ._ID = Строка(СсылкаНаЭлемент.УникальныйИдентификатор());
КонецЕсли;
```
Показать
В таком случае выполняется 3! запроса для случая изменения строки + это вообще построчный insert.

Есть ли возможность типовыми средствами сделать оптимальную запись?

—

В СКЛ есть команда MERGE для случая вставки+обновления, можно ли через 1С ее вызвать в объектном варианте?

—

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

Reply ↓
sparhh 27.06.2019 at 12:57

(49) Но ведь эта функция при правильной настройке прав получит доступ только к требуемой базе и по идее не такая уж и дыра.

Или чем такой подход отличается от выполнения такого же произвольного кода через ADO?

Reply ↓
SmileDDD 20.11.2019 at 10:19

Подскажите, пожалуйста, как в функцию передать параметр с типом массив?

Reply ↓