ВИД (внешние источники данных) в 1С – это объекты конфигурации, позволяющие использовать информацию из внешних ODBC-источников (баз данных), не основанных на 1С:Предприятии, внутри прикладного решения так же, как будто бы она хранится в самой информационной базе.
В качестве ODBC-драйвера был выбран пакет FreeODBC, т.к. он бесплатный и его везде используют.
В процессе гугления использовались источники:
Подробное описание работы с ВИД (да и вообще хороший блог о работе с 1С)
Запись во внешний источник данных из 1С 8.3 (используя хранимые процедуры MS SQL Server)
Содержание
Установка
Достаточно установить пакет tdsodbc (разрядность должна совпадать с сервером 1С!):
# apt install tdsodbc:i386 (дополнительно установятся: libodbc1 odbcinst odbcinst1debian2)
В файл odbcinst.ini добавить (если не добавилось автоматически) секцию для FreeTDS:
# nano /etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS
Driver = /libtdsodbc.so
Setup = /libtdsS.so
FileUsage = 1
UsageCount = 1
- Для 32-битного пакета: /usr/lib/i386-linux-gnu/odbc
- Для 64-битного пакета: /usr/lib/x86_64-linux-gnu/odbc
Если этого не сделать, или поставить пакет не той же разрядности, что сервер 1С, то будет ошибка:
[unixODBC][Driver Manager]Can’t open lib ‘FreeTDS’ : file not found
А вот такая ошибка будет, если неверно указали путь к либам:
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so file not found
При нажатии на кнопку “…” конфигурация выдаст последний вскрик в виде окошка с ошибкой и благополучно покрашится. Что это за НЛО непонятно, но на нашем серваке оно поселилось, видать, надолго:
"Ошибка аутентификации клиента 1cv8 средствми операционной системы: Аутентификационный контекст клиента отсутствует в рабочем процессе"
Как я понял, поключаться и работать с ВИД можно двумя способами:
1) Подключаться программно.
Пример:
(Спасибо caponid за инфу о поле СУБД):
Соед = Новый ПараметрыСоединенияВнешнегоИсточникаДанных;
Соед.СтрокаСоединения = "Строка_соединения";
Соед.АутентификацияОС = Ложь;
Соед.ИмяПользователя = Пользователь;
Соед.Пароль = Пароль;
Соед.СУБД = "MSSQLServer"; //Явно указываем, потому что тот тип, что указали в строке соединения, игнорируется;
Источник = ВнешниеИсточникиДанных["НазваниеИсточника"];
Источник.УстановитьПараметрыСоединенияСеанса(Соед);
Источник.УстановитьСоединение();
Но тут у меня не получилось запихнуть проинициализированный объект ВИД в запрос:
Запрос.Текст =
"ВЫБРАТЬ
| Источник.Поле
|ИЗ
| &ВИД КАК Источник
|";
Запрос.Параметры.Вставить("ВИД", ВИД);
2) Использовать механизм подключения в режиме Предприятия. Параметры подключения хранятся тут:
РегистрСведений.НастройкиПодключенияКВнешнемуИсточникуДанных
или
Все функции -> Стандартные -> Управление внешними источниками данных
Строка подключения выглядит так:
"Driver=
Пользователя и пароль я запихивал в отдельные поля.
Если в режиме предприятия будут отсутствовать параметры подключения к внешнему источнику данных, то будет ошибка:
"[unixODBC][Driver Manager]Data source name not found, and no default driver specified"
Названия Таблиц и полей в конфигураторе могут быть какими угодно, а вот к полям “Имя в источнике данных” нужно присмотреться внимательнее. Если в именах полей БД MS SQL есть, к примеру, символ “_”, то будет вот такая ошибочка:
"[FreeTDS][SQL Server]Incorrect syntax near ‘t_ticket_id’"
“Если значение, находящееся в свойстве Имя в источнике данных заключено в одинарные кавычки, то в SQL-запрос к базе данных это значение попадает без преобразований, вне зависимости от состава символов.”
Т.е. названия таблиц и полей MS SQL в поле “Имя в источнике данных” нужно указать в одинарных кавычках.
Дату нужно форматировать перед добавлением, например, так:
Формат(ТекущаяДата(), "ДФ=’yyyy-MM-dd HH:mm:ss’")
"[FreeTDS][SQL Server]Conversion failed when converting date and/or time from character string."
Для добавления новых записей поле идентификатора с автоинкременом нужно установить в “Только чтение”, иначе будет:
"[FreeTDS][SQL Server]Cannot insert explicit value for identity column in table ‘t_logs’ when IDENTITY_INSERT is set to OFF."
При добавлении таблицы с помощью помощника, поля с внешними ключами (foreign_key) будут по-умолчанию устанавливаться в тип объекта, а не Число. Мне это небыло нужно, поэтому вручную менял тип.
Нужно быть внимательным с поиском записи с помощью функции НайтиПоПолю(). Значение должно совпадать с типом поля в таблице. Например, строку перегонять в число:
По поводу поиска вхождений записей в список значений в запросе:
table.id В(&СписокЗаявки) – не прокатит (СписокЗаявки – это СписокЗначений со значениями – строками, а id – число)
table.id В("+СтрокаЗаявки+") – прокатит (СтрокаЗаявки – строка со значениями через запятую)
Сейчас пишу и не понимаю почему не попробовал значения списка СписокЗаявки привести к численному типу, но проверять уже лень.
Чтобы не выводить таблицы в командный интерфейс раздела (подсистемы) в свойствах таблицы нужно убрать галку “Использовать стандартные команды“.
Что не получилось
Не получилось сделать поля выбора (а-ля combobox) с выпадающим списком значений из ВИД. Ставишь реквизиту формы тип таблицы ВИД, переносишь его на форму. Все ок, элемент (контрол) создается, но не отображается. Заморачиваться не стал, вводил вручную id-шники записи, этого было достаточно.
Выполнить хранимую процедуру. До этого хранимыми процедурами в БД не пользовался, хотя хорошая вещь.
Но как ни крутил я этот функционал в конфигураторе, ничего не вышло (а тут и тут работало).
Какие были ошибки…:
1)
2)
1) FuncName
2) [FreeTDS][SQL Server]Invalid column name ‘FuncName’.
1) dbo.FuncName
2) [FreeTDS][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FuncName", or the name is ambiguous.
1) SELECT
2) [FreeTDS][SQL Server]Incorrect syntax near the keyword ‘SELECT’.
1) EXECUTE
2) [FreeTDS][SQL Server]Incorrect syntax near the keyword ‘EXECUTE’.
1) * from dbo.FuncName()
2) Обращение к процедуре объекта как к функции (FuncName)
Так же не решился вопрос с кириллицей. А точнее, при создании записей из 1С в MS SQL текст на русском языке превращался в кракозябры, например:
" 0:@KB85 70O2:8 87 1!, >
Параметры сортировки базы: Cyrillic_General_CI_AS.
Что пытался сделать:
1) настроить FreeTDS, всунув в его конфиг параметр charset в секцию [global]:
# nano /etc/freetds/freetds.conf
client charset = UTF8
2) добавить параметр charset в строку подключения:
"Driver=
Не получилось. Еще есть вариант 3:
3) сменить API ODBC
Но придется делать все заново. Поэтому, ограничился записью текста на инглише.
Нужно больше прав.
При нехватке прав при выполнении запроса к таблицам ВИД будет ошибка:
Ошибка при вызове метода контекста (Выполнить): Недостаточно прав для работы с таблицей "ВнешнийИсточникДанных.БДTickets.Таблица.
Нужно добавить права:
На ВИД: Использование
На таблицы в запросе: Чтение
Для выполнения функции .НайтиПоПолю() нужно добавить права (иначе “Нарушение прав доступа!”):
На ВИД: Использование
На таблицы в запросе: Чтение, Изменение
Для функции .СоздатьОбъект() соответсвенно: Использование, Чтение и Добавление.
Кстати, стандартная роль “Запуск внешнего соединения” не понадобилась, для меня осталось загадкой ее назначение (разобраться не пытался).
Цель данной статьи – показать принципы обмена данными между базой 1С и внешним источником данных на конкретном примере.
Причем тип внешнего источника данных здесь не так уж и важен. Источником могут выступать базы данных, работающие под управлением MS SQL Server или других СУБД, таблиц Exсel, а также других источников, к которым можно подключиться с помощью строки соединения ODBC.
Задачи синхронизации и обмена данными с внешней БД возникают практически на любом предприятии, это может быть:
- Загрузка данных о заказах и online-оплатах с сайта
- Загрузка данных из внешней системы (произведенная продукция, НСИ, бюджеты)
- Обмен данными с внешней системой – передача данных о заказах.
- Формирование отчетности, основанной на данных внешнего источника или на данных различных источников (например, базы 1С и базы SQL Server).
В этой статье мы рассмотрим возможность хранения данных внешней СУБД в объекте «1С:Предприятие 8». Например, к справочнику можно добавить реквизит, который будет ссылаться на таблицу внешнего источника. Таким образом данные внешнего источника становятся частью информационной базы 1С.
Кроме того, мы рассмотрим синхронизацию (двухсторонний обмен), основанную на совместном хранении данных из разных источников в базе 1С. То есть в базе 1С будут храниться настройки сопоставления данных базы 1С и таблицы внешнего источника для последующей загрузки по нашим правилам.
Штатные механизмы платформы «1С:Предприятие 8»
В версии платформы 8.2.14 появился объект метаданных Внешние источники данных.
У этого объекта существует множество применений:
- Различные интеграционные решения, основанные на хранении в реквизитах объектов 1С ссылки на таблицу из внешнего источника данных
- Получение информации из таблиц внешних источников данных
- Получение аналитических данных из внешних источников данных по технологии OLAP (доступно с версии платформы 8.3.5)
- Выполнение функций и хранимых процедур внешнего источника данных (доступно с версии платформы 8.3.4)
- Запись во внешние источники данных (доступно с версии платформы 8.3.5)
- Использование в отчетах на СКД, причем для каждого набора данных может быть указан свой источник. Например, один набор может получать данные из базы 1С, а другой из базы MS SQL Server, затем эти наборы соединяются и выводятся в один отчет
- Использование в качестве источника данных для динамических списков.
Ранее задачи по получению информации из внешних источников данных решались с помощью подключения через COM-соединение. Для простого переноса данных (например, из таблицы Excel) он и сейчас используется, так как в этом случае не нужно вносить изменения в конфигурацию и можно использовать этот механизм во внешних обработках или отчетах.
Но для других, более сложных, задач по интеграции рекомендуется использовать внешние источники данных. Разработчики платформы реализовали удобный функционал для быстрой интеграции 1С с другими СУБД.
Хотя в некоторых случаях, например, при удаленном подключении к внешней информационной базе (это может быть интернет-магазин, СУБД на MySQL), лучше использовать web-сервисы или http-сервисы. При их использовании нет прямого доступа к базе, а только предоставляются нужные сервисы, что значительно лучше в плане безопасности.
Другое преимущество веб-сервисов – работа в модели клиент-сервер, когда клиент посылает запрос, а сервер мгновенно его обрабатывает и передает результат обратно.
Решаемая задача
Рассматриваемая задача является учебной и внешний источник данных в ней используется как инструмент для синхронизации и загрузки данных в базу 1С.
Задача будет решаться в конфигурации – Демо «Управляемое приложение» 8.3, а в качестве внешнего источника данных будет использоваться учебная база MS SQL Server – «AdventureWorks».
Если кто-то захочет воспроизвести данный пример на своем компьютере, то конфигурацию и учебную базу можно легко найти в интернете. Пробную версию MS SQL Server (Evaluation) можно установить бесплатно (на 180 дней) или использовать бесплатную версию Express.
Суть решаемой задачи в том, чтобы перенести данные по товарам из учебной базы SQL Server в базу 1С, причем каждый товар должен быть помещен в определенную группу в зависимости от настроек в базе 1С.
Рассмотрим структуру таблицы MS SQL Server, а также связи между ними. В базе «AdventureWorks» используются таблицы Production.Product, Production.ProductSubcategory и Production.ProductCategory – это товары и их классификация по категориям и субкатегориям.
Связи между таблицами приведены ниже на схеме:
Содержимое связанных таблиц можно посмотреть с помощью запроса:
Результат выполнения представлен на скриншоте:
Нам нужно для каждой строки таблицы создать в базе 1С элемент справочника «Товары» с наименованием из колонки «Product». Значение поля «SubCategory» мы будем хранить в новом реквизите со ссылкой на таблицу внешнего источника данных, а по значению колонки «Category» помещать созданные элементы в определенную группу.
Таблица «ProductCategory» хранит категории товаров, от которых и зависит, в какую группу мы будем записывать товар. Но зависимость здесь не прямая: группа из «ProductCategory» сопоставляется с существующей группой из справочника «Товары» в регистре сведений.
Например, таблица «ProductCategory» содержит следующие значения:
В 1С заполнен регистр сведений:
Тогда строки из таблицы «Product» с категорией «Bikes» попадают при загрузке в уже существующую в справочнике «Товары» группу «Велосипеды», «Clothing» – в группу «Одежда» и т.д.
Подключение к базе MS SQL Server
Сейчас, когда определена общая концепция решения задачи, можно переходить к ее реализации. В первую очередь нужно подключиться к внешнему источнику данных и настроить его таблицы.
Настроить подключение к внешнему источнику данных в 1С достаточно просто, хотя, конечно, и здесь есть свои особенности.
После добавления внешнего источника данных в конфигураторе (в дереве объектов раздел «Внешние источники данных»), нужно также добавить таблицы, с которыми предстоит работать. Сделать это очень удобно с помощью конструктора, который появляется при добавлении таблицы. С помощью конструктора можно настроить строку подключения, указать логин и пароль для подключения к СУБД.
Для MS SQL Server (при стандартной аутентификации) строка подключения выглядит так:
«DRIVER=
В данном случае ServerName, UserName, Psw, BaseName – это имя сервера, логин, пароль и имя базы данных, к которой подключаемся. Если аутентификация выполняется средствами ОС, то строка подключения будет выглядеть иначе:
Логин и пароль здесь не используются, но включен параметр Trusted_Connection. Для получения строки подключения к другим источникам данных (Excel, Access, другие СУБД) можно воспользоваться сайтом https://www.connectionstrings.com (на английском языке).
Если подключение проходит успешно, то на следующем шаге отображается конструктор таблиц с перечнем таблиц и полей подключенного источника данных.
Здесь следует отметить таблицы, которые нам нужны, а также обязательно проверить правильность определения системой ключевых полей (в данном случае оно должно совпадать с полем первичного ключа таблицы MS SQL Server).
Если не указать поле ключа, то данная таблица считается необъектными данными, т.е. таблицей, в которой нельзя однозначно идентифицировать строку по какому-либо полю. В нашем случае каждая строка таблицы идентифицируется первичным ключом в SQL Server, поэтому это будут объектные данные, что и нужно обязательно указать.
Кроме того, можно указать поле представления, если нужно, чтобы система выводила это поле в качестве представления ключевого поля. В нашем примере поле Name будет отображаться в форме выбора таблицы внешнего источника данных и в регистре сведений, поэтому нужно обязательно указать его в качестве представления.
В общем, можно сказать, что ключевое поле и представление таблицы внешнего источника данных аналогичны ссылке и представлению объектов 1С, например справочника.
По условию задачи для хранения настроек сопоставления используется регистр сведений с измерением «Группа товаров» (тип – справочник «Товары») и ресурсом «Категория» (тип – ссылка на таблицу категорий внешнего источника данных). Значение свойства «Выбор групп и элементов» для измерения «Группа товаров» установлено в «Группы», т.к. выбор элементов нам не нужен.
Для хранения подкатегории из таблицы «ProductSubcategory» базы SQL Server в справочнике «Товары», мы добавим реквизит «ПодкатегорияВИД», который будет ссылаться на данные внешнего источника данных (тип – ВнешнийИсточникДанныхТаблицаСсылка.Демо.Production_ProductSubcategory).
После того как добавлены необходимые объекты в конфигурацию и выполнены настройки подключения к базе MS SQLServer, казалось бы, можно двигаться дальше, однако это еще не все.
Настроить подключение к внешнему источнику данных в режиме конфигуратора недостаточно для работы. Также нужно указать настройки подключения в пользовательском режиме и подключиться к внешнему источнику данных в режиме «1С:Предприятие». Вызывается диалог подключения через команду «Все функции».
Настройки подключения из конфигуратора сюда не переносятся, нужно указывать все по-новому.
Этот способ никак нельзя назвать удобным, т.к. при каждом запуске «1С:Предприятие 8» приходится выполнять подключение.
Как вариант, можно хранить настройки подключения в регистре сведений и подключаться к внешнему источнику данных программно при запуске конфигурации.
В данном случае в конфигурацию был добавлен общий модуль «Интеграция» и регистр сведений для хранения настроек подключения. Регистр сведений состоит из четырех ресурсов: «Сервер», «Логин», «Пароль», «ИмяВнешнейБД» и «АутентификацияОС». В модуль «Интеграция» включены функция и процедура для подключения:
В модуль управляемого приложения, для события «ПриНачалеРаботыСистемы» был добавлен код:
Теперь при запуске приложения подключение выполняется автоматически. То есть сейчас можно настроить соответствия в регистрах сведений и приступить непосредственно к написанию программного кода по загрузке данных.
Загрузка данных из внешнего источника данных
Одно из основных ограничений внешних источников данных, на мой взгляд, это то, что нельзя в рамках одного запроса использовать разные источники данных. То есть нельзя использовать в одном запросе обращение к таблицам внешних источников данных и таблицам информационной базы 1С.
В запросах можно использовать временные таблицы, но с ограничениями, а в контексте данной задачи одно из ограничений очень существенное. Суть его в том, что временная таблица создается в базе данных внешнего источника, а значит, использовать ее совместно с таблицами базы 1С нельзя.
Здесь я сделаю небольшое отступление, чтобы осветить этот момент более подробно.
Временную таблицу нельзя создать в базе 1С, т.е. конструкция запроса ПОМЕСТИТЬ ВТ работать не будет. Создать временную таблицу можно только в СУБД внешнего источника, следующей командой:
Стоит отметить, что создавать временные таблицы можно только в СУБД, где поддерживается эта возможность, например в MS SQL Server.
Возвращаясь к задаче, вспоминаем, что, для того чтобы сопоставить данные из базы 1С и MS SQL Server, нам нужно обойти ограничение при использовании соединения таблиц разных источников, вопрос – как это сделать?
Было принято решение использовать выгрузку таблицы внешнего источника данных в таблицу значений с последующей загрузкой и помещением ее во временную таблицу (базы 1С). В данном случае временная таблица создается в базе 1С и ничто не мешает ее использовать в соединениях с другими таблицами. Создаем нужную нам таблицу значений, используя следующий код:
Здесь выполняется запрос, соединяющий таблицы внешнего источника данных, причем запрос возвращает не только наименование товара, но и его подкатегорию и категорию. Затем результат запроса выгружается в таблицу значений. Для демонстрации выгружаются только первые 100 строк.
Далее справочник «Товары» соединяется с полученной таблицей значений с помощью следующего запроса:
Данный запрос возвращает наименование и подкатегорию товара из внешнего источника данных, а также группу справочника «Товары», в которую нам нужно добавить создаваемый элемент. Группа товара была определена исходя из настройки соответствий в регистре сведений «НастройкаСоответствийСВнешнимИсточникомДанных».
Также запрос осуществляет поиск по наименованию товара, чтобы определить, существует элемент справочника «Товары» с таким наименованием или нет. Если элемент найден (не равен пустой ссылке), то мы не будем создавать дубль в справочнике «Товары», а модифицируем найденный элемент справочника.
После завершения загрузки мы получаем следующий результат:
Товары из таблицы SQL Server «Production.Product» были успешно перенесены в справочник «Товары» и помещены в указанную нами в регистре сведений группу. При этом реквизит «Подкатегория» был заполнен ссылкой на строку таблицы подкатегорий внешнего источника данных «Production_ProductSubcategory».
Подведение итогов
В данной учебной задаче мы рассмотрели перенос данных из базы MS SQL Server, а также реализацию синхронизации (сопоставления) информации внешнего источника с данными базы 1С.
Этот способ можно применять и в других случаях, когда нужно выполнять обмен и синхронизацию с внешними источниками данных. Например, при загрузке элементов справочника номенклатуры или любого другого, где сложно настроить соответствие по какому-либо полю.
При этом изначально соответствия определяются автоматически, например по наименованию. Но на последнем этапе загрузки пользователь может вмешаться и переопределить эти соответствия самостоятельно в окне обработки. Например, указав, что номенклатура с похожим наименованием – это одна и та же номенклатура, а значит, нужно обновить информацию по ней, а не создавать дубли.
Альтернативные инструменты
Подводя итоги, я бы хотел еще сравнить данный способ с альтернативным, например с использованием «Конвертации 2.0». Там также можно работать с данными информационных баз других типов (не 1С), подключаясь к ней с помощью COM-соединения и используя произвольный алгоритм в правиле выгрузки данных. У этого способа есть свои плюсы и минусы.
К плюсам можно отнести то, что данные также будут загружаться по нажатию одной кнопки, при этом не нужно вносить изменения в конфигурацию, что может быть критично, если конфигурация находится на полной поддержке. С другой стороны, в конфигурации не изменяются существующие объекты, а только добавляются новые, поэтому конфликтов при типовом обновлении не будет.
Минус здесь в настройке синхронизации, т.к. во многих случаях нельзя однозначно сопоставить значения из базы 1С и внешнего источника данных, например по коду. Значит, в данном случае придется делать сопоставление по наименованию, что, конечно, не всегда корректно и может приводить к дублям.
Ограничения внешних источников данных
К сожалению, ничто не бывает идеальным, поэтому и у внешних источников данных есть свои ограничения в использовании. Привожу их в виде списка:
- Поддерживаются только управляемые формы (для объектов внешних источников данных можно создавать только управляемые формы).
- Ограничение при использовании временных таблиц в запросах (временные таблицы создаются в базе данных внешнего источника, и если СУБД не поддерживает эту возможность, будет вызвано исключение).
- В одном запросе нельзя использовать данные из разных источников, например из базы 1С и базы сторонней СУБД.
- Так как временные таблицы внешних источников данных создаются не на стороне 1С, то совместно с данными базы 1С в одном запросе их использовать нельзя.
- Запись во внешние источники данных доступна только с версии 8.3.5
- Получение аналитических данных по технологии OLAP также доступно только с версии 8.3.5
- Механизм внешних источников данных требует доработки конфигурации, а значит, не получится его использовать, если конфигурация находится на полной поддержке.
Об авторе
Автор статьи – Алексей Васильев
Внешние источники данных 1С — сравнительно новый объект метаданных 1С 8.3 и 8.2, с помощью которого возможно подключение к 1С внешних источников данных: таблиц SQL, Excel, Access, FoxPro(dbf), другой базы 1С, Oracle, Paradox (db), — и даже чтение из простых файлов txt/csv.
Это даёт множество возможностей для интеграции 1С с другими системами. Рассмотрим подробнее.
Настройка внешних источников данных в 1С 8
Настройка внешних источников индивидуальна для каждого вида системы. Но, как правило, общая часть у настройки одинаковая — это установка строки подключения:
Получите 267 видеоуроков по 1С бесплатно:
Если строка подключения задана верно, система предложит выбрать нужную таблицу из базы данных. В итоге мы получим готовую таблицу, в которой можно указать поле ключа (уникальное поле) и поле представления (как запись будет отражаться в 1С):
Использование внешних источников данных в 1С 8.3
Внешние источники данных в 1С можно использовать, как и другие таблицы базы данных. Платформа автоматически генерирует для них форму, если она не задана. В запросах также допустимо использование данных из внешних источников.
Достаточно важная возможность использования внешних источников данных — использование ссылки на них в реквизитах других объектов:
Однако самая важная возможность этого механизма — использование данных в простом запросе 1С.
Если Вы начинаете изучать 1С программирование, рекомендуем наш бесплатный курс (не забудьте подписаться на YouTube — регулярно выходят новые видео):
К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.
