Немного подумав, решил написать в SQL процедуру, которая принимала бы какие-то параметры и возвращала какую-то таблицу значений с данными.
Во-первых, создадим таблицу.
Для простоты, просто выложу скрипт создания:
CREATE TABLE [dbo].[Sales](
[Date] [DATETIME] NULL,
[Article] [NCHAR](10) NULL,
[Qty] [INT] NULL,
[Sum] [INT] NULL
) ON [PRIMARY]
GO
Затем создадим процедуру, которая будет вытаскивать данные. Процедура будет иметь два параметра: обязательный и необязательный. Обязательным будет дата, начиная с которой брать данные, а необязательным будет массив артикулов.
Итак, процедура:
CREATE PROCEDURE [dbo].[ReturnSell] (@DateStart VARCHAR(255), @Articles AS VARCHAR(8000)= '')
AS
BEGIN
DECLARE @CMD NVARCHAR(4000);
SET @Articles=REPLACE(@Articles,' ','')
CREATE TABLE #tempsales (prodid BIGINT, sum BIGINT)
IF LEN(@Articles)>0
BEGIN
SET @Cmd='insert into #tempsales
SELECT article, SUM(sum) AS summa FROM dbo.Sales
where article in ('+@Articles+') and date >= ''' + @DateStart + ''' GROUP BY Article'
END ELSE
SET @Cmd='insert into #tempsales
SELECT article, SUM(sum) AS summa FROM dbo.Sales
where date >= ''' + @DateStart + ''' GROUP BY Article '
EXECUTE (@Cmd)
SELECT * FROM #tempsales
DROP TABLE #tempsales
END
GO
Процедура вытаскивает данные по параметрам во временную таблицу, выводит её и, после этого, дропает.
По ощущениям, работает быстрее чем просто запрос к внешнему источнику.
С SQL всё. Переходим к 1С
Подключение к SQL Сервер:
Попытка
ADOСоединение = Новый COMОбъект("ADODB.Connection");
ADOСоединение.ConnectionString = "DRIVER={SQL Server};SERVER=localhost;UID=sa;PWD=password;DATABASE=test_db;LANGUAGE=us_english";
ADOСоединение.Open();
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
Установка параметров процедуры и её вызов:
Command = Новый COMObject("ADODB.Command");
Command.ActiveConnection = ADOСоединение;
ТекстЗапроса = "ReturnSell";
Command.CommandText = ТекстЗапроса;
Command.CommandType = 4;
Command.Parameters(1).value = Строка(Формат(Объект.Дата,"ДФ=""гггг-ММ-дд"""));
Command.Parameters(2).value = Объект.СтрокаАртикулов;
Результат = Command.Execute();
Ну и заполнение ТЗ результатами вызова:
Если НЕ Результат.EOF() Тогда
МассивСтруктур = Новый Массив;
Пока Результат.EOF() = 0 Цикл
КолКолонок = Результат.Fields.Count();
Строка = Новый Структура;
Для Инд = 0 По КолКолонок-1 Цикл
Строка.Вставить(Строка(Результат.Fields(Инд).Name),Строка(Результат.Fields(Инд).Value));
КонецЦикла;
СтрокаТЗ = ТЗ.Добавить();
СтрокаТЗ.Артикул =Строка.Prodid;
СтрокаТЗ.Сумма = Строка.sum;
Результат.MoveNext();
КонецЦикла;
КонецЕсли;
После этого с ТЗ уже делаем, что хотим.
Для примера выкладываю обработку и бэкап SQL базы.
Тестировалось на платформе 8.3.12.1685, 8.3.14.1944, 8.3.15.1747.
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
Через внешние источники данных в связке с sql можно решить любую задачу. Зачем использовать такое решение совершенно не понятно
Я вызываю свою процедуру через внешний источник данных. Из минусов — 1С не умеет обрабатывать RAISERROR (почему не RAISEERROR кстати?), прерывая любое выполнение процедуры, даже если вы решили с помощью этой конструкции просто сообщение отправить для отладки в студии. Ну и похоже умеет обрабатывать только ошибки, которые вызывают исключения внутри процедуры. Данные кстати тоже не умеет возвращать.
(1)Если попробовать заджойнить две таблицы из внешнего источника — выйдет коллапс. Если всё-таки заставить это работать (это возможно) — будет намного медленней, чем если средствами самой СУБД
(3) Вся логика отрабатывается в хранимой процедуре. Там джоинить можно что угодно. В 1С только финальный результат. Если нужно вернуть таблицу, то хранимая процедура возращает ID. Дальше SELECT по таблице уже в 1С, куда все написала процедура. Таблица в которую процедура должна написать результат подключается как внешний источник. Все идеально работает.
Автор, а в чем смысл создавать таблицу?
Сначала создаешь таблицу, потом помещаешь туда выборку, потом получаешь все данные из этой таблицы, потом убиваешь таблицу.
Так ты возьми и просто получи данные из выборки.
Также непонятен смысл использования EXEC. Напиши сразу запрос выборки, зачем сначала формировать текст запроса в твоем ЭЛЕМЕНТАРНОМ примере, а потом его выполнять.
Ну и еще конечно непонятен смысл создания хранимой процедуры в твоем случае.
Раз уж ты все равно используешь ком — так сгенерируй ты текст запроса выборки и выполни.
Зачем для этого хранимку создавать?
Тому кто будет после тебя это поддерживать придется устанавливать студию, давать права. И все ради того, чтобы исправить какую-нибудь мелочь в твоем запросе.
(1) Попробуете сделать что нибудь масштабное через внешние источники данных — поймете 🙂
(6) Интеграцию любой степени сложности делал и не раз. И с хранимыми процедурами и с вьюшками и со скалярными функциями. Что угодно. Все идеально работает. Хранимые процедуры пишут в таблицу и возвращают ID. Эта таблица подключена к внешнему источнику и служит для «забрать результат». Что еще может быть проще ?
(5) Автор имеет ввиду, что хранимая процедура может делать что-то сложное и результат возвращать в табличном виде.
Есть только вариант вызывать функцию, которая вернет таблицу и это хорошо обыгрывается через внешние источники.
Но возможности функций в SQL значительно меньше, чем процедур.
В 1С через внешние источники нельзя вызвать процедуру с возвратом таблицы.
Вот это он хотел сказать. Просто самое решение очень корявое.
(7) Попробуйте выгружать через внешние источники данных миллионы записей в MS SQL.
(1) Если необходимо разрабатывать запрос с нуля, то возможно и выгоднее их использовать, но если требуется получить данные, которые уже можно выдрать с использованием хитрых хранимок — себе дороже. К тому же структура внешнего источника может дорабатываться. Использование хранимок позволяет переложить реализацию и контроль получения данных на тех, кто сопровождает внешнюю систему или как минимум получить консультацию, почему данные криво приходят.
Если не сложно — можете рассказать, как обновляете внешние источники (структуру) вкратце. В этом году пришлось много работать над интеграцией с ms sql server — показалось неудобно обновлять базу, после изменения внешнего источника (в расширение не пробовал переносить).
(9) Если такие задачи возникают, то надо пересмотреть само приложение и подходы к работе с данными. Если надо что-то забирать из 1С, то тут лучше использовать ODATA.
Использование хранимых процедур, как и любое подключение возможностей прямого доступа к данным — особая зона возможностей, на мой взгляд очень недооцененная.
Пример с хранимой процедурой из собственного опыта: была поставлена задача максимально быстрой синхронизации справочника «Номенклатура» в двух независимых базах (самостоятельные информационные системы разных организаций).
Самый быстрый вариант — в момент записи элемента в 1 базе, сразу-же создавать(изменять) соответствующий элемент в другой. Связь по ссылке. Т.к организации независимые и самостоятельные, раскрывать внутреннюю структуру и параметры подключения пользователей SQL с доступом к изменению данных — недопустимо. Кроме этого базы разделены территориально. В данном случае использование хранимой процедуры — идеальное решение, которое в описанном случае работает более 3х лет без единого сбоя.
Реализация:
1.В базе-приемнике создана хр. процедура, создающая(обновляющая) прямым запросом элемент номенклатуры по входным параметрам, переданным в процедуру. Возвращаемое значение — признак успешной загрузки;
2.В базе-приемнике создан пользователь, имеющий доступ только к хранимой процедуре;
3.В базе-источнике после записи нового(измененного) элемента выполняется прямое подключение к приемнику и запуск хр. процедуры с ключевыми параметрами записываемого элемента. Выполнение производится под пользователем с правами только к выполняемой процедуре, в запросе только имя процедуры и передаваемые параметры, структура данных скрыта, что и требуется по условиям задачи.
В результате практически мгновенная синхронизация данных без COM-подключений к 1С, файлов, HTTP-сервисов и т.п.
(11) миллиарды записей тоже ODATA? Интересно, как вы будете DWH строить с сотнями таблиц с контролем по ключам с инкрементальным обновлением. Внешние источники мертвы, к сожалению.
(12) а что будет, если в момент записи элемента справочника в одной базе будет недоступен канал связи между двумя базами?
Или такие риски не считаются?
Имхо, это решение — подходящее, но называть его идеальным — это слишком громко
И вообще, то, что создаете элементы в базе 1С прямыми запросами — это как-то не очень похвально, учитывая то, что для решения данной задачи имеются штатные механизмы
Использование костылей в данном примере — не обоснованно.
(14)при отсутствии связи произойдёт то же, что и при любом другом способе обмена. В моем случае структура данных регистрируется для резервной выгрузки и вызов хр. процедуры будет производиться уже регламентным заданием до момента успешной загрузки в приёмник. Но это крайне исключительная ситуация, а если нет, то большой вопрос, как в условиях нестабильной связи работает 1С и все ее типовые методы. Кратчайшее расстояние между двумя точками — прямая, для баз MSSQL — прямая SQL инструкция. Описанным примером я всего лишь показал одну из возможностей использования хранимых процедур. Я не призываю использовать подобные методы где надо и не надо, но зачастую именно они оказываются самыми удобными и надежными.
(1) ограниченный взгляд, отрицание опыта человечества .
Берем данные из другой системы(oracle) используем при обработке и sql и pl-sql.
(15) Автор показал корявый механизм вызова хранимых процедур. Чем мешает вызов штатными средствами ?
(13) Просто не надо путать теплое с мягким. Внешние источники нужны именно для интеграции. Если требуется выгружать миллиарды записей, значит Вы ошиблись с софтом в принципе. Для реальных задач внешние источники очень полезный и простой инструмент.
(16) Что сказать то хотели ? Автор предложил корявый механизм работы с хранимыми процедурами.
(13)
Вы о чем ??? Пусть этим контролем занимается та система, в которой эти сотни таблиц созданы. Писать напрямую в эти таблицы из 1С никто не заставляет. Для этого и есть механизм вызова хранимых процедур, если это разумно при интеграции. Внешние источники очень удобны для загрузки данных в 1С. Во внешней системе делаем нужные вьюшки и подключаем их. В саму эту систему отправляем какие-либо подтверждения через вызов процедур. Если нужны выгрузки миллиардов записей, то задача решается совершенно другим способом.
(17)Штатные средства в контексте обсуждения подробно рассмотрены в публикации . Мне ничем не мешает использование штатных средств, просто я очень ценю свое время, а «корявостей» при использовании внешних источников (в контексте обсуждаемого вопроса) наблюдаю значительно больше, чем при использовании подключения ADO.
(21) Почитал я эту статью. Автор там предлагает не костыль а костылище «глобальную временную таблицу» для возврата результата из процедуры. Сделать такое не поднимется рука. Считаю такие советы очень вредными. Если у тебя есть возможность написать хранимую процедуру, то сделай полноценную, а не временную таблицу. Одна из колонок это идентификатор ответа. Остальные колонки, это то что надо вернуть. Процедура возвращает при своей работе идентификатор. Результирующая таблица подключена как нормальный источник данных без всяких танцев с бубнами. Вызвал процедуру, получил ответ. Сделал запрос к таблице, получил результат. Очистил таблицу для этого ответа. Что может быть проще и естественнее ? Полезность той статьи крайне отрицательная. Учит программистов вредным вещам.
Назвать «вредными вещами» навыки использования SQL при работе с базами данных может только истинный 1С-ник :).
(23) «вредными вещами» это когда ты работаешь с SQL через анальное отверстие. Нормальная работа с SQL только приветствуется.
(1)
Однако далее в комментариях вы утверждаете что для любых задач, другие инструменты нужны 🙂
(24) Ребят, здесь не обсуждается грамотная работа с SQL. Просто пример вызова процедуры. А уже содержимое процедуры — дело рук самих утопающих. Я часто сталкиваюсь с тем, что людям проще и удобней процедуру на SQL написать, чем запросами в 1С. К тому же функционал SQL в данном случае намного шире.