Как взаимодействовать с базой данных MS SQL из 1С:Предприятия и читать из нее данные?

← На главную

В данном руководстве мы подробно рассмотрим, как можно организовать чтение данных из внешней базы данных Microsoft SQL Server в конфигурацию 1С:Предприятие — для этого подойдет готовая обработка загрузки данных из внешних баз. Эта задача часто возникает при необходимости интеграции различных систем или переноса информации. Мы проанализируем основные подходы: использование COM-объекта ADODB.Connection для выполнения прямых запросов и применение штатного механизма платформы "Внешние источники данных".

Итак, перед нами стоит задача прочитать данные из конкретной таблицы dbo.SuperHeroTable, расположенной в базе данных SuperHero на сервере DESKTOP-B075V4Q\SQLSQUIRREL. Подключение осуществляется пользователем DESKTOP-B075V4Q\igorb без явного пароля, что может указывать на использование аутентификации Windows.

1. Чтение данных через COM-объект ADODB.Connection

Этот метод является одним из самых гибких и не требует изменения структуры конфигурации 1С. Мы будем использовать COM-объекты из библиотеки ADODB (ActiveX Data Objects Database), которые предоставляют универсальный интерфейс для работы с различными базами данных.

Шаг 1: Инициализация переменных и попытка подключения

Прежде всего, нам нужно инициализировать необходимые COM-объекты: ADODB.Connection для установки соединения, ADODB.Command для выполнения команд и ADODB.RecordSet для получения результатов.

Рассмотрим пример кода, который попытался использовать автор вопроса (сообщение 6):


&НаКлиенте
Процедура ВыполнитьОбработку()
    //Инициализация переменных
    ИмяСервераSQL = "DESKTOP-B075V4Q\SQLSQUIRREL";
    ПользовательSQL = "DESKTOP-B075V4Q\igorb";
    ПарольSQL = ""; // Автор указал пустой пароль
    БазаДанныхSQL = "SuperHero";
    ТаблицаSQL = "dbo.SuperHeroTable";

    //Подключение к SQL-серверу
    Попытка
        Соединение = Новый COMОбъект("ADODB.Connection");
        Команда = Новый COMОбъект("ADODB.Command");
        Выборка = Новый COMОбъект("ADODB.RecordSet");

        Соединение.ConnectionString = "driver={SQL Server};" +
                                     "server="+ИмяСервераSQL+";"+
                                     "uid="+ПользовательSQL+";"+
                                     "database="+БазаДанныхSQL+";";
        Соединение.ConnectionTimeout = 30;
        Соединение.CommandTimeout = 600;

        //Открытие соединение
        Соединение.Open;
        Команда.ActiveConnection = Соединение;
        Сообщить("Успешное подключение!");
    Исключение
        Сообщить(ОписаниеОшибки);
        Возврат;
    КонецПопытки;
КонецПроцедуры

При попытке выполнить этот код, скорее всего, возникает ошибка подключения, как и сообщил автор. Давайте выясним, в чем причина и как ее исправить.

Шаг 2: Корректировка строки подключения и аутентификация

Ключевым моментом для успешного подключения является правильно составленная строка подключения (Connection String). В данном случае, автор указал, что при подключении у него включена авторизация операционной системы (сообщение 9). Это означает, что SQL Server должен аутентифицировать пользователя, под которым запущена 1С (или пользователь DESKTOP-B075V4Q\igorb), через Windows-аутентификацию, а не через логин/пароль SQL Server.

Если используется Windows-аутентификация (интегрированная безопасность), мы не должны передавать uid (User ID) и pwd (Password) в строке подключения. Вместо этого необходимо использовать параметр Trusted_Connection=Yes. Это было правильно подмечено в сообщении 11.

Корректная строка подключения для Windows-аутентификации должна выглядеть так:


Соединение.ConnectionString = "Driver={SQL Server};" + 
                             "Server=" + ИмяСервераSQL + ";" + 
                             "Database=" + БазаДанныхSQL + ";" +
                             "Trusted_Connection=Yes;";

Обратите внимание, что мы убрали параметры uid и pwd и добавили Trusted_Connection=Yes. Если пользователь, под которым запущено приложение 1С, имеет соответствующие права на SQL Server, подключение будет успешным.

Если бы использовалась SQL Server аутентификация (то есть вход в SQL Server по логину и паролю, которые созданы непосредственно в SQL Server), то строка подключения из сообщения 6 была бы неполной. В этом случае, помимо uid, необходимо было бы явно передать и pwd (Password), даже если он пустой, как было предложено в сообщении 7:


Соединение.ConnectionString = "driver={SQL Server};" + 
                             "server=" + ИмяСервераSQL + ";" + 
                             "uid=" + ПользовательSQL + ";" + 
                             "pwd=" + ПарольSQL + ";" + // Добавлен параметр pwd
                             "database=" + БазаДанныхSQL + ";";

Также стоит отметить, что драйвер {SQL Server} является устаревшим ODBC-драйвером. Для современных систем рекомендуется использовать более новый драйвер, например, {ODBC Driver 17 for SQL Server}, если он установлен и настроен на сервере 1С. Проверьте, какой драйвер установлен в вашей системе (через "Администрирование источников данных ODBC").

Еще один важный момент: код, работающий с COM-объектами для доступа к базам данных, рекомендуется выполнять на сервере 1С (&НаСервере), а не на клиенте (&НаКлиенте). Это обусловлено особенностями взаимодействия клиента 1С с операционной системой и доступом к внешним компонентам.

Шаг 3: Выполнение запроса и получение данных

После успешного подключения мы можем выполнить SQL-запрос и получить данные. Для этого мы можем использовать метод Execute объекта Соединение, как было предложено в сообщении 2, или объект Command для более сложных запросов с параметрами.

Пример получения всех данных из таблицы dbo.SuperHeroTable с учетом исправленной строки подключения и выполнения на сервере:


&НаСервере
Процедура ПрочитатьДанныеИзSQL()
    ИмяСервераSQL = "DESKTOP-B075V4Q\SQLSQUIRREL";
    БазаДанныхSQL = "SuperHero";
    ТаблицаSQL = "dbo.SuperHeroTable"; // Обратите внимание на схему dbo.

    Попытка
        Соединение = Новый COMОбъект("ADODB.Connection");
        
        // Используем Windows-аутентификацию, как заявлено автором
        Соединение.ConnectionString = "Driver={SQL Server};" + 
                                     "Server=" + ИмяСервераSQL + ";" + 
                                     "Database=" + БазаДанныхSQL + ";" +
                                     "Trusted_Connection=Yes;";
        
        Соединение.ConnectionTimeout = 30; // Таймаут подключения в секундах
        Соединение.Open();
        Сообщить("Успешное подключение к MS SQL!");

        // Выполнение запроса
        // Используем SQL-запрос для выборки всех данных из указанной таблицы
        Выборка = Соединение.Execute("SELECT * FROM " + ТаблицаSQL);

        Если Не Выборка.EOF Тогда
            // Метод GetRows() возвращает массив массивов.
            // Первый индекс - это номер колонки (поля), второй - номер строки.
            Данные = Выборка.GetRows(); 
            
            // Количество колонок (полей)
            Перем КоличествоКолонок;
            Если UBound(Данные) >= 0 Тогда
                КоличествоКолонок = UBound(Данные) + 1;
            Иначе
                КоличествоКолонок = 0;
            КонецЕсли;

            // Количество строк
            Перем КоличествоСтрок;
            Если КоличествоКолонок > 0 И UBound(Данные, 2) >= 0 Тогда
                КоличествоСтрок = UBound(Данные, 2) + 1;
            Иначе
                КоличествоСтрок = 0;
            КонецЕсли;

            Сообщить("Данные успешно прочитаны. Количество строк: " + КоличествоСтрок);

            // Пример вывода первых 5 строк в Сообщения
            Для ИндексСтроки = 0 По Мин(КоличествоСтрок - 1, 4) Цикл // Выводим первые 5 строк
                Сообщить("Строка " + (ИндексСтроки + 1) + ":");
                Для ИндексКолонки = 0 По КоличествоКолонок - 1 Цикл
                    Сообщить("    " + Выборка.Fields.Item(ИндексКолонки).Name + ": " + Строка(Данные[ИндексКолонки][ИндексСтроки]));
                КонецЦикла;
            КонецЦикла;

            // Если нам необходимо сохранить данные в ТаблицуЗначений 1С, потребуется дополнительный код:
            // ТаблицаДанных1С = Новый ТаблицаЗначений;
            // 
            // // Добавляем колонки в ТаблицуЗначений 1С, используя метаданные RecordSet
            // Для Каждого Колонка ИЗ Выборка.Fields Цикл
            //    ТаблицаДанных1С.Колонки.Добавить(Колонка.Name); // Можно указать тип, если требуется явное преобразование
            // КонецЦикла;
            // 
            // // Заполняем ТаблицуЗначений 1С
            // Для ИндексСтроки = 0 По КоличествоСтрок - 1 Цикл
            //    НоваяСтрока = ТаблицаДанных1С.Добавить();
            //    Для ИндексКолонки = 0 По КоличествоКолонок - 1 Цикл
            //        НоваяСтрока[Выборка.Fields.Item(ИндексКолонки).Name] = Данные[ИндексКолонки][ИндексСтроки];
            //    КонецЦикла;
            // КонецЦикла;
            // Сообщить("Данные выгружены в ТаблицуЗначений 1С. Количество строк: " + ТаблицаДанных1С.Количество());

        Иначе
            Сообщить("Таблица " + ТаблицаSQL + " не содержит данных.");
        КонецЕсли;

        // Закрываем RecordSet и Connection
        Выборка.Close();
        Соединение.Close();

    Исключение
        Сообщить("Ошибка при работе с MS SQL: " + ОписаниеОшибки);
    КонецПопытки;
КонецПроцедуры

Метод GetRows() объекта ADODB.Recordset возвращает данные в виде двухмерного массива Variant. Первый индекс этого массива соответствует номеру колонки (поля), а второй — номеру строки. Для удобной работы с этими данными в 1С часто требуется преобразование в объект ТаблицаЗначений, пример которого приведен в закомментированном виде.

Преимущества и недостатки прямых SQL-запросов через ADO

Преимущества:

Гибкость: Позволяет использовать всю мощь T-SQL для сложных выборок, агрегации и фильтрации данных, а также выполнения хранимых процедур и команд DML (Data Manipulation Language).
Отсутствие изменений конфигурации: Не требуется модифицировать конфигурацию 1С, что удобно для внешних обработок и отчетов, не предназначенных для постоянной интеграции.
Прямой доступ: Позволяет выполнять операции, которые могут быть затруднены или невозможны средствами 1С.

Недостатки:

Сложность: Требует хороших знаний T-SQL и внутренней структуры внешней базы данных, а также понимания работы с COM-объектами.
Зависимость от структуры: Любые изменения в схеме внешней БД могут привести к неработоспособности кода 1С, требуя его ручной доработки.
Безопасность: Необходимо тщательно управлять правами пользователя SQL, под которым происходит подключение, чтобы избежать несанкционированного доступа или вредоносных операций.
Производительность: При передаче больших объемов данных могут возникнуть задержки из-за сетевых операций и преобразования типов данных.
Отсутствие кроссплатформенности: Использование COM-объектов ограничено ОС Windows и не работает на Linux-клиентах 1С.

2. Использование механизма "Внешние источники данных"

Начиная с платформы 1С:Предприятие 8.2.14, появился более интегрированный и, во многих случаях, предпочтительный способ работы с внешними базами данных — механизм "Внешние источники данных" (сообщение 3).

Что такое "Внешние источники данных"?

Этот механизм позволяет "проецировать" таблицы из внешней СУБД (например, MS SQL) непосредственно в конфигурацию 1С. После настройки такие внешние таблицы становятся доступны как обычные объекты конфигурации, и к ним можно обращаться с помощью стандартных средств языка запросов 1С, а также использовать в отчетах и обработках, как к данным самой 1С.

Пошаговая настройка (обзорно):

Создание объекта "Внешний источник данных": Откройте конфигуратор 1С. В дереве объектов конфигурации найдите ветку "Внешние источники данных" и добавьте новый элемент. Дайте ему осмысленное имя, например, "МояВнешняяБазаSQL".
Настройка подключения: В свойствах созданного внешнего источника данных укажите тип СУБД (выберите "MS SQL Server"), затем заполните параметры подключения:
- Сервер: Укажите имя или IP-адрес вашего SQL-сервера (например, DESKTOP-B075V4Q\SQLSQUIRREL).
- База данных: Укажите имя внешней базы данных (например, SuperHero).
- Аутентификация: Выберите "Аутентификация Windows", если вы используете её, или "Аутентификация SQL Server", если у вас есть логин и пароль SQL-пользователя. Заполните соответствующие поля.
Нажмите кнопку "Проверить подключение", чтобы убедиться в корректности настроек.
Добавление таблиц из внешнего источника: Для созданного внешнего источника данных, перейдите на закладку "Таблицы" или используйте контекстное меню для добавления новых таблиц. Выберите те таблицы из внешней базы, которые вы хотите "увидеть" в 1С (например, SuperHeroTable). Платформа автоматически создаст объекты конфигурации, соответствующие этим таблицам, с соответствующими полями. Вы можете настроить имена полей и их типы в 1С.

Работа с данными: После обновления конфигурации базы данных, вы сможете обращаться к данным этих внешних таблиц с помощью языка запросов 1С. Например, чтобы получить все записи из SuperHeroTable:


Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
               |    ВнешняяСуперГеройТаблица.*
               |ИЗ
               |    ВнешнийИсточникДанных.МояВнешняяБазаSQL.SuperHeroTable КАК ВнешняяСуперГеройТаблица";
        
Результат = Запрос.Выполнить();
Выборка = Результат.Выбрать();
        
Пока Выборка.Следующий() Цикл
    // Обработка данных
    // Например, если поле в SQL таблице называлось 'HeroName':
    // Сообщить("Имя героя: " + Выборка.HeroName); 
    Сообщить("Данные строки: " + Выборка.СсылкаНаОбъект); // Пример вывода
КонецЦикла;

Вы также можете использовать Систему Компоновки Данных (СКД) для построения отчетов на основе внешних данных, что значительно упрощает разработку аналитических отчетов.

Преимущества и недостатки "Внешних источников данных"