0. Dedushka 40 03.12.19 16:25 Сейчас в теме

Использование хранимых процедур MS SQL Server в 1С

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

Перейти к публикации

Комментарии
Избранное Подписка Сортировка: Древо
1. chembulatov76 03.12.19 18:12 Сейчас в теме
Через внешние источники данных в связке с sql можно решить любую задачу. Зачем использовать такое решение совершенно не понятно
3. alexlx 59 03.12.19 21:20 Сейчас в теме
(1)Если попробовать заджойнить две таблицы из внешнего источника - выйдет коллапс. Если всё-таки заставить это работать (это возможно) - будет намного медленней, чем если средствами самой СУБД
4. chembulatov76 04.12.19 07:05 Сейчас в теме
(3) Вся логика отрабатывается в хранимой процедуре. Там джоинить можно что угодно. В 1С только финальный результат. Если нужно вернуть таблицу, то хранимая процедура возращает ID. Дальше SELECT по таблице уже в 1С, куда все написала процедура. Таблица в которую процедура должна написать результат подключается как внешний источник. Все идеально работает.
6. ZLENKO 384 04.12.19 13:08 Сейчас в теме
(1) Попробуете сделать что нибудь масштабное через внешние источники данных - поймете :-)
FirePyres; +1 Ответить
7. chembulatov76 04.12.19 13:29 Сейчас в теме
(6) Интеграцию любой степени сложности делал и не раз. И с хранимыми процедурами и с вьюшками и со скалярными функциями. Что угодно. Все идеально работает. Хранимые процедуры пишут в таблицу и возвращают ID. Эта таблица подключена к внешнему источнику и служит для "забрать результат". Что еще может быть проще ?
9. ZLENKO 384 04.12.19 14:43 Сейчас в теме
(7) Попробуйте выгружать через внешние источники данных миллионы записей в MS SQL.
FirePyres; +1 Ответить
11. chembulatov76 04.12.19 20:05 Сейчас в теме
(9) Если такие задачи возникают, то надо пересмотреть само приложение и подходы к работе с данными. Если надо что-то забирать из 1С, то тут лучше использовать ODATA.
13. pbazeliuk 1731 05.12.19 15:36 Сейчас в теме
(11) миллиарды записей тоже ODATA? Интересно, как вы будете DWH строить с сотнями таблиц с контролем по ключам с инкрементальным обновлением. Внешние источники мертвы, к сожалению.
18. chembulatov76 06.12.19 08:04 Сейчас в теме
(13) Просто не надо путать теплое с мягким. Внешние источники нужны именно для интеграции. Если требуется выгружать миллиарды записей, значит Вы ошиблись с софтом в принципе. Для реальных задач внешние источники очень полезный и простой инструмент.
20. chembulatov76 06.12.19 08:11 Сейчас в теме
(13)
как вы будете DWH строить с сотнями таблиц с контролем по ключам с инкрементальным обновлением


Вы о чем ??? Пусть этим контролем занимается та система, в которой эти сотни таблиц созданы. Писать напрямую в эти таблицы из 1С никто не заставляет. Для этого и есть механизм вызова хранимых процедур, если это разумно при интеграции. Внешние источники очень удобны для загрузки данных в 1С. Во внешней системе делаем нужные вьюшки и подключаем их. В саму эту систему отправляем какие-либо подтверждения через вызов процедур. Если нужны выгрузки миллиардов записей, то задача решается совершенно другим способом.
10. Созинов 04.12.19 16:25 Сейчас в теме
(1) Если необходимо разрабатывать запрос с нуля, то возможно и выгоднее их использовать, но если требуется получить данные, которые уже можно выдрать с использованием хитрых хранимок - себе дороже. К тому же структура внешнего источника может дорабатываться. Использование хранимок позволяет переложить реализацию и контроль получения данных на тех, кто сопровождает внешнюю систему или как минимум получить консультацию, почему данные криво приходят.
Если не сложно - можете рассказать, как обновляете внешние источники (структуру) вкратце. В этом году пришлось много работать над интеграцией с ms sql server - показалось неудобно обновлять базу, после изменения внешнего источника (в расширение не пробовал переносить).
16. teller 06.12.19 06:46 Сейчас в теме
(1) ограниченный взгляд, отрицание опыта человечества .
Берем данные из другой системы(oracle) используем при обработке и sql и pl-sql.
19. chembulatov76 06.12.19 08:06 Сейчас в теме
(16) Что сказать то хотели ? Автор предложил корявый механизм работы с хранимыми процедурами.
25. ZLENKO 384 06.12.19 11:54 Сейчас в теме
(1)
Через внешние источники данных в связке с sql можно решить любую задачу.


Однако далее в комментариях вы утверждаете что для любых задач, другие инструменты нужны :-)
2. PerlAmutor 54 03.12.19 18:29 Сейчас в теме
Я вызываю свою процедуру через внешний источник данных. Из минусов - 1С не умеет обрабатывать RAISERROR (почему не RAISEERROR кстати?), прерывая любое выполнение процедуры, даже если вы решили с помощью этой конструкции просто сообщение отправить для отладки в студии. Ну и похоже умеет обрабатывать только ошибки, которые вызывают исключения внутри процедуры. Данные кстати тоже не умеет возвращать.
5. json 2575 04.12.19 08:10 Сейчас в теме
Автор, а в чем смысл создавать таблицу?
Сначала создаешь таблицу, потом помещаешь туда выборку, потом получаешь все данные из этой таблицы, потом убиваешь таблицу.
Так ты возьми и просто получи данные из выборки.

Также непонятен смысл использования EXEC. Напиши сразу запрос выборки, зачем сначала формировать текст запроса в твоем ЭЛЕМЕНТАРНОМ примере, а потом его выполнять.

Ну и еще конечно непонятен смысл создания хранимой процедуры в твоем случае.
Раз уж ты все равно используешь ком - так сгенерируй ты текст запроса выборки и выполни.
Зачем для этого хранимку создавать?
Тому кто будет после тебя это поддерживать придется устанавливать студию, давать права. И все ради того, чтобы исправить какую-нибудь мелочь в твоем запросе.
tani6e4ka; user774630; +2 Ответить
8. chembulatov76 04.12.19 13:38 Сейчас в теме
(5) Автор имеет ввиду, что хранимая процедура может делать что-то сложное и результат возвращать в табличном виде.
Есть только вариант вызывать функцию, которая вернет таблицу и это хорошо обыгрывается через внешние источники.
Но возможности функций в SQL значительно меньше, чем процедур.
В 1С через внешние источники нельзя вызвать процедуру с возвратом таблицы.
Вот это он хотел сказать. Просто самое решение очень корявое.
12. dmitrydemenew 435 05.12.19 11:46 Сейчас в теме
Использование хранимых процедур, как и любое подключение возможностей прямого доступа к данным - особая зона возможностей, на мой взгляд очень недооцененная.
Пример с хранимой процедурой из собственного опыта: была поставлена задача максимально быстрой синхронизации справочника "Номенклатура" в двух независимых базах (самостоятельные информационные системы разных организаций).
Самый быстрый вариант - в момент записи элемента в 1 базе, сразу-же создавать(изменять) соответствующий элемент в другой. Связь по ссылке. Т.к организации независимые и самостоятельные, раскрывать внутреннюю структуру и параметры подключения пользователей SQL с доступом к изменению данных - недопустимо. Кроме этого базы разделены территориально. В данном случае использование хранимой процедуры - идеальное решение, которое в описанном случае работает более 3х лет без единого сбоя.
Реализация:
1.В базе-приемнике создана хр. процедура, создающая(обновляющая) прямым запросом элемент номенклатуры по входным параметрам, переданным в процедуру. Возвращаемое значение - признак успешной загрузки;
2.В базе-приемнике создан пользователь, имеющий доступ только к хранимой процедуре;
3.В базе-источнике после записи нового(измененного) элемента выполняется прямое подключение к приемнику и запуск хр. процедуры с ключевыми параметрами записываемого элемента. Выполнение производится под пользователем с правами только к выполняемой процедуре, в запросе только имя процедуры и передаваемые параметры, структура данных скрыта, что и требуется по условиям задачи.

В результате практически мгновенная синхронизация данных без COM-подключений к 1С, файлов, HTTP-сервисов и т.п.
14. json 2575 05.12.19 16:25 Сейчас в теме
(12) а что будет, если в момент записи элемента справочника в одной базе будет недоступен канал связи между двумя базами?
Или такие риски не считаются?
Имхо, это решение - подходящее, но называть его идеальным - это слишком громко

И вообще, то, что создаете элементы в базе 1С прямыми запросами - это как-то не очень похвально, учитывая то, что для решения данной задачи имеются штатные механизмы
Использование костылей в данном примере - не обоснованно.
15. dmitrydemenew 435 05.12.19 18:17 Сейчас в теме
(14)при отсутствии связи произойдёт то же, что и при любом другом способе обмена. В моем случае структура данных регистрируется для резервной выгрузки и вызов хр. процедуры будет производиться уже регламентным заданием до момента успешной загрузки в приёмник. Но это крайне исключительная ситуация, а если нет, то большой вопрос, как в условиях нестабильной связи работает 1С и все ее типовые методы. Кратчайшее расстояние между двумя точками - прямая, для баз MSSQL - прямая SQL инструкция. Описанным примером я всего лишь показал одну из возможностей использования хранимых процедур. Я не призываю использовать подобные методы где надо и не надо, но зачастую именно они оказываются самыми удобными и надежными.
17. chembulatov76 06.12.19 08:02 Сейчас в теме
(15) Автор показал корявый механизм вызова хранимых процедур. Чем мешает вызов штатными средствами ?
21. dmitrydemenew 435 06.12.19 08:52 Сейчас в теме
(17)Штатные средства в контексте обсуждения подробно рассмотрены в публикации Трюки с внешними источниками данных. Мне ничем не мешает использование штатных средств, просто я очень ценю свое время, а "корявостей" при использовании внешних источников (в контексте обсуждаемого вопроса) наблюдаю значительно больше, чем при использовании подключения ADO.
22. chembulatov76 06.12.19 09:46 Сейчас в теме
(21) Почитал я эту статью. Автор там предлагает не костыль а костылище "глобальную временную таблицу" для возврата результата из процедуры. Сделать такое не поднимется рука. Считаю такие советы очень вредными. Если у тебя есть возможность написать хранимую процедуру, то сделай полноценную, а не временную таблицу. Одна из колонок это идентификатор ответа. Остальные колонки, это то что надо вернуть. Процедура возвращает при своей работе идентификатор. Результирующая таблица подключена как нормальный источник данных без всяких танцев с бубнами. Вызвал процедуру, получил ответ. Сделал запрос к таблице, получил результат. Очистил таблицу для этого ответа. Что может быть проще и естественнее ? Полезность той статьи крайне отрицательная. Учит программистов вредным вещам.
23. dmitrydemenew 435 06.12.19 10:20 Сейчас в теме
Назвать "вредными вещами" навыки использования SQL при работе с базами данных может только истинный 1С-ник :).
24. chembulatov76 06.12.19 10:54 Сейчас в теме
(23) "вредными вещами" это когда ты работаешь с SQL через анальное отверстие. Нормальная работа с SQL только приветствуется.
Dimasik2007; +1 Ответить
26. alexlx 59 06.12.19 23:08 Сейчас в теме
(24) Ребят, здесь не обсуждается грамотная работа с SQL. Просто пример вызова процедуры. А уже содержимое процедуры - дело рук самих утопающих. Я часто сталкиваюсь с тем, что людям проще и удобней процедуру на SQL написать, чем запросами в 1С. К тому же функционал SQL в данном случае намного шире.
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Разработчик 1С
Нижний Новгород
зарплата до 90 000 руб.
Полный день

Консультант-аналитик 1С
Москва
зарплата от 100 000 руб. до 150 000 руб.
Полный день

Программист 1С
Москва
зарплата до 140 000 руб.
Полный день

Автор новостных обзоров на тему 1С и бухучета
Санкт-Петербург
По совместительству

Консультант 1С
Москва
зарплата от 80 000 руб.
Полный день