Трюки с внешними источниками данных
#1С
#1С:Предприятие
#разработка
#интеграция
Люди, которые думают, будто я ничего не боюсь, когда выполняю все эти трюки, еще более сумасшедшие, чем я.
(с) Джеки Чан
Некоторые трюки для преодоления ограничений внешних источников данных.
Содержание
- С чем боремся
- Полигон для испытаний
- Просто напиши запрос
- Где же возвращаемые параметры
- Вернуть набор из процедуры
- Выполнение любого скрипта
- Опасность
- Вот и все
С чем боремся
Использование BULK-операций для ускорения массовой загрузки / обновления данных в базе достаточно частый сценарий, особенно в интеграциях. Для платформы 1С это можно реализовать через внешние источники данных. Но есть серьезные ограничения этого механизма при работе с базой данных, а именно:
- Отсутствует возможность вызова хранимых процедур с возвратом значений для OUTPUT-параметров.
- Также нет возможности получить возвращаемый набор данных из хранимой процедуры.
- Недоступно выполнение произвольных SQL-скриптов
- И другие специфические ограничения.
Сегодня мы рассмотрим некоторые пути обхода этих ограничений, а также сделаем несколько особых трюков с этим функционалом платформы 1С.
Внимание! Все, что Вы увидите дальше - это воистину костыли, которых еще поискать! Не рекомендую использовать их на рабочем окружении, только если ну очень сильно нужно. В остальных случаях лучше использовать 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]GOCREATE 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]GOCREATE PROCEDURE [dbo].[ExecCustomCommand]@SqlCommand nvarchar(max)ASBEGINEXEC sp_executesql @SqlCommand;ENDGOCREATE PROCEDURE [dbo].[ProcWithOutputParams]@inputParam INT,@outputParam int = NULL OUTPUT,@outputParamOther int = NULL OUTPUTASBEGINSET @outputParam = @inputParam + 10;SET @outputParamOther = @outputParam * 100;ENDGO
Также для примера будет использоваться конфигурация, исходный код которой можно будет найти на GitHub. Используемая версия платформы 8.3.13.1690, но думаю описанные подходы будут работать и на других версиях.
Просто напиши запрос
И так, у Вас есть внешняя база данных, которая тесно интегрирована в некоторые решение на платформе 1С. Для интеграции используются внешние источники данных. Исходные метаданные выглядят таким образом.

Источник включает в себя несколько таблиц и функцию. Не будем останавливаться на назначении каждого объекта, сейчас это добавлено только для примера. Позже Вы сами все увидите.
Для начала решим простую задачу - добавить таблицу, которая будет показывать текущие активные соединения и текст выполняемого запроса. Для SQL Server запрос может выглядеть так.
SELECTs.session_id,r.status,r.cpu_time,Substring(st.TEXT,(r.statement_start_offset / 2) + 1,((CASE r.statement_end_offsetWHEN -1THEN Datalength(st.TEXT)ELSE r.statement_end_offsetEND - r.statement_start_offset) / 2) + 1) AS statement_text,r.command,s.login_name,s.host_name,s.program_nameFROM sys.dm_exec_sessions AS sJOIN sys.dm_exec_requests AS rON r.session_id = s.session_idCROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
Для этого добавим новую таблицу в источник, но в качестве вида таблицы указать "Выражение". В само выражение вставим SQL-запрос (см. под спойлером выше). Что же в таком простейшем примере может пойти не так? А вот что!

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

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

Что это опять за магия? Все дело в том, что платформа преобразует выражение таблицы к следующему виду.
SELECT TOP 1000T1.Поле1,T1.Поле2,-- Перечисление полей источникаT1.ПолеNFROM-- Здесь SQL-запрос из выраженияAS T1
Если бы вместо произвольного запроса была указана таблица, то все работало бы отлично. В этом же случае платформа не оборачивает выражение как вложенный запрос, поэтому происходит ошибка. К счастью, исправить ее не сложно - нужно просто обернуть все выражение в круглые скобки, в начале и в конце.

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

И, ура! Все получилось. Обратите внимание - мы поймали запрос, который сами и выполняем. Просто в тестовой базе больше никого нет :).
Вот такие особенности при использовании произвольных выражений в таблицах внешних источников данных. Наверняка худшего многие с этим сталкивались и успешно обходили. Но по сравнению с остальными ограничениями и проблемами это лишь небольшая фича.
Где же возвращаемые параметры
Следующей интересной задачей будет вызов хранимой процедуры. Вроде все просто - добавляем функцию во внешний источник данных, определяем ее параметры, и она уже готова к вызову. Но хранимая процедура у нас не простая (по крайней мере для платформы 1С)! Она содержит выходные параметры, которые заполняются внутри самой процедуры при выполнении, а после возвращаются вызывающему коду.
В тестовой базе создана процедура с таким определением.
REATE PROCEDURE [dbo].[ProcWithOutputParams]@inputParam INT,-- Выходные параметры определены со значением по умолчанию = NULL-- Это сделано для того, чтобы ее можно было вызвать без указания-- выходных параметров@outputParam int = NULL OUTPUT,@outputParamOther int = NULL OUTPUTASBEGINSET @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С из внешнего источника данных. Для начала нам понадобиться добавить в базу данных хранимую процедуру для произвольного выполнения команд.
CREATE PROCEDURE [dbo].[ExecCustomCommand]@SqlCommand nvarchar(max)ASBEGINEXEC sp_executesql @SqlCommand;END
Эта процедура позволит выполнять произвольные TSQL-команды. Но это еще не все. Мы до сих пор не можем возвращать данные. Возьмем самый доступный в этой ситуации способ - будем использовать глобальную временную таблицу, в которую будем вставлять результаты команд и считывать их отдельным SELECT'ом. Вот так будет выглядеть эта таблица.
-- Имя глобальной временной таблицы начинается с ##.-- Будем считать, что у нас только 1 временная таблица для целей передачи данных,-- хотя можно было бы усложнить пример и создавать свои временные таблицы-- для каждой сессииIF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)BEGINBEGIN TRYCREATE TABLE ##CallsAndValues_ExternalDataSource(-- Поле идентификатора вызова (какой-то GUID)CallId varchar(36) NOT NULL,-- Результат в виде текстаResult nvarchar(max) NULL)-- Создаем индекс для оптимизации поискаCREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource([CallId] ASC)END TRYBEGIN CATCH-- При ошибке ничего не делаемEND CATCHEND
Для доступа к значению в этой таблице нужно знать идентификатор вызова. Чтобы упростить доступ можно добавить таблицу во внешний источник данных со следующим выражением.

После получения значения лучше всего его удалять из таблицы. Также есть нюанс - если попытаться обратиться к таблице до ее создания, то конечно же мы получим ошибку. Для корректной работы необходимо обрабатывать подобные исключения в конфигурации. Вернемся к примеру. Вот так теперь можно вызвать хранимую процедуру и получить результат.
// Инициализируем идентификатор вызоваИдентификаторВызова = Новый УникальныйИдентификатор;ИдентификаторВызова = Строка(ИдентификаторВызова);ПараметрХранимойПроцедуры = 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_ExternalDataSourceWHERE 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-команду. Самое важное - это передача результатирующего набора процедуры в табличную переменную, который потом можно преобразовать к 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;-- Сохраняем набор записей в XMLDECLARE @xmlResult xml =(SELECT * FROM @TFOR XML RAW)DELETE ##CallsAndValues_ExternalDataSourceWHERE CallId = @callId;INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))
Сам по себе способ преобразования значений к XML очень универсален, но имеет некоторые накладные расходы как на сериализацию, так и на десериализацию. Вот более подробная информация по инструкции "FOR XML".
Тут ничего нового не увидите, все аналогично предыдущему примеру.
Команда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);
Так стоит ли усложнять? После этого все то, что мы делали выше, кажется бессмысленным. Но решать конечно же только Вам!